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

# 連接到SQLite數(shù)據(jù)庫(kù)
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查詢語(yǔ)句
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ù)庫(kù)連接
conn.close()

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

# 生成插入數(shù)據(jù)的SQL語(yǔ)句
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語(yǔ)句
sql_update = f"UPDATE employees SET salary = ? WHERE id = ?"

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

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

# 生成刪除數(shù)據(jù)的SQL語(yǔ)句
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ù)庫(kù)連接引擎
engine = create_engine('sqlite:///example.db', echo=True)

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

# 定義Employee類(lèi)
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)建會(huì)話
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)閉會(huì)話
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ù)庫(kù)連接
db = SqliteDatabase('example.db')

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

    class Meta:
        database = db

# 連接到數(shù)據(jù)庫(kù)并創(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ù)庫(kù)連接
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語(yǔ)句生成器
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語(yǔ)句生成器
generator = SQLStatementGenerator('employees')

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

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

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