# 導(dǎo)入所需的庫
import sqlite3

# 連接到SQLite數(shù)據(jù)庫
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 創(chuàng)建employees表（如果不存在）
cursor.execute('''CREATE TABLE IF NOT EXISTS employees
                  (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, salary REAL)''')

# 插入一些示例數(shù)據(jù)
cursor.execute("INSERT INTO employees (name, age, salary) VALUES (?, ?, ?)", ('Alice', 30, 6000))
cursor.execute("INSERT INTO employees (name, age, salary) VALUES (?, ?, ?)", ('Bob', 35, 7000))
cursor.execute("INSERT INTO employees (name, age, salary) VALUES (?, ?, ?)", ('Charlie', 25, 4500))
cursor.execute("INSERT INTO employees (name, age, salary) VALUES (?, ?, ?)", ('David', 40, 5500))

# 提交更改
conn.commit()

# 生成SQL查詢語句
min_salary = 5000
sql_query = f"SELECT * FROM employees WHERE salary > {min_salary}"

# 執(zhí)行查詢并輸出結(jié)果
cursor.execute(sql_query)
result = cursor.fetchall()
for row in result:
    print(row)

# 關(guān)閉數(shù)據(jù)庫連接
conn.close()

# 新員工信息
new_employee = ('Eva', 28, 6000)

# 生成插入數(shù)據(jù)的SQL語句
sql_insert = f"INSERT INTO employees (name, age, salary) VALUES (?, ?, ?)"

# 執(zhí)行插入操作
cursor.execute(sql_insert, new_employee)
conn.commit()

# 更新員工工資的信息
employee_id = 1  # 假設(shè)要更新ID為1的員工的工資信息
new_salary = 6500

# 生成更新數(shù)據(jù)的SQL語句
sql_update = f"UPDATE employees SET salary = ? WHERE id = ?"

# 執(zhí)行更新操作
cursor.execute(sql_update, (new_salary, employee_id))
conn.commit()

# 刪除員工信息
employee_id = 4  # 假設(shè)要刪除ID為4的員工的信息

# 生成刪除數(shù)據(jù)的SQL語句
sql_delete = f"DELETE FROM employees WHERE id = ?"

# 執(zhí)行刪除操作
cursor.execute(sql_delete, (employee_id,))
conn.commit()

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 創(chuàng)建數(shù)據(jù)庫連接引擎
engine = create_engine('sqlite:///example.db', echo=True)

# 創(chuàng)建基類
Base = declarative_base()

# 定義Employee類
class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    salary = Column(Integer)

# 創(chuàng)建表
Base.metadata.create_all(engine)

# 創(chuàng)建會話
Session = sessionmaker(bind=engine)
session = Session()

# 插入新員工
new_employee = Employee(name='Fiona', age=26, salary=6200)
session.add(new_employee)
session.commit()

# 查詢工資大于5000的員工
results = session.query(Employee).filter(Employee.salary > 5000).all()
for employee in results:
    print(employee.name, employee.age, employee.salary)

# 關(guān)閉會話
session.close()

# 安全的參數(shù)化查詢示例
min_salary = 5000
sql_query = "SELECT * FROM employees WHERE salary > ?"

# 執(zhí)行查詢并輸出結(jié)果
cursor.execute(sql_query, (min_salary,))
result = cursor.fetchall()
for row in result:
    print(row)

from peewee import SqliteDatabase, Model, TextField, IntegerField

# 創(chuàng)建數(shù)據(jù)庫連接
db = SqliteDatabase('example.db')

# 創(chuàng)建模型類
class Employee(Model):
    name = TextField()
    age = IntegerField()
    salary = IntegerField()

    class Meta:
        database = db

# 連接到數(shù)據(jù)庫并創(chuàng)建表
db.connect()
db.create_tables([Employee])

# 插入新員工
new_employee = Employee(name='Grace', age=29, salary=6300)
new_employee.save()

# 查詢工資大于5000的員工
results = Employee.select().where(Employee.salary > 5000)
for employee in results:
    print(employee.name, employee.age, employee.salary)

# 關(guān)閉數(shù)據(jù)庫連接
db.close()

class SQLStatementGenerator:
    def __init__(self, table_name):
        self.table_name = table_name
        self.columns = []
        self.conditions = []

    def select(self, *columns):
        self.columns.extend(columns)
        return self

    def where(self, condition):
        self.conditions.append(condition)
        return self

    def build(self):
        if not self.columns:
            columns = '*'
        else:
            columns = ', '.join(self.columns)

        if self.conditions:
            where_clause = ' WHERE ' + ' AND '.join(self.conditions)
        else:
            where_clause = ''

        sql_query = f"SELECT {columns} FROM {self.table_name}{where_clause}"
        return sql_query

# 使用自定義SQL語句生成器
generator = SQLStatementGenerator('employees')
sql_query = generator.select('name', 'age', 'salary').where('salary > 5000').build()
print(sql_query)

class SQLStatementGenerator:
    def __init__(self, table_name):
        self.table_name = table_name
        self.columns = []
        self.conditions = []

    def select(self, *columns):
        self.columns.extend(columns)
        return self

    def where(self, condition):
        self.conditions.append(condition)
        return self

    def build_select(self):
        if not self.columns:
            columns = '*'
        else:
            columns = ', '.join(self.columns)

        if self.conditions:
            where_clause = ' WHERE ' + ' AND '.join(self.conditions)
        else:
            where_clause = ''

        sql_query = f"SELECT {columns} FROM {self.table_name}{where_clause}"
        return sql_query

    def build_insert(self, values):
        columns = ', '.join(values.keys())
        placeholders = ', '.join(['?' for _ in values.values()])
        sql_query = f"INSERT INTO {self.table_name} ({columns}) VALUES ({placeholders})"
        return sql_query, tuple(values.values())

    def build_update(self, values):
        set_clause = ', '.join([f"{column} = ?" for column in values.keys()])
        if self.conditions:
            where_clause = ' WHERE ' + ' AND '.join(self.conditions)
        else:
            where_clause = ''
        sql_query = f"UPDATE {self.table_name} SET {set_clause}{where_clause}"
        return sql_query, tuple(values.values())

    def build_delete(self):
        if self.conditions:
            where_clause = ' WHERE ' + ' AND '.join(self.conditions)
        else:
            where_clause = ''
        sql_query = f"DELETE FROM {self.table_name}{where_clause}"
        return sql_query

# 使用擴(kuò)展后的自定義SQL語句生成器
generator = SQLStatementGenerator('employees')

# 生成插入語句
insert_values = {'name': 'John', 'age': 32, 'salary': 7000}
insert_query, insert_params = generator.build_insert(insert_values)
print(insert_query)
print(insert_params)

# 生成更新語句
update_values = {'age': 33, 'salary': 7200}
update_query, update_params = generator.where('name = "John"').build_update(update_values)
print(update_query)
print(update_params)

# 生成刪除語句
delete_query = generator.where('name = "John"').build_delete()
print(delete_query)