def loader():
q = Loader.quote_lookup()
print(q)
with Database() as db:
db.cursor.execute('''SELECT stop_loss FROM log;''')
all_stop_loss = db.cursor.fetchall()
try:
latest_stop_loss = all_stop_loss[-1][0]
except:
pass
with Database() as db:
try:
db.cursor.execute(
'''SELECT ticker from log WHERE stop_loss="{}";
'''.format(latest_stop_loss))
ticker = db.cursor.fetchone()[0]
tick = ticker.lower()
tick_current = q['{}_current'.format(tick)]
if latest_stop_loss >= tick_current:
if tick == 'tvix':
return Trader.tvix_sell_gains(tick_current)
else:
return Trader.svxy_sell_gains(tick_current)
else:
return tracker(q)
except:
return tracker(q)
def user_page(self, username):
with Database() as db:
db.cursor.execute(
'''SELECT * FROM posts WHERE username='******' ORDER BY time DESC;'''
.format(username=username))
user_posts = db.cursor.fetchall()
return user_posts
def user_page(username):
bill_keys = [
"bill_id", "total_due", "due_by", "due_to", "created_on",
"caption", "due_date", "contributors"
]
pay_keys = [
"payment_id", "amount_paid", "paid_by", "paid_to", "created_on",
"note", "bill_owner", "original_bill_id"
]
with Database() as db:
# db.cursor.execute('''SELECT * FROM bills WHERE due_by='{username}'
# UNION
# SELECT * FROM payments WHERE paid_by='{username}'
# ORDER BY created_on DESC;'''
# .format(username = username))
# user_posts = db.cursor.fetchall()
db.cursor.execute(
'''SELECT * FROM bills WHERE due_by='{username}' '''.format(
username=username))
all_bills = db.cursor.fetchall()
db.cursor.execute(
'''SELECT * FROM payments WHERE paid_by='{username}' '''.
format(username=username))
all_pays = db.cursor.fetchall()
bills = [dict(zip(bill_keys, i)) for i in all_bills]
pays = [dict(zip(pay_keys, i)) for i in all_pays]
posts = bills + pays
posts = sorted(posts, key=itemgetter("created_on"), reverse=True)
return posts
def user_orders(self):
with Database() as db:
db.cursor.execute(
'''SELECT * FROM orders WHERE user_id={user_id};'''.format(
user_id=self.id_lookup(self.username)))
table_user_orders = db.cursor.fetchall()
return table_user_orders
def like(self, post_id):
time_ = datetime.datetime.now()
time_ = time_.strftime("%c")
with Database() as db:
db.cursor.execute(
'''SELECT post_id FROM likes WHERE user_who_liked='{username}' and post_id='{post_id}';'''
.format(username=self.username, post_id=post_id))
user_liked_this_post = db.cursor.fetchone()
print(
'USER LIKED THIS POST USER LIKED THIS POST USER LIKED THIS POST'
)
print(user_liked_this_post)
if user_liked_this_post is None:
print("this user has not yet liked this post")
db.cursor.execute(
'''UPDATE posts SET likes = likes + 1 WHERE post_id={post_id};'''
.format(post_id=post_id))
db.cursor.execute(
'''INSERT INTO likes (user_who_liked, post_id, time)
VALUES(?,?,?);''',
(self.username, post_id, time_))
else:
print("THIS IS THE UNLIKE CONDITION")
db.cursor.execute(
'''UPDATE posts SET likes = likes - 1 WHERE post_id={post_id};'''
.format(post_id=post_id))
db.cursor.execute(
'''DELETE FROM likes WHERE post_id={post_id};'''.format(
post_id=post_id))
return True
def svxy_buy_trade_log(ticker, open_price, current_price, stop_loss):
with Database() as db:
db.cursor.execute(
'''
INSERT INTO log(time,ticker,price,stop_loss,volume,buy_sell)
VALUES(?,?,?,?,?,?);
''', (tm, ticker, current_price, stop_loss, 100, 'buy'))
print('bought svxy')
def id_lookup(self, username):
# Returns user_id according to username
with Database() as db:
db.cursor.execute(
'''SELECT user_id FROM users WHERE "{}"= username;'''.format(
self.username))
user_id = db.cursor.fetchone()[0]
return user_id
def sell_holdings(user_name,ticker_symbol,trade_volume,price,owned):
with Database('master.db') as db:
new_owned = owned - float(trade_volume)
db.update_holding(user_name,ticker_symbol,new_owned)
balance = db.check_balance(user_name)
new_balance = balance + (float(trade_volume)*float(price)) - 6.25
db.update_cash(user_name,new_balance)
db.insert_4('orders','username','ticker_symbol','trade_volume','execution_price',user_name,ticker_symbol,trade_volume,price)
def buy_holdings(user_name,ticker_symbol,trade_volume,price):
with Database('master.db') as db:
try:
current = db.check_holdings(user_name,ticker_symbol)
new_current = trade_volume + current
db.update_holding(user_name,ticker_symbol,new_current)
except:
db.new_holding(user_name,ticker_symbol,trade_volume,(price*trade_volume))
def generate(list_of_users,list_of_bills,list_of_pays):
for user in users:
with Database() as db:
db.cursor.execute('''INSERT INTO users (username)
VALUES (?);''',
(user,))
for bill in list_of_bills:
with Database() as db:
db.cursor.execute('''INSERT INTO bills (total_due, due_by, due_to, created_on, caption)
VALUES (?,?,?,?,?);''',
(bill[0],bill[1],bill[2],bill[3],bill[4]))
for pay in list_of_pays:
with Database() as db:
db.cursor.execute('''INSERT INTO payments (amount_paid, paid_by, paid_to, created_on, note)
VALUES (?,?,?,?,?);''',
(pay[0],pay[1],pay[2],pay[3],pay[4]))
return True
def post(self, photo, caption):
time_ = datetime.datetime.now()
time_ = time_.strftime("%c")
with Database() as db:
db.cursor.execute(
'''INSERT INTO posts (username, photo, caption, time, type)
VALUES (?, ?, ?, ?, ?);''',
(self.username, photo, caption, time_, "original"))
return True
def buy(self, ticker_symbol, trade_volume):
# Will check if user has enough funds to make purchase
last_price = wrapper.lastprice(ticker_symbol)
brokerage_fee = 6.95
buy_cost = (float(last_price) * float(trade_volume)) + brokerage_fee
user_balance = self.user_balance()
if float(user_balance) > float(buy_cost):
with Database() as db:
# Update orders
time_ = time.time()
user_id = self.id_lookup(self.username)
db.cursor.execute(
'''INSERT INTO orders (
user_id,
ticker_symbol,
last_price,
trade_volume,
unix_time)
VALUES(?,?,?,?,?);''',
(user_id, ticker_symbol, buy_cost, trade_volume, time_))
# Update holdings
db.cursor.execute(
'''SELECT trade_volume FROM holdings WHERE ticker_symbol = "{}" and user_id = {user_id};'''
.format(ticker_symbol,
user_id=self.id_lookup(self.username)))
old_volume = db.cursor.fetchone()
if old_volume is None:
db.cursor.execute(
'''INSERT INTO holdings (
user_id,
ticker_symbol,
vwap,
trade_volume)
VALUES(?,?,?,?);''',
(user_id, ticker_symbol, last_price, trade_volume))
else:
new_volume = old_volume[0] + int(trade_volume)
db.cursor.execute(
'''UPDATE holdings SET trade_volume = {} WHERE user_id = {user_id} AND ticker_symbol = "{ticker_symbol}";'''
.format(new_volume,
user_id=self.id_lookup(self.username),
ticker_symbol=ticker_symbol))
db.cursor.execute(
'''UPDATE holdings SET VWAP = {} WHERE user_id = {user_id} AND ticker_symbol = "{ticker_symbol}";'''
.format(self.vwap(ticker_symbol),
user_id=self.id_lookup(self.username),
ticker_symbol=ticker_symbol))
# Update balance
new_balance = user_balance - buy_cost
db.cursor.execute(
'''UPDATE users SET balance = {} WHERE user_id = {user_id};'''
.format(new_balance,
user_id=self.id_lookup(self.username)))
return True
else:
return False
def signup(self, username, password, confirm):
try:
if password == confirm:
with Database() as db:
db.cursor.execute(
'''INSERT INTO users (username, password)
VALUES(?,?);''', (username, password))
return True
except sqlite3.IntegrityError:
return False
def user_balance(self):
with Database() as db:
db.cursor.execute('''SELECT balance FROM users WHERE user_id={user_id};'''
.format(user_id = self.id_lookup(self.username)))
balance = db.cursor.fetchone()[0]
profit = float(balance) - 100000
portfolio_value = balance
db.cursor.execute('''UPDATE users SET portfolio_value = {} WHERE user_id = {user_id};'''
.format(portfolio_value, user_id = self.id_lookup(self.username)))
return float(balance)
def all_payments():
keys = [
"payment_id", "amount_paid", "paid_by", "paid_to", "created_on",
"note", "bill_owner", "original_bill_id"
]
with Database() as db:
db.cursor.execute('''SELECT * FROM payments
ORDER BY created_on DESC;''')
all_payments = db.cursor.fetchall()
payments = [dict(zip(keys, i)) for i in all_payments]
return payments
def all_bills():
keys = [
"bill_id", "total_due", "due_by", "due_to", "created_on", "caption"
]
with Database() as db:
db.cursor.execute('''SELECT * FROM bills
ORDER BY created_on DESC;''')
all_bills = db.cursor.fetchall()
bills = [dict(zip(keys, i)) for i in all_bills]
print(bills)
return bills
def login(self, password):
with Database() as db:
db.cursor.execute(
'''SELECT password FROM users WHERE username="******";'''.
format(username=self.username))
correct_password = db.cursor.fetchone()
if correct_password is None:
return False
else:
if correct_password[0] == password:
return True
else:
return False
def login(self, password):
# Will check if username exists,
# then if password input matches password in db
with Database() as db:
db.cursor.execute('''SELECT password FROM users WHERE username="******";'''
.format(username=self.username))
correct_password = db.cursor.fetchone()
if correct_password is None:
return False
else:
if correct_password[0] == password:
return True
else:
return False
def sell(self,ticker_symbol,trade_volume):
# Will check if user has holding of that stock,
# then will checck if user has enough volume to sell,
with Database() as db:
db.cursor.execute('''SELECT ticker_symbol FROM holdings WHERE user_id = "{user_id}";'''
.format(user_id = self.id_lookup(self.username)))
user_stock = db.cursor.fetchone()[0]
if user_stock is None:
return False
elif user_stock == ticker_symbol:
db.cursor.execute('''SELECT trade_volume FROM holdings WHERE user_id = "{user_id}";'''
.format(user_id = self.id_lookup(self.username)))
user_holdings = db.cursor.fetchone()[0]
if float(user_holdings) >= float(trade_volume):
last_price = wrapper.lastprice(ticker_symbol)
brokerage_fee = 6.95
sell_cost = (float(last_price) * float(trade_volume)) - brokerage_fee
user_balance = self.user_balance()
user_id = self.id_lookup(self.username)
# Update balance
new_balance = user_balance + sell_cost
db.cursor.execute('''UPDATE users SET balance = {} WHERE user_id = {user_id};'''
.format(new_balance, user_id = self.id_lookup(self.username)))
# Update orders
time_ = time.time()
db.cursor.execute('''INSERT INTO orders (
user_id,
order_type,
ticker_symbol,
last_price,
trade_volume,
order_cost,
unix_time)
VALUES(?,?,?,?,?,?,?);''',
(user_id, "Sell", ticker_symbol, last_price, trade_volume, sell_cost, time_))
# Update holdings
if float(user_holdings) == float(trade_volume):
db.cursor.execute('''DELETE FROM holdings WHERE user_id={user_id};'''
.format(user_id = self.id_lookup(self.username)))
return True
else:
new_volume = float(user_holdings) - float(trade_volume)
db.cursor.execute('''UPDATE holdings SET trade_volume = {} WHERE user_id = {user_id};'''
.format(new_volume, user_id = self.id_lookup(self.username)))
db.cursor.execute('''UPDATE holdings SET VWAP = {} WHERE user_id = {user_id};'''
.format(self.vwap(ticker_symbol), user_id = self.id_lookup(self.username)))
return True
else:
return False
def svxy_sell_gains(sell_price):
with Database() as db:
db.cursor.execute('''SELECT * FROM log WHERE ticker="SVXY";''')
all_trades = db.cursor.fetchall()
gain_dict = {}
overall_gains = 0
for i in all_trades:
gain = sell_price - i[3]
gain_dict[i[1]] = gain
print('closed svxy position')
gains = 0
for k, v in gain_dict.items():
share_gain = v * 100
gains += share_gain
print(gains)
with Database() as db:
db.cursor.execute('''DELETE FROM log WHERE ticker="SVXY";''')
db.cursor.execute(
'''INSERT INTO profit_loss(date, ticker, p_l)
VALUES(?,?,?);
''', (date, 'SVXY', gains))
def vwap(self,ticker_symbol):
with Database() as db:
db.cursor.execute('''SELECT last_price, trade_volume FROM orders WHERE ticker_symbol="{}";'''
.format(ticker_symbol))
purchase_hist = db.cursor.fetchall()
pxv = []
volumes = []
# IF "purchase_hist" RETURNS LIST OF TUPLES LIKE [(last_price,trade_volume),(last_price,trade_volume))]
for purchase in purchase_hist:
price = float(purchase[0])
# "last_price" IS SET TO FLOAT IN SCHEMA (WILL THIS BE A PROBLEM?)
volume = float(purchase [1])
pxv.append(price * volume)
volumes.append(volume)
vwap = sum(pxv) / sum(volumes)
return vwap
def signup(self, username, password, confirm):
# Will create new user if username is not already in use (SQL UNIQUE in db) AND if user inputs password correctly (twice)
try:
if password == confirm:
with Database() as db:
db.cursor.execute('''INSERT INTO users (
username,
password)
VALUES(?,?);''',
(username,password))
return True
else:
return False
except:
if sqlite3.IntegrityError:
return False
def sell(user_name, ticker_symbol, trade_volume):
price = quote(ticker_symbol)
balance = check_balance(user_name)
owned = float(check_holdings(user_name, ticker_symbol))
if owned >= float(trade_volume):
with Database('master.db') as db:
new_owned = owned - float(trade_volume)
db.update_holding(user_name, ticker_symbol, new_owned)
new_balance = balance + (float(trade_volume) * float(price)) - 6.25
db.update_cash(user_name, new_balance)
db.insert_4('orders', 'username', 'ticker_symbol', 'trade_volume',
'execution_price', user_name, ticker_symbol,
trade_volume, price)
return "Congratulations toy just sold {} stocks of {}".format(
trade_volume, ticker_symbol)
else:
return "You don't have that many to sell"
def create_customer(name, username, password, email, stripe_id,
default_payment):
with Database() as db:
db.cursor.execute(
'''INSERT INTO users (name, username, password, email, stripe_id, default_payment)
VALUES (?, ?, ?, ?, ?, ?);''',
(name, username, password, email, stripe_id, default_payment))
user_id = db.cursor.lastrowid
keys = [
"user_id", "name", "username", "password", 'email',
'stripe_id', 'default_payment'
]
values = [
user_id, name, username, password, email, stripe_id,
default_payment
]
user = [dict(zip(keys, values))]
return user
def superuser(self, password, superkey):
# Will check if username exists,
# then if password input matches password in db,
# then if super key input is correct key ('super'),
# then updates permission in users table in db from default 'user' to 'super'
with Database() as db:
db.cursor.execute('''SELECT password FROM users WHERE username="******";'''
.format(username=self.username))
correct_password = db.cursor.fetchone()
if correct_password is None:
return False
else:
if correct_password[0] == password and superkey == 'super':
db.cursor.execute('''UPDATE users SET permission = "super" WHERE username = "******";'''
.format(username=self.username))
return True
else:
return False
def post_bill(total_due, due_by, due_to, caption, due_date):
created_on = int(time.time())
#created_on = created_on.strftime("%c")
keys = [
"bill_id", "total_due", "due_by", "due_to", "created_on",
"caption", "due_date"
]
with Database() as db:
db.cursor.execute(
'''INSERT INTO bills (total_due, due_by, due_to, created_on, caption, due_date)
VALUES (?, ?, ?, ?, ?, ?);''',
(total_due, due_by, due_to, created_on, caption, due_date))
bill_id = db.cursor.lastrowid
values = [
bill_id, total_due, due_by, due_to, created_on, caption,
due_date
]
bill = [dict(zip(keys, values))]
return bill
def pay_bill(amount_paid, paid_by, note, bill_id):
created_on = int(time.time())
#created_on = created_on.strftime("%c")
keys = [
"payment_id", "amount_paid", "paid_by", "paid_to", "created_on",
"note", "bill_owner", "original_bill_id"
]
with Database() as db:
db.cursor.execute('''SELECT * FROM bills WHERE bill_id = ?''',
(bill_id))
bill = db.cursor.fetchall()
bill_amount = bill[0][1]
bill_owner = bill[0][2]
paid_by = "Maria"
paid_to = bill[0][3]
contributors = bill[0][7]
db.cursor.execute(
'''INSERT INTO payments (amount_paid, paid_by, paid_to, created_on, note, bill_owner, original_bill_id)
VALUES (?, ?, ?, ?, ?, ?, ?);''',
(amount_paid, paid_by, paid_to, created_on, note, bill_owner,
bill_id))
payment_id = db.cursor.lastrowid
new_total = (bill_amount) - (amount_paid)
if contributors != None and contributors != paid_by:
contributors = contributors + ', ' + paid_by
else:
contributors = paid_by
db.cursor.execute(
'''UPDATE bills SET total_due = ? WHERE bill_id = ?''',
(new_total, bill_id))
db.cursor.execute(
'''UPDATE bills SET contributors = ? WHERE bill_id = ?''',
(contributors, bill_id))
db.cursor.execute('''SELECT * FROM bills WHERE bill_id = ?''',
(bill_id))
updated_bill = db.cursor.fetchall()
values = [
payment_id, amount_paid, paid_by, paid_to, created_on, note,
bill_owner, original_bill_id
]
payment = [dict(zip(keys, values))]
return payment
def repost(self, post_id):
reposted_from = '123456789'
time_ = datetime.datetime.now()
time_ = time_.strftime("%c")
with Database() as db:
db.cursor.execute(
'''UPDATE posts SET reposts = reposts + 1 WHERE post_id={post_id};'''
.format(post_id=int(post_id)))
db.cursor.execute(
'''SELECT * FROM posts WHERE post_id={post_id};'''.format(
post_id=int(post_id)))
post = db.cursor.fetchall()
post = post[0]
db.cursor.execute(
'''INSERT INTO reposts (user_who_reposted, reposted_from, post_id, time)
VALUES(?,?,?,?);''',
(self.username, reposted_from, post_id, time_))
db.cursor.execute(
'''INSERT INTO posts (username, photo, caption, time, likes, reposts, type, reposted_from)
VALUES (?,?,?,?,?,?,?,?);''',
(self.username, post[2], post[3], time_, post[5], post[6],
"repost", post[1]))
return True
#!/usr/bin/env python3
from mapper import Database
if __name__ == '__main__':
with Database('master.db') as db:
db.create_table('users')
db.add_column('users', 'username', 'VARCHAR')
db.add_column('users', 'password', 'VARCHAR')
db.create_table('tweets')
db.add_column('tweets', 'username', 'VARCHAR')
db.add_column('tweets', 'tweet', 'VARCHAR')
db.add_column('tweets', 'date', 'VARCHAR')
db.create_table('friends')
db.add_column('friends', 'username', 'VARCHAR')
db.add_column('friends', 'friends', 'VARCHAR')
print('System agent: Database created.')
def user(username):
with Database() as db:
db.cursor.execute('''SELECT * FROM users WHERE user_id = ?''',
(username))
userlist = db.cursor.fetchall()[0]
return userlist