如何在MySQL查询和Python中使用WHERE&AND。

Question

早上好，我的MySQL工作台模式中有一个表，如下所示，我直接从工作台运行查询：SELECT voltage, time FROM patient_results_tbl WHERE date ='2021-11-05' AND patient_id = 260939631 LIMIT 4;

# patient_id,   voltage,     time,         date
'260939631',    '98',       '18:51:46',    '2021-11-13'
'260939631',    '98',       '18:51:47',    '2021-11-13'
'260939631',    '111',      '18:51:48',    '2021-11-13'
'260939631',    '106',      '18:51:49',    '2021-11-13'
'260939631',    '115',      '18:51:50',    '2021-11-13'
'260939631',    '114',      '18:51:51',    '2021-11-13'
'785393661',    '5',        '18:57:32',    '2021-11-13'
'785393661',    '317',      '18:57:33',    '2021-11-13'
'785393661',    '321',      '18:57:34',    '2021-11-13'
'785393661',    '325',      '18:57:34',    '2021-11-13'
'785393661',    '328',      '18:57:35',    '2021-11-13'
'785393661',    '337',      '18:57:35',    '2021-11-13'

输出还可以，就像我预期的一样

# patient_id,   voltage,     time,         date
'785393661',    '317',      '18:57:33',    '2021-11-13'
'785393661',    '321',      '18:57:34',    '2021-11-13'
'785393661',    '325',      '18:57:34',    '2021-11-13'
'785393661',    '328',      '18:57:35',    '2021-11-13'

现在，我想使用Tkinter GUI运行相同的查询，用户必须在其中输入日期和LIMIT，这就是查询所产生的：

def analyze_voltage_time():
lim = limit_var.get()
start = s_date_var.get()
end = e_date_var.get()
_id = id_selector.get()
pat_id = _id[:9]
query = "SELECT voltage, time FROM patient_results_tbl WHERE date = "+start +" AND patient_id ="+pat_id + " LIMIT  "+lim
mycursor.execute(query)
result = mycursor .fetchall()
voltage, time = list(zip(*result))
for volts in voltage:
voltage_container.append(volts)
for tim in time:
time_container.append(str(tim))                        
my_ax1.plot(time_container, voltage_container)        
my_canvas1.draw_idle()
analyse_btn['state'] = DISABLED

当我运行时得到错误：

Exception in Tkinter callback
Traceback (most recent call last):
File "C:UsersKennedy MulengaAppDataLocalProgramsPythonPython39libsite-packagesmysqlconnectorconnection_cext.py", line 513, in cmd_query
self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND patient_id =785393661 LIMIT' at line 1
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:UsersKennedy MulengaAppDataLocalProgramsPythonPython39libtkinter__init__.py", line 1892, in __call__
return self.func(*args)
File "C:UsersKennedy MulengaDesktopLevel 518136709_BIT_280_Arduino_ECG_Project18136709_ECG_LArdmon_APP_Final.py", line 260, in analyze_voltage_time
mycursor.execute(query)
File "C:UsersKennedy MulengaAppDataLocalProgramsPythonPython39libsite-packagesmysqlconnectorcursor_cext.py", line 269, in execute
result = self._cnx.cmd_query(stmt, raw=self._raw,
File "C:UsersKennedy MulengaAppDataLocalProgramsPythonPython39libsite-packagesmysqlconnectorconnection_cext.py", line 518, in cmd_query
raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND patient_id =785393661 LIMIT' at line 1

是不是有什么不对劲？

Answer 1

似乎您错过了日期值周围的引号，但无论如何，您都应该避免在查询代码中使用字符串串联-请使用参数(仅使用var表示限制，或使用过程进行全参数查询(

cursor.execute("SELECT voltage, time FROM patient_results_tbl WHERE date = %s AND patient_id = %s LIMIT " + lim , (start,pat_id))