在SQL的where子句中传递Dataframe列(列的类型为字符串)

import pandas as pd
from sqlalchemy import create_engine

# 创建一个示例DataFrame
data = {'id': [1, 2, 3], 'category': ['A', 'B', 'A']}
df = pd.DataFrame(data)

# 数据库连接（这里以SQLite为例）
engine = create_engine('sqlite:///example.db')

# 将DataFrame写入数据库（如果表不存在则创建）
df.to_sql('my_table', engine, if_exists='replace', index=False)

# 获取DataFrame中的'category'列的唯一值
categories = df['category'].unique()

# 构建SQL查询
sql_query = "SELECT * FROM my_table WHERE category IN ({})".format(', '.join('?' for _ in categories))

# 执行SQL查询
with engine.connect() as conn:
    result = pd.read_sql(sql_query, conn, params=categories)

print(result)

# 使用参数化查询
sql_query = "SELECT * FROM my_table WHERE category IN :categories"
with engine.connect() as conn:
    result = pd.read_sql(sql_query, conn, params={'categories': tuple(categories)})

print(result)