模式SQL:动态日期参数

CREATE TABLE sales (
    id INT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

import sqlite3
from datetime import datetime

# 连接到数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 定义日期范围
start_date = '2023-01-01'
end_date = '2023-12-31'

# 构建动态SQL查询
query = f"SELECT * FROM sales WHERE sale_date BETWEEN '{start_date}' AND '{end_date}';"

# 执行查询
cursor.execute(query)

# 获取结果
results = cursor.fetchall()

# 打印结果
for row in results:
    print(row)

# 关闭连接
conn.close()

query = "SELECT * FROM sales WHERE sale_date BETWEEN ? AND ?;"
cursor.execute(query, (start_date, end_date))

try:
    start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
    end_date = datetime.strptime(end_date, '%Y-%m-%d').date()
except ValueError:
    print("日期格式错误，请使用YYYY-MM-DD格式")

CREATE INDEX idx_sale_date ON sales(sale_date);