Python:无法从 MySQL 查询用户定义列表

Question

我正在使用flask sqlalchemy。但是，在命令行中调试时，我发现 session.execute 返回错误。

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 978, in execute
    clause, params or {})
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 884, in _execute_context
    None, None)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 174, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 167, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 878, in _execute_context
    conn = self._revalidate_connection()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 241, in _revalidate_connection
    "Can't reconnect until invalid "
sqlalchemy.exc.StatementError: Can't reconnect until invalid transaction is rolled back (original cause: sqlalchemy.exc.InvalidRequestError: Can't 
reconnect until invalid transaction is rolled back) 'SELECT * FROM KLSE WHERE Stock in %(param)s,%(param1)s' [{'param1': 'SHELL', 'param': 'GENM'}]

我的代码如下:它实际上是由用户从应用程序定义的。不过，我通过在 stockList 中定义两只股票来使其变得更容易。

from flask import Flask,jsonify,abort,make_response,request,render_template
from flask.ext.sqlalchemy import SQLAlchemy

db = SQLAlchemy(app)

@app.route('/KLSE/watchlist', methods=['GET'])
def KLSEwatch():
    #for testing this example purpose
    stockList = ['SHELL','GENM']
    #use in actual application
    #stockList = request.args['stockList'].split(',')
    placeholders = []
    for x in range (len(stockList)):
         placeholders.append(':param'+str(x))        
    query = 'SELECT * FROM KLSE WHERE Stock IN ({})'.format(placeholders)
    call = db.session.execute(query,{placeholders:stockList})

出于某种原因，我必须使用db.session.execute并以原始方式进行查询。我在MySQL上尝试，SELECT * FROM KLSE WHERE Stock IN ('SHELL','GENM');可以得到正确的查询。我相信某些 sqlalchemy 特定语法导致了这种情况。

更新:对 @wkzhu 的解决方案进行测试返回此错误

query = 'SELECT * FROM KLSE WHERE Stock IN ({})'.format(
    "'" + "', '".join(stockList) + "'")
call = db.session.execute(query)

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 978, in execute
    clause, params or {})
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 884, in _execute_context
    None, None)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 174, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 167, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 878, in _execute_context
    conn = self._revalidate_connection()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 241, in _revalidate_connection
    "Can't reconnect until invalid "
sqlalchemy.exc.StatementError: Can't reconnect until invalid transaction is rolled back (original cause: sqlalchemy.exc.InvalidRequestError: Can't 
reconnect until invalid transaction is rolled back) "SELECT * FROM KLSE WHERE Stock IN ('SHELL', 'GENM')" [{}]

Answer 1

来自SQLAlchemy doc's ，试试这个:

from sqlalchemy import text

def KLSEwatch():
    stockList = ['SHELL','GENM']
    placeholders = []
    for x in range(len(stockList)):
         placeholders.append('param'+str(x))  

    query = text('SELECT * FROM KLSE WHERE Stock IN ({})'.format(','.join(map(lambda s: ':'+s, placeholders))))
    #query = "SELECT * FROM KLSE WHERE Stock IN (':param0', ':param1')"

    params = dict(zip(placeholders, stockList)
    # params = {'param0': 'SHELL', 'param1': 'GENM'}

    call = db.session.execute(query, params)