[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[GNUtrition-commits] /srv/bzr/gnutrition/trunk r14: Several small bugfix
|
From: |
Thomas Sinclair |
|
Subject: |
[GNUtrition-commits] /srv/bzr/gnutrition/trunk r14: Several small bugfixes. Most related to SQL queries. |
|
Date: |
Sun, 30 Sep 2012 19:13:51 -0400 |
|
User-agent: |
Bazaar (2.5.0) |
------------------------------------------------------------
revno: 14
committer: Thomas Sinclair <address@hidden>
branch nick: trunk
timestamp: Sun 2012-09-30 19:13:51 -0400
message:
Several small bugfixes. Most related to SQL queries.
modified:
src/database.py
src/date_widget.py
src/food_srch_dlg.py
src/nutr_composition_dlg.py
src/nutr_goal_dlg.py
src/person.py
src/plan_compute_dlg.py
src/plan_win.py
src/recipe_srch_dlg.py
src/recipe_srch_res_dlg.py
src/recipe_win.py
src/store.py
=== modified file 'src/database.py'
--- a/src/database.py 2012-09-26 05:09:15 +0000
+++ b/src/database.py 2012-09-30 23:13:51 +0000
@@ -120,7 +120,7 @@
self.create_load_table("CREATE TABLE fd_group " +
"(FdGrp_Cd INTEGER PRIMARY KEY NOT NULL, " +
"FdGrp_Desc TEXT NOT NULL)",
- ### Insert statement
+ ### Insert statement for one row
"INSERT INTO 'fd_group' VALUES (?, ?)",
'fd_group')
@@ -134,7 +134,6 @@
"Num_Data_Pts REAL NOT NULL, " +
"Std_Error REAL, " +
"Src_Cd TEXT NOT NULL, " +
- # New fields in sr24
"Deriv_Cd TEXT, " +
"Ref_NDB_No TEXT, " +
"Add_Nutr_Mark TEXT, " +
@@ -148,7 +147,7 @@
"AddMod_Date TEXT, " +
"CC TEXT, " +
"PRIMARY KEY(NDB_No, Nutr_No))",
- ### Insert statement
+ ### Insert statement for one row
"INSERT INTO 'nut_data' VALUES " +
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
'nut_data')
@@ -164,7 +163,7 @@
# Two new in sr24
"Num_Dec INTEGER NOT NULL, " +
"SR_Order INTEGER NOT NULL)",
- ### Insert statement
+ ### Insert statement for one row
"INSERT INTO 'nutr_def' VALUES " +
"(?, ?, ?, ?, ?, ?)",
'nutr_def')
@@ -190,48 +189,32 @@
"Num_Data_Pts INTEGER, " +
"Std_Dev REAL, " +
"PRIMARY KEY(NDB_No, Seq))",
- ### Insert statement
+ ### Insert statement for one row
"INSERT INTO 'weight' VALUES " +
"(?, ?, ?, ?, ?, ?, ?)",
'weight')
# May have user data from previous install that we don't want to lose
- try:
- self.query("SELECT name FROM sqlite_master WHERE type='table'")
- except self.Error, sqlerr:
- self.con.rollback()
- import sys
- print 'Error :', sqlerr, '\nquery:', sql
- if caller: print 'Caller ', caller
- sys.exit()
- search = ['recipe', 'ingredient', 'preparation', 'person',
- 'food_plan', 'recipe_plan', 'nutr_goal']
- tables = []
- for t in self.get_result():
- if t[0] in search:
- tables.append(t[0])
+ # so IF NOT EXISTS is used
# create recipe table
- if not 'recipe' in tables:
- self.create_table("CREATE TABLE recipe " +
+ # Note: Want index on recipe_name, category_no?
+ self.create_table("CREATE TABLE IF NOT EXISTS recipe" +
"(recipe_no INTEGER PRIMARY KEY AUTOINCREMENT, " +
"recipe_name TEXT NOT NULL, " +
"no_serv INTEGER NOT NULL, " +
"no_ingr INTEGER NOT NULL, " +
"category_no INTEGER NOT NULL)", 'recipe')
- # Want index on recipe_name, category_no
# create ingredient table
- if not 'ingredient' in tables:
- self.create_table("CREATE TABLE ingredient " +
- "(recipe_no NOT NULL, " +
+ self.create_table("CREATE TABLE IF NOT EXISTS ingredient" +
+ "(recipe_no INTEGER NOT NULL, " +
"amount REAL NOT NULL, " +
"Msre_Desc TEXT NOT NULL, " +
"NDB_No INTEGER NOT NULL)", 'ingredient')
# create recipe category table
- self.query("DROP TABLE IF EXISTS category")
- self.create_load_table("CREATE TABLE category " +
+ self.create_load_table("CREATE TABLE IF NOT EXISTS category" +
"(category_no INTEGER PRIMARY KEY NOT NULL, " +
"category_desc TEXT NOT NULL)",
### Insert statement
@@ -239,22 +222,19 @@
'category')
# create recipe preparation table
- if not 'preparation' in tables:
- self.create_table("CREATE TABLE preparation " +
+ self.create_table("CREATE TABLE IF NOT EXISTS preparation" +
"(recipe_no INTEGER PRIMARY KEY NOT NULL, " +
"prep_time TEXT, " +
"prep_desc TEXT)", 'preparation')
# create person table
- if not 'person' in tables:
- self.create_table("CREATE TABLE person " +
+ self.create_table("CREATE TABLE IF NOT EXISTS person" +
"(person_no INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
"person_name TEXT, " +
"user_name TEXT)", 'person')
# create food_plan table
- if not 'food_plan' in tables:
- self.create_table("CREATE TABLE food_plan " +
+ self.create_table("CREATE TABLE IF NOT EXISTS food_plan" +
"(person_no INTEGER NOT NULL, " +
"date TEXT NOT NULL, " +
"time TEXT NOT NULL, " +
@@ -263,8 +243,7 @@
"NDB_No INTEGER NOT NULL)", 'food_plan')
# create recipe_plan table
- if not 'recipe_plan' in tables:
- self.create_table("CREATE TABLE recipe_plan " +
+ self.create_table("CREATE TABLE IF NOT EXISTS recipe_plan" +
"(person_no INTEGER NOT NULL, " +
"date TEXT NOT NULL, " +
"time TEXT NOT NULL, " +
@@ -272,8 +251,7 @@
"recipe_no INTEGER NOT NULL)", 'recipe_plan')
# create nutr_goal table
- if not 'nutr_goal' in tables:
- self.create_table("CREATE TABLE nutr_goal " +
+ self.create_table("CREATE TABLE IF NOT EXISTS nutr_goal" +
"(person_no INTEGER NOT NULL, " +
"Nutr_No INTEGER NOT NULL, " +
"goal_val REAL NOT NULL)", 'nutr_goal')
@@ -286,6 +264,17 @@
def curdate(self):
return curdate()
+ def show_query(self, sql, sql_params, caller=None):
+ if not caller: return
+ s = ''
+ if caller: s = '{0:s}(): '.format(caller)
+ s = s + '{0:s}'.format(sql)
+ if sql_params:
+ s = s + '\n\tparams:'
+ print s, sql_params
+ else:
+ print s
+
def query(self, sql, many=False, sql_params=None, caller=None):
"""Execute the SQL statement with given SQL parameters."""
try:
@@ -310,6 +299,8 @@
self.result = tuple(result)
self.last_query = sql
self.last_query_params = sql_params
+ # Added for debugging
+ self.show_query(sql, sql_params, caller)
def get_result(self):
result = self.result
@@ -501,15 +492,15 @@
print 'found', len(result), 'entries in food_plan table'
for i in range(len(result)):
person_no = result[i][0]
- date = result[i][1]
- time = result[i][2]
+ date = str(result[i][1])
+ time = str(result[i][2])
amount = result[i][3]
if use_msre_no:
Msre_Desc = msre_desc_from_msre_no(result[i][4])
else:
Msre_Desc = result[i][4]
NDB_No = result[i][5]
- params = (person_no, date, time, amount, Msre_Desc, NDB_No)
+ params = (person_no, date, time[:-3], amount, Msre_Desc,
NDB_No)
lite.query("INSERT INTO 'food_plan' VALUES (?,?,?,?,?,?)",
many=False, sql_params=params, caller='migrate')
# recipe_plan table
@@ -527,7 +518,7 @@
time = str(result[r][2])
no_portions = result[r][3]
recipe_no = result[r][4]
- params = (person_no, date, time, no_portions, recipe_no)
+ params = (person_no, date, time[:-3], no_portions, recipe_no)
print params
lite.query("INSERT INTO 'recipe_plan' VALUES (?,?,?,?,?)",
many=False, sql_params=params, caller='migrate')
@@ -535,13 +526,5 @@
return True
#---------------------------------------------------------------------------
if __name__ == '__main__':
- import mysql
- try:
- db = mysql.Database('gnutrition', 'gnutrition')
- except Exception:
- dialog = Dialog('error',
- "Unable to connect to MySQL's GNUtrition database.")
- else:
- sqlite = Database()
- sqlite.initialize()
- migrate('gnutrition','gnutrition')
+ print 'curdate:', curdate()
+ print 'curtime:', curtime()
=== modified file 'src/date_widget.py'
--- a/src/date_widget.py 2012-05-28 03:12:23 +0000
+++ b/src/date_widget.py 2012-09-30 23:13:51 +0000
@@ -97,7 +97,7 @@
def on_day_selected(self, w, d=None):
y, m, d = self.popup.calendar.get_date()
- self.entry.set_text('%d-%d-%d' % (y, m + 1, d))
+ self.entry.set_text('%d-%02d-%02d' % (y, m + 1, d))
self.emit('date-changed', self)
gobject.type_register(MyDateEntry)
=== modified file 'src/food_srch_dlg.py'
--- a/src/food_srch_dlg.py 2012-05-28 03:12:23 +0000
+++ b/src/food_srch_dlg.py 2012-09-30 23:13:51 +0000
@@ -114,13 +114,11 @@
where = "Long_Desc LIKE '%%%s%%'" % (txt)
if fg_desc == 'All Foods':
- self.db.query("SELECT NDB_No FROM food_des " +
- "WHERE %s" % (where))
+ self.db.query("SELECT NDB_No FROM food_des WHERE %s" % (where))
else:
fg_num = self.store.fg_desc2num[fg_desc]
self.db.query("SELECT NDB_No FROM food_des " +
- "WHERE FdGrp_Cd = '%s' AND %s"
- % (str(fg_num), where))
+ "WHERE FdGrp_Cd = %d AND %s" % (fg_num, where))
result = self.db.get_result()
food_num_list = []
@@ -174,8 +172,8 @@
for nutr_desc, constraint in constr_list:
nutr_num = dict[nutr_desc]
nutr_tot_list.append((nutr_num, '0.0', constraint))
- query = query + " Nutr_No = '%s' OR" % (str(nutr_num))
- query = query + " Nutr_No = '208' )"
+ query = query + " Nutr_No = %d OR" % (nutr_num)
+ query = query + " Nutr_No = 208 )"
dict = self.store.fg_desc2num
if fg_desc == 'All Foods':
@@ -185,7 +183,7 @@
fg_num = self.store.fg_desc2num[fg_desc]
query = ("SELECT nut_data.NDB_No, Nutr_No, Nutr_Val FROM " +
"nut_data, food_des WHERE " +
- "food_des.FdGrp_Cd = '" + str(fg_num) + "' AND " +
+ "food_des.FdGrp_Cd = {0:d} AND ".format(fg_num) +
"nut_data.NDB_No = food_des.NDB_No AND " + query)
self.db.query(query)
result = self.db.get_result()
=== modified file 'src/nutr_composition_dlg.py'
--- a/src/nutr_composition_dlg.py 2012-07-27 01:47:47 +0000
+++ b/src/nutr_composition_dlg.py 2012-09-30 23:13:51 +0000
@@ -90,11 +90,11 @@
def add_food_to_nutr_total(self, amount, msre_desc, food_num):
self.db.query("SELECT Nutr_No, Nutr_Val FROM nut_data " +
- "WHERE NDB_No ='%d'" % (food_num))
+ "WHERE NDB_No = %d" % (food_num))
list_food_nutr = self.db.get_result()
self.db.query("SELECT Gm_wgt FROM weight " +
- "WHERE NDB_No ='{0:d}' AND Msre_Desc ='{1:s}'".format(
+ "WHERE NDB_No = {0:d} AND Msre_Desc ='{1:s}'".format(
food_num, msre_desc))
gm_per_msre = self.db.get_single_result()
@@ -159,17 +159,17 @@
person_num = self.person.get_person_num()
self.db.query("SELECT Nutr_No, goal_val FROM nutr_goal " +
- "WHERE person_no ='%d'" % (person_num))
+ "WHERE person_no = %d " % (person_num))
list_nutr_goal = self.db.get_result()
dict = {}
- print 'list_nutr_tot:'
+ print 'list_nutr_tot (nutr_composition_dlg.py):'
for num, val in self.list_nutr_tot:
print 'num:', num, 'val:', val
dict[num] = val
list_pcnt_goal = []
- print 'list_nutr_goal:'
+ print 'list_nutr_goal (nutr_composition_dlg.py):'
for num, val in list_nutr_goal:
print 'num:', num, 'val:', val
if val == 0.0:
=== modified file 'src/nutr_goal_dlg.py'
--- a/src/nutr_goal_dlg.py 2012-05-28 03:12:23 +0000
+++ b/src/nutr_goal_dlg.py 2012-09-30 23:13:51 +0000
@@ -66,7 +66,7 @@
person_no = self.person.get_person_num()
self.person.db.query("SELECT Nutr_No, goal_val FROM nutr_goal " +
- "WHERE person_no = '%d'" % (person_no))
+ "WHERE person_no = %d" % (person_no))
goal_list = self.person.db.get_result()
return goal_list
@@ -76,8 +76,10 @@
# delete the old goals if necessary
self.person.db.query("DELETE FROM nutr_goal " +
- "WHERE person_no = '%d'" % (person_num))
+ "WHERE person_no = %d" % (person_num))
for nutr_num, nutr_val in goal_list:
self.person.db.query("INSERT INTO nutr_goal VALUES " +
- "('%d', '%d', '%f')" % (person_num, int(nutr_num),
float(nutr_val)))
+ "('%d', '%d', '%f')" % (person_num, int(nutr_num),
+ float(nutr_val)),
+ caller='save_goal')
=== modified file 'src/person.py'
--- a/src/person.py 2012-09-26 05:09:15 +0000
+++ b/src/person.py 2012-09-30 23:13:51 +0000
@@ -39,7 +39,7 @@
if not result:
# first name to be added to the table
person_num = 10001
- self.db.query("INSERT INTO person VALUES ('%d', '%s', '%s')"
+ self.db.query("INSERT INTO person VALUES (%d, '%s', '%s')"
% (person_num, person_name, user))
else:
match = 0
@@ -68,7 +68,8 @@
"NDB_No INTEGER NOT NULL, " +
"PRIMARY KEY (date, time, NDB_No))")
- self.db.query("CREATE TEMPORARY TABLE recipe_plan_temp " +
+ #self.db.query("CREATE TEMPORARY TABLE recipe_plan_temp " +
+ self.db.query("CREATE TABLE recipe_plan_temp " +
"(person_no INTEGER NOT NULL, " +
"date TEXT NOT NULL, " +
"time TEXT NOT NULL, " +
@@ -77,26 +78,26 @@
"PRIMARY KEY (date, recipe_no, time) )")
# copy any data from stored tables to temporary ones
- self.db.query("SELECT * FROM food_plan WHERE person_no = '%d'"
+ self.db.query("SELECT * FROM food_plan WHERE person_no = %d"
% (person_num))
result = self.db.get_result()
if result and len(result) != 0:
for person_no, date, time, amount, msre_desc, ndb_no in result:
self.db.query("INSERT INTO food_plan_temp VALUES" +
- "('%d', '%s', '%s', '%f', '%s', '%d' )"
- %(person_no, str(date), str(time), amount, msre_desc,
ndb_no))
+ "(%d, '%s', '%s', %f, '%s', %d )"
+ %(person_no, str(date), str(time), amount, msre_desc,
ndb_no),
+ caller='Person.setup')
- self.db.query("SELECT * FROM recipe_plan WHERE person_no = '%d'"
+ self.db.query("SELECT * FROM recipe_plan WHERE person_no = %d"
% (person_num))
result = self.db.get_result()
-
if result and len(result) != 0:
for person_num, date, time, num_portions, recipe_num in result:
self.db.query("INSERT INTO recipe_plan_temp VALUES" +
- " ('%d', '%s', '%s', '%f', '%d' )"
- % (person_num, str(date), str(time), num_portions,
- recipe_num))
+ " (%d, '%s', '%s', %f, %d )"
+ % (person_num, date, time, num_portions,
+ recipe_num), caller='Person.setup')
def get_user(self):
#return self.db.user
=== modified file 'src/plan_compute_dlg.py'
--- a/src/plan_compute_dlg.py 2012-09-16 23:02:12 +0000
+++ b/src/plan_compute_dlg.py 2012-09-30 23:13:51 +0000
@@ -35,13 +35,17 @@
self.ui.dialog.show()
def start_later_than_end(self, start, end):
- date_split1 = string.split(start, '-')
- date_split2 = string.split(end, '-')
- if int(date_split1[0]) > int(date_split2[0]): # year
- return 1
- if int(date_split1[1]) > int(date_split2[1]): # month
- return 1
- if int(date_split1[2]) > int(date_split2[2]): # day
+ start, end = string.split(start, '-'), string.split(end, '-')
+ year_start, year_end = int(start[0]), int(end[0])
+ month_start, month_end = int(start[1]), int(end[1])
+ day_start, day_end = int(start[2]), int(end[2])
+ # Ex: start = 2012-12-31
+ # end = 2013-1-1
+ if year_start > year_end:
+ return 1
+ if month_start > month_end and not year_end > year_start:
+ return 1
+ if day_start > day_end and not month_end > month_start:
return 1
return 0
@@ -73,7 +77,7 @@
# get recipes in plan within the dates
self.db.query("SELECT recipe_no, no_portions FROM " +
- "recipe_plan_temp WHERE date >='%s' AND date <='%s'"
+ "recipe_plan_temp WHERE date >= '%s' AND date <= '%s'"
%(start_date, end_date))
result = self.db.get_result()
@@ -82,7 +86,7 @@
# get foods in plan within the dates
self.db.query("SELECT amount, Msre_Desc, NDB_No FROM " +
- "food_plan_temp WHERE date >='%s' AND date <='%s'"
+ "food_plan_temp WHERE date >= '%s' AND date <= '%s'"
%(start_date, end_date))
result = self.db.get_result()
@@ -115,17 +119,18 @@
def get_ingredients(self, recipe_num):
self.db.query("SELECT amount, Msre_Desc, NDB_No FROM " +
- "ingredient WHERE recipe_no = '%d'" %(recipe_num))
+ "ingredient WHERE recipe_no = %d" %(recipe_num))
return self.db.get_result()
def get_food_nutrients(self, food_num):
self.db.query("SELECT Nutr_No, Nutr_Val FROM nut_data " +
- "WHERE NDB_No = '%d'" %(food_num))
+ "WHERE NDB_No = %d" %(food_num))
return self.db.get_result()
-#HERE: take into account Amount unit modifier?
+
+ #HERE: take into account Amount unit modifier?
def get_gm_per_measure(self, food_num, msre_desc):
self.db.query("SELECT Gm_wgt FROM weight WHERE " +
- "NDB_No = '%d' AND Msre_Desc = '%s'" %(food_num, msre_desc))
+ "NDB_No = %d AND Msre_Desc = '%s'" %(food_num, msre_desc))
return float(self.db.get_single_result())
def add_food_nutr_comp(self, tot_list, food_num, amount, gm_per_msre):
@@ -143,7 +148,7 @@
def add_recipe_to_total(self, tot_list, recipe_num, num_portions):
ingr_list = self.get_ingredients(recipe_num)
self.db.query("SELECT no_serv FROM recipe WHERE " +
- "recipe_no = '%d'" %(recipe_num))
+ "recipe_no = %d" %(recipe_num))
num_serv = float(self.db.get_single_result())
for amount, msre_desc, fd_num in ingr_list:
tot_amount = amount * num_portions / num_serv
=== modified file 'src/plan_win.py'
--- a/src/plan_win.py 2012-09-16 23:02:12 +0000
+++ b/src/plan_win.py 2012-09-30 23:13:51 +0000
@@ -299,7 +299,7 @@
food_list = []
for time, amount, msre_desc, ndb_no in result:
food = gnutr.Ingredient()
- food.time = str(time)
+ food.time = time
food.amount = amount
food.food_num = ndb_no
food.food_desc = self.store.fd_num2desc[food.food_num]
@@ -308,26 +308,25 @@
return food_list
def food_desc_from_NDB_No(self, food_no):
- self.db.query("SELECT Long_Desc FROM food_des WHERE NDB_No =
'{0:d}'".format(food_no))
+ self.db.query("SELECT Long_Desc FROM food_des WHERE NDB_No =
{0:d}".format(food_no))
return self.db.get_result()
def food_quantity_info(self, food_no, msre_desc):
- self.db.query("SELECT Amount, Gm_wgt FROM weight WHERE NDB_No =
'{0:d}'" +
+ self.db.query("SELECT Amount, Gm_wgt FROM weight WHERE NDB_No = {0:d}"
+
"AND Msre_Desc = '{1:s}'".format(food_no, msre_desc))
return self.db.get_result()
def get_recipes_for_date(self, date):
- self.db.query("SELECT time, no_portions, " +
- "recipe.recipe_no, recipe_name FROM recipe_plan_temp, recipe " +
- "WHERE date = '" + date + "' AND " +
- "recipe_plan_temp.recipe_no = recipe.recipe_no")
+ self.db.query("SELECT time, no_portions, recipe_plan_temp.recipe_no," +
+ "recipe_name FROM recipe_plan_temp, recipe WHERE date = '" + date
+
+ "' AND recipe_plan_temp.recipe_no = recipe.recipe_no")
result = self.db.get_result()
recipe_list = []
for time, num_portions, recipe_num, recipe_desc in result:
recipe = gnutr.Recipe()
- recipe.time = str(time)
+ recipe.time = time
recipe.num_portions = num_portions
recipe.num = recipe_num
recipe.desc = recipe_desc
@@ -355,17 +354,17 @@
def delete_from_plan_temp_db(self, date, food=None, recipe=None):
if food:
self.db.query("DELETE FROM food_plan_temp WHERE " +
- "date = '%s' AND time = '%s' AND NDB_No = '%d'"
+ "date = '%s' AND time = '%s' AND NDB_No = %d"
%(date, food.time, food.food_num))
else:
self.db.query("DELETE FROM recipe_plan_temp WHERE " +
- "date = '%s' AND time = '%s' AND recipe_no = '%d'"
+ "date = '%s' AND time = '%s' AND recipe_no = %d"
%(date, recipe.time, recipe.num))
def edit_plan_temp_db(self, date, food=None, recipe=None):
if food:
self.db.query("SELECT * FROM food_plan_temp WHERE " +
- "date = '{0:s}' AND time = '{1:s}' AND NDB_No =
'{2:d}'".format(
+ "date = '{0:s}' AND time = '{1:s}' AND NDB_No = {2:d}".format(
date, food.time, food.food_num))
data = self.db.get_result()
# FIXME: catches a bug where two foods have the same name,
@@ -377,15 +376,15 @@
data
self.db.query("DELETE FROM food_plan_temp WHERE " +
- "date = '%s' AND time = '%s' AND NDB_No = '%d'"
+ "date = '%s' AND time = '%s' AND NDB_No = %d"
%(date, food.time, food.food_num))
-
self.db.query("INSERT INTO food_plan_temp VALUES (" +
- "'{0:d}', '{1:s}', '{2:s}', '{3:f}', '{4:s}', '{5:d}')".format(
- person_num, date2, time, food.amount, food.msre_desc,food_num))
+ "{0:d}, '{1:s}', '{2:s}', {3:f}, '{4:s}', {5:d})".format(
+ person_num, date2, time, food.amount, food.msre_desc,food_num),
+ caller='PlanWin.edit_plan_temp_db')
else:
self.db.query("SELECT * FROM recipe_plan_temp WHERE " +
- "date = '%s' AND time = '%s' AND recipe_no = '%d'"
+ "date = '%s' AND time = '%s' AND recipe_no = %d"
%(date, recipe.time, recipe.num))
data = self.db.get_result()
# FIXME: catches a bug where two recipes have the same name,
@@ -396,20 +395,21 @@
((person_num, date2, time, num_portions, recipe_num),) = data
self.db.query("DELETE FROM recipe_plan_temp WHERE " +
- "date = '%s' AND time = '%s' AND recipe_no = '%d'"
+ "date = '%s' AND time = '%s' AND recipe_no = %d"
%(date, recipe.time, recipe.num))
self.db.query("INSERT INTO recipe_plan_temp VALUES (" +
- "'%d', '%s', '%s', '%s', '%d')"
- %(person_num, date2, time, recipe.num_portions, recipe_num))
+ "%d, '%s', '%s', '%s', %d)"
+ %(person_num, date2, time, recipe.num_portions, recipe_num),
+ caller='PlanWin.edit_plan_temp_db')
def save_plan(self):
person_num = self.person.get_person_num()
# delete old plan
- self.db.query("DELETE FROM food_plan WHERE person_no = '%d'"
+ self.db.query("DELETE FROM food_plan WHERE person_no = %d"
%(person_num))
- self.db.query("DELETE FROM recipe_plan WHERE person_no = '%d'"
+ self.db.query("DELETE FROM recipe_plan WHERE person_no = %d"
%(person_num))
# transfer from tempory to stored table
@@ -419,16 +419,18 @@
for person, date, time, amount, msre_desc, ndb_no in plan_list:
self.db.query("INSERT INTO food_plan VALUES (" +
- "'{0:d}', '{1:s}', '{2:s}', '{3:f}', '{4:s}', '{5:d}')".format(
- person, date, time, amount, msre_desc, ndb_no))
+ "{0:d}, '{1:s}', '{2:s}', {3:f}, '{4:s}', {5:d})".format(
+ person, date, time, amount, msre_desc, ndb_no),
+ caller='PlanWin.save_plan')
self.db.query("SELECT * FROM recipe_plan_temp")
recipe_list = self.db.get_result()
for person_num, date, time, num_portions, recipe_num in recipe_list:
self.db.query("INSERT INTO recipe_plan VALUES (" +
- "'%d', '%s', '%s', '%f', '%d')"
- %(person_num, date, time, num_portions, recipe_num))
+ "%d, '%s', '%s', %f, %d)"
+ %(person_num, date, time, num_portions, recipe_num),
+ caller='PlanWin.save_plan')
def add_recipe(self, recipe):
date = self.ui.date.entry.get_text()
@@ -441,9 +443,9 @@
return
person_num = self.person.get_person_num()
- self.db.query("INSERT INTO recipe_plan_temp VALUES (" +
- "'%d', '%s', '%s', '%f', '%d' )"
- %(person_num, date, time, recipe.num_portions, recipe.num))
+ self.db.query("INSERT INTO recipe_plan_temp VALUES
(%d,'%s','%s',%f,%d)"
+ % (person_num, date, time, recipe.num_portions, recipe.num),
+ caller='PlanWin.add_recipe')
self.update()
def add_food(self, food):
@@ -459,6 +461,7 @@
# Note: the temporary table is used
self.db.query("INSERT INTO food_plan_temp VALUES (" +
- "'{0:d}', '{1:s}', '{2:s}', '{3:f}', '{4:s}', '{5:d}')".format(
- person_num, date, time, food.amount, food.msre_desc,
food.food_num))
+ "{0:d}, '{1:s}', '{2:s}', {3:f}, '{4:s}', {5:d})".format(
+ person_num, date, time, food.amount, food.msre_desc,
food.food_num),
+ caller='PlanWin.add_food')
self.update()
=== modified file 'src/recipe_srch_dlg.py'
--- a/src/recipe_srch_dlg.py 2012-07-27 01:59:48 +0000
+++ b/src/recipe_srch_dlg.py 2012-09-30 23:13:51 +0000
@@ -76,7 +76,7 @@
dict = self.store.cat_desc2num
cat_num = dict[cat_desc]
self.db.query(("SELECT recipe_no, recipe_name " +
- "FROM recipe WHERE category_no = '%d' " +
- "AND recipe_name REGEXP '%s'") %(cat_num, srch_text))
+ "FROM recipe WHERE category_no = %d" +
+ " AND recipe_name REGEXP '%s'") %(cat_num, srch_text))
result_list = self.db.get_result()
return result_list
=== modified file 'src/recipe_srch_res_dlg.py'
--- a/src/recipe_srch_res_dlg.py 2012-07-27 01:47:47 +0000
+++ b/src/recipe_srch_res_dlg.py 2012-09-30 23:13:51 +0000
@@ -90,16 +90,16 @@
# plan_win.py
if self.view == gnutr_consts.RECIPE:
self.db.query("SELECT no_serv, category_no FROM " +
- "recipe WHERE recipe_no = '{0:d}'".format(recipe.num))
+ "recipe WHERE recipe_no = {0:d}".format(recipe.num))
recipe.num_serv, recipe.cat_num = self.db.get_row_result()
recipe.cat_desc = self.store.cat_num2desc[ recipe.cat_num]
self.db.query("SELECT prep_desc FROM preparation WHERE " +
- "recipe_no = '{0:d}'".format(recipe.num))
+ "recipe_no = {0:d}".format(recipe.num))
recipe.prep_desc = self.db.get_single_result()
self.db.query("SELECT amount, Msre_Desc, NDB_No FROM " +
- "ingredient WHERE recipe_no = '{0:d}'".format(recipe.num))
+ "ingredient WHERE recipe_no = {0:d}".format(recipe.num))
ingr_list = self.db.get_result()
recipe.ingr_list = []
=== modified file 'src/recipe_win.py'
--- a/src/recipe_win.py 2012-07-27 01:59:48 +0000
+++ b/src/recipe_win.py 2012-09-30 23:13:51 +0000
@@ -195,30 +195,39 @@
def save_recipe(self, recipe):
print 'Saving recipe:', recipe.desc
recipe_no = self.db.next_row('recipe_no', 'recipe')
- self.db.query("""INSERT INTO recipe VALUES
- ('%d', '%s', '%s', '%s', '%s')""" % (recipe_no, recipe.desc,
- recipe.num_serv, str(self.num_ingr), str(recipe.cat_num)))
+ print "*** TYPES ***"
+ print 'recipe_no', type(recipe_no)
+ print 'recipe.desc', type(recipe.desc)
+ print 'recipe.num_serv', type(recipe.num_serv)
+ print 'self.num_ingr', type(self.num_ingr)
+ print 'recipe.cat_num', type(recipe.cat_num)
+ print "*** END TYPES ***"
+ self.db.query("INSERT INTO recipe VALUES" +
+ "(%d, '%s', %d, %d, %d)" % (recipe_no, recipe.desc,
+ recipe.num_serv, self.num_ingr, recipe.cat_num),
+ caller='RecipeWin.save_recipe')
for ingr in recipe.ingr_list:
- self.db.query("""INSERT INTO ingredient VALUES
- ('{0:d}', '{1:f}', '{2:s}', '{3:d}')""".format(recipe_no,
- ingr.amount, ingr.msre_desc, ingr.food_num))
+ self.db.query("INSERT INTO ingredient VALUES" +
+ "({0:d}, {1:f}, '{2:s}', {3:d})".format(recipe_no,
+ ingr.amount, ingr.msre_desc, ingr.food_num),
+ caller='RecipeWin.save_recipe')
- self.db.query("""INSERT INTO preparation VALUES
- ('%d', '0.0', "%s")""" % (recipe_no, recipe.prep_desc))
+ self.db.query("INSERT INTO preparation VALUES (%d, 0.0, '%s')"
+ % (recipe_no, recipe.prep_desc), caller='RecipeWin.save_recipe')
self.dirty = False
def delete_recipe(self, recipe_name):
self.db.query("""SELECT recipe_no FROM recipe
WHERE recipe_name = '%s'""" %(recipe_name))
recipe_num = str(self.db.get_single_result())
- self.db.query("DELETE FROM recipe WHERE recipe_no = '%s'"
- % (recipe_num))
- self.db.query("DELETE FROM ingredient WHERE recipe_no = '%s'"
- % (recipe_num))
- self.db.query("DELETE FROM recipe_plan WHERE recipe_no = '%s'"
- % (recipe_num))
- self.db.query("DELETE FROM preparation WHERE recipe_no = '%s'"
+ self.db.query("DELETE FROM recipe WHERE recipe_no = %d"
+ % (recipe_num))
+ self.db.query("DELETE FROM ingredient WHERE recipe_no = %d"
+ % (recipe_num))
+ self.db.query("DELETE FROM recipe_plan WHERE recipe_no = %d"
+ % (recipe_num))
+ self.db.query("DELETE FROM preparation WHERE recipe_no = %d"
% (recipe_num))
def prep_description(self):
@@ -231,7 +240,7 @@
num_serv = self.ui.num_serv_entry.get_text()
cat_desc = self.ui.category_combo.get_active_text()
prep_desc = self.prep_description()
- return (desc,num_serv,cat_desc,prep_desc)
+ return (desc,int(num_serv),cat_desc,prep_desc)
def get_recipe(self):
r = gnutr.Recipe()
@@ -285,7 +294,7 @@
return True
self.db.query("""SELECT prep_desc FROM preparation
- WHERE recipe_no = '{0:d}'""".format(recipe_no))
+ WHERE recipe_no = {0:d}""".format(recipe_no))
prep_desc = self.db.get_single_result()
start = self.ui.text_buffer.get_start_iter();
=== modified file 'src/store.py'
--- a/src/store.py 2012-09-15 22:37:11 +0000
+++ b/src/store.py 2012-09-30 23:13:51 +0000
@@ -106,6 +106,6 @@
def get_msre_desc_tuples(self, fd_num):
self.db.query("SELECT Msre_Desc FROM weight WHERE " +
- "NDB_No = '{0:d}'".format(fd_num))
+ "NDB_No = {0:d}".format(fd_num))
result = self.db.get_result()
return result
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [GNUtrition-commits] /srv/bzr/gnutrition/trunk r14: Several small bugfixes. Most related to SQL queries.,
Thomas Sinclair <=