def sell(ticker, num_shares): with Database('Terminal.db') as db: username = login() price = float(get_quote(ticker)) num_shares = int(num_shares) db.cursor.execute( """SELECT shares FROM stocks WHERE username='******' AND company='{}';""" .format(username, ticker)) my_shares = db.cursor.fetchone()[0] db.cursor.execute( """SELECT funds FROM users WHERE username='******';""".format( username)) funds = db.cursor.fetchone()[0] if int(my_shares) >= num_shares: difference_shares = my_shares - num_shares sum_funds = funds + (price * num_shares) db.cursor.execute( """UPDATE stocks SET shares={} WHERE username='******' AND company='{}';""" .format(difference_shares, username, ticker)) db.cursor.execute( """UPDATE users SET funds={} WHERE username='******';""".format( sum_funds, username)) return ('You have sold {} shares for {}'.format( num_shares, ticker)) else: return ('Insufficient Shares... Please buy more')
def create_table(): with Database() as db: db.c.execute('''CREATE TABLE IF NOT EXISTS balance( pk INTEGER PRIMARY KEY AUTOINCREMENT, user_name VARCHAR, available_balance FLOAT, original_deposits FLOAT);''') db.c.execute('''CREATE TABLE IF NOT EXISTS Holdings( pk INTEGER PRIMARY KEY AUTOINCREMENT, Stocks VARCHAR, numShares INTEGER, user_name VARCHAR);''') db.c.execute('''CREATE TABLE IF NOT EXISTS users( pk INTEGER PRIMARY KEY AUTOINCREMENT, user_names VARCHAR, password VARCHAR, apikey VARCHAR, admin BOOL);''') db.c.execute('''CREATE TABLE IF NOT EXISTS transactions( pk INTEGER PRIMARY KEY AUTOINCREMENT, user_names VARCHAR, history VARCHAR);''')
def update_earnings(username): with Database('Terminal.db') as db: db.cursor.execute( """SELECT funds FROM users WHERE username='******';""".format( username)) funds = db.cursor.fetchone()[0] db.cursor.execute( """SELECT shares FROM stocks WHERE username='******';""".format( username)) shares = db.cursor.fetchall() db.cursor.execute( """SELECT company from stocks WHERE username='******';""".format( username)) ticker_symbol = db.cursor.fetchall() quotelist = [] sharelist = [] share_values = [] tickerlist = [] for all in ticker_symbol: quotelist.append(get_quote(all[0])) tickerlist.append(all[0]) for all2 in shares: sharelist.append(all2[0]) for x in range(len(quotelist)): share_values.append(quotelist[x] * sharelist[x]) net_worth = funds + sum(share_values) earnings = net_worth - 10000.0 db.cursor.execute( """UPDATE users SET earnings={} WHERE username='******';""".format( earnings, username)) print('Earnings have been updated...') input('Press any key to continue: ')
def buy(ticker, num_shares): with Database('Terminal.db') as db: username = login() price = float(get_quote(ticker)) * num_shares db.cursor.execute( """SELECT funds FROM users WHERE username='******';""".format( username)) funds = db.cursor.fetchone()[0] if float(funds) >= float(price): difference_funds = float(funds) - float(price) db.cursor.execute( """UPDATE users SET funds={} WHERE username='******';""". format(difference_funds, username=username)) try: db.cursor.execute( """SELECT shares FROM stocks WHERE username='******' AND company='{}';""" .format(username, ticker)) old_shares = db.cursor.fetchone()[0] sum_shares = int(old_shares) + int(num_shares) db.cursor.execute( """UPDATE stocks SET shares={} WHERE username='******' AND company='{}';""" .format(sum_shares, username, ticker)) except: db.cursor.execute( """INSERT INTO stocks(username,company,shares) VALUES('{}','{}',{});""" .format(username, ticker, num_shares)) print('You have successfully purchased {} stocks of "{}"'.format( str(num_shares), ticker)) else: print('Insufficient Funds')
def create_login(username, password): with Database('Terminal.db') as db: password2 = input('Please confirm your password: '******'Would you like to make this user an admin?(Y/N): ') if create_admin.lower() == 'y': db.cursor.execute( """INSERT INTO users(username,password,funds, earnings, apikey, admin) VALUES('{}','{}', 10000.0, 0.0, '{}', {});""" .format(username, password, apikey, True)) return (username) elif create_admin.lower() == 'n': db.cursor.execute( """INSERT INTO users(username,password,funds, earnings, apikey, admin) VALUES('{}','{}', 10000.0, 0.0, '{}', {});""" .format(username, password, apikey, False)) return (username) else: print('Please enter a Y or N') create_login( input('What would you like your username to be?: '), input('Select a password: '******'Your passwords did not match...please try again.') create_login(input('What would you like your username to be?: '), input('Select a password: '))
def view_portfolio(): with Database('Terminal.db') as db: try: username = login() db.cursor.execute( """SELECT * FROM stocks WHERE username='******';""".format( username)) all_table = db.cursor.fetchall() db.cursor.execute( """SELECT earnings FROM users WHERE username='******';""".format( username)) earnings = db.cursor.fetchone()[0] listofinfo = [] print('\nYour current earnings are ' + str(earnings)) if len(all_table) == 0: print('User does not own any shares') else: for each in all_table: if each[3] == 0: continue else: print('\nYou own ' + str(each[3]) + ' shares of ' + str(each[2]) + '\n') except: print('User does not own any shares')
def leaderboard(username): with Database('Terminal.db') as db: db.cursor.execute( """SELECT admin FROM users WHERE username='******';""".format( username)) admin_status = db.cursor.fetchone()[0] if admin_status: db.cursor.execute( """SELECT earnings FROM users ORDER BY earnings DESC;""") leaderearnings = db.cursor.fetchall() db.cursor.execute( """SELECT username FROM users ORDER BY earnings DESC;""") leaders = db.cursor.fetchall() print('The current standings are :') time.sleep(1) listofleaders = [] if len(leaders) <= 10: for x in range(len(leaders)): listofleaders.append('Rank:' + str(x + 1) + ' is ' + str(leaders[x][0]) + ' with ' + str(leaderearnings[x][0]) + ' in earnings \n') else: for x in range(10): listofleaders.append('Rank:' + str(x + 1) + ' is ' + str(leaders[x][0]) + ' with ' + str(leaderearnings[x][0]) + ' in earnings \n') return listofleaders else: return ('You do not have admin status')
def get_id_in_matrix(username): with Database() as db: db.c.execute( '''SELECT user_id FROM users WHERE username = '******';'''.format( username)) results = db.c.fetchone() return results[0]
def deposit(user_name, deposit): with Database() as db: transaction = "You deposited $" + str(deposit) + " into your account." record_transaction(user_name, transaction) db.c.execute('''SELECT available_balance FROM balance WHERE user_name ='{}';'''.format(user_name)) balance = db.c.fetchone() db.c.execute('''SELECT original_deposits FROM balance WHERE user_name ='{}';'''.format(user_name)) orig_deposits = db.c.fetchone() if balance and orig_deposits: new_deposit = orig_deposits[0] + float(deposit) new_balance = balance[0] + float(deposit) db.c.execute( '''UPDATE balance SET available_balance = {}, original_deposits={} WHERE user_name = '{}';'''.format( new_balance, new_deposit, user_name)) return 1 else: db.c.execute( '''INSERT INTO balance(user_name, available_balance, original_deposits) VALUES('{}',{},{});'''.format( user_name, deposit, deposit)) return 1
def admin(): with Database() as db: db.c.execute('''SELECT * FROM users;''') rows = db.c.fetchall() if len(rows) == 0: return 1 else: return 0
def api_authenticate(apikey): with Database('Terminal.db') as db: db.cursor.execute( """SELECT username FROM users WHERE apikey='{}';""".format(apikey)) username = db.cursor.fetchone() if username: return username[0] else: username = False
def get_original_deposits(user_name): with Database() as db: db.c.execute('''SELECT original_deposits FROM balance WHERE user_name='{}';'''.format(user_name)) result = db.c.fetchone() if result: return result[0] else: return 0
def check_user_exist(user_name): with Database() as db: db.c.execute('''SELECT * FROM users WHERE username='******';'''.format(user_name)) result=db.c.fetchone() if result: return True else: return False
def get_balance(user_name): with Database() as db: db.c.execute('''SELECT available_balance FROM balance WHERE user_name='{}';'''.format(user_name)) result = db.c.fetchone() if result: return result[0] else: return 0
def api_authenticate(apikey): with Database() as db: sql = '''SELECT user_names FROM users WHERE apikey=?;''' db.c.execute(sql, (apikey, )) result = db.c.fetchone() if result: return result[0] else: return False
def search(search_item): '''search the item in restaurant name column ''' with Database() as db: db.c.execute( '''SELECT business_id FROM restaurants WHERE name LIKE '%{}%';'''. format(search_item)) results = db.c.fetchall() return results
def check_user(user_name, password): with Database() as db: db.c.execute('''SELECT * FROM users WHERE user_names='{}' AND password='******';'''.format(user_name, password)) result = db.c.fetchone() if result: return True else: return False
def current_balance(username): with Database('Terminal.db') as db: try: db.cursor.execute( """SELECT funds FROM users WHERE username='******';""".format( username)) funds = db.cursor.fetchone() return (str(funds[0])) except: return ('Could not find user')
def print_transactions(user_name): with Database() as db: db.c.execute( '''SELECT history FROM transactions WHERE user_names='{}';'''. format(user_name)) trades = db.c.fetchall() dict = {"Username": user_name} for x in range(len(trades)): dict["Transaction " + str(x)] = trades[x][0] return (dict)
def lookup_database(stock, user_name): with Database() as db: db.c.execute('''SELECT numShares FROM Holdings WHERE Stocks='{}' AND user_name='{}';'''.format( stock.upper(), user_name)) result = db.c.fetchone() if result: return result[0] else: return False
def create_user(user_name , password): with Database() as db: user_name_taken = check_user_exist(user_name) if user_name_taken==False: sql='''INSERT INTO users(username,password) Values(?,?);''' db.c.execute(sql, (user_name, password)) return True else: return False
def get_status(user_name): with Database() as db: db.c.execute( '''SELECT admin FROM users WHERE user_names='{}';'''.format( user_name)) result = db.c.fetchone() if result: return result[0] else: return False
def get_total_equity(user_name): with Database() as db: db.c.execute('''SELECT * FROM Holdings WHERE user_name='{}';'''.format( user_name)) list = db.c.fetchall() total_equity = 0 if list: for x in list: total_equity += quote(x[1]) * x[2] else: print("\nNo Stocks Owned") return total_equity
def get_preference(preference): with Database() as db: db.c.execute('''SELECT categories, business_id FROM restaurants;''') results = db.c.fetchall() recommends = [] for x in results: if (all(y in x[0] for y in preference)): recommends.append(x[1]) if len(recommends) == 0: for x in results: if (y in x[0] for y in preference): recommends.append(x[1]) return recommends
def create_user(user_name, password): with Database() as db: create_table() user_name_taken = check_user_exist(user_name) if user_name_taken == False: apikey = str(uuid.uuid4()) admin_ = admin() sql = '''INSERT INTO users(user_names,password,apikey,admin) Values(?,?,?,?);''' db.c.execute(sql, (user_name, password, apikey, admin_)) return True else: return False
def print_holdings(user_name): with Database() as db: db.c.execute('''SELECT * FROM Holdings WHERE user_name='{}';'''.format( user_name)) list = db.c.fetchall() dict = {} if list: for x in list: dict[x[1]] = { "Shares": str(x[2]), "Equity": str(quote(x[1]) * x[2]) } else: dict.update({"No Stocks Owned": False}) return (dict)
def add_value(value): with Database('Terminal.db') as db: try: username = login() db.cursor.execute( """SELECT funds FROM users WHERE username='******';""".format( username)) old_val = db.cursor.fetchone()[0] new_val = float(value) + float(old_val) db.cursor.execute( """UPDATE users SET funds={} WHERE username='******';""".format( new_val, username)) return ('Your new balance is {}.\n'.format(new_val)) except: return ('Please enter a valid amount. ')
def get_user_id(business_id, star, username): with Database() as db: db.c.execute( '''SELECT user_id FROM reviews WHERE business_id = '{}' AND stars = {};''' .format(business_id, star)) results = db.c.fetchall() count = 0 while (len(results) == 0): db.c.execute( '''SELECT user_id FROM reviews WHERE business_id = '{}' AND stars = {};''' .format(business_id, star + count)) results = db.c.fetchall() count += 1 db.c.execute( '''UPDATE users SET user_id = '{}' WHERE username = '******'; '''. format(results[0][0], username)) return results[0][0]
def get_leaderboards(): with Database() as db: db.c.execute('''SELECT user_names FROM users WHERE admin=0;''') list_users = db.c.fetchall() leaderboard = [] print(list_users) for x in list_users: leaderboard.append((x[0], get_percentage_gain(x[0]))) leaderboard.sort(key=lambda tup: tup[1], reverse=True) count = 0 for x in leaderboard: print("User: "******". Percentage Gain or Loss: " + str(x[1])) count += 1 if (count == 10): break time.sleep(10)
def search(search_item): '''search the item in restaurant name column ''' with Database() as db: db.c.execute('''SELECT name, stars, address, state, city, postal_code FROM restaurants WHERE name LIKE '%{}%';'''.format(search_item)) results=db.c.fetchall() if results: restaurants = [] for row in results: dic = { 'name': row[0], 'mlstar': row[1], 'Address': row[2]+' '+row[3]+' '+row[4]+' '+row[5] } restaurants.append(dic) return restaurants else: return False
import os from random import random from orm import Database db = Database('db.sqlite.test') class Post(db.Model): random = float text = str def __init__(self, text): self.text = text self.random = random() try: post = Post('Hello World').save() assert(post.id == 1) post.text = 'Hello Mundo' post.update() db.commit() assert(post.text == 'Hello Mundo') post.delete() db.commit() objects = Post.manager() objects.save(Post('Hello World')) assert(set(objects.get(2).public.keys()) == set(['id', 'text', 'random'])) assert(isinstance(objects.get(2).random, float)) db.close() assert(list(objects.all()) == [])