mysql.connector, multi=True, sql variable assignment not working

Question

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;

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 code:

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

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

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.

I kindly appreciate some help.

Answer 1

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 will be True if there are results to fetch for the current statement.

This is all describend in the documentation of MySQLCursor.execute()