如何使用pandas创建一对多关系并添加数量列适配SQL表结构

完全可以通过pandas结合SQLite的to_sql方法完成该转换需求，具体操作步骤如下：

第一步：转换DataFrame结构 #

首先将原DataFrame中按空格分隔的products字段拆分、展开并统计每个产品的出现次数：

import pandas as pd
import sqlite3

# 拆分products列为单个产品ID的整数列表
df["product_id"] = df["products"].str.split().apply(lambda x: list(map(int, x)))
# 展开为多行，每行对应一条产品记录
df_exploded = df.explode("product_id", ignore_index=True)
# 按订单、客户、产品ID分组统计产品数量
df_result = df_exploded.groupby(["id", "customer", "product_id"], as_index=False).size()
# 重命名字段匹配目标SQL schema
df_result = df_result.rename(columns={
    "id": "order_id",
    "customer": "customer_id",
    "size": "product_qty"
})

执行上述代码后得到的df_result就和你给出的预期输出格式完全一致。

第二步：写入SQLite数据库 #

可以直接调用pandas的to_sql方法写入数据库，如果需要设置联合主键，建议先手动建表再写入：

# 连接SQLite数据库
conn = sqlite3.connect("test.db")
cursor = conn.cursor()

# 先创建符合schema的表，提前设置联合主键
cursor.execute("""
CREATE TABLE IF NOT EXISTS order_products (
    order_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    product_qty INTEGER NOT NULL,
    PRIMARY KEY (order_id, customer_id, product_id)
)
""")
conn.commit()

# 将处理好的DataFrame写入表中
df_result.to_sql(
    name="order_products",
    con=conn,
    if_exists="append",
    index=False
)

# 关闭连接
conn.close()

如果不需要提前设置主键，也可以省略建表步骤，直接调用to_sql方法自动创建表即可。

内容的提问来源于stack exchange，提问作者nbari