I am an app that will get user stocklist that save and use it to query the data from MySQL. Since it is user define, I am not sure how many stocks in the stocklist by user. I know I need to put :params, just like %s used in MySQLdb. However, I get error for
call = db.session.execute(query,{placeholders:stockList})
What is the correct format to query for multiple user define input?
from flask import Flask,jsonify,abort,make_response,request,render_template
from flask.ext.sqlalchemy import SQLAlchemy
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299
db = SQLAlchemy(app)
@app.route('/watchlist', methods=['GET'])
def watch():
#stockList = ['msft','APPLE']
stockList = request.args['stockList'].split(',')
if len(stockList)>0:
placeholders = ','.join([':param'] * len(stockList))
query = 'SELECT * FROM KLSE WHERE Stock IN ({})'.format(placeholders)
try:
call = db.session.execute(query,{placeholders:stockList})
f = call.fetchall()
except Exception:
return 'Error: unable to fetch items'
else:
return "No stock in stockList"
return jsonify({'Stock': f})
The error as below:
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 880, in _execute_context
context = constructor(dialect, self, conn, *args)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 550, in _init_compiled
grp, m in enumerate(parameters)]
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 550, in <listcomp>
grp, m in enumerate(parameters)]
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/compiler.py", line 453, in construct_params
% bindparam.key)
sqlalchemy.exc.StatementError: A value is required for bind parameter 'param' (original cause: sqlalchemy.exc.InvalidRequestError: A value is requi
red for bind parameter 'param') 'SELECT * FROM KLSE WHERE Stock IN (%(param)s,%(param)s)' [{':param,:param': ['msft', 'APPLE']}]
