CODEWITHSUNDEEP
pythonmysql-pythonmysql-connectormysql-connectmysql-connector-python
CodeSsscala
CodeSsscala

Reputation: 749

Can't create temporary tables through Python (Mysql.Connector)

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

Upvotes: 2

Views: 2922

Answers (1)

evan_b
evan_b

Reputation: 1239

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

Upvotes: 6

Related Questions