基于pymysql包封装的可以进行增删改查的工具类

最新推荐文章于 2024-06-02 14:44:28 发布

MicoJack Honey

最新推荐文章于 2024-06-02 14:44:28 发布

阅读量141

点赞数

分类专栏： Pythoon 提升文章标签： sql 数据库 python

本文链接：https://blog.csdn.net/qq_43577241/article/details/129412518

版权

Pythoon 提升专栏收录该内容

6 篇文章 1 订阅

订阅专栏

此代码可以满足增删改查的需求，除此之外还能够完成批量的插入功能

代码如下：

from pymysql import connect
class MySQLHelper(object):
    def __init__(self, host, user, password, db, port=3306, charset='utf8mb4'):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.charset = charset
        self.conn = connect(host=self.host, user=self.user, password=self.password, db=self.db,
                            port=self.port, charset=self.charset)
        self.cursor = self.conn.cursor()

    def execute(self, sql, args=None):
        try:
            self.cursor.execute(sql, args)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print(e)

    def fetchall(self, sql, args=None):
        self.cursor.execute(sql, args)
        return self.cursor.fetchall()

    def fetchone(self, sql, args=None):
        self.cursor.execute(sql, args)
        return self.cursor.fetchone()

    def update(self, table, data, condition):
        sql = 'UPDATE %s SET %s WHERE %s' % (
        table, ','.join(['%s=%%s' % k for k in data.keys()]), condition)
        args = list(data.values())
        args.append(condition.split('=')[1])
        self.execute(sql, args)

    def delete(self, table, condition):
        sql = 'DELETE FROM %s WHERE %s' % (table, condition)
        self.execute(sql)

    def insert(self, table, data):
        sql = 'INSERT INTO %s(%s) VALUES(%s)' % (
        table, ','.join(data.keys()), ','.join(['%s' for i in range(len(data))]))
        args = list(data.values())
        self.execute(sql, args)

    def batch_insert(self, table, data_list):
        sql = 'INSERT INTO %s(%s) VALUES(%s)' % (
        table, ','.join(data_list[0].keys()), ','.join(['%s' for i in range(len(data_list[0]))]))
        args = []
        for data in data_list:
            args.append(list(data.values()))
        self.cursor.executemany(sql, args)
        self.conn.commit()