求教一个python访问mysql存储过程的问题
生成数据库、表、存储过程如下：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 (2,2000);
delimiter //
create procedure proc1()
begin
select a,b from t;
end
//
delimiter ;
call proc1();
python代码如下
#!/usr/bin/python
import MySQLdb
db = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test')
cursor = db.cursor()
sql = "call proc1()"
print sql
try:
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
print "%s %s" % (row,row)
except:
print "Bad"
db.close()
可是执行结果不对
cj@cj-virtual-machine:~/mysql$ ./2.py
call proc1()
1 1000
2 2000
Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query') in <bound method Cursor.__del__ of <MySQLdb.cursors.Cursor object at 0xb6c28f2c>> ignored
不知道这个异常是怎么来的？
但如果python里用的不是存储过程，而是"select a,b from t"，就没有问题
求教一下大神python里怎么使用存储过程 本帖最后由 cjaizss 于 2016-07-31 17:50 编辑
想了想，存储过程可能返回多个结果集，所以应该采用别的接口
非常非常胸闷啊，请有经验的可以帮我看一下，鄙人对python完全不懂，但只要知道这里怎么用就行了
drop database if exists test;
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 ;
call proc1();
call proc2(2);
call proc3(2,2000);
代码在网上找了找，要用一个callproc的函数，可是还是不行
#!/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)
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 0xb6b71f6c>> ignored
楼主是 mysql 版里 python 用得最好的 :D 回复 3# seesea2517
......
create procedure proc3(inout a_max int,inout b_max int)
http://www.mysqltutorial.org/calling-mysql-stored-procedures-python/
页:
[1]