I have a query of the following format,
DROP TABLE X;
CREATE TEMPORARY TABLE IF NOT EXISTS X (SELECT * FROM TABLE);
SELECT A,B,C FROM X;
However, if I run this in Python using mysql connector. I get the following message, 'No result set to fetch from'.
My understanding is that it only executes the first DROP TABLE X; and returns.
Is there another way to achieve this?. I've also tried using multi=True but that didn't work either.
Thanks in advance
mysql.connector will raise an exception if you try to run multiple statements in a single call to execute(), unless multi=true is set.
But, as you've found, it doesn't seem to use the same session (or otherwise does something strange with sessions) if multi=true is set, which results in the loss of any temp tables you've created.
So, the solution I use is to just execute each separately on the same cursor/connection:
conn = mysql.connector.connect(...)
cursor = conn.cursor()
cursor.execute('DROP TABLE X')
cursor.execute('CREATE TEMPORARY TABLE IF NOT EXISTS X (SELECT * FROM TABLE);')
cursor.execute('SELECT A,B,C FROM X;')
data = cursor.fetchall()
