python操作mysql数据库增删改查的dbutils实例
# 数据库配置文件# cat gconf.py#encoding=utf-8import json# json里面的字典不能用单引号，一定要用双引号USER_FILE='users.json'# mysql数据库连接信息MYSQL_HOST = '192.168.3.91'MYSQL_PORT = 3306MYSQL_USER = 'root'MYSQL_PASSWORD = 'root'MYSQL_DB = 'cmdb'MYSQL_CHARSET = 'utf8'# 邮件发送的信息SMTP_SERVER_HOST='smtp.exmail.qq.com'SMTP_SERVER_PORT=25SMTP_USER='jack@qq.com'# 邮箱客户端专用密码SMTP_PWD='pass'# 接收邮件ALARM_RECIVE = ['admin@163.com']# app验证信息APP_KEY = 'adI23SaE926DSslieo'APP_SECRET = 'adI23SaE926DSslieo'
# 操作数据库增删改查的dbutils.py代码
#encoding=utf-8import MySQLdbimport gconf# 主类class MysqlConnection(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() except BaseException as e: print e def close(self): # 在关闭连接之前将内存中的文件写入磁盘 self.commit() if self.__cur: self.__cur.close() self.__cur = None if self.__conn: self.__conn.close() self.__conn = None # 设置提交 def commit(self): if self.__conn: self.__conn.commit() def execute(self, sql, args = ()): _cnt = 0 if self.__cur: self.__cur.execute(sql, args) return _cnt def 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 @classmethod def 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) print sql if fetch: count, rt_list = conn.fetch(sql, args) else: count = conn.execute(sql, args) conn.close() print rt_list return count, rt_listdef execute_fetch_sql(sql, args = (), fetch = True): return execute_sql(sql, args, fetch)def execute_commit_sql(sql, args = (), fetch = False): return execute_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) # 如果是查询 if fetch: rt = cur.fetchall() # if args: # rt = cur.fetchone() # else: # rt = cur.fetchall() else: conn.commit() except BaseException, e: print e finally: if cur: cur.close() if conn: conn.close() print 'dbutils:%s,%s' %(count,rt) return count,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() print sql # 循环执行插入语句，一次性全部提交 for line in rt_list: count += cur.execute(sql, line) conn.commit() except BaseException, e: print e finally: if cur: cur.close() if conn: conn.close() return count# 测试代码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