mysql.connector，multi = True，sql变量赋值不起作用 [英] mysql.connector, multi=True, sql variable assignment not working

问题描述

SQL代码(全部保存在一个文件中，该文件最终保存在python变量"query"中):

SQL code (all in one file that is eventually saved in the python variable "query"):

select @dtmax:=DATE_FORMAT(max(dt), '%Y%m') from table_A;
delete from table_B where  DATE_FORMAT(dt, '%Y%m')=@dtmax;

像我在上面的查询中所做的那样，mysql-connector是否允许使用变量赋值.即从TABLE_A中获取max(date)的值，然后从TABLE_B中删除具有该日期的所有内容.

Does mysql-connector allow the use of variable assignment like I've done in the query above. i.e. take the value of max(date) from TABLE_A and delete everything with that date from TABLE_B.

python代码:

    c = conn.cursor(buffered=True)
    c.execute(query, multi=True)
    conn.commit()
    conn.close()

我所知道的是第二条SQL语句未执行.

All I know is that the 2nd SQL statement doesnt execute.

我可以将SQL代码复制并粘贴到Toad中，然后在其中运行，而不会出现任何问题，但是不能通过mysql.connector库.我本来会用熊猫的，但这是别人写的遗留脚本，所以我没有时间重写所有内容.

I can copy and paste the SQL code into Toad and run it there without any problems but not through mysql.connector library. I would have used pandas but this is legacy script written by someone else and I don't have time to re-write everything.

我非常感谢您的帮助.

推荐答案

使用multi=True时，execute()将返回一个生成器.您需要遍历该生成器以实际将处理推进到多语句查询中的下一个sql语句:

When you use multi=True, then execute() will return a generator. You need to iterate over that generator to actually advance the processing to the next sql statement in your multi-statement query:

c = conn.cursor(buffered=True)
results = c.execute(query, multi=True)
for cur in results:
    print('cursor:', cur)
    if cur.with_rows:
        print('result:', cur.fetchall())
conn.commit()
conn.close()

如果有当前语句的结果要提取，则

cur.with_rows将为True.

cur.with_rows will be True if there are results to fetch for the current statement.

在 查看全文