sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ":"

Question

Here's my query:

QUERY = """
        UPDATE my_table
        SET
            my_prop=:foo
        WHERE hello='world'
"""

I execute the statement like this:

sqlalchemy_engine.execute(QUERY, foo='bar')

When I do I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ":"
UPDATE my_table\n        SET\n            my_prop.foo = :foo
                                                         ^

I've never run into problems before passing values into the SQL Alchemy execute. What am I doing wrong?

Answer 1

I've never run into problems before passing values into the SQLAlchemy execute. What am I doing wrong?

You might have been using Session.execute() before, instead of Engine.execute(). The former automatically wraps SQL strings with the text() construct, or at least it behaves as such, which is the key here; it is text() that provides the DB-API driver independent handling of bound parameters/placeholders. Without it you're at the whims of your DB-API, as noted by Craig Ringer in their answer.

So in order to use :name style placeholders with an engine directly, simply wrap your query with text():

sqlalchemy_engine.execute(text(QUERY), foo='bar')

Note that an engine is the starting point of an SQLAlchemy app and everything builds on top of it, so that might be the reasoning behind one not doing the automatic wrapping, while the other does. A Session is a higher level abstraction.

Answer 2

psycopg2 doesn't support :named parameters. The Python DB-API spec allows for :named parameters with paramstyle = 'named', but psycopg2 uses:

>>> import psycopg2
>>> psycopg2.paramstyle
'pyformat'

as documented in the psycopg2 docs.

It's a pity that Python's DB-API allows a variety of parameter styles, but that's how it is. Some drivers appear to support changing paramstyle as a DB-API extension. But it doesn't look like psycopg2 is one of them:

>>> psycopg2.paramstyle = 'named'
>>> psycopg2.paramstyle
'named'
>>> cur.execute("SELECT :a, :b, :c", {'a': 1, 'b': 2, 'c': 3})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: syntax error at or near ":"
LINE 1: SELECT :a, :b, :c

>>> conn.rollback()
>>> cur.execute("SELECT %(a)s, %(b)s, %(c)s", {'a': 1, 'b': 2, 'c': 3})
>>> 

So if you've seen :param style parameters work before, it's because some outer layer (maybe SQLAlchemy) is adapting your queries for you. A quick look at the SQLAlchemy docs suggests that it may do just that.

At a guess, you're bypassing the SQLAlchemy dialect layer with your current code. But I don't really know SQLAlchemy...