无法使用PYODBC和FLASK Python获取更新的DB数据
问题描述:
我似乎在从Python中的Flask连接查询PYODBC连接时遇到问题。最初我使用相同的连接使整个文件,但注意到当你点击获取请求的URL时,它不会返回最新的数据。所以我调整了脚本,每次启动获取请求时启动并终止连接,这将返回相同的非更新数据。我注意到偶尔它会更新数据,但大部分时间不会。无法使用PYODBC和FLASK Python获取更新的DB数据
请帮助更好地澄清此过程。
def getChg(s, cursor, arr):
getIncResolved = "SELECT COUNT(incident.number) FROM SCHEMA.OAUSER.incident incident WHERE incident.dv_resolved_by = '" + str(s) + "' AND incident.resolved_at BETWEEN '" + str(past) + "' AND '" + str(current) + "' AND incident.dv_opened_by != '" + str(s) + "';"
getTaskResolved = "SELECT COUNT(sc_task.number) FROM SCHEMA.OAUSER.sc_task sc_task WHERE sc_task.dv_closed_by = '" + str(s) + "' AND sc_task.closed_at BETWEEN '" + str(past) + "' AND '" + str(current) + "' AND sc_task.dv_opened_by != '" + str(s) + "';"
getCallStarted = "SELECT COUNT(new_call.number) FROM SCHEMA.OAUSER.new_call new_call WHERE new_call.opened_at BETWEEN '" + str(past) + "' AND '" + str(current) + "' AND new_call.dv_opened_by = '" + str(s) + "';"
i = 0
t = 0
c = 0
cursor.execute(getIncResolved)
for row in cursor.fetchall():
i = row[0]
cursor.execute(getTaskResolved)
for row in cursor.fetchall():
t = row[0]
cursor.execute(getCallStarted)
for row in cursor.fetchall():
c = row[0]
if c > -1:
test = {'Agent': str(s), 'Calls': c, 'Inc': i, 'Task': t}
arr.append(test)
@app.route('/data',methods=['GET', 'POST'])
def api_root(): cnxn=pyodbc.connect('DSN=ServiceNow;Uid=ServiceOps;Pwd=********;',autocommit=True)
cursor = cnxn.cursor()
data = []
staffjson = request.get_json(force=True)
staff = staffjson['users']
print(staff)
del data[:]
for s in staff:
getChg(s, cursor, data)
print(data)
cnxn.close()
return json.dumps(data)
答
Current变量仅在脚本的初始启动时创建。因此,一旦它被加载,它就不会更新到当前时间。
现在在每个请求中创建变量,并按我的预期工作。
正确缩进代码。这是不可读的。 –
**您可以使用SQL注入。**不要使用连接来构建查询,使用参数化查询。 – davidism
谢谢你。此ODBC驱动程序只允许SELECT的。将不会写入数据库。为适应更好的习惯,我将调整为参数化查询。 –