1

I have written a script which imports data in from excel sheet to MySQL database. Then I am executing a query to select data and put it in database. MySQL contains two statements, one setting variable and second executing the select. However I am seeing multi=True error and I understand the error but I am confused where to I have to mention multi=True. 

if __name__ == "__main__":
    gesdb_engine_str = 'mysql+mysqlconnector://dbuser:dbpass@localhost/dbname'
    gesdb_engine = sqlalchemy.create_engine(gesdb_engine_str, echo=False, encoding='utf-8', multi - )
    gesdb_connection = gesdb_engine.connect()
    file_name = "tmp/dataload/so_tracking.XLSX"
    df = pd.read_excel(file_name, converters={'Billing Doc.': str, 'Sales Order': str})
    if df.shape[1] == 35 and compare_columns(list(df.columns.values)) == 1:
        # insert_so(df)
        # insert_so_lineitem(df)
        # insert_billing(df)
        scriptFile = open('script.sql','r')
        script = scriptFile.read()
        df2 = pd.read_sql(script, gesdb_connection)

    else:
        print("Incorrect column count, column order or column headers.\n")

Error:

File "C:\winpython\python-3.5.2.amd64\lib\site-packages\mysql\connector\cursor.py", line 511, in execute
    "Use multi=True when executing multiple statements")
sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) Use multi=True when executing multiple statements [SQL: "SET @last_sales_order := 0, @next_open = 0; SELECT `Open`, .....

]

Script.sql sample:

SET @last_sales_order := 0, @next_open = 0;
SELECT `Open`, .....
nomad
  • 973
  • 2
  • 9
  • 22
  • Try something like `pd.read_sql(script, gesdb_connection, params = {"multi":"True"})` and report back if this worked for you. – Sergey Bushmanov Mar 12 '17 at 07:14
  • @SergeyBushmanov It threw an error. `"Use multi=True when executing multiple statements") sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) Use multi=True when executing multiple statements [SQL: "SET @last_sales_order := 0, @next_open = 0; SELECT `Open`, Order_Reason, ....... [parameters: {'multi': 'True'}] ` – nomad Mar 12 '17 at 18:12
  • Executing multiple statements together isn't supported by SQLAlchemy: it's necessary to call `cursor.execute` directly. This isn't possible from Pandas. Rather than setting the query values in an SQL statement, pass them as parameters as shown in the linked duplicate. – snakecharmerb Apr 17 '22 at 14:35

0 Answers0