CODEWITHSUNDEEP
postgresqlpsycopg2
Becky S
Becky S

Reputation: 83

psycopg2.ProgrammingError: syntax error

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.

Upvotes: 2

Views: 1562

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

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.

Upvotes: 3

Related Questions