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?

That is not how you use parameters in psycopg2. Also what are you trying to do by setting a table name to something else? — mechanical_meat
– mechanical_meat, Commented Nov 8, 2017 at 22:26

Ilja Everilä · Accepted Answer · 2017-11-09 09:52:51Z

15

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.

edited Nov 9, 2017 at 9:52

answered Nov 9, 2017 at 9:47

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

Comments

Craig Ringer · Accepted Answer · 2017-11-09 03:52:26Z

4

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...

edited Nov 9, 2017 at 3:52

answered Nov 9, 2017 at 3:46

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

1 Comment

Ilja Everilä Over a year ago

In the end what's missing is just the text() wrapper around QUERY, which would handle the :name style in a DB-API independent fashion. Some methods such as Session.execute() actually do that automatically, but not so for Engine.execute().

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related