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()) == [])
