Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

psycopg2 raises a syntax error because of how the query is built

Errorpsycopg2.errors.SyntaxError: syntax error at or near ...

Short answer

In most psycopg2 syntax errors the SQL that reaches PostgreSQL is not the SQL you meant. Pass values as the second argument with %s placeholders, as a sequence. Never quote the placeholder, never merge values with % or +, and use psycopg2.sql for table and column names.

  • Applies to psycopg2 2.9, PostgreSQL 16, Python 3.12
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the exact SQL psycopg2 will send, with the values merged in. The error position then points into real text.

    print(cur.mogrify("SELECT * FROM users WHERE id = %s", (user_id,)))
  2. 2Match what you see, then jump to its fix

    1. 1Error near the value, or TypeError about argumentsA single value is not passed as a sequenceFix
    2. 2SQL shows doubled quotes such as ''bar''The placeholder is quoted, or values are merged into the stringFix
    3. 3Error at or near a quoted name like 'users'A table or column name was passed as a parameterFix
    4. 4Error with LIKE patterns, or with IN ()A literal % in the SQL, or an empty IN listFix

What does the error look like?

  • psycopg2.errors.SyntaxError: syntax error at or near ...

Cause 1 of 4

A single value is not passed as a sequence

1Check

The second argument to execute() must be a sequence. A bare string or a parenthesised string is not one.

cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))

2Fix

Add the trailing comma so Python creates a one-item tuple.

cur.execute("INSERT INTO foo VALUES (%s)", ("bar",))

3Verify

The insert runs.

conn.commit()

Confirmed by psycopg.org

Did this fix it?

Cause 2 of 4

The placeholder is quoted, or values are merged into the string

1Check

Look for '%s' in quotes, or for % and + used to build the query text.

cur.execute("SELECT * FROM t WHERE name = '%s'", (name,))

2Fix

Write the placeholder bare; psycopg2 adds the quotes. Pass the values as the second argument.

cur.execute("SELECT * FROM t WHERE name = %s", (name,))

3Verify

mogrify output shows one pair of quotes.

print(cur.mogrify("SELECT * FROM t WHERE name = %s", (name,)))

Confirmed by psycopg.org

Did this fix it?

Cause 3 of 4

A table or column name was passed as a parameter

1Check

Parameters bind values only; a name passed this way becomes a string literal.

cur.execute("SELECT * FROM %s", (table,))

2Fix

Build the identifier with psycopg2.sql and keep values as parameters.

from psycopg2 import sql
cur.execute(
    sql.SQL("SELECT * FROM {} WHERE id = %s").format(sql.Identifier(table)),
    (user_id,),
)

3Verify

The query returns rows.

print(cur.fetchall())

Confirmed by psycopg.org

Did this fix it?

Cause 4 of 4

A literal % in the SQL, or an empty IN list

1Check

When parameters are present, a literal percent sign must be written %%. An empty tuple becomes IN (), which SQL rejects.

cur.execute("SELECT * FROM t WHERE name LIKE 'a%'", (x,))

2Fix

Double the percent sign, and for lists that may be empty use = ANY(%s) with a Python list.

cur.execute("SELECT * FROM t WHERE name LIKE 'a%%' AND id = ANY(%s)", (ids,))

3Verify

The query runs for both an empty and a full list.

print(cur.rowcount)

Confirmed by psycopg.org

Did this fix it?

How do I stop it coming back?

  • Always call mogrify() while debugging a failing query.
  • Never format values into SQL text with f-strings, %, or +.
  • Keep identifiers in psycopg2.sql objects.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.