def update_kyc(account: str, token: str, newkyc: str):
""" Changes kyc for a specific account with a specific token """
token = token.lower()
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(f"UPDATE cbalances SET kyc = '{newkyc}' \
WHERE account = '{account}' AND token = '{token}' ")
conn.commit()
def get_total_token_balance(token: str):
"""Sums all token balances from all accounts"""
token = token.lower()
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(f"SELECT amount FROM cbalances WHERE token='{token}'")
result = cursor.fetchall()
return sum([r[0] for r in result])
def get_cost_basis(account):
"""Returns cost basis from an account"""
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"SELECT amount FROM costbasis WHERE account = '{account}'")
res = cursor.fetchall()
return res[0][0] if res != [] else 0
def get_all_tokens_with_amount():
"""Returns tuples as (token, total token amount)"""
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
"""SELECT token, SUM(amount) as sum_amount FROM cbalances GROUP BY token"""
)
return cursor.fetchall()
def update_balance(account: str, token: str, newbalance: float):
""" Changes balance for a specific account with a specific token """
token = token.lower()
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(f"UPDATE cbalances SET amount = {newbalance} \
WHERE account = '{account}' AND token = '{token}' ")
conn.commit()
def get_total_balance_all_accounts():
"""
Returns the sum of all the balances of all the accounts on this table
expressed in btc
"""
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT token,amount FROM cbalances")
return sum(prices.to_btc(d[0], d[1]) for d in cursor.fetchall())
def delete_account(account_name: str, token_name: str):
"""Deletes the account with name=account_name and token=token_name"""
token_name = token_name.lower()
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"DELETE FROM cbalances WHERE account= '{account_name}' AND token= '{token_name}'"
)
conn.commit()
def get_balances_with_account(account: str):
"""Returns all entries where an account is involved"""
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"SELECT date,balance_btc,balance_{confighandler.get_fiat_currency().lower()} \
FROM cbalancehistory \
WHERE account = '{account}'")
return cursor.fetchall()
def get_balances_with_account_tuple(account: str):
"""
Returns all entries where an account is involved
Formatted as dict[date] = (balance_btc,balance_fiat)
"""
conn = create_connection()
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"SELECT date, balance_btc, balance_{confighandler.get_fiat_currency().lower()} FROM cbalancehistory \
WHERE account='{account}'")
data = cursor.fetchall()
final = {d[0]: (0, 0) for d in data}
for entry in data:
date, btc, fiat = entry
final[date] = (final[date][0] + btc, final[date][0] + fiat)
return final
def get_balances_from_last_day():
with create_connection() as conn:
cursor = conn.cursor()
tdy = datetime.today()
tdy_start_tmstp = datetime(tdy.year, tdy.month, tdy.day, 0, 0,
0).timestamp()
cursor.execute(
f"SELECT * FROM cbalancehistory WHERE date > {tdy_start_tmstp}")
return cursor.fetchall()
def get_balances_with_token(token: str):
"""Returns all entries where a token is involved"""
token = token.lower()
with create_connection() as conn:
cursor = conn.cursor()
fiat = confighandler.get_fiat_currency().lower()
cursor.execute(
f"SELECT date,balance_btc,balance_{fiat} FROM cbalancehistory \
WHERE token = '{token}'")
return cursor.fetchall()
def get_account_with_token(account: str, token: str):
"""Returns data from a specific account-token combination"""
token = token.lower()
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"SELECT * FROM cbalances WHERE token= '{token}' AND account='{account}'"
)
result = cursor.fetchall()
return result[0] if result != [] else None
def get_total_account_balance(account: str):
"""
Returns the total balance from all tokens from a certain account
expressed in btc
"""
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"SELECT token,amount FROM cbalances WHERE account = '{account}'")
result = cursor.fetchall()
return sum([prices.to_btc(d[0], d[1]) for d in result])
def add_strategy(strategy: str, markettype: str):
with create_connection() as conn:
cursor = conn.cursor()
query = "INSERT INTO strategies ('strategy','markettype','amount') VALUES (?,?,?);"
try:
cursor.execute(query,
(strategy, markettype, 0))
logging.info(f"Added new strategy '{strategy}' on database")
except sqlite3.IntegrityError:
logging.warning(
f"Strategy {strategy} already exists on database")
conn.commit()
def create_tables(path_to_db):
results_table_query = """CREATE TABLE IF NOT EXISTS results(
id integer PRIMARY KEY,
date integer NOT NULL,
account text NOT NULL,
strategy text NOT NULL,
amount integer NOT NULL,
description text,
FOREIGN KEY (strategy)
REFERENCES strategies (strategy)
)"""
strategies_table_query = """CREATE TABLE IF NOT EXISTS strategies(
strategy text PRIMARY KEY,
markettype text NOT NULL,
amount integer NOT NULL
)"""
transactions_table_query = """CREATE TABLE IF NOT EXISTS transactions(
id integer PRIMARY KEY,
date timestamp NOT NULL,
account_send text NOT NULL,
amount integer NOT NULL,
account_receive text NOT NULL,
depositwithdrawal integer NOT NULL,
description text
)"""
balances_table_query = """CREATE TABLE IF NOT EXISTS balances(
account text PRIMARY KEY,
amount integer NOT NULL
)
"""
balancehistory_table_query = """CREATE TABLE IF NOT EXISTS balancehistory(
id integer PRIMARY KEY,
account text NOT NULL,
date timestamp NOT NULL,
balance integer NOT NULL,
FOREIGN KEY (account)
REFERENCES balances (account)
)"""
costbasis_table_query = """CREATE TABLE IF NOT EXISTS costbasis(
account text PRIMARY KEY,
amount integer NOT NULL,
FOREIGN KEY (account)
REFERENCES balances (account)
)"""
queries = (results_table_query, strategies_table_query,
transactions_table_query, balances_table_query,
balancehistory_table_query, costbasis_table_query)
with create_connection() as conn:
cursor = conn.cursor()
for q in queries:
cursor.execute(q)
def update_strategies_with_new_result(strategy: str, amount: float):
"""
Adds the new result to the specific
strategy involved, updating its balance
"""
amount = int(round(float(amount[:-2]), 0) if '.' in amount else amount)
with create_connection() as conn:
cursor = conn.cursor()
new_balance = get_strategy_balance(strategy) + amount
cursor.execute(
f"UPDATE strategies SET amount = {new_balance} WHERE strategy = '{strategy}'")
conn.commit()
def add_todays_balances():
"""
Reads balances from balances table,
and updates the balancehistory table accordingly
"""
conn = create_connection()
with create_connection() as conn:
cursor = conn.cursor()
todays_balances = get_balances_from_last_day()
current_accounts = balances.get_all_accounts()
# Delete previous balances from today
# (that way we'll avoid dealing with new accounts)
for balance_history in todays_balances:
_id = balance_history[0]
delete_balance_from_id(_id)
# Write today's balances
query = "INSERT INTO 'balancehistory' (account, date, balance) VALUES (?,?,?);"
for acc in current_accounts:
account, balance, *_ = acc
cursor.execute(
query, (account, int(datetime.today().timestamp()), balance))
conn.commit()
def get_all_accounts_with_amount():
""" Returns tuples as (account, total amount in btc)"""
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT account,token,amount FROM cbalances")
result = cursor.fetchall()
# Convert to BTC
accounts = {d[0]: 0 for d in result}
for r in result:
acc, token, amount = r # Unpack
accounts[acc] += prices.to_btc(token, amount)
return [(acc, accounts[acc]) for acc in accounts]
def _get(account: str, token: str, item: str):
"""
Retuns a specific item from an entry with a certain account-token
combination
"""
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"SELECT {item.lower()} from cbalances WHERE account = '{account}' AND token = '{token.lower()}'"
)
try:
return cursor.fetchall()[0][0]
except IndexError:
raise IndexError(f"Could not find {item} of {account},{token}")
def get_balances_by_day(cryptoaccs=None):
""" Returns a dictionary with the total btc balance of all accounts by each day """
with create_connection() as conn:
cursor = conn.cursor()
query = "SELECT date, balance_btc FROM cbalancehistory"
if cryptoaccs is not None:
query += f" WHERE account IN {cryptoaccs}"
cursor.execute(query)
result = cursor.fetchall()
final = {d[0]: 0 for d in result}
for entry in result:
date, balance = entry
final[date] += balance
return final
def update_cost_basis_with_new_transaction(account: str, amount: str):
"""
Adds the new transaction to the specific
account involved, updating its cost basis
"""
# Convert
amount = int(round(float(amount[:-2]), 0) if '.' in amount else amount)
with create_connection() as conn:
cursor = conn.cursor()
new_costbasis = get_cost_basis(account) + amount
cursor.execute(
f"UPDATE costbasis SET amount = {new_costbasis} WHERE account = '{account}'"
)
conn.commit()
def add_cost_basis(new_account: str, starting_costbasis: float):
""" Adds new account with an initial costbasis """
with create_connection() as conn:
cursor = conn.cursor()
query = """INSERT INTO 'costbasis'
('account','amount')
VALUES (?,?);"""
try:
cursor.execute(query, (new_account, starting_costbasis))
logger.info(
f"Added new account's '{new_account}' costbasis on database")
conn.commit()
except sqlite3.IntegrityError:
logger.warning(f"Account {new_account} cost basis already exists")
return
def delete_result(resultid):
with create_connection() as conn:
cursor = conn.cursor()
# First, we need to select the result so that we know the amount and the account involved
# as we'll need to update the balances table aswell
cursor.execute(
f"SELECT account,amount FROM results WHERE id= {resultid}")
account_from_result, amount_from_result = cursor.fetchall()[0]
# Now, we delete the result from the results table on the database
cursor.execute(f"DELETE FROM results WHERE id= {resultid}")
conn.commit()
# Finally, we update the previous balance on the balances table
# taking the removal of the result into consideration
balances.update_balances_with_new_result(account_from_result,
-amount_from_result)
def get_balances_by_day_fiat(cryptoaccs=None):
""" Returns a dictionary with the total btc balance of all accounts by each day """
fiat = confighandler.get_fiat_currency().lower()
with create_connection() as conn:
cursor = conn.cursor()
query = f"SELECT date, balance_{fiat} FROM cbalancehistory"
if cryptoaccs is not None:
query += f" WHERE account IN {tuple(cryptoaccs)}"
cursor.execute(query)
result = cursor.fetchall()
final = {d[0]: 0 for d in result}
for entry in result:
date, balance = entry
final[date] += balance
return final
def add_account(account: str, token: str, amount: float, _type: str, kyc: str,
description: str):
token = token.lower()
with create_connection() as conn:
cursor = conn.cursor()
query = """INSERT INTO 'cbalances'
('account','token','amount','type','kyc','description')
VALUES (?,?,?,?,?,?);"""
try:
cursor.execute(query,
(account, token, amount, _type, kyc, description))
conn.commit()
logger.info(f"Added new account {account} ({token}) on database")
return cursor.lastrowid
except sqlite3.IntegrityError:
logger.warning(f"{account=} with {token=} already exists")
return
def get_balances_by_day_tuple():
"""
Returns a dict of tuples with the total balance of all accounts by each day
Formatted as dict[date] = (balance_btc,balance_fiat)
"""
fiat = confighandler.get_fiat_currency().lower()
with create_connection() as conn:
cursor = conn.cursor()
cursor.execute(
f"SELECT date, balance_btc,balance_{fiat} FROM cbalancehistory")
result = cursor.fetchall()
final = {str(d[0]): (0, 0) for d in result}
for entry in result:
date, balance_btc, balance_fiat = entry
date = str(date)
final[date] = (balance_btc + final[date][0],
balance_fiat + final[date][1])
return final
def create_tables(path_to_db):
transactions_table_query = """
CREATE TABLE IF NOT EXISTS ctransactions(
id integer PRIMARY KEY,
date timestamp NOT NULL,
account_send text NOT NULL,
token text NOT NULL,
amount integer NOT NULL,
account_receive text NOT NULL
)"""
balances_table_query = """
CREATE TABLE IF NOT EXISTS cbalances(
account text NOT NULL,
token text NOT NULL,
amount integer NOT NULL,
type text NOT NULL,
kyc text NOT NULL,
description text ,
UNIQUE(account,token)
)
"""
balancehistory_table_query = """
CREATE TABLE IF NOT EXISTS cbalancehistory(
id integer PRIMARY KEY,
account text NOT NULL,
date timestamp NOT NULL,
token text NOT NULL,
balance integer NOT NULL,
balance_btc integer NOT NULL,
balance_eur integer NOT NULL,
balance_usd integer NOT NULL,
balance_jpy integer NOT NULL
)"""
with create_connection() as conn:
cursor = conn.cursor()
# Inserting tables
cursor.execute(transactions_table_query)
cursor.execute(balances_table_query)
cursor.execute(balancehistory_table_query)
conn.commit()
def delete_transaction(transactionid):
with create_connection() as conn:
cursor = conn.cursor()
# First, we need to get it
# so that we know the amount and the account involved,
# as we new to update the balances table aswell
select_query = f"SELECT account_send, account_receive, amount FROM transactions \
WHERE id= {transactionid}"
result = cursor.execute(select_query).fetchall()[0]
sender, receiver, amount, *_ = result
# Now, we delete the result from the results table on the database
cursor.execute(f"DELETE FROM transactions WHERE id= {transactionid}")
conn.commit()
# Finally, we update the previous balance on the balances and cost basis tables,
# taking the removal of the transaction into consideration
balances.update_balances_with_new_result(sender, amount)
balances.update_balances_with_new_result(receiver, -amount)
costbasis.update_cost_basis_with_new_transaction(sender, amount)
costbasis.update_cost_basis_with_new_transaction(receiver, -amount)
def update_transaction(transactionid,
new_date=None,
new_sender: str = None,
new_amount: float = None,
new_receiver: str = None,
new_d_or_w: float = None,
new_description: str = None):
"""
Updates a transaction entry
Note that it does not update the balances or strategies, etc.
Meaning that if you change the transaction of an account,
the account balance+costbasis of the balances+costbasis tables won't be updated here
"""
transactionid = int(transactionid)
with create_connection() as conn:
cursor = conn.cursor()
# First, we select the current result data, in case some of it does not need to be updated
cursor.execute(
f" SELECT * FROM transactions WHERE id= {transactionid}")
r = cursor.fetchall()[0]
date, sender, amt, receiver, _type, d_or_w, descr, *_ = r
new_date = date if new_date is None else new_date
new_sender = sender if new_sender is None else new_sender
new_amount = amt if new_amount is None else new_amount
new_receiver = receiver if new_receiver is None else new_receiver
new_d_or_w = d_or_w if new_d_or_w is None else new_d_or_w
new_description = descr if new_description is None else new_description
cursor.execute(f"""UPDATE transactions
SET date = {new_date} ,
account_send = {new_sender} ,
amount = {new_amount},
account_receive = {new_receiver},
depositwithdrawal = {new_d_or_w},
description = {new_description}
WHERE id = {transactionid}""")
conn.commit()
logging.info(
f"Updated {r} \nChanged to {new_date}{new_sender}{new_amount}{new_receiver}{new_d_or_w}{new_description}"
)
def add_transaction(date,
account_send: str,
amount: float,
account_receive: str,
depositwithdrawal: str,
description: str = ""):
with create_connection() as conn:
cursor = conn.cursor()
# If accounts do not exist, they get created
acc_send_exists = balances.get_account(account_send) != []
acc_receive_exists = balances.get_account(account_receive) != []
if not acc_send_exists:
balances.add_account(account_send, 0)
if not acc_receive_exists:
balances.add_account(account_receive, 0)
# Check args
if depositwithdrawal not in (0, 1, -1):
raise ValueError(
"deposit/withdrawal must be 1/-1, respectively, or 0 if normal trnasfer"
)
amount = int(amount)
# Add
query = """INSERT INTO 'transactions'
('date', 'account_send', 'amount', 'account_receive',
'depositwithdrawal','description')
VALUES (?,?,?,?,?,?)"""
cursor.execute(query, (date, account_send, amount, account_receive,
depositwithdrawal, description))
conn.commit()
# Finally, we update the account balance on the balance and costbasis tables
# Sender account
balances.update_balances_with_new_result(account_send, -amount)
balances.update_balances_with_new_result(account_receive, amount)
costbasis.update_cost_basis_with_new_transaction(account_send, -amount)
costbasis.update_cost_basis_with_new_transaction(account_receive, amount)
