import psycopg2 from psycopg2.extras import RealDictCursor import os def connect(): rds_host = os.environ['DATABASE_HOST'] db_user = os.environ['DATABASE_USER'] password = os.environ['DATABASE_PASSWORD'] db_name = os.environ['DATABASE_DB_NAME'] port = os.environ['DATABASE_PORT'] return psycopg2.connect(sslmode="require", host=rds_host, user=db_user, password=password, dbname=db_name, connect_timeout=10000, port=port, keepalives_interval=30) class Order: def __init__(self, email): self.email = email def get_orders(self, order_id=None): with connect() as dbconn: with dbconn.cursor(cursor_factory=RealDictCursor) as cur: sqlstmt = "select d.item_id, d.qty, d.unit_price, o.order_date from order_details d join orders o on o.order_id = d.order_id and o.email = '{}'".format(self.email) if order_id: sqlstmt = "{} where o.order_id = {}".format(sqlstmt, order_id) print (sqlstmt) cur.execute(sqlstmt) return cur.fetchall() def add(self, data): with connect() as dbconn: with dbconn.cursor(cursor_factory=RealDictCursor) as cur: sqlstmt = "select nextval('order_seq');" cur.execute(sqlstmt) order_id = cur.fetchone()['nextval'] items = data.get('items') total = 0 for x in items: sqlstmt = 'insert into order_details(order_id, item_id, qty, unit_price) values({}, {}, {}, {});'.format(order_id, x.get('item_id'), x.get('qty'), x.get('unit_price')) cur.execute(sqlstmt) total += (x.get('qty') * x.get('unit_price')) sqlstmt = "insert into orders(order_id, order_date, order_total, email) values({}, now(), {}, '{}')".format(order_id, total, self.email) cur.execute(sqlstmt) dbconn.commit() return order_id