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")
CodePudding user response:
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
CodePudding user response:
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;"
)