search

kennethreitz / records

SQL for Humans™
https://pypi.python.org/pypi/records/
ISC License
7.15k stars 570 forks source link

parameter binding doesn't work well with IN clause #83

Closed Dieken closed 6 years ago

Dieken commented 7 years ago 
db.query('SELECT * FROM order WHERE pay_status IN (:pay_status) LIMIT :n', pay_status = (1, 2), n = 100)

It reports exception:

Traceback (most recent call last):
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/connections.py", line 42, in defaulterrorhandler
    raise errorvalue
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/connections.py", line 270, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "a.py", line 6, in <module>
    rows = db02.query('SELECT * FROM order WHERE pay_status IN (:pay_status) LIMIT :n', pay_status = (1, 2), n=100)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/records.py", line 237, in query
    cursor = self.db.execute(text(query), **params) # TODO: PARAMS GO HERE
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 185, in reraise
    raise value.with_traceback(tb)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/connections.py", line 42, in defaulterrorhandler
    raise errorvalue
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/Users/liuyb/anaconda/lib/python3.5/site-packages/MySQLdb/connections.py", line 270, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1241, 'Operand should contain 1 column(s)') [SQL: 'SELECT * FROM order WHERE pay_status IN (%s) LIMIT %s'] [parameters: ((1, 2), 100)]
Dieken commented 7 years ago

This works:

db.query('SELECT * FROM order WHERE pay_status IN :pay_status LIMIT :n', pay_status = (1, 2), n = 100)

But I can't use this workaround, because my script doesn't know the semantics of the SQL clause and whether pay_status should be a tuple, the SQL comes from configuration file and the "pay_status" comes from HTTP request parameters, the HTTP request may contain single "pay_status" parameter and breaks the Python statement above.

Even with this workaround, it can't express '.... IN (0, :extra_pay_status)'.

vlcinsky commented 6 years ago

I guess, this issue shall be possibly fixed in sqlalchemy package.