def buy_item(item_id):
with database(True) as (curs, _):
query = "SELECT * FROM items WHERE id=%s"
curs.execute(query, (item_id, ))
item = curs.fetchone()
if item is None:
abort(404)
with database(True) as (curs, conn):
query = "SELECT money FROM users WHERE id=%s"
curs.execute(query, (session['id'], ))
prev_money, = curs.fetchone()
query = "UPDATE users SET money = money - (SELECT cost FROM items WHERE id=%s) WHERE id=%s"
curs.execute(query, (item_id, session['id']))
conn.commit()
query = "UPDATE users SET money = money + (SELECT cost FROM items WHERE id=%s) WHERE id=%s"
curs.execute(query, (item_id, item['owner_id']))
conn.commit()
query = "SELECT money FROM users WHERE id=%s"
curs.execute(query, (session['id'], ))
new_money = curs.fetchone()['money']
if new_money < 0:
query = "UPDATE users SET money = %s WHERE id=%s"
curs.execute(query, (prev_money, session['id']))
conn.commit()
return 'Not enough money', 403
return jsonify(dict(item))
def edit_user():
data = request.form.to_dict()
if data.get('money'):
with database(True) as (curs, conn):
query = 'SELECT money FROM users WHERE id=%s'
curs.execute(query, (session['id'], ))
user = curs.fetchone()
if user['money'] < int(data['money']):
data['money'] = user['money']
keys = list(
map(
lambda x: security.quote_identifier(x).replace('"', '`'),
data.keys(),
))
values = list(map(security.quote_identifier, data.values()))
query = ', '.join(map(lambda x: f'{x[0]}={x[1]}', zip(keys, values)))
with database(True) as (curs, conn):
query = "UPDATE users SET " + query + " WHERE id=" + str(session['id'])
curs.execute(query)
conn.commit()
return 'OK'
def get_user():
with database(True) as (curs, _):
query = 'SELECT id, username, money FROM users WHERE id=%s'
curs.execute(query, (session['id'], ))
user = curs.fetchone()
return jsonify(dict(user))
def list_all_items():
offset = request.args.get('offset', 0)
with database(True) as (curs, _):
query = 'SELECT id, name, cost, owner_id FROM items ORDER BY id DESC LIMIT %s, 1000'
curs.execute(query, (offset, ))
items = curs.fetchall()
return jsonify(list(map(dict, items)))
def list_my_items():
offset = request.args.get('offset', 0)
with database(True) as (curs, _):
query = """
SELECT id, name, description, cost, owner_id
FROM items
WHERE owner_id=%s
ORDER BY id DESC LIMIT %s, 1000
"""
curs.execute(query, (session['id'], offset))
items = curs.fetchall()
return jsonify(list(map(dict, items)))
def get_item(item_id):
with database(True) as (curs, _):
query = 'SELECT id, name, description, cost, owner_id FROM items WHERE id=%s'
curs.execute(query, (item_id, ))
item = curs.fetchone()
if item is None:
abort(404)
item = dict(item)
if item['owner_id'] != session.get('id'):
item.pop('description')
return jsonify(item)
def login():
data = request.form.to_dict()
if not data.get('username') or not data.get('password'):
return 'Specify username, password', 400
with database(True) as (curs, _):
query = "SELECT id FROM users WHERE username=%s AND password=%s"
curs.execute(query, (data['username'], data['password']))
user = curs.fetchone()
if user is None:
return 'No such user', 400
session['id'] = user['id']
return 'OK'
def register():
data = request.form.to_dict()
if not data.get('username') or not data.get('password'):
return 'Specify username, password', 400
with database() as (curs, conn):
query = "SELECT id FROM users WHERE username=%s"
curs.execute(query, (data['username'], ))
user = curs.fetchone()
if user is not None:
return 'Already registered', 400
query = "INSERT INTO users (username, password) VALUES (%s, %s)"
curs.execute(query, (data['username'], data['password']))
conn.commit()
return 'OK', 201
def change_item(item_id):
data = request.form.to_dict()
data['owner_id'] = data.get('owner_id') or session['id']
keys = list(
map(
lambda x: security.quote_identifier(x).replace('"', '`'),
data.keys(),
))
values = list(map(security.quote_identifier, map(str, data.values())))
query = ', '.join(map(lambda x: f'{x[0]}={x[1]}', zip(keys, values)))
query = f'UPDATE items SET {query} WHERE id=%s'
with database() as (curs, conn):
curs.execute(query, (item_id, ))
conn.commit()
return 'OK'
def create_item():
data = request.form.to_dict()
data['owner_id'] = data.get('owner_id') or session['id']
key_part = ', '.join(
map(lambda x: security.quote_identifier(x).replace('"', ''),
data.keys()), )
value_path = ', '.join(['%s'] * len(list(data.values())))
query = f'INSERT INTO items ({key_part}) VALUES ({value_path})'
args = list(data.values())
with database() as (curs, conn):
curs.execute(query, args)
conn.commit()
query = "SELECT LAST_INSERT_ID()"
curs.execute(query)
result, = curs.fetchone()
return str(result), 201
