CODEWITHSUNDEEP
pythonpostgresqlpython-3.xsqlalchemy
JoshuaBoshi
JoshuaBoshi

Reputation: 1286

Sqlalchemy: postgresql paramstyle for engine.execute()

I am connected to postgresql with sqlalchemy. When I try this code:

e = create_engine('')
r = e.execute("select ?", 5)

it gives me: 

sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at end of input

Corresponding to http://www.sqlalchemy.org/docs/core/connections.html?highlight=engine#sqlalchemy.engine.base.Connection.execute there are different types of paramstyles... After deeper research, i found out that default paramstyle for postgresql Dialect is "pyformat".

Can somebody show me how to use this pyformat on some example? I would just like to use placeholders or named placeholders for forming up the sql statement.

I tried:

e.execute("select %s, %s;", "test", "test2")

But this is not working either.

Thank you

edit: of course, i am passing valid connection string to create_engine method :-)

Upvotes: 2

Views: 4852

Answers (2)

svalemento
svalemento

Reputation: 722

The accepted answer was correct before the release of SqlAlchemy 1.4.

In case people searching how to do that now, here's the solution:

session.connection().exec_driver_sql(
    "select %(param1)s, %(param2)s;", 
    {"param1": "test", "param2": "test2"}
)

https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.exec_driver_sql

Upvotes: 0

Lennart Regebro
Lennart Regebro

Reputation: 172309

The documentation you link to, directly links to PEP 249, which shows you the different styles.

About pyformat it says:

'pyformat' Python extended format codes, e.g. '...WHERE name=%(name)s'

So that's what you need to use.

By Googling on "DBAPI pyformat" the second link is the link to the psycopg2 documentation. After mentioning that it uses pyformat there is a "See Also" linking to a page with loads of examples.

Examples of how to use it are in your original link:

e.execute("select %s, %s;", ("param1", "test"), ("param2", "test2"))

You can also use this syntax:

e.execute("select %(param1)s, %(param2)s;", param1="test", param2="test2"))

Which is nicer.

Upvotes: 4

Related Questions