-
Notifications
You must be signed in to change notification settings - Fork 0
/
recipe_db.py
78 lines (66 loc) · 2.91 KB
/
recipe_db.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
__author__ = 'hammy'
import sys
import sqlite3
import hashlib
import inspect
import os
import traceback
class RecipeDB:
CURRENT_DIR = os.path.dirname(os.path.abspath(inspect.getfile(inspect.currentframe())))
DATABASE_PATH = '%s/FoodData.db' % CURRENT_DIR
def __init__(self):
print "Opening database connection to '%s'" % RecipeDB.DATABASE_PATH
self.connection = sqlite3.connect(database=RecipeDB.DATABASE_PATH)
self.connection.text_factory = sqlite3.OptimizedUnicode
def close(self):
self.connection.close()
def __delete_recipe(self, recipeid):
recipeid = str(recipeid)
recipequery = 'DELETE FROM recipes WHERE id=?;'
recipecursor = self.connection.cursor()
recipecursor.execute(recipequery, (recipeid,))
recipecursor.close()
foodquery = 'DELETE FROM ingredients WHERE recipe_id=?;'
foodcursor = self.connection.cursor()
foodcursor.execute(foodquery, (recipeid,))
foodcursor.close()
def __insert_recipe(self, hashid, recipeitem):
name = recipeitem['name']
address = recipeitem['url']
servings = recipeitem['servings']
recipedata = (name, servings, address, hashid)
recipequery = 'INSERT INTO recipes (recipe, servings, address, recipe_sha256) VALUES(?, ?, ?, ?);'
self.connection.execute(recipequery, recipedata)
locatequery = 'SELECT id FROM recipes WHERE recipe_sha256=?;'
recipeid = self.connection.execute(locatequery, (hashid,)).fetchone()[0]
def ingredient_iter():
for ingredient in recipeitem['ingredients']:
food = ingredient['food']
amount = ingredient['amount']
index = ingredient['index']
use = ingredient['use']
yield (recipeid, amount, use, index, food)
itemquery = 'INSERT INTO ingredients (recipe_id, amount, use, swap_index, name) VALUES(?, ?, ?, ?, ?);'
self.connection.executemany(itemquery, ingredient_iter())
def insert_recipe(self, recipeitem):
name = recipeitem['name']
hashid = hashlib.sha256()
hashid.update(name.encode('unicode_escape'))
hashid = hashid.hexdigest()
query = 'SELECT id FROM recipes WHERE recipe_sha256=?;'
cursor = self.connection.cursor()
cursor.execute(query, (hashid,))
idlist = cursor.fetchall()
try:
for recipeid in idlist:
self.__delete_recipe(recipeid)
self.__insert_recipe(hashid, recipeitem)
self.connection.commit()
except sqlite3.InterfaceError as e:
self.connection.rollback()
sys.stderr.write('---------------------------------------\n')
print traceback.print_exc(file=sys.stderr)
sys.stderr.write('---------------------------------------\n')
raise e
if __name__ == '__main__':
print RecipeDB.DATABASE_PATH