python中mysql的相关操作

ゞ浴缸里的玫瑰 2022-08-04 11:54 199阅读 0赞

安装第三方模块：MYSQLdb：

下载地址：[https://pypi.python.org/pypi/MySQL-python/1.2.4][https_pypi.python.org_pypi_MySQL-python_1.2.4]

安装会自动检测python的安装环境的，不需要任何配置

一、相应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()

运行结果：

![Center][]

二、调用存储过程

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

运行结果：

![Image 1][]![Center 1][]

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

运行结果：

![Image 1][]![Center 2][]

[https_pypi.python.org_pypi_MySQL-python_1.2.4]: https://pypi.python.org/pypi/MySQL-python/1.2.4
[Center]: /images/20220731/b9a222f575fb4cef82847656856df761.png
[Image 1]: 
[Center 1]: /images/20220731/b364a283ac974656a1f6342acfeadbe4.png
[Center 2]: /images/20220731/a1d228a07b6547dd81eef2df79bdf80b.png