Run multiple MySQL statements with a single SQLAlchemy .execute()?

Question

There is a variable defined in my mysql statement, and the variable is operated in the sql statement, but when I use sqlalchemy to execute the sql statement, the error is displayed in the position where the variable is defined. The syntax error (set @num=1;), Seems like sqlalchemy can't execute sql with defined variables

mysql code：

set @a=0;set @b=null;select @a:=if(@b=num,@a+1,1) as rk,@b:=num from logs;

python code:

engine=create_engine("mysql+pymysql://root:root@localhost:3306/test",echo=True)
c = engine.raw_connection()
cursor = c.cursor()
result=cursor.execute("set @a=0;set @b=null;select @a:=if(@b=num,@a+1,1) as rk,@b:=num from logs;")

I only use sqlalchemy to execute the above sql statement, and the error is reported:

C:\Program Files\Python36\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\xD6\xD0\xB9\xFA\xB1\xEA...' for column 'VARIABLE_VALUE' at row 485") result = self._query(query)
Traceback (most recent call last):
  File "C:/Users/DHL/PycharmProjects/fortify/sqladd.py", line 11, in 
    result=cursor.execute("set @a=0;set @b=null;select @a:=if(@b=num,@a+1,1) as rk,@b:=num from logs;")
  File "C:\Program Files\Python36\lib\site-packages\pymysql\cursors.py", line 170, in execute 
    result = self._query(query)
  File "C:\Program Files\Python36\lib\site-packages\pymysql\cursors.py", line 328, in _query 
    conn.query(q)
  File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 517, in query 
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result 
    result.read()
  File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 1075, in read 
    first_packet = self.connection._read_packet()
  File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 684, in _read_packet 
    packet.check_error()
  File "C:\Program Files\Python36\lib\site-packages\pymysql\protocol.py", line 220, in check_error 
    err.raise_mysql_exception(self._data)
  File "C:\Program Files\Python36\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception 
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @b=null;select @a:=if(@b=num,@a+1,1) as rk,@b:=num from logs' at line 1")

score 1 · Answer 1 · answered Jul 17 '22 at 13:59

As mentioned in a related Q&A you need to pass client_flag=CLIENT.MULTI_STATEMENTS to the PyMySQL .connect() method. You can do that by using the creator= argument to .create_engine():

import pymysql
from pymysql.constants import CLIENT
import sqlalchemy as sa


def get_pymysql_connection():
    return pymysql.connect(
        host="127.0.0.1",
        port=3307,
        user="scott",
        password="tiger",
        database="mydb",
        client_flag=CLIENT.MULTI_STATEMENTS,
    )


engine = sa.create_engine("mysql+pymysql://", creator=get_pymysql_connection)
c = engine.raw_connection()
cursor = c.cursor()
result = cursor.execute(
    "set @a=0;set @b=null;select @a:=if(@b=num,@a+1,1) as rk,@b:=num from logs;"
)

You can also pass the `client_flag` by `create_engine(uri, connect_args={'client_flag': CLIENT.MULTI_STATEMENTS})`. That way, you don't have to pass the creator function. — Jelle de Fries, Aug 07 '23 at 14:56

score 0 · Answer 2 · answered Jul 17 '22 at 10:10

You are trying to run more than one SQL statement with a single cursor.execute() call. Each SQL statement ends with ;. The MySQL error message shows your input starting with the first SQL it did not understand, and that happens to be your second statement set @b=null;.

Either call cursor.execute() once for each distinct SQL statement, or use

result=cursor.execute(
   "set @a=0;set @b=null;select @a:=if(@b=num,@a+1,1) as rk,@b:=num from logs;",
   multi = True)

I recommend the one-statement-per-call solution because it's easier to troubleshoot.

More information here. Enable executing multiple statements while execution via sqlalchemy

Run multiple MySQL statements with a single SQLAlchemy .execute()?

2 Answers2