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