AI专业者开发社区 1 号成员
  •  0 回帖  •  10 浏览  •  4 个月前
 

Python MySQLdb 学习总结

    任何应用都离不开数据，所以在学习 python 的时候，当然也要学习一个如何用 python 操作数据库了。MySQLdb 就是 python 对 mysql 数据库操作的模块。官方 Introduction : MySQLdb is an thread-compatible interface to the popular MySQL database server that provides the Python database API. 它其实相当于翻译了对应 C 的接口。

   使用这种数据库接口大多是就是执行连接数据库 -> 执行 query-> 提取数据 -> 关闭连接 这几个步骤。MySQLdb 提供比较关键的对象，分别是 Connection、Cursor、Result。具体使用步骤很简单先不写了，先写一些个人认为比较重要、值得注意的地方。

 1、虽然在 MySQLdb.Connect(host ,user , passw , db) 函数中，我们经常使用的只是这几个参数，但是其实里面还有很多比如字符集、线程安全、ssl 等也都是很重要的参数，使用时要身份注意。

 2、当使用 Connection.query() 函数进行 query 后，connection 对象可以返回两种 result，分别是 store_result 和 use_result，store_result 将结果集存回 client 端，而 use_result 则是结果集保存在 server 端，并且维护了一个连接，会占用 server 资源。此时，不可以进行任何其他的查询。建议使用 store_result，除非返回结果集（result set）过大或是无法使用 limit 的情形。

 3、提取（fetch）数据的返回形式大多有三种情形。 as a tuple(how=0) ；as dictionaries, key=column or table.column if duplicated(how=1)；as dictionaries, key=table.column (how=2)

 4、每次 fetch，在 result 内部都会产生数据位置的移动，也就是说假如有 10 行数据，执行 result.fetch_row(3,0)，会得到前三行，再执行 result.fetch_row(3,0)，则会得到中间的三行，所以说 fetch 会导致 position 的移动。另外值得注意的是，如果使用 use_result，也就是数据存储在 server 时，在 fetch 所有的条目之前，不能进行任何的 query 操作。

 5、mysql 本身不支持游标（Cursor），但是 MySQLdb 对 Cursor 进行了仿真。重要的执行 query 方法有 execute 和 executemany 。execute 方法，执行单条 sql 语句，调用 executemany 方法很好用，数据库性能瓶颈很大一部分就在于网络 IO 和磁盘 IO 将多个 insert 放在一起，只执行一次 IO，可以有效的提升数据库性能。游标 cursor 具有 fetchone、fetchmany、fetchall 三个方法提取数据，每个方法都会导致游标游动，所以必须关注游标的位置。游标的 scroll(value, mode)方法可以使得游标进行卷动，mode 参数指定相对当前位置 (relative) 还是以绝对位置 (absolute) 进行移动。

 6、MySQLdb 提供了很多函数方法，在官方指南里没有完全罗列，使用者可以用 help 去看看，里面提供了很多方便的东西。

 7、对于 mysql 来说，如果使用支持事务的存储引擎，那么每次操作后，commit 是必须的，否则不会真正写入数据库，对应 rollback 可以进行相应的回滚，但是 commit 后是无法再 rollback 的。commit() 可以在执行很多 sql 指令后再一次调用，这样可以适当提升性能。

 8、executemany 处理过多的命令也不见得一定好，因为数据一起传入到 server 端，可能会造成 server 端的 buffer 溢出，而一次数据量过大，也有可能产生一些意想不到的麻烦。合理，分批次 executemany 是个不错的办法。

  最后，我自己写了个 pyMysql 模块，主要是对 MySQLdb 提供的常用方法进行了简单的再次封装，也借此机会好好学习下 MySQLdb，以及练习 python 的编码。该程序使用的数据库表，采用 myisam 引擎，所以没加上 commit()，一般最好还是要加上的。

代码如下：PyMysql.py

?
#-*- encoding:gb2312 -*-_
'''
Created on 2012-1-12
@author: xiaojay
'''
import MySQLdb
import MySQLdb.cursors
 
STORE_RESULT_MODE = 0
USE_RESULT_MODE = 1
 
CURSOR_MODE = 0
DICTCURSOR_MODE = 1
SSCURSOR_MODE = 2
SSDICTCURSOR_MODE = 3
 
FETCH_ONE = 0
FETCH_MANY = 1
FETCH_ALL = 2
 
class PyMysql:
    def __init__(self):
        self.conn = None
        pass
    def newConnection(self,host,user,passwd,defaultdb):
        """
        建立一个新连接，指定host、用户名、密码、默认数据库
        """
        self.conn = MySQLdb.Connect(host,user,passwd,defaultdb)
        if self.conn.open == False:
            raise None
    def closeConnnection(self):
        """
        关闭当前连接
        """
        self.conn.close()
     
    def query(self,sqltext,mode=STORE_RESULT_MODE):
        """
        作用：使用connection对象的query方法，并返回一个元组(影响行数(int),结果集(result))
        参数：sqltext：sql语句
             mode=STORE_RESULT_MODE（0） 表示返回store_result，mode=USESTORE_RESULT_MODE（1） 表示返回use_result
        返回：元组(影响行数(int),结果集(result)
        """
        if self.conn==None or self.conn.open==False :
            return -1
        self.conn.query(sqltext)
        if mode == 0 :
            result = self.conn.store_result()
        elif mode == 1 :
            result = self.conn.use_result()
        else :
            raise Exception("mode value is wrong.")
        return (self.conn.affected_rows(),result)
     
    def fetch_queryresult(self,result,maxrows=1,how=0,moreinfo=False):
        """
        参数:result： query后的结果集合
            maxrows： 返回的最大行数
            how： 以何种方式存储结果
             (0：tuple,1：dictionaries with columnname,2：dictionaries with table.columnname)
            moreinfo 表示是否获取更多额外信息（num_fields，num_rows,num_fields）
        返回：元组（数据集，附加信息（当moreinfo=False）或单一数据集（当moreinfo=True）
        """
        if result == None : return None
        dataset =  result.fetch_row(maxrows,how)
        if moreinfo is False :
            return dataset
        else :
            num_fields = result.num_fields()
            num_rows = result.num_rows()
            field_flags = result.field_flags()
            info = (num_fields,num_rows,field_flags)
            return (dataset,info)
         
    def execute(self,sqltext,args=None,mode=CURSOR_MODE,many=False):
        """
        作用：使用游标（cursor）的execute 执行query
        参数：sqltext： 表示sql语句
             args： sqltext的参数
             mode：以何种方式返回数据集
                CURSOR_MODE = 0 ：store_result , tuple
                DICTCURSOR_MODE = 1 ： store_result , dict
                SSCURSOR_MODE = 2 : use_result , tuple
                SSDICTCURSOR_MODE = 3 : use_result , dict
             many：是否执行多行操作（executemany）
        返回：元组（影响行数（int），游标（Cursor））
        """
        if mode == CURSOR_MODE :
            curclass = MySQLdb.cursors.Cursor
        elif mode == DICTCURSOR_MODE :
            curclass = MySQLdb.cursors.DictCursor
        elif mode == SSCURSOR_MODE :
            curclass = MySQLdb.cursors.SSCursor
        elif mode == SSDICTCURSOR_MODE :
            curclass = MySQLdb.cursors.SSDictCursor
        else :
            raise Exception("mode value is wrong")
         
        cur = self.conn.cursor(cursorclass=curclass)
        line = 0
        if many == False :
            if args == None :
                line = cur.execute(sqltext)
            else :
                line = cur.execute(sqltext,args)
        else :
            if args == None :
                line = cur.executemany(sqltext)
            else :
                line = cur.executemany(sqltext,args)
        return (line , cur )
     
    def fetch_executeresult(self,cursor,mode=FETCH_ONE,rows=1):
        """
        作用：提取cursor获取的数据集
        参数：cursor：游标
             mode：执行提取模式
              FETCH_ONE: 提取一个； FETCH_MANY :提取rows个 ；FETCH_ALL : 提取所有
             rows：提取行数
        返回：fetch数据集
        """
        if cursor == None :
            return
        if mode == FETCH_ONE :
            return cursor.fetchone()
        elif mode == FETCH_MANY :
            return cursor.fetchmany(rows)
        elif mode == FETCH_ALL :
            return cursor.fetchall()
         
if __name__=="__main__" :
    print help (PyMysql)

　　

测试代码：

?
#-*- encoding:gb2312 -*-
import PyMysql
 
"""
authors 这张表很简单。
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| author_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| author_last  | varchar(50) | YES  |     | NULL    |                |
| author_first | varchar(50) | YES  | MUL | NULL    |                |
| country      | varchar(50) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
本文主要的所有操作都针对该表。
"""
 
def printAuthors(res,mode=0,lines=0):
    """
    格式化输出
    """
    print "*"*20, " lines: ",lines ," ","*"*20
    if mode==0  :
        for author_id , author_last , author_first , country in res :
            print "ID : %s , Author_last : %s , Author_First : %s , Country : %s" \
            % (author_id , author_last , author_first , country )
    else :
        for item in res :
            print "-----------"               
            for key in item.keys():
                print key ," : ",item[key]
 
#建立连接
mysql = PyMysql.PyMysql()
mysql.newConnection(
        host="localhost",
        user="root",
        passwd="peterbbs",
        defaultdb="bookstore")
""
#定义sql语句
sqltext = "select * from authors order by author_id "
#调用query方法,得到result
lines , res = mysql.query(sqltext, mode=PyMysql.STORE_RESULT_MODE)
#提取数据
data = mysql.fetch_queryresult(res, maxrows=20, how=0, moreinfo=False)
#打印
printAuthors(data,0,lines)
 
#演示多行插入
sqltext = "insert into authors (author_last,author_first,country) values (%s,%s,%s)"
args = [('aaaaaa','bbbbbb','cccccc'),('dddddd','eeeeee','ffffff'),('gggggg','hhhhhh','iiiiii')]
lines ,cur = mysql.execute(sqltext,args,mode=PyMysql.DICTCURSOR_MODE,many=True)
print "*"*20, lines ,"行被插入 ","*"*20
 
sqltext = "select * from authors order by author_id "
#调用cursor.execute方法,得到result
lines ,cur = mysql.execute(sqltext,mode=PyMysql.DICTCURSOR_MODE)
#提取数据
data = mysql.fetch_executeresult(cur, mode=PyMysql.FETCH_MANY, rows=20)
#打印
printAuthors(data,1,lines)
 
#关闭连接
mysql.closeConnnection()

　　

测试输出：

 ******************** lines: 5 ********************

ID : 1 , Author_last : Greene , Author_First : Graham , Country : United Kingdom
ID : 4 , Author_last : Peter , Author_First : David , Country : China
ID : 5 , Author_last : mayday , Author_First : Feng , Country : France
ID : 6 , Author_last : zhang , Author_First : lixin , Country : France
ID : 9 , Author_last : zhang111 , Author_First : lixin , Country : France
******************** 3 行被插入 ********************
******************** lines: 8 ********************
-----------
country : United Kingdom
author_id : 1
author_first : Graham
author_last : Greene
-----------
country : China
author_id : 4
author_first : David
author_last : Peter
-----------
country : France
author_id : 5
author_first : Feng
author_last : mayday
-----------
country : France
author_id : 6
author_first : lixin
author_last : zhang
-----------
country : France
author_id : 9
author_first : lixin
author_last : zhang111
-----------
country : cccccc
author_id : 53
author_first : bbbbbb
author_last : aaaaaa
-----------
country : ffffff
author_id : 54
author_first : eeeeee
author_last : dddddd
-----------
country : iiiiii
author_id : 55
author_first : hhhhhh
author_last : gggggg