def has_value(exchange, symbol, date):
sql = "SELECT id FROM value WHERE exchange = %s AND symbol = %s AND date = %s;"
conn = get_con()
cursor = conn.cursor()
cursor.execute(sql, [exchange, symbol, date])
rows = cursor.fetchall()
return len(rows) > 0
def add_record(exchange, symbol, date, price, quantity):
sql = "INSERT INTO value (date, exchange, symbol, price, quantity, value) VALUES (%s, %s, %s, %s, %s, %s);"
conn = get_con()
cursor = conn.cursor()
cursor.execute(
sql,
[date, exchange, symbol, price, quantity,
round(price * quantity, 2)])
conn.commit()
def get_quantity(exchange, symbol, date):
sql = "SELECT sum(quantity) FROM transaction WHERE exchange = %s AND symbol = %s AND date <= %s;"
conn = get_con()
cursor = conn.cursor()
cursor.execute(sql, [exchange, symbol, date])
rows = cursor.fetchall()
if len(rows) == 0:
return None
return rows[0][0]
def get_price(exchange, symbol, date):
sql = "SELECT price::numeric::float8 FROM price_history WHERE exchange = %s AND symbol = %s AND date = %s;"
conn = get_con()
cursor = conn.cursor()
cursor.execute(sql, [exchange, symbol, date])
rows = cursor.fetchall()
if len(rows) == 0:
return None
return rows[0][0]
def run():
sql = "SELECT DISTINCT exchange, symbol FROM transaction ORDER BY exchange, symbol;"
conn = get_con()
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
exchange = row[0]
symbol = row[1]
catch_up(exchange, symbol)
def import_file(filename, account_id):
con = get_con()
with open(filename) as csv_file:
csv_reader = csv.reader(csv_file, delimiter=",")
line_count = 1
next(csv_reader, None) # skip headers
for row in csv_reader:
data = convert_csv_row_to_json(row)
if maybe_load_row(data, con, account_id):
print("loaded row {} : {}".format(line_count, data))
else:
print("skipped row {} as already got".format(line_count))
line_count = line_count + 1
con.commit()
def update_categories():
con = get_con()
categories = get_categories(con)
cur = con.cursor()
sql = (
"SELECT id, account_id, date, amount, payee, particulars, code, reference, "
"transaction_type, this_account, other_account, serial, transaction_code, batch_number, "
"originating_bank, date_processed, category, sub_category FROM transaction WHERE category IS NULL"
)
cur.execute(sql)
rows = cur.fetchall()
updates = 0
for row in rows:
record = convert_database_row_to_json(row)
category = guess_category(record, categories)
print("{} : {} = {}".format(record["id"], record["category"],
category))
if category:
update_row_category(record, category, con)
updates = updates + 1
print("checked {} rows and updated {}".format(len(rows), updates))