python mysql dbutils_python操作mysql数据库增删改查的dbutils实例

#encoding=utf-8

importMySQLdbimportgconf#主类

classMysqlConnection(object):def __init__(self, host, port, user, passwd, db, charset='utf8'):

self.__host =host

self.__port =port

self.__user =user

self.__passwd =passwd

self.__db =db

self.__charset =charset

self.__conn =None

self.__cur =None

self.__connect()#连接数据库

def __connect(self):try:

self.__conn = MySQLdb.connect(host = self.__host, port = self.__port,\

user= self.__user, passwd = self.__passwd,\

db= self.__db, charset = self.__charset)

self.__cur = self.__conn.cursor()exceptBaseException as e:printedefclose(self):#在关闭连接之前将内存中的文件写入磁盘

self.commit()if self.__cur:

self.__cur.close()

self.__cur =Noneif self.__conn:

self.__conn.close()

self.__conn =None#设置提交

defcommit(self):if self.__conn:

self.__conn.commit()def execute(self, sql, args =()):

_cnt=0if self.__cur:

self.__cur.execute(sql, args)return_cntdef fetch(self, sql, args =()):

_cnt=0

rt_list=[]#_cnt = self.execute(sql, args)

if self.__cur:

_cnt= self.__cur.execute(sql, args)

rt_list= self.__cur.fetchall()return_cnt, rt_list

@classmethoddef execute_sql(cls, sql, args=(), fetch =True):

count=0

rt_list=[]

conn= MysqlConnection(host = gconf.MYSQL_HOST, port =gconf.MYSQL_PORT,\

user= gconf.MYSQL_USER, passwd = gconf.MYSQL_PASSWORD, db =gconf.MYSQL_DB,\

charset=gconf.MYSQL_CHARSET)printsqliffetch:

count, rt_list=conn.fetch(sql, args)else:

count=conn.execute(sql, args)

conn.close()printrt_listreturncount, rt_listdef execute_fetch_sql(sql, args = (), fetch =True):returnexecute_sql(sql, args, fetch)def execute_commit_sql(sql, args = (), fetch =False):returnexecute_sql(sql, args, fetch)#区别在于是查询还是修改，增加，删除操作，用fetch来标识

def execute_sql(sql, args = (), fetch =True):

cur=None

conn=None

count=0

rt=()try:

conn= MySQLdb.connect(host = gconf.MYSQL_HOST, port =gconf.MYSQL_PORT,\

user= gconf.MYSQL_USER, passwd = gconf.MYSQL_PASSWORD, db =gconf.MYSQL_DB,\

charset=gconf.MYSQL_CHARSET)

cur=conn.cursor()print 'dbutils sql:%s, args = %s' %(sql, args)

count=cur.execute(sql, args)#如果是查询

iffetch:

rt=cur.fetchall()#if args:

#rt = cur.fetchone()

#else:

#rt = cur.fetchall()

else:

conn.commit()exceptBaseException, e:printefinally:ifcur:

cur.close()ifconn:

conn.close()print 'dbutils:%s,%s' %(count,rt)returncount,rt#批量插入数据库

def batch_execute_sql(sql, rt_list =[]):

cur=None

conn=None

count=0

rt=()try:

conn= MySQLdb.connect(host = gconf.MYSQL_HOST, port =gconf.MYSQL_PORT,\

user= gconf.MYSQL_USER, passwd = gconf.MYSQL_PASSWORD, db =gconf.MYSQL_DB,\

charset=gconf.MYSQL_CHARSET)

cur=conn.cursor()printsql#循环执行插入语句，一次性全部提交

for line inrt_list:

count+=cur.execute(sql, line)

conn.commit()exceptBaseException, e:printefinally:ifcur:

cur.close()ifconn:

conn.close()returncount#测试代码

if __name__ == '__main__':#conn = MysqlConnection(host = gconf.MYSQL_HOST, port = gconf.MYSQL_PORT,\

#user = gconf.MYSQL_USER, passwd = gconf.MYSQL_PASSWORD, db = gconf.MYSQL_DB,\

#charset = gconf.MYSQL_CHARSET)

## conn.execute('insert into user(username) values(%s)', ('jack123',))

#cnt, rt_list = conn.fetch('select * from user')

#print cnt,rt_list

#conn.close()

count, rt_list = MysqlConnection.execute_sql('insert into user(username) values(%s)',('tomkeeper',))print rt_list