sqlalchemy double quoting list items

Question

I'm have a quoting issue with raw query when using "WHERE IN" statement. SQLAlchemy is adding double quotes around single quotes in a list...

Query that i'm trying to execute

sql_query = "SELECT col1, col2, col3 FROM preferences WHERE recipient IN :recipients"
preferences = sqlsession.execute(sql_query,dict(recipient=tuple(message.recipients))

message.recipients is a list like so: ["recipient1","recipient2","recipient3"]

SQLAlchemy debug log

INFO:sqlalchemy.engine.base.Engine:SELECT col1, col2, col2 FROM preferences WHERE recipient IN %s
INFO:sqlalchemy.engine.base.Engine:(('recipient1', 'recipient2', 'recipient3'),)

Mariadb log

9 Query     SELECT col1, col2, col3 FROM preferences WHERE recipient IN ("'recipient1'", "'recipient1'", "'recipient1'") <-- double quotes around single quotes

I have run strace to see where those quotes are added and it's sqlalchemy fault.

Table schema:

CREATE TABLE `preferences` (
  `recipient` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `col1` tinyint(1) NOT NULL DEFAULT '1',
  `col2` tinyint(1) NOT NULL DEFAULT '1',
  `col3` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`recipient`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Environment

CentOS 7
python-sqlalchemy.x86_64 0.9.7-3.el7 epel

Answer 1

Hi.

As proposed on the gmane.comp.python.sqlalchemy.user, you could use the autoload feature.

To summarise Michael Bayer answer:

t = sqlalchemy.Table(
  'preferences',             # your table name
  sqlalchemy.MetaData(),
  autoload=True,
  autoload_with=sqlsession,
)

query = sqlalchemy.select([t.c.col1, t.c.col2, t.c.col3]) \
  .where(t.c.recipient.in_(message.recipients))

preferences = query.fetchall()

In my case I would had to "autoload" a bunch of tables, and it was not really convenient as they had complex joins.

I ended using something in the lines:

query = "SELECT col1, col2, col3 FROM preferences\nWHERE recipient IN (%s);" % (
  ', '.join(['%s'] * len(message.recipients)
)
result = sqlsession.execute(query, (message.recipients,)) # notice the ","

The idea is to build the query with the number of items which will be given to the IN expression; by doing so you will have the benefits of the auto-escaping functionality and be compatible with all databases backends (as far as I know).

You can see the resulting query with:

>>> print result._saved_cursor._last_executed
SELECT col1, col2, col3 FROM preferences
WHERE recipient IN ('recipient1', 'recipient2', ...);