我正在嘗試通過使用 Python 發送 MySQL 查詢來從 MySQL 數據庫中檢索數據。當我在 MySQL 工作臺中發送 MySQL 查詢時，它運行得非常好。當我使用 Python（在 Jupyter Notebook 中）嘗試相同的操作時，它會返回錯誤。蟒蛇代碼：import pymysqlimport pandas as pddef run_mysql(SQLQ): conn = pymysql.connect(host='IP address', user='username', passwd='password', db='database name') df = pd.read_sql(SQLQ, conn) conn.close() return dfmysql_query = '''set @Yesterday = curdate() -1 ; SELECT * FROM mt4_daily where date(time) = date(@Yesterday) '''df = run_mysql(mysql_query)display(df)錯誤：DatabaseError: Execution failed on sql 'set @Yesterday = curdate() -1 ; SELECT * FROM mt4_daily where date(time) = date(@Yesterday) ': (1064, "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 'SELECT * FROM mt4_daily\n where date(time) = date(@Yesterday)' at line 2")如果我刪除 MySQL Query 中的變量，它運行良好：import pymysqlimport pandas as pddef run_mysql(SQLQ): conn = pymysql.connect(host='IP address', user='username', passwd='password', db='database name') df = pd.read_sql(SQLQ, conn) conn.close() return dfmysqlquery = '''SELECT * FROM mt4_daily where date(time) = date(curdate() -1) '''df = run_mysql(mysqlquery)display(df)我究竟做錯了什么？
3 回答
慕萊塢森
TA貢獻1810條經驗 獲得超4個贊
下面的代碼完成了這項工作，已經測試過了。如果出現問題，您可能需要糾正一些縮進問題。
import pymysql
def run_mysql(query1, query2):
try:
conn = pymysql.connect(host='localhost', user='root', passwd='', db='data_new_es')
cursor = conn.cursor()
cursor.execute(query1)
cursor.execute(query2)
row = cursor.fetchone()
print(row)
except Exception as e:
print(str(e))
finally:
cursor.close()
conn.close()
mysqlquery1 = "set @Yesterday = curdate() -1 ;"
mysqlquery2 = "select * from abcde where date(accrual_date) =
date(@Yesterday)"
df1 = run_mysql(mysqlquery1,mysqlquery2)
慕婉清6462132
TA貢獻1804條經驗 獲得超2個贊
我想是因為有兩個語句，這個函數只允許同時讀取和執行一個。根據 pandas read_sql 文檔，您可以使用 read_sql "params" 關鍵字參數來解決此問題并將 @Yesterday 值計算移至 python 端：
import pymysql
import pandas as pd
from datetime import datetime, timedelta
def run_mysql(SQLQ, params):
conn = pymysql.connect(host='IP address', user='username', passwd='password', db='database name')
df = pd.read_sql(SQLQ, conn, params=params)
conn.close()
return df
mysqlquery = '''SELECT * FROM mt4_daily
where date(time) = date(%(yesterday)s)
'''
yesterday = datetime.date(datetime.now())- timedelta(days=1)
params = {'yesterday': yesterday}
df = run_mysql(mysqlquery, params)
display(df)
我無法執行代碼，但想法是這樣的。
烙印99
TA貢獻1829條經驗 獲得超13個贊
嘗試將它們作為兩個單獨的查詢運行。
mysql_query = '''set @Yesterday = curdate() -1 ;'''
df = run_mysql(mysql_query)
mysql_query = '''SELECT * FROM mt4_daily
where date(time) = date(@Yesterday)
'''
df = run_mysql(mysql_query)
添加回答
舉報
0/150
提交
取消