def get(self, args):
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute(f"select * from users where id={args}")
userinfo = cursor.fetchone()
cursor.execute(
f"SELECT dishes.name, dishes.price, dishes.id, dishes_order.count, users_orders.order_id, dishes_order.id as d_o_id FROM `users`\
INNER JOIN `users_orders`\
ON `users_orders`.`user_id` = `users`.`id`\
LEFT JOIN `orders`\
ON `users_orders`.`order_id` = `orders`.`id`\
JOIN dishes_order\
ON dishes_order.order_id = users_orders.order_id\
JOIN dishes\
ON dishes.id = dishes_order.dish_id\
WHERE `users`.`id` = {args} AND users.last_order_id = orders.id;")
last_orders = cursor.fetchall()
last_order = []
for item in last_orders:
last_order.append({
'dish_name': item['name'],
'dish_price': item['price'],
'dish_id': item['id'],
'dish_count': item['count'],
'users_orders_id': item['order_id'],
'dishes_order_id': item['d_o_id']
})
cursor.close()
connect.close()
self.render("userbasket.html",
timed=timed(),
user=get_user_info(self),
userinfo=userinfo,
last_order=last_order)
def get_table_data():
tablename = request.json.get('table', None)
schemaname = request.json.get('schema', None)
if not tablename:
return sender.BadRequest("missing field: table")
if not schemaname:
schemaname = "public"
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT *
FROM {}.\"{}\"
LIMIT 20""".format(schemaname,tablename)
try:
curr.execute(query)
rows = curr.fetchall()
data = {}
if len(rows) == 0:
data["message"] = "Table doesn't exists or is empty"
else:
data["headers"] = [desc[0] for desc in curr.description]
dataQuery = """SELECT data_type FROM
information_schema.columns WHERE
table_name = '{}' AND table_schema = '{}';""".format(tablename,schemaname);
curr.execute(dataQuery);
data["types"] = [type[0] for type in curr.fetchall()]
data["values"] = rows
return sender.OK(data)
except Exception as e:
return sender.Error(str(e))
def get(self, args):
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute(f"select * from users where id={args}")
userinfo = cursor.fetchone()
cursor.execute(
f"SELECT orders.id, orders.total_price, orders.date FROM `users` \
INNER JOIN `users_orders`\
ON `users_orders`.`user_id` = `users`.`id`\
LEFT JOIN `orders`\
ON `users_orders`.`order_id` = `orders`.`id`\
WHERE `users`.`id` = {args}")
orders = cursor.fetchall()
cursor.execute(f"SELECT dishes.name FROM `users`\
INNER JOIN `users_orders`\
ON `users_orders`.`user_id` = `users`.`id`\
LEFT JOIN `orders`\
ON `users_orders`.`order_id` = `orders`.`id`\
JOIN dishes_order\
ON dishes_order.order_id = users_orders.order_id\
JOIN dishes\
ON dishes.id = dishes_order.dish_id\
WHERE `users`.`id` = {args} AND users.last_order_id = orders.id;")
last_orders = cursor.fetchall()
last_order = []
for item in last_orders:
last_order.append(item['name'])
cursor.close()
connect.close()
self.render("userinfo.html",
timed=timed(),
user=get_user_info(self),
userinfo=userinfo,
orders=orders,
last_order=last_order)
def delete(self, **kwargs):
if not self.is_deletable():
raise RuntimeError(
'Attempting to delete object that is not deletable')
modulename = self.__module__
db = connections.getConnection(modulename)
db.delete(self, **kwargs)
def export_table():
table_name = request.json.get('table', None)
schema_name = request.json.get('schema', None)
if not table_name:
return sender.BadRequest("missing field: table")
if not schema_name:
schema_name = "public"
curr = getConnection(session["user-token"])[0].cursor()
# get columns info
col_query = """SELECT *
FROM information_schema.columns
WHERE table_name = '{}'
AND table_schema = '{}';""".format(table_name, schema_name)
# get columns data
data_query = """SELECT *
FROM {}.\"{}\"""".format(schema_name, table_name)
try:
curr.execute(col_query)
col_rows = curr.fetchall()
data = {}
if len(col_rows) == 0:
data["message"] = "Table does not exists"
else:
data["columns"] = [rows[3:] for rows in col_rows]
curr.execute(data_query)
val_rows = curr.fetchall()
data["values"] = val_rows
return jsonify(data)
except Exception as e:
return sender.Error(str(e))
def get_schemas():
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT schema_name FROM information_schema.schemata"""
try:
curr.execute(query)
rows = [row[0] for row in curr.fetchall()]
return sender.OK(rows)
except Exception as e:
return sender.Error(str(e))
def getDataFromDB(self, dataclass, dbid, **kwargs):
dbmodulename = dataclass.__module__
db = connections.getConnection(dbmodulename)
### try to get data from dbcache before doing query
try:
dat = self.dbcache[dataclass, dbid]
except KeyError:
dat = db.direct_query(dataclass, dbid, **kwargs)
return dat
def create_schema():
name = request.json.get('schema', None)
if not name:
return sender.BadRequest("missing field: schema")
curr = getConnection(session["user-token"])[0].cursor()
query = "CREATE SCHEMA \"{}\";".format(name)
try:
curr.execute(query)
return sender.OK("Schema {} successfully created!".format(name))
except Exception as e:
return sender.Error(str(e))
def post(self, args):
new_name = self.get_argument('name')
new_phone = self.get_argument('phone')
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute(
f'UPDATE users SET name="{new_name}", phone="{new_phone}" WHERE id={args}'
)
self.set_secure_cookie("phone", new_phone)
connect.commit()
self.redirect(f"/user/{args}")
def model():
conn, connstring = getConnection(session["user-token"])
curr = conn.cursor()
query = """SELECT schema_name
FROM information_schema.schemata"""
try:
curr.execute(query)
except Exception as e:
print(e)
abort(500)
rows = curr.fetchall()
return render_template('model.html', template=connstring, schemas=rows)
def raw_sql():
query = request.json.get("query", None)
if not query:
return sender.BadRequest()
curr = getConnection(session["user-token"])[0].cursor()
try:
curr.execute(query)
return sender.OK(curr.fetchall())
except Exception as e:
if "no results to fetch" == str(e): # not an error
return sender.OK("executed")
else:
return sender.Error(str(e))
def drop_schema():
name = request.json.get('schema', None)
if not name:
return sender.BadRequest("missing field: schema")
not_allowed = ["pg_toast", "pg_catalog", "public", "information_schema"]
if name in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = "DROP SCHEMA \"{}\";".format(name)
try:
curr.execute(query)
return sender.OK("Schema {} successfully dropped!".format(name))
except Exception as e:
return sender.Error(str(e))
def get_current_user(self):
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute("select * from users")
users = cursor.fetchall()
connect.commit()
cursor.close()
connect.close()
if self.get_secure_cookie("phone") is not None:
cookier = self.get_secure_cookie("phone").decode('utf-8')
for user in users:
if user['phone'] == cookier:
return self.get_secure_cookie("phone")
return None
def get_user_info(self):
if self.current_user is not None:
name = tornado.escape.xhtml_escape(self.current_user)
connect = connections.getConnection()
cursor = connect.cursor()
count = cursor.execute(f"select * from users where phone = {name}")
# if count == 1:
user_info = cursor.fetchone()
connect.commit()
cursor.close()
connect.close()
return user_info
else:
return None
def get_tables():
schema = request.json.get('schema', None)
if not schema:
return sender.BadRequest("Missing parameter: schema")
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT table_name FROM information_schema.tables
WHERE table_schema = '{}'""".format(schema)
try:
curr.execute(query)
rows = [row[0] for row in curr.fetchall()]
if len(rows) == 0:
return sender.OK({"message": "Schema doesn't exists or is empty"})
return sender.OK(rows)
except Exception as e:
return sender.Error(str(e))
def rename_schema():
name = request.json.get('schema', None)
new_name = request.json.get('new_name', None)
if not name or not new_name:
return sender.BadRequest()
not_allowed = ["pg_toast", "pg_catalog","public","information_schema"]
if name in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = "ALTER SCHEMA \"{}\" RENAME TO \"{}\";".format(name, new_name)
try:
curr.execute(query)
return sender.OK("Schema {} successfully renamed to {}!".format(name, new_name))
except Exception as e:
return sender.Error(str(e))
def post(self, args):
user_id = (get_user_info(self)['id'])
dish_cost = self.request.body.decode('utf8')[:-1]
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute(
f"DELETE FROM `diplom`.`dishes_order` WHERE `id`={args}")
connect.commit()
cursor.execute(f"SELECT * FROM `users` where id = {user_id}")
last_order_id = cursor.fetchone()['last_order_id']
cursor.execute(
f"UPDATE `diplom`.`orders` SET `total_price`=`total_price` - {dish_cost} WHERE `id`={last_order_id}"
)
connect.commit()
self.redirect("/basket/" + str(user_id))
def post(self, *args):
dic = json.loads(self.request.body.decode('utf-8'))
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute(f"SELECT * FROM dishes \
INNER JOIN dish_types \
ON dishes.dish_type = dish_types.id where dish_types.id = {dic['new_val']}"
)
dishes = cursor.fetchall()
cursor.close()
connect.close()
# print(dishes)
dic = json.loads(self.request.body.decode('utf-8'))
# print(dic)
self.write(json.dumps({'status': 'ok', 'sent': dishes}))
self.finish()
def get(self, args):
# print(args)
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute(f"SELECT * FROM dishes \
INNER JOIN dish_types \
ON dishes.dish_type = dish_types.id where dish_types.id = {args}")
dishes = cursor.fetchall()
cursor.execute(f"SELECT * FROM dish_types")
dish_types = cursor.fetchall()
try:
get_us = tornado.escape.xhtml_escape(self.current_user)
except Exception as e:
get_us = False
if get_us != False:
cursor.execute(f"select * from users where phone={get_us}")
userinfo = int(cursor.fetchone()['id'])
cursor.execute(
f"SELECT dishes.id, dishes.name, dishes.price, dishes.description, dishes.img_src, dish_types.id, dishes.dish_type, dish_types.dish_type_name FROM users \
INNER JOIN users_orders \
ON users_orders.user_id = users.id \
JOIN dishes_order \
ON dishes_order.order_id = users_orders.order_id \
JOIN dishes \
ON dishes.id = dishes_order.dish_id \
JOIN dish_types \
ON dish_types.id = dishes.dish_type \
WHERE users.id = {userinfo} AND users.last_order_id = dishes_order.order_id ORDER BY dishes_order.order_id desc"
)
# cursor.execute(f"SELECT * FROM dish_types")
dishes_list = cursor.fetchall()
cursor.close()
connect.close()
self.render("index.html",
dishes=dishes,
timed=timed(),
user=get_user_info(self),
dishes_list=dishes_list,
dish_types=dish_types)
else:
self.render("index.html",
dishes=dishes,
timed=timed(),
user=get_user_info(self),
dishes_list=None,
dish_types=dish_types)
def schema(schema):
conn, connstring = getConnection(session["user-token"])
curr = conn.cursor()
query = """SELECT table_name
FROM information_schema.tables
WHERE table_schema
LIKE '{}'""".format(schema)
try:
curr.execute(query)
except:
abort(500)
message = ""
rows = curr.fetchall()
if len(rows) == 0:
message = "No tables found. Schema name invalid/Schema is empty"
return render_template('schema.html', message=message,
schema=schema, template=connstring, tables=rows)
def drop_table():
schema = request.json.get("schema", None)
name = request.json.get("table", None)
if not name:
return sender.BadRequest()
not_allowed = ["pg_toast", "pg_catalog", "information_schema"]
if not schema:
schema = "public"
if schema in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = "DROP TABLE \"{}\".\"{}\";".format(schema, name)
try:
curr.execute(query)
return sender.OK("Table {} successfully dropped".format(name))
except Exception as e:
return sender.Error(str(e))
def get(self, args):
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute(
f"SELECT * FROM `dishes` INNER JOIN `dishes_ingredients` ON `dishes_ingredients`.`dish_id` = `dishes`.`id` LEFT JOIN `ingredients` ON `dishes_ingredients`.`ingredient_id` = `ingredients`.`id` WHERE `dishes`.`id` = {args}"
)
dishes = cursor.fetchone()
cursor.execute(
f"SELECT ingredients.name, ingredients.description, ingredients.calories, ingredients.id, dishes_ingredients.weight FROM `dishes` INNER JOIN `dishes_ingredients` ON `dishes_ingredients`.`dish_id` = `dishes`.`id` LEFT JOIN `ingredients` ON `dishes_ingredients`.`ingredient_id` = `ingredients`.`id` WHERE `dishes`.`id` = {args}"
)
ingredients = cursor.fetchall()
# print(ingredients)
cursor.close()
connect.close()
self.render("dishinfo.html",
dishes=dishes,
ingredients=ingredients,
timed=timed(),
user=get_user_info(self))
def import_table():
table_name = request.json.get('table', None)
schema_name = request.json.get('schema', None)
data = request.json.get('data', None)
if not table_name or not data:
return sender.BadRequest()
if not schema_name:
schema_name = "public"
columns = data.get('columns', None)
values = data.get('values', None)
if not columns or not values:
return sender.BadRequest('Export data malformed')
try:
conn, connstring = getConnection(session["user-token"])
curr = conn.cursor()
check_query = """SELECT * FROM information_schema.tables WHERE
table_name='{}'
AND table_schema='{}'""".format(table_name, schema_name)
curr.execute(check_query)
if len(curr.fetchall()) != 0:
return sender.Error("Table already exists. Remove it to import")
# create table
create_query = """CREATE TABLE {}.\"{}\"(""".format(
schema_name, table_name)
for items in columns:
create_query = create_query + items[0] + " " + items[4]
if items[3] == "NO":
create_query = create_query + " NOT NULL"
if items[2]:
create_query = create_query + " DEFAULT " + items[2]
create_query = create_query + ","
create_query = create_query[:-1] + ");"
curr.execute(create_query)
# insert data
insert_query = """INSERT INTO {}.\"{}\" VALUES""".format(
schema_name, table_name)
insert_query = insert_query + "(%s" + ", %s" * (len(values[0]) -
1) + ")"
curr.executemany(insert_query, values)
return sender.OK("Data imported successfully!")
except Exception as e:
return sender.Error(str(e))
def rename_table():
schema = request.json.get("schema", None)
name = request.json.get("table", None)
new_name = request.json.get("new_name", None)
if not name or not new_name:
return sender.BadRequest()
not_allowed = ["pg_toast", "pg_catalog", "information_schema"]
if not schema:
schema = "public"
if schema in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = """ALTER TABLE {}.{}
RENAME TO \"{}\"""".format(schema, name, new_name)
try:
curr.execute(query)
return sender.OK("Table {} successfully renamed to {}".format(
name, new_name))
except Exception as e:
return sender.Error(str(e))
def table(schema, table):
conn, connstring = getConnection(session["user-token"])
curr = conn.cursor()
query = """SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name LIKE '{}'
AND table_schema LIKE '{}'""".format(table, schema)
try:
curr.execute(query)
except:
abort(500)
message = ""
rows = curr.fetchall()
if len(rows) == 0:
message = "No columns found. Table name invalid/Table is empty"
return render_template('table.html',
message=message,
schema=schema,
table=table,
template=connstring,
columns=rows)
def get_table_info():
tablename = request.json.get('table', None)
schemaname = request.json.get('schema', None)
if not tablename:
return sender.BadRequest("missing field: table")
if not schemaname:
schemaname = "public"
data = {}
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT
table_schema,
table_name,
column_name,
data_type,
column_default,
is_nullable
FROM information_schema.columns
WHERE table_name = '{}' AND table_schema = '{}';""".format(
tablename, schemaname)
try:
curr.execute(query)
rows = curr.fetchall()
if len(rows) == 0:
data["message"] = "Table doesn't exists or is empty"
else:
data["schema"] = rows[0][0]
data["table"] = rows[0][1]
data["columns"] = []
for row in rows:
data["columns"].append({
"name": row[2],
"type": row[3],
"default": row[4],
"nullable": row[5]
})
return sender.OK(data)
except Exception as e:
return sender.Error(str(e))
def post(self, *args):
dic = json.loads(self.request.body.decode('utf-8'))
order_id = dic['order_id']
way = dic['way']
user_id = (get_user_info(self)['id'])
# print(elemid)
# print(reason)
connect = connections.getConnection()
cursor = connect.cursor()
if way == "up":
cursor.execute(
f"UPDATE `diplom`.`dishes_order` SET `count`= `count` + 1 WHERE `id`={order_id}"
)
connect.commit()
elif way == "down":
cursor.execute(
f"UPDATE `diplom`.`dishes_order` SET `count`= `count` - 1 WHERE `id`={order_id}"
)
connect.commit()
else:
print('SOME ERROR!')
cursor.close()
connect.close()
def post(self):
phone = re.sub('[^0-9]', '', self.get_argument("phone"))
password = self.get_argument("password")
connect = connections.getConnection()
cursor = connect.cursor()
cursor.execute("select * from users")
users = cursor.fetchall()
connect.commit()
cursor.close()
connect.close()
for user in users:
if user['phone'] == phone and user['password'] == password:
self.set_secure_cookie("phone", user['phone'])
# connect = connections.getConnection()
# cursor = connect.cursor()
# cursor.execute("update users set last_cookie = %s where login = %s", (user['login'], user['login']))
# connect.commit()
# cursor.close()
# connect.close()
self.redirect("/")
break
else:
self.clear_all_cookies(path="/")
self.write("u need to be logined")
def insert(self, **kwargs):
modulename = self.__module__
db = connections.getConnection(modulename)
db.insert(self, **kwargs)
def direct_query(cls, dbid, **kwargs):
modulename = cls.__module__
db = connections.getConnection(modulename)
result = db.direct_query(cls, dbid, **kwargs)
return result
def close(self):
modulename = self.__module__
db = connections.getConnection(modulename)
db.close()
def delete(self, **kwargs):
if not self.is_deletable():
raise RuntimeError('Attempting to delete object that is not deletable')
modulename = self.__module__
db = connections.getConnection(modulename)
db.delete(self, **kwargs)
def query(self, **kwargs):
modulename = self.__module__
db = connections.getConnection(modulename)
results = db.query(self, **kwargs)
return results
