J'utilise flask sqlalchemy. Cependant, lors du débogage en ligne de commande, j'ai trouvé que session.execute renvoie une erreur.

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'}]

Mon code comme ci-dessous: Il est en fait défini par l'utilisateur à partir d'une application. Cependant, je vous facilite la tâche en définissant deux valeurs dans le 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})

Pour une raison quelconque, je dois utiliser db.session.execute et interroger en brut. J'essaye sur MySQL, SELECT * FROM KLSE WHERE Stock IN ('SHELL','GENM'); peut obtenir la bonne requête. Je crois qu'une syntaxe spécifique à sqlalchemy a causé cela.

MISE À JOUR: le test sur la solution de @wkzhu renvoie cette erreur

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')" [{}]
0
vindex 14 janv. 2017 à 17:10

2 réponses

Meilleure réponse

Depuis documentation SQLAlchemy, essaye ça:

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)
0
Iron Fist 14 janv. 2017 à 15:35

Deux problèmes que j'ai repérés. Vous avez deux paramètres nommés param dans votre requête, ce qui, je crois, provoque un conflit. Vous mettez également placeholders dans l'instruction d'exécution kwargs, ce qui je crois est incorrect (les dictionnaires ne peuvent pas avoir de listes comme clés).

Plus d'informations sur la façon dont vous êtes censé formater les paramètres ici: http://docs.sqlalchemy.org/ fr / latest / core / sqlelement.html # sqlalchemy.sql.expression.text

Je passerais simplement les paramètres directement dans la chaîne de requête, et execute le sql brut lui-même:

query = 'SELECT * FROM KLSE WHERE Stock IN ({})'.format(
    "'" + "', '".join(stockList) + "'")
call = db.session.execute(query)
0
wkzhu 14 janv. 2017 à 14:31