def put(self, id):
''' Update a single product based on ID'''
idToUpdate = str(id)
infoToSearch = "SELECT * from products WHERE id=" + idToUpdate
result = execute_read_query(infoToSearch)
if len(result) == 0:
return "Cannot update", 404
else:
updatedInfo = [
request.json.get('name'),
request.json.get('description'),
str(request.json.get('price')),
str(request.json.get('qty'))
]
updateID = "UPDATE products SET name ='" + updatedInfo[
0] + "', description ='" + updatedInfo[
1] + "', price =" + updatedInfo[
2] + ", qty =" + updatedInfo[
3] + " WHERE id=" + idToUpdate
execute_query(updateID)
result = execute_read_query(infoToSearch)
jsonResult = vars(
ProductModel(result[0][1], result[0][2], result[0][3],
result[0][4]))
return "Product id: " + idToUpdate + " is updated. " + str(
jsonResult), 200
def delete(self, id):
''' Delete a single product based on ID'''
get_product_query = "SELECT * from products WHERE products.id = " + str(
id)
product = execute_read_query(get_product_query)
if (product == []):
return "invalid request. id no. " + str(
id) + " does not exist.", 404
else:
del_query = "DELETE FROM products WHERE products.id = " + str(id)
delete = execute_read_query(del_query)
return "ID no. " + str(id) + " has been deleted", 204
def search_shops():
"""
Endpoint: '/search_shops'
EXAMPLE Parameter http://127.0.0.1:5000/api/api/search_shop?search_term=""
Verb: GET
Parameter: search_term
Output: { 'shop_name': [], 'shop_town' = [], 'shop_city' = [], 'shop_country': []}
"""
search_term = request.args.get('search_term')
if search_term:
sql_query = f"""select * from shop
where shop.name ilike '%{search_term}%'
"""
else:
sql_query = f"""select * from shop
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
shops_dict = {'shop_ids': [], 'shop_names': [], 'shop_pics': []}
for row in output:
shops_dict['shop_ids'].append(row[0])
shops_dict['shop_names'].append(f"{row[1]} {row[2]}")
shops_dict['shop_pics'].append(row[-1])
conn.close()
return dumps(shops_dict)
def get_strings(peer_id):
resp = db.execute_read_query(
connection, f"SELECT text FROM messages WHERE peer_id={peer_id}")
strings = list()
for a in resp:
strings.append(a[0])
return strings
def search_drinks():
"""
Endpoint: 'api/search_drinks'
EXAMPLE Parameter http://127.0.0.1:5000/api/api/search_drinks?search_term=""
Verb: GET
Parameter: search_term
Output: { 'drink_id': [], 'drink_name' = [], 'drink_rating' = []}
"""
search_term = request.args.get('search_term')
if search_term:
sql_query = f"""select * from drink
where name ilike '%{search_term}%'
"""
else:
sql_query = f"""select * from drink
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
drinks_dict = {
'drink_ids': [],
'drink_names': [],
'drink_pictures': [],
'drink_ratings': []
}
for row in output:
drinks_dict['drink_ids'].append(row[0])
drinks_dict['drink_names'].append(row[1])
drinks_dict['drink_pictures'].append(row[2])
drinks_dict['drink_ratings'].append(row[3])
conn.close()
return dumps(drinks_dict)
def login():
"""
Endpoint: 'api/login'
EXAMPLE Parameter http://127.0.0.1:5000/api/login
Verb: GET
Parameter: user_token, name, email
Output: { 'drink_id': [], 'drink_name' = [], 'drink_rating' = []}
"""
try:
data = request.get_json()
user_token = data['user_token']
name = data['name']
email = data['email']
sql_check = f"""select user_token from account
where user_token = '{user_token}'
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_check)
if not output:
sql_insert = f"""INSERT INTO account VALUES('{user_token}', '{name}', '{email}', {1})
"""
db.execute_query(conn, sql_insert)
else:
sql_update = f"""UPDATE account set status = 1 where user_token = '{user_token}'
"""
db.execute_query(conn, sql_update)
conn.close()
return dumps({'is_success': 200})
except:
return dumps({'is_success': 500})
def drinks_sold():
"""
Endpoint: '/drinks_sold'
EXAMPLE Parameter http://127.0.0.1:5000/api/drink_sold_where?drink_id=1
Verb: GET
Parameter: drink_id
Output: { 'shop_name' : [], 'price' : [] }
"""
drink_id = request.args.get('drink_id')
sql_query = f"""select shop.name, shop.town, menu.price, shop.picture_file, shop.id from menu
inner join shop on menu.shop_id = shop.id
where menu.drink_id = {drink_id}"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
drink_sold_where_dict = {
'shop_name': [],
'prices': [],
'pictures': [],
'ids': []
}
for row in output:
drink_sold_where_dict['shop_name'].append(f"{row[0]} {row[1]}")
drink_sold_where_dict['prices'].append(str(row[2]).format('.2f'))
drink_sold_where_dict['pictures'].append(row[3])
drink_sold_where_dict['ids'].append(row[4])
conn.close()
return dumps(drink_sold_where_dict)
def update_ratings():
"""
Endpoint: '/update_ratings'
EXAMPLE Parameter http://127.0.0.1:5000/api/update_ratings?rating=3.0&drink_id=1
Verb: POST
Parameter: rating, drink_id in json dict
Output: { 'old_rating' : curr_rating, 'new_rating' : new_rating}
"""
# returns none if no input
data = request.get_json()
rating = data['rating']
drink_id = data['drink_id']
# query the database for current rating
sql_query = f"""select rating from drink where id = {drink_id}"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
curr_rating = output[0][0]
# NOTE: need to include num of ratings to determien average
new_rating = round((curr_rating + float(rating)) / 2.0, 2)
update_post_description = f"""update drink set rating = {new_rating} WHERE id = {drink_id}"""
db.execute_query(conn, update_post_description)
# test change
# select_users = "SELECT * FROM drink"
# out = db.execute_read_query(conn, select_users)
conn.close()
return dumps({
'old_rating': curr_rating,
'new_rating': new_rating,
# 'output' : out,
})
def get(self): ''' Get All Products''' all_products_query = "SELECT * from products" products = execute_read_query(all_products_query) productsList = [] for product in products: print(products) productsList.append(vars(ProductModel(product[1],product[2],product[3],product[4]))) return productsList, 200
def put(self, id):
''' Update a single product based on ID'''
productName = request.json.get("name")
productDescription = request.json.get("description")
productPrice = request.json.get("price")
productQty = request.json.get("qty")
update_single_product = f"UPDATE products SET name = '{productName}', description = '{productDescription}', price = '{productPrice}', qty = '{productQty}' WHERE id = '{id}'"
products = execute_read_query(update_single_product)
return products, 200
def get(self, id):
''' Get a single product based on ID'''
get_product_query = "SELECT * from products WHERE products.id = " + str(
id)
product = execute_read_query(get_product_query)
if (product == []):
return "product. id no. " + str(id) + " does not exist.", 404
else:
return product, 200
def put(self,id):
''' Update a single product based on ID'''
product_update = "SELECT * from products WHERE id = %s" % id
product_To_update = execute_read_query(product_update)
if len(product_To_update) == 0:
return 'no product present', 404
else:
_payload = api.payload
product_To_update = _payload
_name = product_To_update['name']
_description = product_To_update['description']
_price = product_To_update['price']
_qty = product_To_update['qty']
update_exec_query = "UPDATE products SET name = '{}', description = '{}', price = {}, qty = {} WHERE id = {}".format(_name, _description,_price,_qty, id)
fin = execute_read_query(update_exec_query)
return 'updated', 200
def delete(self, id):
''' Delete a single product based on ID'''
idToDelete = str(id)
infoToSearch = "SELECT * from products WHERE id=" + idToDelete
result = execute_read_query(infoToSearch)
if len(result) == 0:
return "Nothing to delete. Since, it is empty.", 404
else:
idToSearch = "DELETE FROM products WHERE id=" + idToDelete
execute_query(idToSearch)
return "Product with id: " + str(
id) + " is successfully deleted.", 200
def get(self, id):
''' Get a single product based on ID'''
searchedID = str(id)
infoToSearch = "SELECT * from products WHERE id=" + searchedID
result = execute_read_query(infoToSearch)
if len(result) == 0:
return "There is no recorded info on the given id number. It is possible that it is already deleted.", 404
else:
jsonResult = vars(
ProductModel(result[0][1], result[0][2], result[0][3],
result[0][4]))
return jsonResult, 200
def put(self, id):
''' Update a single product based on ID'''
get_product_query = "SELECT * from products WHERE products.id = " + str(
id)
product = execute_read_query(get_product_query)
if (product == []):
return "invalid request. id no. " + str(
id) + " does not exist.", 404
else:
new_name = request.json.get("name")
new_desc = request.json.get("description")
new_price = request.json.get("price")
new_qty = request.json.get("qty")
update_product_query = f"UPDATE products SET name = '{new_name}', description = '{new_desc}', price = '{new_price}', qty = '{new_qty}' WHERE products.id = " + str(
id)
execute_read_query(update_product_query)
return str(id) + " has been updated", 200
def put(self, id):
''' Update a single product based on ID'''
name_data = request.json.get("name")
desc_data = request.json.get("description")
price_data = request.json.get("price")
qty_data = request.json.get("qty")
update_single_product = f""" UPDATE products
SET name = '{name_data}', description = '{desc_data}', price = '{price_data}', qty = '{qty_data}'
WHERE id = '{id}' """
products = execute_read_query(update_single_product)
return products, 200
def get(self,id):
''' Get a single product based on ID'''
specific_product = "SELECT * from products WHERE id={}".format(id)
products = execute_read_query(specific_product)
_prodID = id - 1
productsList = []
for product in products:
print(products)
productsList.append(vars(ProductModel(product[1],product[2],product[3],product[4])))
if (id < 1):
return 'Input a correct product ID', 400
elif (id > len(productsList)):
return 'No product has a product ID number of %s' % id, 404
else:
return productsList[_prodID], 200
def get_ingredients():
"""
Endpoint: '/get_drink_info'
EXAMPLE Parameter http://127.0.0.1:5000/api/get_ingredients?drink_id=1
Verb: GET
Parameter: drink_id
Output: { 'ingredients' : []}
"""
drink_id = request.args.get('drink_id')
sql_query = f"""select * from drink_ingredients where drink_ingredients.drink_id = {drink_id}"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
ingredients_dict = {'ingredients': []}
for row in output:
ingredients_dict['ingredients'].append(row[1])
conn.close()
return dumps(ingredients_dict)
def get_rewards():
"""
Endpoint: 'api/get_rewards'
EXAMPLE Parameter http://127.0.0.1:5000/api/get_rewards
Verb: GET
Parameter: user_token
Output: { 'rewards': }
"""
user_token = request.args.get('user_token')
sql_query = f"""select * from reward
where user_token = '{user_token}'
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
rewards = []
for row in output:
rewards.append(row[1])
return dumps({'rewards': rewards})
def get_shop_times():
"""
Endpoint: '/get_shop_times'
EXAMPLE Parameter http://127.0.0.1:5000/api/get_shop_times?shop_id-=1
Verb: GET
Parameter: shop_id
Output: { 'days_of_week': [], 'shop_open' = [], 'shop_close' = []}
"""
shop_id = request.args.get('shop_id')
sql_query = f"""select day_of_week, opening_time, closing_time from shop_opening_times
where shop_id = {shop_id}"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
time_dict = {'days_of_week': [], 'shop_open': [], 'shop_close': []}
for row in output:
time_dict['days_of_week'].append(row[0])
time_dict['shop_open'].append(row[1])
time_dict['shop_close'].append(row[2])
conn.close()
return dumps(time_dict)
def get_toppings():
"""
Endpoint: '/get_toppings'
EXAMPLE Parameter http://127.0.0.1:5000/api/get_toppings?shop_id-=1
Verb: GET
Parameter: shop_id
Output: { 'topping': [], 'price':[] }
"""
id = request.args.get('shop_id')
sql_query = f"""select * from topping
where shop_id = {id}
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
topping_dict = {'topping': [], 'price': []}
for row in output:
topping_dict['topping'].append(row[1])
topping_dict['price'].append(str(row[2]).format('.2f'))
conn.close()
return dumps(topping_dict)
def get_drink_info():
"""
Endpoint: '/get_drink_info'
EXAMPLE Parameter http://127.0.0.1:5000/api/get_drink_info?drink_id=1
Verb: GET
Parameter: drink_id
Output: { 'id' : number, 'name' : string, 'rating' : float, 'drink_img' : string}
"""
# returns none if no input
drink_id = request.args.get('drink_id')
# query the database for drink from drink_id
sql_query = f"""select * from drink where id = {drink_id}"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)[0]
conn.close()
return dumps({
'id': output[0],
'name': output[1],
'pictures': output[2],
'rating': output[3]
})
def get_shop_menu():
"""
Endpoint: '/get_shop_times'
EXAMPLE Parameter http://127.0.0.1:5000/api/get_shop_menu?shop_id-=1
Verb: GET
Parameter: shop_id
Output: { 'drinks': [], 'price' = []}
"""
shop_id = request.args.get('shop_id')
sql_query = f"""select drink.name, menu.price from menu
inner join drink on drink.id = menu.drink_id
where menu.shop_id = {shop_id}
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
drinks_dict = {'drinks': [], 'prices': []}
for row in output:
drinks_dict['drinks'].append(row[0])
drinks_dict['prices'].append(str(row[1]).format('.2f'))
conn.close()
return dumps(drinks_dict)
def set_session_token():
try:
data = request.json
service_name = data['name']
token = data['token']
dataset = data['dataset']
except Exception:
abort(400, 'POST request content is incorrect (should contain name, token, dataset)')
with create_connection() as conn:
print(service_name)
service = execute_read_query(conn, f"SELECT id,token FROM services WHERE name='{service_name}' LIMIT 1;")
# check for empty response
if (len(service) == 0) or service[0][1] != token:
abort(401, 'Incorrect name or token')
# take only first row
service = service[0]
# insert new session_token:
try:
with create_connection() as conn:
# first make all previous session inactive:
execute_query(conn, f"UPDATE session_tokens SET active=FALSE WHERE service='{service[0]}';")
# generate session_token
session_token = uuid.uuid4()
# put session_token to db
create_users = f"""
INSERT INTO session_tokens
(service, session_token, issue_date, expiry_date, active)
VALUES
('{service[0]}', '{session_token}', datetime('now'), datetime('now', '1 days'), TRUE);
"""
session = execute_query(conn, create_users)
except:
abort(401, 'Incorrect name or token')
with create_connection() as conn:
# get list of ids for selected datasets
ds = pd.read_sql_query(f"SELECT id, title FROM datasets WHERE title LIKE '{dataset}'", conn)
# check for empty response
if len(ds.index) == 0:
abort(404, 'Dataset not found')
# shuffle list
ids = ds.loc[ds.title==dataset].id.astype('int').sample(frac=1).to_list()
# add testing items to testing table
for f in ids:
create_list = f"""
INSERT INTO testing
(session, dataset_title, dataset_file_id, created, requests)
VALUES
('{session}', '{dataset}', '{f}', datetime('now'), 0);
"""
execute_query(conn, create_list)
return jsonify(
session_token=str(session_token),
expire_in='24 hours',
number_of_items=len(ids)
), 200
def delete(self, id):
''' Delete a single product based on ID'''
delete_single_product = f"DELETE from products WHERE id = '{id}'"
products = execute_read_query(delete_single_product)
return products, 200
def get(self, id):
''' Get a single product based on ID'''
get_single_product = f"SELECT * from products WHERE id = '{id}'"
products = execute_read_query(get_single_product)
return products, 200
bot.send_sticker(m.chat.id, random.choice(andrew_stickers))
if __name__ == '__main__':
bot.set_update_listener(listener) # register listener
db.init()
logger = logging.getLogger('Wall•e')
formatter = logging.Formatter(
'%(asctime)s (%(filename)s:%(lineno)d %(threadName)s) %(levelname)s - %(name)s: "%(message)s"'
)
console_output_handler = logging.StreamHandler(sys.stderr)
console_output_handler.setFormatter(formatter)
logger.addHandler(console_output_handler)
logger.setLevel(logging.DEBUG)
scheduler.add_job(logger.debug, 'interval', seconds=10, args=(' ', ))
rows = db.execute_read_query(
f"SELECT datetime, text, from_id FROM reminder")
if rows:
for row in rows:
logger.info(f'{row[0]} {row[1]} {row[2]}')
scheduler.add_job(print_msg,
'date',
run_date=row[0],
args=(
f'Напоминаю: {row[1]}',
row[0],
row[2],
))
scheduler.start()
bot.polling(none_stop=True, timeout=20)
