Created
October 2, 2018 06:59
-
-
Save joelethan/808cd3aa0f6a2c09f8b77beabf4ef6d5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import psycopg2 | |
| class DatabaseConnection: | |
| def __init__(self): | |
| connection_credentials = """ | |
| dbname='postgres' user='postgres' password='166091postgres' | |
| host='localhost' port='5432' | |
| """ | |
| try: | |
| self.connection = psycopg2.connect(connection_credentials) | |
| self.connection.autocommit = True | |
| self.cursor = self.connection.cursor() | |
| except Exception as e: | |
| print(e) | |
| print('Failed to connect to db') | |
| def create_tables(self): | |
| """ Create all database tables""" | |
| create_table = "CREATE TABLE IF NOT EXISTS users \ | |
| ( user_id SERIAL PRIMARY KEY, username VARCHAR(10), \ | |
| email VARCHAR(100), password VARCHAR(100), admin BOOLEAN NOT NULL);" | |
| self.cursor.execute(create_table) | |
| create_table = "CREATE TABLE IF NOT EXISTS menu \ | |
| ( food_id SERIAL PRIMARY KEY, foodname VARCHAR(15), price INTEGER);" | |
| self.cursor.execute(create_table) | |
| create_table = "CREATE TABLE IF NOT EXISTS orders \ | |
| ( order_id SERIAL PRIMARY KEY, \ | |
| user_id INTEGER NOT NULL REFERENCES users(user_id), \ | |
| food_id INTEGER NOT NULL REFERENCES menu(food_id), \ | |
| quantity INTEGER, status VARCHAR(10));" | |
| self.cursor.execute(create_table) | |
| def add_user(self, username, email, password): | |
| query = "INSERT INTO users (username, email, password, admin) VALUES\ | |
| ('{}', '{}', '{}', False);".format(username, email, password) | |
| self.cursor.execute(query) | |
| def add_food_to_menu(self, foodname, price): | |
| query = "INSERT INTO menu (foodname, price) VALUES ('{}', '{}');"\ | |
| .format(foodname, price) | |
| self.cursor.execute(query) | |
| def get_orders(self): | |
| query = "SELECT * FROM orders;" | |
| self.cursor.execute(query) | |
| orders = self.cursor.fetchall() | |
| return orders | |
| def get_users(self): | |
| query = "SELECT * FROM users;" | |
| self.cursor.execute(query) | |
| users = self.cursor.fetchall() | |
| return users | |
| def get_menu(self): | |
| query = "SELECT * FROM menu; " | |
| self.cursor.execute(query) | |
| menu = self.cursor.fetchall() | |
| return menu | |
| def get_an_order(self, column, value): | |
| query = "SELECT * FROM orders WHERE {} = '{}';".format(column, value) | |
| self.cursor.execute(query) | |
| user = self.cursor.fetchone() | |
| return user | |
| def get_user(self, column, value): | |
| query = "SELECT * FROM users WHERE {} = '{}';".format(column, value) | |
| self.cursor.execute(query) | |
| user = self.cursor.fetchone() | |
| return user | |
| def promote_user(self, order_id): | |
| query = "UPDATE users SET admin = True WHERE user_id = '{}';\ | |
| ".format(order_id) | |
| self.cursor.execute(query) | |
| def place_order(self, user_id, food_id, quantity): | |
| query = "INSERT INTO orders (user_id, food_id, quantity, status) \ | |
| VALUES ('{}', '{}', '{}', 'pending');".format(user_id, food_id, quantity) | |
| self.cursor.execute(query) | |
| def update_status(self, order_id, status): | |
| query = "UPDATE orders SET status = '{}' WHERE order_id = '{}';\ | |
| ".format(status, order_id) | |
| self.cursor.execute(query) | |
| def get_history_by_userid(self, userid): | |
| query = "SELECT * FROM orders WHERE user_id = '{}';".format(userid) | |
| self.cursor.execute(query) | |
| history = self.cursor.fetchall() | |
| return history | |
| def auto_admin(self): | |
| query = "UPDATE users SET admin = True WHERE user_id < 2;" | |
| self.cursor.execute(query) | |
| def drop_tables(self):#drop table ticket;drop table userr;drop table event; | |
| query = "DROP TABLE orders;DROP TABLE menu;DROP TABLE users; " | |
| self.cursor.execute(query) | |
| return "Droped" | |
| # DatabaseConnection().create_tables() | |
| # (DatabaseConnection().add_user('joel1', 'joel@mail', 'password')) # | |
| # (DatabaseConnection().place_order(2, 1, 9)) # | |
| # print(DatabaseConnection().get_an_order('order_id', 2)) # | |
| # print(DatabaseConnection().update_status( 2, 'Pending')) # | |
| # print(DatabaseConnection().get_menu()) # | |
| # (DatabaseConnection().add_food_to_menu('Pork', 20000)) # | |
| # print(DatabaseConnection().get_history_by_userid(1)) # | |
| # print(DatabaseConnection().get_orders()) # | |
| # print(DatabaseConnection().get_users()) # | |
| # (DatabaseConnection().drop_tables()) | |
| # print(DatabaseConnection().get_user('email', '3')) # |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment