尝试发送包含 MySQL 变量的 MySQL 查询时 Python 返回错误

Question

我正在尝试通过使用 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)我究竟做错了什么？

Answer 1

下面的代码完成了这项工作，已经测试过了。如果出现问题，您可能需要纠正一些缩进问题。

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)

Answer 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)

我无法执行代码，但想法是这样的。

Answer 3

尝试将它们作为两个单独的查询运行。

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)