pymysql 简单封装

# _*_ coding:utf-8 _*_
"""
file_name:conn_mysql
author:Sam
date:2020/5/8
"""
import pymysql


class Connection(object):
    def __init__(self, host='127.0.0.1', user="root", pwd="root", database="visual", port=3306):
        # 连接mysql
        self.conn = pymysql.connect(host=host, port=port, user=user, passwd=pwd, db=database)
        # 获取游标 cursor=pymysql.cursors.DictCursor不加这个查询出来的数据不带字段名是一个列表，否则返回字典
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
	# 新增
	# table 表名 str
	# data 新增数据 dict: {'name': 1}
    def insert(self, table, data):
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'insert into %s (%s) values (%s)' % (table, keys, values)
        try:
            count = self.cursor.execute(sql, tuple(data.values()))
            # 成功返回受影响行数，否则返回False
            if isinstance(count, int):
                self.close()
                return 1
        except Exception as e:
            self.close(False)
            return False
	# 批量新增
	# table 表名 str
	# data 新增数据 list: [{'name': 1}]
    def insertAll(self, table, data):
        # 获取要插入的字段名
        keys = ', '.join(data[0].keys())
        # 获取字段数，并构建sql
        values = ', '.join(['%s'] * len(data[0]))
        sql = 'insert into %s (%s) values (%s)' % (table, keys, values)
        # 获取要插入的数据值
        val = []
        for n in data:
            val.append(tuple(n.values()))
        value = tuple(val)

        try:
            # 执行sql; count：受影响行数
            count = self.cursor.executemany(sql, value)
            if isinstance(count, int):
                self.close()
                return count
        except Exception as e:
            self.close(False)
            return False

    # 删除
    # table 表名 str
    # data 更新数据 dict：{'name': 2}
    # where 条件 str：'id = 1'
    def update(self, table, data, where):
        set_values = []
        # 更新的字段以及值
        for k in data:
            set_values.append(k + ' = "%s"')
        set_value = ', '.join(set_values)
        # update table set name = "%s" where id = 1
        sql = 'update %s set %s where (%s)' % (table, set_value, where)
        # 值要以元组的形式(1,2,3),count:受影响的行数
        try:
            count = self.cursor.execute(sql, tuple(data.values()))
            if isinstance(count, int):
                self.close()
                return count
        except Exception as e:
            self.close(False)
            return False

    def select(self, table, where, field='*'):
        self.cursor.execute('select %s from %s where (%s)' % (field, table, where))
        data = self.cursor.fetchall()
        self.close()
        return data

	# 查询
    # table 表名 str
    # where 条件 str：'id = 1'
    def delete(self, table, where):
        try:
            count = self.cursor.execute('delete from %s where (%s)' % (table, where))
            if isinstance(count, int):
                self.close()
                return count
        except Exception as e:
            self.close(False)
            return False

    # 关闭连接
    # success 执行成功 bool：True
    def close(self, success=True):
        if success:
            # 提交
            self.conn.commit()
        else:
            # 回滚
            self.conn.rollback()
        # 关闭游标
        self.conn.close()


if __name__ == "__main__":
    mysql = Connection('127.0.0.1', 'root', 'root', 'visual')
    insert = {'title': 'test', 'rank': 10}
    # insert = [{'title': 'test', 'rank': 11}, {'title': 'test1', 'rank': 12}]
    mysql.insert('mkt_white', insert)
    # mysql.insertAll('mkt_white', insert)
    # mysql.update('mkt_white', {'rank': 4}, 'id = 7')
    # data = mysql.select('mkt_white', 'id = 1')
    # mysql.delete('mkt_white', 'id = 1')