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

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

# 数据库配置文件

# cat gconf.py

#encoding=utf-8
import json
# json里面的字典不能用单引号，一定要用双引号
USER_FILE='users.json'

# mysql数据库连接信息
MYSQL_HOST = '192.168.3.91'
MYSQL_PORT = 3306
MYSQL_USER = 'root'
MYSQL_PASSWORD = 'root'
MYSQL_DB = 'cmdb'
MYSQL_CHARSET = 'utf8'

# 邮件发送的信息
SMTP_SERVER_HOST='smtp.exmail.qq.com'
SMTP_SERVER_PORT=25
SMTP_USER='jack@qq.com'
# 邮箱客户端专用密码
SMTP_PWD='pass'

# 接收邮件
ALARM_RECIVE = ['admin@163.com']

# app验证信息
APP_KEY = 'adI23SaE926DSslieo'
APP_SECRET = 'adI23SaE926DSslieo'

# 操作数据库增删改查的dbutils.py代码

#encoding=utf-8

import MySQLdb

import 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_list


def 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