求教一个python访问mysql存储过程的问题

cjaizss

生成数据库、表、存储过程如下：

1. 
   
2. create database test;
   
3. use test;
   
4. create table t ( a int, b int);
   
5. insert into t(a,b) values (1,1000);
   
6. insert into t(a,b) values (2,2000);
   
7. delimiter //
   
8. create procedure proc1()
   
9. begin
   
10. select a,b from t;
    
11. end
    
12. //
    
13. delimiter ;
    
14. call proc1();
    

复制代码

python代码如下

1. 
   
2. #!/usr/bin/python
   
3. import MySQLdb
   
4. 
   
5. db = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test')
   
6. 
   
7. cursor = db.cursor()
   
8. sql = "call proc1()"
   
9. print sql
   
10. try:
    
11.         cursor.execute(sql)
    
12.         results = cursor.fetchall()
    
13.         for row in results:
    
14.                 print "%s %s" % (row[0],row[1])
    
15. except:
    
16.         print "Bad"
    
17. 
    
18. db.close()
    

复制代码

可是执行结果不对

1. 
   
2. cj@cj-virtual-machine:~/mysql$ ./2.py
   
3. call proc1()
   
4. 1 1000
   
5. 2 2000
   
6. 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

想了想，存储过程可能返回多个结果集，所以应该采用别的接口
非常非常胸闷啊，请有经验的可以帮我看一下，鄙人对python完全不懂，但只要知道这里怎么用就行了

1. 
   
2. drop database if exists test;
   
3. create database test;
   
4. use test;
   
5. create table t ( a int, b int);
   
6. insert into t(a,b) values (1,1000);
   
7. insert into t(a,b) values (1,2000);
   
8. insert into t(a,b) values (1,3000);
   
9. insert into t(a,b) values (2,1000);
   
10. insert into t(a,b) values (2,2000);
    
11. insert into t(a,b) values (2,3000);
    
12. insert into t(a,b) values (3,1000);
    
13. insert into t(a,b) values (3,2000);
    
14. insert into t(a,b) values (3,3000);
    
15. delimiter //
    
16. create procedure proc1()
    
17. begin
    
18. select a,b from t;
    
19. end
    
20. //
    
21. create procedure proc2(in a_max int)
    
22. begin
    
23. select a,b from t where a<=a_max;
    
24. end
    
25. //
    
26. create procedure proc3(in a_max int,in b_max int)
    
27. begin
    
28. select a,b from t where a<=a_max and b<=b_max;
    
29. end
    
30. //
    
31. delimiter ;
    
32. call proc1();
    
33. call proc2(2);
    
34. call proc3(2,2000);
    

复制代码

代码在网上找了找，要用一个callproc的函数，可是还是不行

1. 
   
2. #!/usr/bin/python
   
3. import MySQLdb
   
4. 
   
5. db = MySQLdb.Connect(host='localhost', user='root', passwd='123456', db='test')
   
6. 
   
7. cursor = db.cursor()
   
8. 
   
9. try:
   
10.         args=(2,2000)
    
11.         cursor.callproc("proc3",args)
    
12.         print "XX1"
    
13.         for result in cursor.stored_results():
    
14.                 print "XX2";
    
15. except:
    
16.         print "Bad"
    
17. 
    
18. 
    
19. print "TEST"
    
20. db.close()
    

复制代码

结果还是异常

1. 
   
2. XX1
   
3. Bad
   
4. TEST
   
5. 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
   

复制代码

seesea2517

楼主是 mysql 版里 python 用得最好的

cjaizss

回复 3# seesea2517
......


   

devilkin0312

create procedure proc3(inout a_max int,inout b_max int)
http://www.mysqltutorial.org/cal ... -procedures-python/