SQLAlchemy 0.7.8 raw string queries issue with params

Question

I'm using SQLAlchemy with Pyramid application and I need to use 'format' paramstyle. For example:

DBSession.execute('SELECT id FROM users WHERE email = %s;', email)

This in theory should work, but I get sqla error:

('SQL Error!', AttributeError("'list' object has no attribute 'keys'",))

even while email is string and not list. I tried using tuple but I get the same error (but instead of "list" it says "tuple").

I use pg8000 as DB driver.

How else should I supply params to the query?

Answer 1

By using the execute method of the session, the parameters handling is done by SQLAlchemy, thus you need something like:

DBSession.execute('SELECT id FROM users WHERE email = :email;', {'email': email})

However, if you use the execute method of the engine instead, then the parameters handling is done by the underlying DB-API, which is pg8000 in this case, e.g.

DBSession.bind.execute('SELECT id FROM users WHERE email = %s;', email)

Anyway, I always stick with the former, to have a consistent way to handle parameters across different drivers / databases.

Answer 2

Reading through http://pybrary.net/pg8000/dbapi.html#pg8000.dbapi.CursorWrapper.execute it looks like pg8000.dbapi.paramstyle is set to named thus it is expecting a mapping which would look like:

DBSession.execute('SELECT id FROM users WHERE email = %(email)s', {'email': email})

It appears you want to set paramstyle to pyformat which looks like the most flexible option - allowing a sequence or mapping (or maybe just to format if not required).