python-pymysql-类对象映射为sql语句

查询语句

import pymysql
class UserQuery:
    def __init__(self, name=None, age=None, email=None):
        self.name = name
        self.age = age
        self.email = email
def select_data(table, condition):
    # 连接到数据库
    connection = pymysql.connect(host='localhost', user='username', password='password', database='database_name')
    cursor = connection.cursor()
     # 构建SELECT语句
    columns = ', '.join(condition.__dict__.keys())
    values = ' AND '.join([f"{key} = '{value}'" for key, value in condition.__dict__.items() if value is not None])
    sql = f"SELECT {columns} FROM {table} WHERE {values}"
     # 执行SQL语句
    cursor.execute(sql)
    results = cursor.fetchall()
     # 关闭数据库连接
    cursor.close()
    connection.close()
     # 将查询结果映射为类对象
    objects = []
    for row in results:
        user = User(id=row[0], name=row[1], age=row[2], email=row[3])
        objects.append(user)
     return objects
 # 测试数据
query = UserQuery(name='John', age=25)
 # 调用函数查询数据
results = select_data('users', query)
for user in results:
    print(user.id, user.name, user.age, user.email)

插入语句

import pymysql
 class User:
    def __init__(self, name, age, email):
        self.name = name
        self.age = age
        self.email = email
     def to_insert_sql(self, table):
        columns = ', '.join(self.__dict__.keys())
        values = ', '.join([f"'{value}'" for value in self.__dict__.values()])
        sql = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        return sql
 def insert_data(table, data):
    # 连接到数据库
    connection = pymysql.connect(host='localhost', user='username', password='password', database='database_name')
    cursor = connection.cursor()
     # 执行插入语句
    cursor.execute(data.to_insert_sql(table))
    connection.commit()
     # 关闭数据库连接
    cursor.close()
    connection.close()
 # 测试数据
user = User(name='John', age=25, email='john@example.com')
 # 调用函数插入数据
insert_data('users', user)

更新语句

import pymysql
 class User:
    def __init__(self, id, name, age, email):
        self.id = id
        self.name = name
        self.age = age
        self.email = email
     def to_update_sql(self, table):
        values = ', '.join([f"{key} = '{value}'" for key, value in self.__dict__.items() if key != 'id'])
        sql = f"UPDATE {table} SET {values} WHERE id = {self.id}"
        return sql
 def update_data(table, data):
    # 连接到数据库
    connection = pymysql.connect(host='localhost', user='username', password='password', database='database_name')
    cursor = connection.cursor()
     # 执行更新语句
    cursor.execute(data.to_update_sql(table))
    connection.commit()
     # 关闭数据库连接
    cursor.close()
    connection.close()
 # 测试数据
user = User(id=1, name='John', age=25, email='john@example.com')
 # 调用函数更新数据
update_data('users', user)