def get_products(): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("SELECT * FROM product") rows = cur.fetchall() return rows
def get_shop_types(): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("SELECT * FROM shop_type") rows = cur.fetchall() return rows
def sell_stock(sellingCorpId, targetCorpId, quantity): sellerCorp = corp_api.get_corp_by_id(sellingCorpId) sellerMoney = sellerCorp['money'] sellerShares = shares_api.get_corp_shares_for_corp_id( sellingCorpId, targetCorpId) quantityOfShares = sellerShares['quantity'] targetCorp = corp_api.get_corp_by_id(targetCorpId) targetCorpSharePrice = targetCorp['share_price'] totalAmount = targetCorpSharePrice * int(quantity) if quantityOfShares >= quantity: con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute( "DELETE FROM shares WHERE corporation_id = %s AND owning_corporation_id = %s LIMIT %s", (targetCorpId, sellingCorpId, quantity)) cur.execute( "UPDATE corporation SET money = money + %s WHERE id = %s", (totalAmount, sellingCorpId)) cur.execute( "UPDATE corporation SET momentum = momentum + %s WHERE id = %s", (quantity, targetCorpId))
def get_users(): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("SELECT id, username, email FROM user") rows = cur.fetchall() return rows
def get_product_by_id(id): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("SELECT * FROM product WHERE id = %s", id) rows = cur.fetchone() return rows
def get_user_by_id(id): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("SELECT id, username, email FROM user WHERE id = %s", id) row = cur.fetchone() row['corp'] = corp_api.get_corp_by_id(row['id']) return row
def get_corp_shares_for_corp_id(id, corpId): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute( "SELECT count(*) as quantity FROM shares WHERE owning_corporation_id = %s AND corporation_id = %s", (id, corpId)) row = cur.fetchone() return row
def get_corp_by_id(id): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute( "SELECT id, name, ticker, money, share_price FROM corporation WHERE id = %s", id) rows = cur.fetchone() rows['shops'] = shop_api.get_shops_by_corp_id(rows['id']) return rows
def get_userid_from_session(sessionid): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("SELECT user_id FROM sessions WHERE session_id = %s", (sessionid, )) rows = cur.fetchone() if rows: return rows['user_id'] return None
def create_session(userid): con = dbconn.get_new_connection() with con: sessionid = str(uuid.uuid4()) cur = con.cursor() cur.execute("INSERT INTO sessions VALUES (%s, %s, NOW())", (sessionid, userid)) cur.close() con.commit() return sessionid
def is_valid_session(sessionid, userid): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute( "SELECT count(*) as cnt FROM sessions WHERE session_id = %s and user_id = %s", (sessionid, userid)) row = cur.fetchone() if row['cnt'] > 0: return True return False
def get_all_shares_for_corp_id(id): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute( """SELECT corporation_id, count(*) as quantity, round(avg(purchase_price), 2) as purchase_price, c.share_price, round((c.share_price - avg(purchase_price)) * count(*), 2) as net_gain FROM shares s JOIN corporation c on c.id = s.corporation_id WHERE owning_corporation_id = %s GROUP BY corporation_id, date_created""", id) rows = cur.fetchall() return rows
def login(username, password): con = dbconn.get_new_connection() with con: cur = con.cursor() passwordhash = hashlib.sha224(password).hexdigest() cur.execute( "SELECT id FROM user WHERE username = %s and password_hash = %s", (username, passwordhash)) row = cur.fetchone() if row: sessionid = create_session(row['id']) return sessionid return None
def get_shops_by_corp_id(id): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute( "SELECT corporation_id, name, shop_type_id, product_id, product_start, product_complete FROM shop WHERE corporation_id = %s", id) rows = cur.fetchall() for row in rows: if row['product_id']: row['product'] = product_api.get_product_by_id( row['product_id']) else: row['product'] = None return rows
def buy_stock(buyingCorpId, targetCorpId, quantity): buyerCorp = corp_api.get_corp_by_id(buyingCorpId) buyerMoney = buyerCorp['money'] targetCorp = corp_api.get_corp_by_id(targetCorpId) targetCorpSharePrice = targetCorp['share_price'] totalAmount = targetCorpSharePrice * int(quantity) if buyerMoney >= totalAmount: con = dbconn.get_new_connection() with con: cur = con.cursor() for x in range(0, quantity): cur.execute( "INSERT INTO shares VALUES (null, %s, %s, %s, NOW())", (targetCorpId, buyingCorpId, targetCorpSharePrice)) cur.execute( "UPDATE corporation SET money = money - %s WHERE id = %s", (totalAmount, buyingCorpId)) cur.execute( "UPDATE corporation SET momentum = momentum - %s WHERE id = %s", (quantity, targetCorpId))
import dbconn #UPDATE STOCKS con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("UPDATE corporation SET momentum = momentum - sign(momentum)")
def destroy_session(sessionid): con = dbconn.get_new_connection() with con: cur = con.cursor() cur.execute("DELETE FROM sessions WHERE session_id = %s", sessionid) return True