Update PostgreSQL user credentials using variables in Python

Question

I'm trying to update user credentials for PostgreSQL db user using Python. I've tried referring to the following thread but that doesn't seem to solve my issue unfortunately:

How to change password of a newly created user using variable in postgresql 9.5 SP

Here's my code:

con = p.connect(database="mydB", user="abc", password="testing", host="127.0.0.1", port="5432")
cur = con.cursor()

uid = "adi"
pwd = "test6"

statement = statement = '''CREATE or REPLACE FUNCTION add_user ({}, {}) RETURNS void AS $$ EXECUTE ALTER USER ' || $1 || ' WITH PASSWORD || $2||'''.format(uid,pwd)

cur.execute( statement)
cur.execute('''COMMIT''')

I get the following error: ProgrammingError: syntax error at or near "'adi'"

Please help or refer me to a thread with a better solution. Thanks in advance, everyone!

error image

Your traceback differs from the code. Please post the code that leads to that error. — Klaus D.
– Klaus D., Commented May 16, 2020 at 3:29
@KlausD., added the original code - edited my question. Thank you! — Aditya J
– Aditya J, Commented May 16, 2020 at 3:33
@KlausD. I have added my answer here. I was able to solve this. — Aditya J
– Aditya J, Commented May 16, 2020 at 5:07

Aditya J · Accepted Answer · 2020-05-16 04:59:40Z

2

I was able to solve this successfully.

import psycopg2 as p
con = p.connect(database="mydB", user="adi", password="xxxx", host="127.0.0.1", port="5432")
cur = con.cursor()

uid = "adi"
pwd = "test"

statement = '''ALTER USER {} WITH PASSWORD %s '''.format(uid)

cur.execute(statement, [pwd])
cur.execute('''COMMIT''')

edited May 16, 2020 at 4:59

answered May 16, 2020 at 4:11

Aditya J

316 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Panagiotis Simakis · Accepted Answer · 2020-05-16 07:44:39Z

0

Try to give the username and password in the actual statement and try executing.

First try removing the extra quotation before ALTER USER and execute. If doesn't work try this:

statement = '''CREATE or REPLACE FUNCTION add_user ({}, {}) RETURNS void AS $$ EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD || $2||'''.format(uid,pwd)

cur.execute(statement)

edited May 16, 2020 at 7:44

Panagiotis Simakis

1,2671 gold badge20 silver badges48 bronze badges

answered May 16, 2020 at 3:12

Aditya Katari

646 bronze badges

1 Comment

Aditya J Over a year ago

Tried the following: statement = '''CREATE or REPLACE FUNCTION add_user ({}, {}) RETURNS void AS $$ EXECUTE ALTER USER ' || $1 || ' WITH PASSWORD || $2||'''.format(uid,pwd) I get the error: ProgrammingError: unterminated dollar-quoted string at or near "$$ EXECUTE ALTER USER ' || $1 || ' WITH PASSWORD || $2||"

Collectives™ on Stack Overflow

Update PostgreSQL user credentials using variables in Python

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related