Use python to operate mysql, and use python to operate mysql

Use python to operate mysql, and use python to operate mysql

Copyright statement: This article is original by Colin Cai. Please post it. If you want to paste, must indicate the original web site http://www.cnblogs.com/Colin-Cai/p/7643047.html Author: Windows QQ: 6679072 E-mail: 6679072@qq.com

 

Python can use MYSQLdb to operate databases.

The SQL statement is as follows:

-- Database Name: testcreate database test; use test; -- generate the table tcreate table t (a int, B int );
-- Insert data
Start transaction; insert into t (a, B) values (); insert into t (a, B) values (); insert into t (a, B) values (); insert into t (a, B) values (); insert into t (a, B) values (3,3000); commit work;

-- Myinsert
-- The Stored Procedure myprocdelimiter that returns two result sets //
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) beginselect a, B from t where a <= a_max; select a, B from t where B <= B _max; end // delimiter;

The python database operation code is as follows:

#!/usr/bin/pythonimport MySQLdbdb = MySQLdb.Connect(host='localhost', user='root', passwd='123456', 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 sqltry:        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:                        breakexcept:        print "Wrong"print "OK"db.close()

The preceding code can be used for SQL statements with or without result sets and with multiple result sets (stored procedures. If no result set exists, and no cursor is required, no result set can be found.

Cursor. nextset () is used to traverse the next result set, which is used to store multiple result sets.

Close the opened database.

Run

$ ./test_mysql.pycall myproc(4,2000)No.11 10001 20001 30002 10002 20002 30003 10003 20003 3000No.21 10001 20002 10002 20003 10003 2000OK