def insert_category(category):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'INSERT INTO menu_category VALUES (\'%s\')' % category
cursor.execute(query)
conn.commit()
conn.close()
def delete_menu(rid, mcategory):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'DELETE FROM restaurant_menu WHERE rid = %d AND category = \'%s\';' % (
rid, mcategory)
cursor.execute(query)
conn.commit()
conn.close()
def delete_menu_item(rid, mcategory, item_id):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'DELETE FROM menu_items WHERE rid = %d AND category = \'%s\' AND item_id = \'%s\';' % (
rid, mcategory, item_id)
cursor.execute(query)
conn.commit()
conn.close()
def insert_menu(rid, category):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'INSERT INTO restaurant_menu (rid, category) VALUES (%d, \'%s\')' % (
rid, category)
cursor.execute(query)
conn.commit()
conn.close()
def category_exists(category):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'SELECT * FROM menu_category where category = \'%s\'' % category
cursor.execute(query)
if cursor.fetchone() is None:
return False
return True
def rest_category_exists(rid, category):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'SELECT * FROM restaurant_menu WHERE rid = %d AND category = \'%s\'' % (
rid, category)
cursor.execute(query)
if cursor.fetchone() is None:
return False
return True
def delete_restaurant(rid):
conn = get_mysql_conn()
cursor = conn.cursor()
delete_query = 'DELETE FROM restaurant where rid = %d ' % int(rid)
cursor.execute(delete_query)
conn.commit()
conn.close()
def exists(rid):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'select rid, rname, raddress, rcity, rstate, rating, rcontact, rwebsite, remail from restaurant where rid = %d ;' % int(
rid)
cursor.execute(query)
if cursor.fetchone() is None:
return False
return True
def fetch_categories():
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'SELECT category FROM menu_category'
cursor.execute(query)
all_data = []
for row in cursor.fetchall():
all_data.append(row[0])
conn.close()
return all_data
def fetch_menu(rid):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'SELECT category FROM restaurant_menu WHERE rid = %d ' % rid
cursor.execute(query)
all_data = []
for row in cursor.fetchall():
all_data.append(row[0])
conn.close()
return all_data
def fetch_category_menu_items(rid, mcategory):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'SELECT item_id, name, price FROM menu_items WHERE rid = %d AND category = \'%s\' ' % (
rid, mcategory)
cursor.execute(query)
all_data = []
for row in cursor.fetchall():
item_details = {}
item_details['id'] = row[0]
item_details['name'] = row[1]
item_details['price'] = row[2]
all_data.append(item_details)
conn.close()
return all_data
def fetch_item(rid, item_id):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'SELECT item_id, category, name, price from menu_items where rid = %d AND item_id = \'%s\'' % (
rid, item_id)
cursor.execute(query)
all_data = []
for row in cursor.fetchall():
item_details = {}
item_details['id'] = row[0]
item_details['category'] = row[1]
item_details['name'] = row[2]
item_details['price'] = row[3]
all_data.append(item_details)
conn.close()
return all_data
def fetch_all_menu_items(rid):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'SELECT item_id, category, name, price from menu_items where rid = %d ' % rid
cursor.execute(query)
all_data = {}
for row in cursor.fetchall():
item_details = {}
item_details['id'] = row[0]
item_details['name'] = row[2]
item_details['price'] = row[3]
if row[1] not in all_data:
all_data[row[1]] = []
all_data[row[1]].append(item_details)
conn.close()
return all_data
def fetch_restaurant(rid):
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'select rid, rname, raddress, rcity, rstate, rating, rcontact, rwebsite, remail from restaurant where rid = %s;' % (
rid)
cursor.execute(query)
data = cursor.fetchone()
if data is None:
return {}
details = {}
details['rid'] = data[0]
details['name'] = data[1]
details['address'] = data[2]
details['city'] = data[3]
details['state'] = data[4]
details['rating'] = data[5]
details['contact'] = data[6]
details['website'] = data[7]
details['email'] = data[8]
conn.close()
return details
def insert_menu_item(data):
conn = get_mysql_conn()
cursor = conn.cursor()
input_columns = data.keys()
input_column_data = []
for col in input_columns:
input_column_data.append(col)
input_column_data = 'item_id, rid, category, name, price'
input_value_data = '\'%s\', %d, \'%s\',\'%s\',\'%s\'' % (
data['item_id'], int(data['rid']), str(
data['category']), str(data['name']), str(data['price']))
insert_query = 'INSERT INTO `menu_items` (%s) values (%s);' % (
input_column_data, input_value_data)
cursor.execute(insert_query)
conn.commit()
conn.close()
return True
def fetch_all_restaurants():
conn = get_mysql_conn()
cursor = conn.cursor()
query = 'select rid, rname, raddress, rcity, rstate, rating, rcontact, rwebsite, remail from restaurant;'
cursor.execute(query)
all_data = []
for row in cursor.fetchall():
row_data = {}
row_data['rid'] = row[0]
row_data['name'] = row[1]
row_data['address'] = row[2]
row_data['city'] = row[3]
row_data['state'] = row[4]
row_data['rating'] = row[5]
row_data['contact'] = row[6]
row_data['website'] = row[7]
row_data['email'] = row[8]
all_data.append(row_data)
conn.close()
return all_data
def insert_restaurant(data):
conn = get_mysql_conn()
cursor = conn.cursor()
input_columns = data.keys()
input_column_data = []
input_value_data = []
for col in input_columns:
input_column_data.append(col)
input_value_data.append(data[col])
# this is a temp thing, the column name mapping needs to be improved
insert_query = 'INSERT INTO `restaurant` (%s) values (%s);' % (
"`r" + "`, `r".join(input_column_data) + "`",
"'" + "', '".join(input_value_data) + '\'')
cursor.execute(insert_query)
conn.commit()
rid = cursor.lastrowid
conn.close()
return rid