python访问mysql的存储过程怎么访问
存储过程返回结果集，获得这个结果集，现在写法有异常。存储结果本身没有问题，mysql里都好用
#!/usr/bin/python
import MySQLdb
db = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test')
cursor = db.cursor()
try:
args=(2,2000)
cursor.callproc("proc3",args) #call proc3(2,2000)
print "XX1"
for result in cursor.stored_results():
print "XX2";
except:
print "Bad"
print "TEST"
db.close()
运行结果会产生这个异常
XX1
Bad
TEST
Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query') in <bound method Cursor.__del__ of <MySQLdb.cursors.Cursor object at 0xb6b81f6c>> ignored
create database test;
use test;
create table t ( a int, b int);
insert into t(a,b) values (1,1000);
insert into t(a,b) values (1,2000);
insert into t(a,b) values (1,3000);
insert into t(a,b) values (2,1000);
insert into t(a,b) values (2,2000);
insert into t(a,b) values (2,3000);
insert into t(a,b) values (3,1000);
insert into t(a,b) values (3,2000);
insert into t(a,b) values (3,3000);
delimiter //
create procedure proc1()
begin
select a,b from t;
end
//
create procedure proc2(in a_max int)
begin
select a,b from t where a<=a_max;
end
//
create procedure proc3(in a_max int,in b_max int)
begin
select a,b from t where a<=a_max and b<=b_max;
end
//
delimiter ;
模块用错了，import mysql.connector 才能用cursor.stored_results() lz66 发表于 2016-08-01 12:14 static/image/common/back.gif
模块用错了，import mysql.connector 才能用cursor.stored_results()
{:qq23:} 我回去试试{:qq23:} lz66 发表于 2016-08-01 12:14 static/image/common/back.gif
模块用错了，import mysql.connector 才能用cursor.stored_results()
我安装不了mysql-connector-python
但我已经安装了MYSQLdb
请问用MYSQLdb如何搞定这个存储过程？ lz66 发表于 2016-08-01 12:14 static/image/common/back.gif
模块用错了，import mysql.connector 才能用cursor.stored_results()
我还是用MYSQLdb
#!/usr/bin/python
import MySQLdb
db = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test')
cursor = db.cursor()
try:
sql = "select a,b from t where a<=2 and b<=2000";
#sql = "call proc3(2,2000)";
cursor.execute(sql)
while 1:
data = cursor.fetchall()
if data:
for row in data:
print "%s %s" % (row,row)
else:
break
except:
print "Bad"
print "TEST"
db.close()
还是一楼的数据库、存储过程
以上运行结果正常：
1 1000
1 2000
2 1000
2 2000
TEST
如果把sql="call proc(2,2000)"
也可以执行
但db.close()为什么就崩溃了呢？
1 1000
1 2000
2 1000
2 2000
TEST
Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query') in <bound method Cursor.__del__ of <MySQLdb.cursors.Cursor object at 0xb6bbbf4c>> ignored
完全不懂python,请教这段怎么改? 我试了下，这样不会报错
cursor.close()
db.close()
本帖最后由 lolizeppelin 于 2016-08-02 10:21 编辑
你的TEST被打印出来了....
也就是说call proc是非阻塞的
然后你关闭了数据库连接
自然就报错了
估计call完游标还要fetchall一下
楼上的直接关闭游标也可以 lz66 发表于 2016-08-02 10:05 static/image/common/back.gif
我试了下，这样不会报错
cursor.close()
db.close()
测试了，如果返回一个结果集的确没问题了，cursor.close()一下即可，下次再用再重新cursor=db.cursor()即可
但还有一个问题：如果存储过程返回多个结果集就不行了，以下代码只可以接收到第一个结果集。
#!/usr/bin/python
import MySQLdb
db = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test')
cursor = db.cursor()
try:
sql = "call proc_ddee33(0)";
cursor.execute(sql)
while 1:
data = cursor.fetchall()
if data:
for row in data:
print "%s %s" % (row,row)
else:
break
except:
print "Bad"
print "TEST3"
cursor.close()
db.close()
用callproc(procname, args) 和 nextset()试试......
官方文档有说明:Advances the cursor to the next result set, discarding the remaining rows in the current result set. If there are no additional result sets, it returns None; otherwise it returns a true value. ning_lianjie 发表于 2016-08-03 11:56 static/image/common/back.gif
用callproc(procname, args) 和 nextset()试试......
官方文档有说明:
终于完全明白了，这才是关键。
不需要cursor.close()
#!/usr/bin/python
import MySQLdb
db = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test')
cursor = db.cursor()
try:
sql = "call proc_ddee33(0)";
cursor.execute(sql)
while 1:
data = cursor.fetchall()
if data:
for row in data:
print "%s %s" % (row,row)
else:
break
cursor.nextset(); #要加这句
except:
print "Bad"
print "TEST3"
#cursor.close()
db.close()
页:
[1]
2