使用单个MySQL .execute()运行多个.execute语句？

Question

mysql语句中定义了一个变量，该变量在sql语句中进行操作，但是当我使用sqlalchemy执行sql语句时，错误将显示在定义变量的位置。语法错误(设置为@num=1;)，似乎sqlalchemy无法使用定义的变量执行sql

mysql代码：

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

python代码：

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;")

我只使用sqlalchemy来执行上面的sql语句，并报告了错误：

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

Answer 1

您正在尝试使用单个cursor.execute()调用运行多个SQL语句。每个SQL语句都以;结尾。MySQL错误消息显示您的输入从它不理解的第一个SQL开始，这恰好是您的第二个语句set @b=null;。

为每个不同的SQL语句调用cursor.execute()一次，或者使用

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)

我推荐单语句每次调用解决方案，因为它更容易解决疑难解答。

这里有更多的信息。Enable executing multiple statements while execution via sqlalchemy

Answer 2

正如在related Q&A中提到的，您需要将client_flag=CLIENT.MULTI_STATEMENTS传递给PyMySQL .connect()方法。您可以通过使用creator=参数来实现.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;"
)