现在我已经看到了一个实际的终端，下面是对代码的重构，它运行得很好。
无论如何，我们的想法是，一旦我们从API下载了一个文件，我们就可以通过它来获得完整的列列表，基于此创建一个表，然后使用相同的列列表来生成行（其中包含None s，用于特定原始行中不存在的列;并且在默认情况下，SQLite无法处理的数据将被转换回JSON存储）。

import json
import sqlite3

import requests

connection = sqlite3.connect("JSONTestDB.sqlite3")

def jsonify_complex_value(val):
    if isinstance(val, (dict, list)):
        return json.dumps(val)
    return val

def create_table_from_list(table_name, list_url):
    print(f"Downloading {table_name} from {list_url}")
    data_resp = requests.get(list_url)
    data_resp.raise_for_status()
    data = data_resp.json()
    assert isinstance(data, list)
    # Get all the columns
    all_columns = set()
    for row in data:
        all_columns.update(row.keys())

    # Some columns may be reserved words in SQLite, so we need to quote them
    # (but here's hoping there's nothing that needs more escaping)
    columns_for_sql = ",".join(f'"{col}"' for col in all_columns)
    qmarks_for_sql = ", ".join("?" * len(all_columns))

    print(f"{table_name} has {len(all_columns)} columns and {len(data)} rows")

    cursor = connection.cursor()
    # Do everything here in a transaction for speed
    cursor.execute("BEGIN TRANSACTION")
    # Create the table
    create_table_sql = (
        f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_for_sql})"
    )
    cursor.execute(create_table_sql)

    # Create the insert statement
    insert_sql = f"INSERT INTO {table_name} ({columns_for_sql}) VALUES ({qmarks_for_sql})"

    # Define a generator that will replace missing columns with None;
    # also, convert complex values to JSON.
    row_gen = (
        tuple(jsonify_complex_value(row.get(col)) for col in all_columns)
        for row in data
    )

    # Insert the data by running `insert_sql` in bulk
    cursor.executemany(insert_sql, row_gen)

    # Commit the transaction, we're done
    connection.commit()

def main():
    bulk_data_resp = requests.get("https://api.scryfall.com/bulk-data")
    bulk_data_resp.raise_for_status()
    for obj in bulk_data_resp.json()["data"]:
        table_name = obj["type"]
        url = obj["download_uri"]
        if table_name not in ("oracle_cards", "default_cards"):
            # Skip things we don't care about for this example
            print("Skipping", table_name)
            continue
        create_table_from_list(table_name, url)

if __name__ == "__main__":
    main()

字符串
要验证数据是否存在，并且可能包含蛇发女妖：

$ sqlite3 JSONTestDB.sqlite3 'select name from oracle_cards order by random() limit 1;'
Vraska the Unseen

型