如何使用Python将含多值的JSON文件导入SQLite数据库并生成itemid

1. 修正原有建表逻辑的错误 #

你原有代码的建表语句存在两处会直接运行失败的问题，需要先调整：

• items表不能声明两个独立的主键，需要改为(orderid, itemid)联合主键，保证同一个订单内的商品itemid唯一
• charges、payment表需要先定义外键关联的orderid字段，再声明外键约束
  修正后的建表语句如下：

sql_create_items_table = """ CREATE TABLE IF NOT EXISTS items (
                                    orderid integer,
                                    itemid integer, 
                                    name text,
                                    price numeric,
                                    PRIMARY KEY (orderid, itemid)); """

sql_create_charges_table = """CREATE TABLE IF NOT EXISTS charges (
                                orderid integer PRIMARY KEY,
                                date datetime,
                                subtotal numeric,
                                taxes numeric, 
                                total numeric,
                                FOREIGN KEY (orderid) REFERENCES items (orderid));"""

sql_create_payment_table = """CREATE TABLE IF NOT EXISTS payment (
                                orderid integer PRIMARY KEY,
                                card_type text,
                                card_last4 text,
                                zip text, 
                                cardholder text,
                                method text,
                                FOREIGN KEY (orderid) REFERENCES items (orderid));"""

注意：payment表的银行卡号只存后4位，所以字段类型调整为text，和JSON中的字段格式匹配

2. 新增JSON数据导入逻辑 #

你需要引入json模块读取解析JSON文件，遍历订单列表生成全局唯一的orderid，再遍历每个订单下的商品列表，用enumerate生成订单内唯一的itemid，依次写入三张表即可，完整代码如下：

import sqlite3
import json
from sqlite3 import Error


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
    return conn


def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

def insert_order_data(conn, order_data, orderid):
    """
    插入单个订单的所有数据到三张表
    :param conn: 数据库连接
    :param order_data: 单个订单的JSON数据
    :param orderid: 当前订单的全局唯一id
    """
    cur = conn.cursor()
    # 插入商品列表，生成订单内唯一itemid
    for itemid, item in enumerate(order_data['items'], start=1):
        cur.execute("INSERT INTO items (orderid, itemid, name, price) VALUES (?, ?, ?, ?)",
                    (orderid, itemid, item['name'], item['price']))
    # 插入账单数据
    charge = order_data['charges']
    cur.execute("INSERT INTO charges (orderid, date, subtotal, taxes, total) VALUES (?, ?, ?, ?, ?)",
                (orderid, charge['date'], charge['subtotal'], charge['taxes'], charge['total']))
    # 插入支付数据
    payment = order_data['payment']
    cur.execute("INSERT INTO payment (orderid, card_type, card_last4, zip, cardholder, method) VALUES (?, ?, ?, ?, ?, ?)",
                (orderid, payment['card_type'], payment['last_4_card_number'], payment['zip'], payment['cardholder'], payment['method']))
    conn.commit()

def main():
    database = r"C:\sqlite\db\pythonsqlite.db"
    # 建表语句用上面修正后的版本
    sql_create_items_table = """ CREATE TABLE IF NOT EXISTS items (
                                        orderid integer,
                                        itemid integer, 
                                        name text,
                                        price numeric,
                                        PRIMARY KEY (orderid, itemid)); """

    sql_create_charges_table = """CREATE TABLE IF NOT EXISTS charges (
                                    orderid integer PRIMARY KEY,
                                    date datetime,
                                    subtotal numeric,
                                    taxes numeric, 
                                    total numeric,
                                    FOREIGN KEY (orderid) REFERENCES items (orderid));"""

    sql_create_payment_table = """CREATE TABLE IF NOT EXISTS payment (
                                    orderid integer PRIMARY KEY,
                                    card_type text,
                                    card_last4 text,
                                    zip text, 
                                    cardholder text,
                                    method text,
                                    FOREIGN KEY (orderid) REFERENCES items (orderid));"""

    # 创建数据库连接
    conn = create_connection(database)

    # 创建表
    if conn is not None:
        create_table(conn, sql_create_items_table)
        create_table(conn, sql_create_charges_table)
        create_table(conn, sql_create_payment_table)
        
        # 读取并导入JSON数据，替换为你的JSON文件路径
        with open(r'你的JSON文件路径.json', 'r', encoding='utf-8') as f:
            json_data = json.load(f)
        # 遍历所有订单，orderid从1开始自增
        for orderid, order in enumerate(json_data['orders'], start=1):
            insert_order_data(conn, order, orderid)
        conn.close()
    else:
        print("Error! cannot create the database connection.")


if __name__ == '__main__':
    main()

核心逻辑说明 #

• 全局orderid通过遍历所有订单列表的enumerate生成，保证每个订单唯一
• 单个订单内的itemid通过遍历商品列表的enumerate生成，start=1保证从1开始计数，符合你要的计数变量需求
• 你提供的JSON示例存在语法错误（orders数组缺少闭合]），实际使用前请保证JSON格式合法

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