def findLocalityById(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM locality where locality_id = " + id)
row = cursor.fetchone()
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findAllDishes():
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM dish")
row = cursor.fetchall()
return row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findCityById(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM city WHERE city_id = " + id)
row = cursor.fetchone()
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findLocalitiesByCityId(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM locality where city_id = " + id)
rows = cursor.fetchall()
for row in rows:
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findDishesByRestaurantId(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM menu where restaurant_id = " + id)
rows = cursor.fetchall()
for row in rows:
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findDishesByRestaurantId(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM menu where restaurant_id = " + id)
rows = cursor.fetchall()
for row in rows:
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findAllCities():
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM city")
rows = cursor.fetchall()
return rows
# for row in rows:
# print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def insertManyLocalities(locality_info):
query = "INSERT INTO locality(locality_id, name, city_id, data_resource) " "VALUES(%s, %s, %s, %s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, locality_info)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "LOCALITY DATA INSERTED!!!"
def findCityByName(query):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if query:
query = "%" + query + "%"
cursor.execute("SELECT * FROM city WHERE name LIKE '%s'" % query)
rows = cursor.fetchall()
for row in rows:
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def insertManyDishes(menu_info): query = "INSERT INTO dish(dish_name) " \ "VALUES(%s)" try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.executemany(query, menu_info) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "DISHES DATA INSERTED!!!"
def insertManyCities(city_info):
query = "INSERT INTO city(city_id, name, data_resource) " \
"VALUES(%s, %s, %s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, city_info)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "CITY DATA INSERTED!!!"
def findLocalityByName(query):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if query:
query = "%" + query + "%"
cursor.execute("SELECT * FROM locality WHERE name LIKE '%s'" % query)
rows = cursor.fetchall()
for row in rows:
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def connect():
""" Connect to MySQL database """
db_config = read_db_config()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def insertManyDishes(menu_info): query = "INSERT INTO menu(dish_id, restaurant_id, dish_name, type, cost, data_resource) " \ "VALUES(%s, %s, %s, %s, %s, %s)" try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.executemany(query, menu_info) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "DISHES DATA INSERTED!!!"
def insertOneDish(dish_id, restaurant_id, dish_name, type, cost, data_resource): query = "INSERT INTO menu(dish_id, restaurant_id, dish_name, cost, type, data_resource) " \ "VALUES(%s, %s, %s, %s, %s, %s)" args = (dish_id, restaurant_id, dish_name, cost, type, data_resource) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "DISHES DATA INSERTED!!!"
def findDishByName(query):
rows = []
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if query:
query = query + "%"
cursor.execute("SELECT DISTINCT(dish_name) FROM menu WHERE dish_name LIKE '%s'" % query)
rows = cursor.fetchall()
# for row in rows:
# print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
return rows
def insertOneLocality(locality_id, name, city_id, data_resource):
query = "INSERT INTO locality(locality_id, name, city_id, data_resource) " \
"VALUES(%s, %s, %s, %s)"
args = (locality_id, name, city_id, data_resource)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "LOCALITY DATA INSERTED!!!"
def insertOneDish(dish_name): query = "INSERT INTO dish(dish_name) " \ "VALUES(%s)" args = (dish_name) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "DISHES DATA INSERTED!!!"
def findLocalityByName(city_id, query):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if query:
query = query + "%"
city_id = str(city_id)
command = "SELECT * FROM locality WHERE city_id = '" + city_id + "' AND name LIKE '" + query + "'"
cursor.execute(command)
rows = cursor.fetchall()
#for row in rows:
# print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
return rows
def findCityByName(query): rows = [] try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() if query: query = query + "%" command = "SELECT * FROM city WHERE name LIKE '%" + query +"'" #print command cursor.execute(command) rows = cursor.fetchall() #for row in rows: # print row except Error as error: print error finally: cursor.close() conn.close() return rows