psycopg2.ProgrammingError: syntax error

Question

I know there a lot of similar questions to this, but I haven't found one that helped me.

I am using Python and psycopg2 to interface with a PostGresQL database. Here is my code:

conn = psycopg2.connect(dbname=db_name, user=user_name)
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
add_query = 'CREATE ROLE %s WITH CREATEDB LOGIN PASSWORD \'%s\''
add_data = (user_name, password)
cursor.execute(add_query, add_data)

I keep getting this error:

psycopg2.ProgrammingError: syntax error at or near "'foo'" LINE 1: CREATE ROLE 'foo' WITH CREATEDB LOGIN PASSWORD ''bar''

I thought this was an issue with escaping quotes, but I have every combination of slashes and quotes that I can think of with no luck.

Clodoaldo Neto · Accepted Answer · 2015-11-18 10:40:49Z

3

AsIs

from psycopg2.extensions import AsIs

add_query = "CREATE ROLE %s WITH CREATEDB LOGIN PASSWORD %s"
add_data = (AsIs(user), password)

The role name is an identifier so no single quotes. It can be wrapped in double quotes if illegal characters are expected, but a bad idea otherwise.

The password is a string so let Psycopg adapt and escape it.

edited Nov 18, 2015 at 10:40

answered Nov 18, 2015 at 5:47

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Craig Ringer Over a year ago

Note that this will subject the role name to case folding. If you want to avoid that you need to identifier-quote it. Unfortunately psycopg2 does not offer an identifer-quoting variant of psycopg2.extensions.QuotedString so you have to write one or just manually double any double quotes.

Collectives™ on Stack Overflow

psycopg2.ProgrammingError: syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related