Python+SQLite创建公共书橱数据库报错，请求技术排查

我有Python基础，正在进一步提升技能，练习用Python和SQLite创建一个记录阿姆斯特丹街头公共书橱位置及书籍信息的数据库。跟着教程写的代码运行报错，使用VS Code作为开发环境。

我的代码

import sqlite3

connection = sqlite3.connect('boekenkast.db')

cursor = connection.cursor()

commmand1 = """CREATE TABLE IF NOT EXISTS
boekenkast(boekenkast_id INTEGER PRIMARY KEY, location TEXT, latitude FLOAT, longitude FLOAT)"""

cursor.execute(commmand1)

command2 = """CREATE TABLE IF NOT EXISTS
boeken(boeken_id INTEGER PRIMARY KEY, writer TEXT, title TEXT, FOREIGN KEY(boekenkast_id) REFERENCES boekenkast(boekenkast_id))"""

cursor.execute(command2)

# add book shelfs to boekenkast

cursor.execute("INSERT INTO boekenkast VALUES (0001, 'Meeuwenlaan 21', 52.38330303722245, 4.912016781394647)")
cursor.execute("INSERT INTO boekenkast VALUES (0002, 'Pasteurstraat 11', 52.34968085037797, 4.935466766049933)")

# add books to boeken

cursor.execute("INSERT INTO boeken VALUES (00001, 'Gerard Reve', 'De avonden', 0001)")
cursor.execute("INSERT INTO boeken VALUES (00002, 'Anya Niewierra', 'De camino', 0002)")

cursor.execute("SELECT * FROM boeken")

results = cursor.fetchall()

print(results)

报错信息

PS C:\Users\gebruiker\OneDrive\Bureaublad\Programmeren\Bookadaba\Bookadaba> & c:/Users/gebruiker/OneDrive/Bureaublad/Programmeren/Bookadaba/venv/Scripts/python.exe c:/Users/gebruiker/OneDrive/Bureaublad/Programmeren/Bookadaba/Bookadaba/Bookadaba/database_utils.py
Traceback (most recent call last):
  File "c:\Users\gebruiker\OneDrive\Bureaublad\Programmeren\Bookadaba\Bookadaba\Bookadaba\database_utils.py", line 15, in <module>
    cursor.execute(command2)

问题排查与修复 #

1. 外键字段未定义
   创建boeken表时，你声明了外键boekenkast_id，但表结构里根本没这个字段。必须先在表中添加该字段，再设置外键约束。修改后的command2应该是：

   CREATE TABLE IF NOT EXISTS
   boeken(boeken_id INTEGER PRIMARY KEY, writer TEXT, title TEXT, boekenkast_id INTEGER, FOREIGN KEY(boekenkast_id) REFERENCES boekenkast(boekenkast_id))
   

2. 前导零数字的问题
   插入数据时用了0001这种带前导零的数字，Python会把它解析为八进制数，可能导致数值异常。直接用普通整数即可，比如1、2。

3. 未提交事务
   SQLite默认需要手动提交事务，否则插入的数据不会保存到数据库。操作完成后要调用connection.commit()，最后记得关闭连接释放资源。

修正后的完整代码 #

import sqlite3

connection = sqlite3.connect('boekenkast.db')
cursor = connection.cursor()

# 创建书橱表
command1 = """CREATE TABLE IF NOT EXISTS
boekenkast(boekenkast_id INTEGER PRIMARY KEY, location TEXT, latitude FLOAT, longitude FLOAT)"""
cursor.execute(command1)

# 创建书籍表，补充外键字段
command2 = """CREATE TABLE IF NOT EXISTS
boeken(boeken_id INTEGER PRIMARY KEY, writer TEXT, title TEXT, boekenkast_id INTEGER, FOREIGN KEY(boekenkast_id) REFERENCES boekenkast(boekenkast_id))"""
cursor.execute(command2)

# 插入书橱数据
cursor.execute("INSERT INTO boekenkast VALUES (1, 'Meeuwenlaan 21', 52.38330303722245, 4.912016781394647)")
cursor.execute("INSERT INTO boekenkast VALUES (2, 'Pasteurstraat 11', 52.34968085037797, 4.935466766049933)")

# 插入书籍数据
cursor.execute("INSERT INTO boeken VALUES (1, 'Gerard Reve', 'De avonden', 1)")
cursor.execute("INSERT INTO boeken VALUES (2, 'Anya Niewierra', 'De camino', 2)")

# 提交事务
connection.commit()

# 查询并打印结果
cursor.execute("SELECT * FROM boeken")
results = cursor.fetchall()
print(results)

# 关闭连接
connection.close()

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