python pymysql.cursors如何从mysql存储过程中获取INOUT返回结果 [英] How python pymysql.cursors get INOUT return result from mysql stored procedure
本文介绍了python pymysql.cursors如何从mysql存储过程中获取INOUT返回结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有 mysql 过程:
I have mysql proc:
CREATE DEFINER=`user`@`localhost` PROCEDURE `mysproc`(INOUT par_a INT(10), IN par_b VARCHAR(255) , IN par_c VARCHAR(255), IN par_etc VARCHAR(255))
BEGIN
// bla... insert query here
SET par_a = LAST_INSERT_ID();
END$$
DELIMITER ;
测试该 sp,如果我运行:
to test that sp, if i run:
SET @par_a = -1;
SET @par_b = 'one';
SET @par_c = 'two';
SET @par_etc = 'three';
CALL mysproc(@par_a, @par_b, @par_c, @par_etc);
SELECT @par_a;
COMMIT;
它以我想要的方式返回@par_a - 所以我认为我的数据库很好......
it return @par_a as what i want - so i assume my db is fine...
然后...
我有如下pyhton:
i have pyhton as follow:
import pymysql.cursors
def someFunction(self, args):
# generate Query
query = "SET @par_a = %s; \
CALL mysproc(@par_a, %s, %s, %s); \
SELECT @par_a \
commit;"
try:
with self.connection.cursor() as cursor:
cursor.execute(query,(str(par_a), str(par_b), str(par_c), str(par_etc)))
self.connection.commit()
result = cursor.fetchone()
print(result) # <-- it print me 'none' how do i get my @par_a result from mysproc above?
return result
except:
raise
finally:
self.DestroyConnection()
结果:存储过程执行,我可以看到记录.
result: the stored proc executed, as i can see record in.
问题:但我无法在上面的 mysproc 的 python 代码中得到我的 @par_a 结果?
problem: but i cant get my @par_a result in my python code from mysproc above?
而且,如果我改变:
# generate Query
query = "SET @par_a = '" + str(-1) + "'; \
CALL mysproc(@par_a, %s, %s, %s); \
SELECT @par_a \
commit;"
到
# generate Query
query = "SELECT 'test' \
commit;"
和
cursor.execute(query)
奇怪的是,它给了我正确的结果 ('test',)
strangely, it give me the correct result ('test',)
推荐答案
我使用了这个课程,我得到了回应.
I used this class and I got response.
import pymysql.cursors
class connMySql:
def __init__(self, User, Pass, DB, Host='localhost', connShowErr=False, connAutoClose=True):
self.ShowErr = connShowErr
self.AutoClose = connAutoClose
self.DBName = DB
try:
self.connection = pymysql.connect(host=Host,
user=User,
password=Pass,
db=DB, #charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
except ValueError as ValErr:
if self.ShowErr == True: print(ValErr)
return False
def Fetch(self, Query):
try:
with self.connection.cursor() as cursor:
# Read a single record
cursor.execute(Query)
result = cursor.fetchall()
return result
except ValueError as ValErr:
if self.ShowErr == True: print(ValErr)
return False
finally:
if self.AutoClose == True: self.connection.close()
def Insert(self, Query):
try:
with self.connection.cursor() as cursor:
# Create a new record
cursor.execute(Query)
# connection is not autocommit by default. So you must commit to save
# your changes.
self.connection.commit()
except ValueError as ValErr:
if self.ShowErr == True: print(ValErr)
return False
finally:
if self.AutoClose == True: self.connection.close()
def ProcedureExist(self, ProcedureName):
try:
result = self.Fetch("SELECT * FROM mysql.proc WHERE db = \"" + str(self.DBName) + "\";")
Result = []
for item in result:
Result.append(item['name'])
if ProcedureName in Result:
return True
else:
return False
except ValueError as ValErr:
if self.ShowErr == True: print(ValErr)
return False
def CallProcedure(ProcedureName, Arguments=""):
try:
# Set arguments as a string value
result = self.Fetch('CALL ' + ProcedureName + '(' + Arguments + ')')
except ValueError as ValErr:
if self.ShowErr == True: print(ValErr)
return False
finally:
if self.AutoClose == True: self.connection.close()
def CloseConnection(self):
try:
self.connection.close()
return True
except ValueError as ValErr:
if self.ShowErr == True: print(ValErr)
return False
def main():
objMysqlConn = connMySql('user', '1234', 'myDB', connShowErr=True, connAutoClose=False)
ProcedureName= "mysproc"
if objMysqlConn.ProcedureExist(ProcedureName):
result = objMysqlConn.Fetch('CALL ' + ProcedureName + '()')
if result != False:
result = result[0]
print(result)
else:
print("The procecure does not exist!")
if __name__ == '__main__':
main()
这篇关于python pymysql.cursors如何从mysql存储过程中获取INOUT返回结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文