使用mysqlclient 封装一个操作数据库的类

最新推荐文章于 2024-10-02 12:42:27 发布

LXSHYZHX

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量406

点赞数 9

分类专栏： mysql 文章标签： mysql python

本文链接：https://blog.csdn.net/u012074900/article/details/135862978

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文介绍了如何使用Python中的MysqlManager类，通过面向对象的方式实现MySQL数据库的增删改查操作，包括insert、update、delete和select方法的使用实例。

摘要由CSDN通过智能技术生成


import mysqlclient
from datetime import date, time, datetime

class MysqlManager:
    def __init__(self, host='localhost', user='root', password='123456'):
        self.conn = mysqlclient.connect(host=host, user=user, password=password)
        self.cursor = self.conn.cursor()

    def execute_query(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def insert(self, table, data):
        columns = ', '.join([f'{column}' for column in data.keys()])
        values = ', '.join(['%s'] * len(data))
        sql_insert = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        self.cursor.execute(sql_insert, tuple(data.values()))
        self.conn.commit()

    def update(self, table, data):
        set_clause = ', '.join([f'{column} = %s' for column in data.keys()])
        where_clause = ' AND '.join([f'{column} = %s' for column in data.keys()])
        sql_update = f"UPDATE {table} SET {set_clause} WHERE {where_clause}"
        self.cursor.execute(sql_update, tuple(data.values()))
        self.conn.commit()

    def delete(self, table, data):
        where_clause = ' AND '.join([f'{column} = %s' for column in data.keys()])
        sql_delete = f"DELETE FROM {table} WHERE {where_clause}"
        self.cursor.execute(sql_delete)
        self.conn.commit()

    def select(self, table, condition=None):
        if condition:
            where_clause = ' AND '.join([f'{column} = %s' for column in condition.keys()])
            sql_select = f"SELECT * FROM {table} WHERE {where_clause}"
        else:
            sql_select = f"SELECT * FROM {table}"
        return self.execute_query(sql_select)

    def close(self):
        self.cursor.close()
        self.conn.close()

def main():
    mysqlManager = MysqlManager()
    data = {'name': 'John', 'age': 25, 'birthday': datetime.strptime('1980-07-04', '%Y-%m-%d')}
    # insert
    # mysqlManager.insert("users", data)

    # update
    # data = {'name': 'Michael'}
    # mysqlManager.update("users", data, condition={'id': 1})

    # delete
    # condition = {'age': 25}
    # mysqlManager.delete('users', condition)

    # select
    result = mysqlManager.select('users')
    for row in result:
        print(row)

if __name__ == '__main__':
    main()