python工具之myql数据库操作

import pymysql
import config

'''
1.0 简单封装
1.1 添加了insert_id属性，返回insert时返回的主键
1.2 添加了column属性，返回查询的column
1.3 添加一个insert
1.4 添加了一个insertList支持批量添加
'''


class Db:
    '''
    数据库操作类
    Attributes：
        transactionFlag:是否开启事务
        insert_id:insert时返回的主键
    '''

    def __init__(self):
        '''
        insert_id:insert时返回的主键
        '''
        self.insert_id = 0
        self.column = None

    def __init__(self, transactionFlag=False):
        '''
        transactionFlag:是否开启事务
        insert_id:insert时返回的主键
        '''
        self.insert_id = 0
        self.column = None
        self.transFlag = transactionFlag
        self.conn = pymysql.connect(host=config.host, port=config.port, user=config.user,
                                    passwd=config.passwd, db=config.db, charset='utf8')

    def close():
        '''
        关闭连接
        '''
        self.conn.close()

    def execSql(self, sql, param=None):
        '''
        执行增删改语句，返回影响的行数
        sql：要执行的sql
        param:sql是的参数，默认值为None
        '''
        cursor = self.conn.cursor()
        if param == None:
            a = cursor.execute(sql)
        else:
            a = cursor.execute(sql, param)
        self.insert_id = cursor.lastrowid
        self.column = cursor.description
        if self.transFlag == False:
            self.commit()
        return a

    def query(self, sql, param=None):
        '''
        执行查询语句
        sql：要执行的sql
        param:sql是的参数，默认值为None
        '''
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        if param == None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, param)
        ret = cursor.fetchall()
        self.column = cursor.description
        cursor.close()
        return ret

    def queryOne(self, sql, param=None):
        '''
        执行查询语句
        sql：要执行的sql
        param:sql是的参数，默认值为None
        '''
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        if param == None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, param)
        ret = cursor.fetchone()
        cursor.close()
        return ret

    def insert(self, table, keyvalue):
        '''
        以键值对的方式添加数据，简化insert
        '''
        keylist = []
        valuelist = []
        for key, value in keyvalue.items():
            keylist.append(key)
            valuelist.append("'%s'" % value)
        sql = "insert into %s(%s) values(%s)" % (
            table, ','.join(keylist), ",".join(valuelist))
        return self.execSql(sql)

    def insertList(self, table, keyvalueList):
        '''
        以键值对的方式添加数据，简化insert
        '''
        keylist = []
        first = keyvalueList[0]
        for key, value in first.items():
            keylist.append(key)
        sql = "insert into %s(%s) values" % (
            table, ','.join(keylist))
        valuelist = []
        for kv in keyvalueList:
            vlist = []
            for k in keylist:
                vlist.append("'%s'" % kv[k])
            valuelist.append("(" + ','.join(vlist) + ")")
        sql = sql + ",".join(valuelist)
        return self.execSql(sql)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()