def insert_refresh_token(username: str, refresh_token: str, expiration_time):
db, db_cursor = get_cursor()
query = f"""
INSERT INTO access_tokens (username, refresh_token, expiry, expired)
VALUES ('{username}', '{refresh_token}', '{expiration_time}', false )
"""
db_cursor.execute(query)
commit_to_database(db, db_cursor)
def insert_security(username: str, security_symbol: str, exchange_name: str):
db, db_cursor = get_cursor()
query = f"""
INSERT INTO watchlist (username, security_symbol, exchange_name)
VALUES ('{username}', '{security_symbol}', '{exchange_name}')
"""
db_cursor.execute(query)
commit_to_database(db, db_cursor)
return
def get_user(username):
db, db_cursor = get_cursor()
query = f"""
SELECT *
FROM users
WHERE users.username = '******'
"""
db_cursor.execute(query)
result = db_cursor.fetchone()
return result
def revoke_refresh_tokens(username: str):
db, db_cursor = get_cursor()
query = f"""
UPDATE access_tokens
SET expired = 'true'
WHERE
access_tokens.username LIKE '{username}'
"""
db_cursor.execute(query)
commit_to_database(db, db_cursor)
def insert_transaction(params):
db, db_cursor = get_cursor()
timestamp = datetime.utcnow()
query = f"""
INSERT INTO transactions (username, security_symbol, exchange_name, price, quantity, transaction_time, transaction_type)
VALUES ('{params['username']}', '{params['symbol']}', '{params['exchange']}', {params['price']}, {params['quantity']}, '{timestamp}', '{params['transaction_type']}' )
"""
db_cursor.execute(query)
commit_to_database(db, db_cursor)
return
def get_purchase_value(symbol, exchange):
db, db_cursor = get_cursor()
query = f"""
SELECT security_value
FROM public_securities
WHERE public_securities.security_symbol = '{symbol}' AND public_securities.exchange_name = '{exchange}'
"""
db_cursor.execute(query)
result = db_cursor.fetchone()
result_float = float(result[0])
return result_float
def get_refresh_token_status(username: str, refresh_token: str):
db, db_cursor = get_cursor()
query = f"""
SELECT expiry, expired
FROM access_tokens
WHERE
access_tokens.username = '******' AND
access_tokens.refresh_token = '{refresh_token}'
"""
db_cursor.execute(query)
result = db_cursor.fetchone()
return result
def get_owned_securities(username: str, table_name: str = 'owned_securities', order_by: str = 'security_symbol', order: str = 'ASC' ):
db, db_cursor = get_cursor()
query = f"""
SELECT SUM(quantity), security_symbol, exchange_name
FROM {table_name}
WHERE {table_name}.username = '******'
GROUP BY security_symbol, exchange_name
ORDER BY {order_by} {order}
"""
db_cursor.execute(query)
results = db_cursor.fetchall()
return results
def delete_security(table_name: str, username: str, symbol: str,
exchange: str):
db, db_cursor = get_cursor()
query = f"""
DELETE FROM {table_name}
WHERE
{table_name}.username = '******' AND
{table_name}.security_symbol = '{symbol}' AND
{table_name}.exchange_name = '{exchange}'
"""
db_cursor.execute(query)
commit_to_database(db, db_cursor)
return
def get_valid_refresh_token(username: str):
db, db_cursor = get_cursor()
query = f"""
SELECT *
FROM access_tokens
WHERE
access_tokens.username = '******' AND
access_tokens.expired = 'false'
"""
db_cursor.execute(query)
result = db_cursor.fetchone()
return result
def get_one_security(username: str, table_name: str, security_symbol: str,
exchange: str):
db, db_cursor = get_cursor()
query = f"""
SELECT *
FROM {table_name}
WHERE {table_name}.username = '******' AND
{table_name}.security_symbol = '{security_symbol}' AND
{table_name}.exchange_name = '{exchange}'
"""
db_cursor.execute(query)
results = db_cursor.fetchall()
return results
def get_quantity(username, symbol, exchange):
db, db_cursor = get_cursor()
query = f"""
SELECT SUM(quantity) AS quantity
FROM owned_securities
WHERE
owned_securities.security_symbol = '{symbol}' AND
owned_securities.exchange_name = '{exchange}' AND
owned_securities.username = '******'
"""
db_cursor.execute(query)
result = db_cursor.fetchone()
return result[0]
def get_watched_securities(username: str,
table_name: str = 'watchlist',
order_by: str = 'security_symbol',
order: str = 'ASC'):
db, db_cursor = get_cursor()
query = f"""
SELECT security_symbol, exchange_name
FROM {table_name}
WHERE {table_name}.username = '******'
ORDER BY {order_by} {order}
"""
db_cursor.execute(query)
results = db_cursor.fetchall()
return results
def update_quantity(table_name: str, transaction_id, username: str, symbol: str, exchange: str, new_quantity: int):
db, db_cursor = get_cursor()
query = f"""
UPDATE {table_name}
SET quantity = {new_quantity}
WHERE
{table_name}.username = '******' AND
{table_name}.security_symbol = '{symbol}' AND
{table_name}.exchange_name = '{exchange}' AND
{table_name}.transaction_id = {transaction_id}
"""
db_cursor.execute(query)
commit_to_database(db, db_cursor)
return
def get_transactions(username: str, symbol: str, exchange:str, table_name: str, order_by: str = 'transaction_time', order: str = 'ASC' ):
db, db_cursor = get_cursor()
query = f"""
SELECT *
FROM {table_name}
WHERE
{table_name}.username = '******' AND
{table_name}.security_symbol = '{symbol}' AND
{table_name}.exchange_name = '{exchange}'
ORDER BY {order_by} {order}
"""
db_cursor.execute(query)
results = db_cursor.fetchall()
return results
