python pymysql.cursors如何从mysql存储过程中获取INOUT返回结果
python pymysql.cursors如何从mysql存储过程中获取INOUT返回结果
提问于 2016-10-18 23:42:48
我有mysql进程：
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，如果我运行以下命令：
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作为我想要的-所以我假设我的数据库是好的…
然后..。
我的pyhton如下：
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:执行存储的proc，我可以在中看到记录。
问题:但是我不能在我的python代码中从上面的mysproc中获得@par_a结果？
如果我改变了：
# 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',)
回答 1
Stack Overflow用户
发布于 2018-02-07 19:14:41
我使用了这个类，并得到了响应。
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()页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40112599
复制相关文章
相似问题
腾讯云开发者
