在上篇博客中主要介绍了关于使用mysql-python进行简单的数据库操作。本篇博客主要是使用篇博客的内容,然后添加事务操作。内容很简单,就是在操作出现错误的时候回退所有未提交的操作。具体的代码如下。
对mysql的相关操作进行一层封装,代码如下:
#coding=utf8
from MySQLdb.cursors import Cursor
from MySQLdb.cursors import DictCursor
from MySQLdb.cursors import SSCursor
from MySQLdb.cursors import SSDictCursor
class Mysql(object):
def __init__(self,db_connect,auto_commit=False):
self.connector = db_connect
self.auto_commit = auto_commit
self.connector.autocommit(auto_commit)
self.cursor = self.connector.cursor()
def __del__(self):
self.connector.commit()
self.cursor.close()
del self.cursor
del self.connector
def __call__(self, *args, **kwargs):
print 'call:'
print args,kwargs
def __repr__(self):
print 'repr'
def create_table(self,sql):
record_count = self.cursor.execute(sql)
return [record_count]
def insert_data(self,sql,params=None):
record_count = self.cursor.execute(sql,params)
return [record_count]
def query_data(self,sql,params):
record_count = self.cursor.execute(sql,params)
if isinstance(self.cursor,Cursor):
data = self.cursor.fetchall()
elif isinstance(self.cursor,DictCursor):
data = self.cursor.fetchallDict()
elif isinstance(self.cursor,SSCursor):
data = self.cursor.fetchall()
elif isinstance(self.cursor,SSDictCursor):
data = self.cursor.fetchallDict()
return [record_count,data]
def update_date(self,sql,params=None):
record_count = self.cursor.execute(sql, params)
return [record_count]
def delete_date(self,sql,params=None):
record_count = self.cursor.execute(sql, params)
if self.auto_commit:
pass
else:
self.connector.commit()
return [record_count]
在这里,我将数据的增删改查全部封装在DBUtil中。
在使用事务操作的时候需要关闭自动提交,也就是autoCommit需要设置为False。
代码如下所示:
#coding=utf8
import MySQLdb.connections
from MySQLdb.cursors import Cursor
from DBUtil import Mysql as mysql
import datetime
create_table = "create table user (id int AUTO_INCREMENT ,name varchar(40) not null,age int,\
birth_day VARCHAR(30) NOT NULL,PRIMARY KEY (id))ENGINE=INNODB DEFAULT CHARSET=UTF8;"
if __name__ == '__main__':
s_time=datetime.datetime.now();
db_connection = MySQLdb.connections.Connection(host="127.0.0.1", user="root", passwd="zh4y4q5ang", db="world",
charset="utf8", cursorclass=Cursor)
e_time=datetime.datetime.now()
print ('connect time ; %s' %(str(e_time-s_time)))
mysql = mysql(db_connection)
try:
insert_sql="insert into user (name,age,birth_day) values(%s,%s,%s)"
insert_param=['Bob1','23','2013-12-31']
data = mysql.insert_data(insert_sql,insert_param)
insert_sql = "insert into user (name,age,birth_day) values('Bob1','23')"
data = mysql.insert_data(insert_sql, insert_param)
except Exception ,e:
print e
db_connection.rollback()
print 'rollback'
else:
db_connection.commit()
print 'commit'
print data
在第二次的数据插入sql语句存在错误,会导致整个操作回退。
关于事务操作在源码中看到的接口比较少,暂时没有看到其他的相关事务操作接口。而且模块的实际操作都是通过mysql-connector中的C驱动完成的,具体实现不清楚。