如何将Python列表元素插入SQL数据表并设置Location为外键

• 原INSERT语句SQL语法不完整，缺少列名声明、VALUES关键字，且SQL字符串未闭合，无法被数据库解析
• 不能直接在SQL字符串中写入Python变量Location[0]，需通过参数绑定的方式传入变量值
• 未提前创建符合外键约束的表结构，sqlite默认关闭外键校验，需要手动开启才能实现你要的关联查询逻辑
• 缺少国家名称到LocationID的映射逻辑，无法给城市匹配正确的外键值

import sqlite3

# 1. 定义国家和ID的映射关系
location_id_map = {
    "United Kingdom": 1,
    "France": 2,
    "Denmark": 3,
    "Germany": 4
}
# 定义城市所属国家的对应关系
place_location_map = {
    "London": "United Kingdom",
    "Tottenham": "United Kingdom",
    "Munich": "Germany",
    "Paris": "France",
    "Frankfurt": "Germany"
}

conn = sqlite3.connect("Location.db")
c = conn.cursor()
# 开启sqlite外键约束
c.execute("PRAGMA foreign_keys = ON")

# 2. 先创建Location主表
c.execute('''
CREATE TABLE IF NOT EXISTS Location (
    LocationID INTEGER PRIMARY KEY,
    CountryName TEXT NOT NULL UNIQUE
)
''')
# 插入国家数据到主表
for country, lid in location_id_map.items():
    c.execute("INSERT OR IGNORE INTO Location (LocationID, CountryName) VALUES (?, ?)", (lid, country))

# 3. 创建Places从表，绑定外键
c.execute('''
CREATE TABLE IF NOT EXISTS Places (
    PlaceID INTEGER PRIMARY KEY AUTOINCREMENT,
    PlaceName TEXT NOT NULL,
    LocationID INTEGER NOT NULL,
    FOREIGN KEY (LocationID) REFERENCES Location(LocationID)
)
''')

# 4. 批量插入城市数据
for place, country in place_location_map.items():
    lid = location_id_map[country]
    # ?是sqlite的参数占位符，后面传元组绑定参数，避免SQL注入
    c.execute("INSERT INTO Places (PlaceName, LocationID) VALUES (?, ?)", (place, lid))

conn.commit()
conn.close()

插入完成后你可以执行以下语句查询英国所有城市：

SELECT PlaceName FROM Places WHERE LocationID = 1

即可返回London、Tottenham两条结果，符合你后续业务扩展的查询需求。

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