python对mysql操作封装

python 对MySQL操作进行封装

后面直接拿来用就ok

import pymysql


class MysqlManager(object):
    '''mysql管理器'''

    def __init__(self, db, user, passwd, host='localhost', port=3306, charset='utf8'):
        '''初始化数据库'''
        self.__db = db
        self.__user = user
        self.__passwd = passwd
        self.__host = host
        self.__port = port
        self.__charset = charset
        self.__connect = None
        self.__cursor = None

    def _connect_db(self):
        """
            dbManager._connect_db()
        连接数据库
        """
        params = {
            "db": self.__db,
            "user": self.__user,
            "passwd": self.__passwd,
            "host": self.__host,
            "port": self.__port,
            "charset": self.__charset
        }
        self.__connect = MySQLdb.connect(**params)
        self.__cursor = self.__connect.cursor()

    def _close_db(self):
        '''
            dbManager._close_db()
        关闭数据库
        '''
        self.__cursor.close()
        self.__connect.close()

    def insert(self, table, insert_data):
        '''
            dbManager.insert(table, insert_data)
        添加数据到数据库
        str -> table 为字符串
        [{},{}] -> 为列表中嵌套字典类型
        '''
        # 用户传入数据字典列表数据，根据key, value添加进数据库
        # 连接数据库
        self._connect_db()

        try:
            for data in insert_data:
                # 提取插入的字段
                key = ','.join(data.keys())
                # 提取插入的值
                values = map(self._deal_values, data.values())
                insert_data = ', '.join(values)
                # 构建sql语句
                sql = "insert into {table}({key}) values ({val})".format(table=table, key=key, val=insert_data)

                self.__cursor.execute(sql)
                self.__connect.commit()
        except Exception as error:
            print error
        finally:
            self._close_db()

    def delete(self, table, condition):
        '''
            dbManager.delete(table, condition)
        删除数据库中的数据
        str -> table 字符串类型
        dict -> condition 字典类型
        '''
        self._connect_db()

        # 处理删除的条件
        condition_list = self._deal_values(condition)
        condition_data = ' and '.join(condition_list)

        # 构建sql语句
        sql = "delete from {table} where {condition}".format(table=table, condition=condition_data)

        self.__cursor.execute(sql)
        self.__connect.commit()
        self._close_db()

    def update(self, table, data, condition=None):
        """
            dbManager.update(table, data, [condition])
        更新数据
        str -> table 字符串类型
        dict -> data 字典类型
        dict -> condition 字典类型
        """
        self._connect_db()

        # 处理传入的数据
        update_list = self._deal_values(data)
        update_data = ",".join(update_list)
        # 判断是否有条件
        if condition is not None:
            # 处理传入的条件
            condition_list = self._deal_values(condition)
            condition_data = ' and '.join(condition_list)
            sql = "update {table} set {values} where {condition}".format(table=table, values=update_data, condition=condition_data)
        else:
            sql = "update {table} set {values}".format(table=table, values=update_data)
        print sql

        self.__cursor.execute(sql)
        self.__connect.commit()
        self._close_db()

    def get(self, table, show_list, condition=None, get_one=False):
        """
            dbManager.get(table, show_list, [condition, get_one]) -> tupe
        获取数据 返回一个元祖
        str -> table 字符串类型
        list -> show_list 列表类型
        dict -> condition 字典类型
        boolean -> get_one 布尔类型
        
        """
        self._connect_db()

        # 处理显示的数据
        show_list = ",".join(show_list)
        sql = "select {key} from {table}".format(key=show_list, table=table)
        # 处理传入的条件
        if condition:
            condition_list = self._deal_values(condition)
            condition_data = 'and'.join(condition_list)
            sql = "select {key} from {table} where{condition}".format(key=show_list, table=table, condition=condition_data)

        self.__cursor.execute(sql)

        # 返回一条数据还是所有数据
        if get_one:
            result = self.__cursor.fetchone()
        else:
            result = self.__cursor.fetchall()
        self._close_db()
        return result

    def _deal_values(self, value):
        """
        self._deal_values(value) -> str or list
            处理传进来的参数
        
        """
        # 如果是字符串则加上''
        if isinstance(value, str):
            value = ("'{value}'".format(value=value))
        # 如果是字典则变成key=value形式
        elif isinstance(value, dict):
            result = []
            for key, value in value.items():
                value = self._deal_values(value)
                res = "{key}={value}".format(key=key, value=value)
                result.append(res)
            return result
        else:
            value = (str(value))
        return value

方式二：去除mysql语句的操作

import pymysql
 
class MysqlHelper():
    def __init__(self,host,port,db,user,passwd,charset='utf8'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = passwd
        self.charset = charset
    def open(self):
        # 连接数据库
        self.conn = pymysql.connect(host=self.host,port=self.port,db=self.db,
                                    user=self.user,passwd=self.password,charset=self.charset)
        # 创建游标对象
        self.cursor = self.conn.cursor()
    # 关闭
    def close(self):
        self.cursor.close()
        self.conn.close()
 
    # 增加、修改、删除命令语句
    def cud(self,sql,params,msg="操作成功"):
        try:
            self.open()
            # 处理逻辑数据，传入sql语句以及参数化
            self.cursor.execute(sql,params)
            # 执行事务
            self.conn.commit()
            self.close()
            # 这样可以修改输出的操作成功信息提示
            print(msg)
        except Exception as e:
            self.conn.rollback()
            print("错误",e)
    # 查询所有数据,多个值
    def all(self,sql,params=()):
        try:
            self.open()
            self.cursor.execute(sql,params)
            data = self.cursor.fetchall()
            self.close()
            return data
        except Exception as e:
            print("错误", e)