版权申明：本文为博主窗户(Colin Cai)原创，欢迎转帖。如要转贴，必须注明原文网址

　　http://www.cnblogs.com/Colin-Cai/p/7643047.html 

　　作者：窗户

　　QQ：6679072

　　E-mail：6679072@qq.com

-- http://www.cnblogs.com/Colin-Cai

-- 数据库名称为test

create database test;

use test;

-- 生成表t

create table t ( a int, b int);

-- 插入数据

start transaction;

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

commit work;

-- 一个插入的存储过程myinsert
-- 一个返回两个结果集的存储过程myproc

delimiter //
create procedure myinsert(in a_in int, in b_in int)
begin
insert into t(a,b) values(a_in, b_in);
end
//

create procedure myproc(in a_max int, in b_max int)

begin

select a,b from t where a <= a_max;

select a,b from t where b <= b_max;

end

//

delimiter ;

#!/usr/bin/python

import MySQLdb

db = MySQLdb.Connect(host='localhost', user='root', passwd='', db='test')

cursor = db.cursor()

sql = 'call myproc(4,2000)'

#sql = 'select a,b from t'

#sql = 'insert into t(a,b) values(100,10000)';

print sql

try:

        cursor.execute(sql)

        seq = 1

        while 1:

                if seq > 1:

                        cursor.nextset()

                results = cursor.fetchall()

                if results:

                        print "No.%d" % (seq)

                        seq = seq + 1

                        for row in results:

                                print "%s %s" % (row[0],row[1])

                else:

                        break

except:

        print "Wrong"

print "OK"

db.close()

$ ./test_mysql.py

call myproc(4,2000)

No.1

1 1000

1 2000

1 3000

2 1000

2 2000

2 3000

3 1000

3 2000

3 3000

No.2

1 1000

1 2000

2 1000

2 2000

3 1000

3 2000

OK