求教一個(gè)python訪問(wèn)mysql存儲(chǔ)過(guò)程的問(wèn)題

cjaizss

生成數(shù)據(jù)庫(kù)、表、存儲(chǔ)過(guò)程如下：

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();
    

復(fù)制代碼

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()
    

復(fù)制代碼

可是執(zhí)行結(jié)果不對(duì)

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
   

復(fù)制代碼

不知道這個(gè)異常是怎么來(lái)的？
但如果python里用的不是存儲(chǔ)過(guò)程，而是"select a,b from t"，就沒(méi)有問(wèn)題
求教一下大神python里怎么使用存儲(chǔ)過(guò)程

cjaizss

想了想，存儲(chǔ)過(guò)程可能返回多個(gè)結(jié)果集，所以應(yīng)該采用別的接口
非常非常胸悶啊，請(qǐng)有經(jīng)驗(yàn)的可以幫我看一下，鄙人對(duì)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);
    

復(fù)制代碼

代碼在網(wǎng)上找了找，要用一個(gè)callproc的函數(shù)，可是還是不行

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()
    

復(fù)制代碼

結(jié)果還是異常

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
   

復(fù)制代碼

seesea2517

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

cjaizss

回復(fù) 3# seesea2517
......


   

devilkin0312

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