python - MySQLdb 事务处理及批量执行executemany

MySQL数据库有一个自动提交事务的概念，autocommit。含义是，如果开启autocommit, 则每一个语句执行后会自动提交。即一个语句视为一个事务。

在python使用的MySQLdb中，默认是不开启autocommit的。所以，只有在显示commit后，数据库操作才会真正提交。或者在rollback()后，回滚到上一次commit的状态。

#!/bin/env python

#coding=utf-8

import MySQLdb

class MYSQL(object):

    def __init__(self):

        self.db = MySQLdb.connect("localhost","root","","TESTTABLE",charset='utf8')

        self.cursor = self.db.cursor()

    def test(self):

        try:

            name = 't5'

            #insert

            sql = "insert into test_distinct(name, type) values('%s','1')" % name

            self.cursor.execute(sql)

            #search

            sql = "select * from test_distinct where name = '%s'" % name   #查询新插入的数据

            self.cursor.execute(sql)

            res = self.cursor.fetchone()

            print res

            #insert

            sql = "update test_distinct set type='2' where name='%s'" % name

            raise   #引起异常

            self.cursor.execute(sql)

        except:

            self.db.rollback()

        else:

            self.db.commit()

if __name__ == "__main__":

    obj = MYSQL()

    obj.test()

结果：

　　可以正确查询到新插入的数据，并且数据成功回滚，没有写入数据。

结论：

　　虽然没有commit时，数据库不会真正变化，但是会有一个临时变化的版本，供我们查询还未真正加入的数据。

=======================================================================================================================================

批量执行(executemany)：

　　转载自：http://blog.csdn.net/colourless/article/details/41444069

　　先写sql语句。要注意的是里面的参数，不管什么类型，统一使用%s作为占位符

例如：

　　向user表(username,salt,pwd)插入数据

sql = 'INSERT INTO 表名 VALUES(%s,%s,%s)'

　　对应的param是一个tuple或者list

param = ((username1, salt1, pwd1), (username2, salt2, pwd2), (username3, salt3, pwd3))

　　这样就包含了三条数据，通过executemany插入

n=cursor.executemany(sql,param)

实例：

    # -------------------------------------------

    # Python MySQLdb 循环插入execute与批量插入executemany性能分析

    # 插入数据量：10000条

    # 每条字段：username, salt, pwd

    # Author : Lrg

    # -------------------------------------------

    # encoding = utf-8

    import MySQLdb

    import xlrd

    import time

    import sys

    reload(sys)

    sys.setdefaultencoding("utf-8")  

    # 从users.xls文件获取10000条用户数据

    # 该文件由create_users.py生成

    def get_table():

        FILE_NAME = 'users.xls'

        data = xlrd.open_workbook(FILE_NAME)

        table = data.sheets()[0]

        return table  

    # 循环插入execute

    def insert_by_loop(table):

        nrows = table.nrows

        for i in xrange(1,nrows):

            param=[]

            try:

                sql = 'INSERT INTO user values(%s,%s,%s)'

                # 第一列username，第二列salt，第三列pwd

                print 'Insert: ',table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value

                param = (table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value)

                # 单条插入

                cur.execute(sql, param)

                conn.commit()

            except Exception as e:

                print e

                conn.rollback()

        print '[insert_by_loop execute] total:',nrows-1  

    # 批量插入executemany

    def insert_by_many(table):

        nrows = table.nrows

        param=[]

        for i in xrange(1,nrows):

            # 第一列username，第二列salt，第三列pwd

            param.append([table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value])

        try:

            sql = 'INSERT INTO user values(%s,%s,%s)'

            # 批量插入

            cur.executemany(sql, param)

            conn.commit()

        except Exception as e:

            print e

            conn.rollback()

        print '[insert_by_many executemany] total:',nrows-1   

    # 连接数据库

    conn = MySQLdb.connect(host="127.0.0.1", port=3306, user="lrg", passwd="lrg", db="pythontest")

    cur = conn.cursor()  

    # 新建数据库

    cur.execute('DROP TABLE IF EXISTS user')

    sql = """CREATE TABLE user(

            username CHAR(255) NOT NULL,

            salt CHAR(255),

            pwd CHAR(255)

            )"""

    cur.execute(sql)  

    # 从excel文件获取数据

    table = get_table()  

    # 使用循环插入

    start = time.clock()

    insert_by_loop(table)

    end = time.clock()

    print '[insert_by_loop execute] Time Usage:',end-start  

    # 使用批量插入

    start = time.clock()

    insert_by_many(table)

    end = time.clock()

    print '[insert_by_many executemany] Time Usage:',end-start  

    # 释放数据连接

    if cur:

        cur.close()

    if conn:

        conn.close()

一共10000条数据

一行行循环execute，耗时200秒左右（下面244秒的数据是每次循环加了输出语句的，应该有点影响）

而用executemany一次提交全部，耗时只有0.86秒……

巴特西

python - MySQLdb 事务处理及批量执行executemany

最新文章

热门文章