Python mysql connector with multiple statements

Question

I'm trying to run a SQL query through mysql.connector that requires a SET command in order to query a specific table:

import mysql.connector
import pandas as pd   

cnx = mysql.connector.connect(host=ip,
                              port=port,
                              user=user,
                              passwd=pwd,
                              database="")
sql="""SET variable='Test'; 
       SELECT * FROM table  """

df = pd.read_sql(sql, cnx)

when I run this I get the error "Use multi=True when executing multiple statements". But where do I put multi=True?

Possibly pd.read_sql? That seems to be the line that'll raise this error. — xyzjayne
– xyzjayne, Commented Jul 26, 2018 at 16:29

james · Accepted Answer · 2018-07-27 00:08:50Z

1

after many hours of experimenting, i figured out how do to this. forgive me if this is not the most succinct way, but the best i could come up with-

import mysql.connector
import pandas as pd   

cnx = mysql.connector.connect(host=ip,
                              port=port,
                              user=user,
                              passwd=pwd,
                              database="")
sql1="SET variable='Test';" 

sql2="""SELECT * FROM table  """

cursor=cnx.cursor()
cursor.execute(sql1)
cursor.close()

df = pd.read_sql(sql2, cnx)

answered Jul 27, 2018 at 0:08

james

416 bronze badges

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

Comments

Bubble Bubble Bubble Gut · Accepted Answer · 2018-07-26 16:31:43Z

0

Pass the parameters as a dictionary into the params argument should do the trick, documentation here:

pd.read_sql(sql, cnx, params={'multi': True})

The parameters are passed to the underlying database driver.

answered Jul 26, 2018 at 16:31

Bubble Bubble Bubble Gut

3,37617 silver badges30 bronze badges

1 Comment

james Over a year ago

Thanks for the help. I get the same error even when passing the params as you suggest

Collectives™ on Stack Overflow

Python mysql connector with multiple statements

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related