python操作mysql
一、安装第三方模块:MYSQLdb
1、地址:http://sourceforge.net/projects/mysql-python/
新的可用地址:https://pypi.python.org/pypi/MySQL-python/1.2.4
2、安装
安装前:(site-packages目录)
安装后:(site-packages目录)
二、执行DML语句
import os, sys, string import MySQLdb #连接数据库 try: conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='feng',db='test') except Exception, e: print e sys.exit() # 获取cursor对象来进行操作 cursor = conn.cursor() #插入单条数据 sql = "insert into staffinfo(i_staff_no,v_staff_name) values (%d, '%s')"%(333,"ccc") try: cursor.execute(sql) except Exception, e: print e #插入多条数据 sql = "insert into staffinfo(i_staff_no,v_staff_name) values (%s, %s)"#此处都是%s类型 val = ((444,"ddd"),(555,"eee"), (666,"fff")) try: cursor.executemany(sql, val) except Exception, e: print e #查询数据 sql= "select * from staffinfo order by i_staff_no" cursor.execute(sql) alldata = cursor.fetchall() # 如果有数据返回,就循环输出, alldata是有个二维的列表 if alldata: for rec in alldata: print rec[0], rec[1] cursor.close() conn.close()
运行结果:
三、调用存储过程
1、带参数的存储过程
存储过程:
DELIMITER // create procedure python_pro_param(in _i_staff_no int,out _v_staff_name varchar(50)) begin select v_staff_name into _v_staff_name from staffinfo where i_staff_no=_i_staff_no; end // DELIMITER ;
调用:
import os, sys, string import MySQLdb i_staff_no=222 v_staff_name='' try: conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='feng',db='test') except Exception, e: print e sys.exit() cursor=conn.cursor() cursor.callproc('python_pro_param',(i_staff_no,v_staff_name)) cursor.execute('select @_python_pro_param_0,@_python_pro_param_1')#0:第一个参数 data=cursor.fetchall() #1:第二个参数 if data: for rec in data: v_staff_name=rec[1] print i_staff_no,v_staff_name cursor.close() conn.close()
运行结果:
2、返回结果集的存储过程
存储过程:
DELIMITER // create procedure python_pro_dataset(in _i_staff_no_start int,in _i_staff_no_end int) begin select i_staff_no,v_staff_name from staffinfo where i_staff_no between _i_staff_no_start and _i_staff_no_end; end // DELIMITER ;
调用:
import os, sys, string import MySQLdb i_staff_no_start=222 i_staff_no_end=555 try: conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='feng',db='test') except Exception, e: print e sys.exit() cursor=conn.cursor() cursor.execute('call python_pro_dataset(%s,%s)',(i_staff_no_start,i_staff_no_end)) data=cursor.fetchall() if data: for rec in data: print rec[0],rec[1] cursor.nextset() cursor.close() conn.close()
运行结果: