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.

mata · Accepted Answer · 2021-04-12 17:15:42Z

25

+50

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 described in the documentation of MySQLCursor.execute()

edited Apr 12, 2021 at 17:15

answered Aug 27, 2015 at 19:25

mata

69.3k10 gold badges168 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related