python - 为什么我在没有设置唯一约束的数据上不断收到错误“唯一约束失败”?
问题描述
我正在创建一个程序,它从食谱中刮取信息并将其放入数据库。我已经成功地抓取了食谱的名称和网址,并将它们插入到“食谱”表中,并创建了一个充满成分的“项目”表。我现在正在尝试创建一个 recipeItemAmount 表,其中包含一个项目 ID、它所在的配方的配方 ID 以及它的数量。但是,我不断收到一个错误,即当我插入它时它使唯一约束失败,但我没有在该数据库中实现唯一约束。这是代码:
def recipe_item_amount():
'''
Grab every web page in the BBC Food sitemap and
save it as a local html file.
'''
# Cycle through the sitemap grabbing each recipe
with open('bbc_sitemap.txt', 'r') as f:
for line in f.readlines():
db = create_connection("C:\\Users\\Eva Morris\\PycharmProjects\\pantry\\instance\\flaskr.sqlite")
line = line.strip('\n')
error = None
filepath = os.path.join('BBC_Food_Repo', line.split('/')[-1] + '.html')
# Don't get pages that have already been collected
if not os.path.isfile(filepath):
page = requests.get(line)
try:
page.raise_for_status()
except requests.RequestException:
continue
soup = bs4.BeautifulSoup(page.text, 'html.parser')
# Update the data file
data = []
for i in soup.find_all('li', class_='recipe-ingredients__list-item'):
data.append(i)
for i, datum in enumerate(data):
if data[i]:
data[i] = datum.text.replace('\n', '')
else:
data[i] = ''
name = [soup.find('h1', class_='content-title__text'),
]
for i, datum in enumerate(name):
if name[i]:
name[i] = datum.text.replace('\n', '')
else:
name[i] = ''
title = name[0].strip('\"')
ingredientAmount = 0
ingredients = data[2:-1]
for i in range(len(ingredients)):
ingredients[i] = ingredients[i].split()
for j in range(0, len(ingredients[i])-1):
if 48 <= ord(ingredients[i][j][0]) <= 57:
ingredientAmount = str(ingredients[i][j])
elif (
db.execute("SELECT itemID FROM item WHERE itemName = ?",
(ingredients[i][j+1] + ' ' + ingredients[i][j] + '\n',)).fetchone()
is not None
):
item = ingredients[i][j+1] + ' ' + ingredients[i][j] + '\n'
elif (
db.execute("SELECT itemID FROM item WHERE itemName = ?",
(ingredients[i][j] + '\n',)).fetchone()
is not None
):
item = ingredients[i][j] + '\n'
recipeID = db.execute('SELECT recipeID FROM recipe WHERE recipeName = (?)',(title,)).fetchone()[0]
itemID = db.execute('SELECT itemID FROM item WHERE itemName = (?)', (item,)).fetchone()[0]
db.execute('INSERT INTO recipeItemAmount VALUES (?, ?, ?) ', (recipeID, itemID, ingredientAmount))
db.commit()
break
db.close()
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS item;
DROP TABLE IF EXISTS recipe;
DROP TABLE IF EXISTS recipeItemAmount;
DROP TABLE IF EXISTS pantry;
CREATE TABLE user (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
password TEXT NOT NULL
);
CREATE TABLE pantry (
itemID INT,
quantity TEXT NOT NULL,
FOREIGN KEY (itemID) REFERENCES item (itemID),
PRIMARY KEY (itemID)
);
CREATE TABLE recipe (
recipeID INTEGER PRIMARY KEY AUTOINCREMENT,
recipeName TEXT UNIQUE,
weblink TEXT UNIQUE
);
CREATE TABLE item (
itemID INTEGER PRIMARY KEY AUTOINCREMENT,
itemName TEXT NOT NULL,
brand TEXT
);
CREATE TABLE recipeItemAmount(
recipeID INT,
itemID INT,
quantity TEXT,
FOREIGN KEY (recipeID) REFERENCES recipe(recipeID),
FOREIGN KEY (itemID) REFERENCES item (itemID),
PRIMARY KEY (recipeID, itemID)
)
和错误信息:
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "C:\Program Files\JetBrains\PyCharm 2019.2.2\helpers\pydev\_pydev_bundle\pydev_umd.py", line 197, in runfile
pydev_imports.execfile(filename, global_vars, local_vars) # execute the script
File "C:\Program Files\JetBrains\PyCharm 2019.2.2\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
exec(compile(contents+"\n", file, 'exec'), glob, loc)
File "C:/Users/Eva Morris/PycharmProjects/pantry/flaskr/BBCscraper/scraperecipes.py", line 202, in <module>
recipe_item_amount()
File "C:/Users/Eva Morris/PycharmProjects/pantry/flaskr/BBCscraper/scraperecipes.py", line 178, in recipe_item_amount
db.execute('INSERT INTO recipeItemAmount VALUES (?, ?, ?) ', (recipeID, itemID, ingredientAmount))
sqlite3.IntegrityError: UNIQUE constraint failed: recipeItemAmount.recipeID, recipeItemAmount.itemID
解决方案
主键是唯一键。PRIMARY KEY (recipeID, itemID)
是主键,recipeItemAmount
所以这两个键必须是唯一的。
推荐阅读
- angular - 在 angular2+ 应用程序中指定谷歌地图键的最佳实践
- android - Android 下载管理器不适合我
- django - 如何动态地将两个内联表单集添加到表单中?
- javascript - Javascript 替换为 3 个参数
- visual-studio-2017 - 如何在 VS 2017 扩展项目中访问剪贴板
- elasticsearch - Elasticsearch - 在嵌套聚合桶上应用多级过滤器?
- xslt - 在 XSLT 中转义标签 <#>
- base64 - 如何使用 Python 解码编码的 zipfile?
- c# - 从soap11切换到soap12
- node.js - 对于这个用例,最有效的 Firestore 模型是什么?