def parse_csv(path: str, encoding="windows-1252"):
_transactions = []
_offset = 8
with open(path, encoding=encoding, newline="") as csv_file:
_reader = reader(csv_file, delimiter=";", quotechar='"')
for _row in _reader:
_line = _reader.line_num
if _line == 1:
_account_number = _row[1]
elif _line == 2:
_account_type = _row[1]
elif _line == 3:
_account_currency = _row[1]
elif _line == 4:
_account_balance_date = parse_date(_row[1])
elif _line == 5:
_account_balance_euros = parse_amount(_row[1])
elif _line == 6:
_account_balance_francs = parse_amount(_row[1])
elif _line > _offset:
_transactions.append(
Transaction(date=parse_date(_row[0]),
memo=_row[1],
amount=parse_amount(_row[2]),
amount_francs=parse_amount(_row[3])))
_account = Account(number=_account_number,
type=_account_type,
currency=_account_currency,
balance_date=_account_balance_date,
balance=_account_balance_euros,
balance_francs=_account_balance_francs)
_data = Data(account=_account, transactions=_transactions)
return _data
def __generateTransactions(edges, transactionsFile, batchSize, label):
try:
os.remove(transactionsFile)
except OSError:
pass
totalNumberOfTransactions = 0
with open(transactionsFile, 'a') as transactions:
batch = []
sourceNodesCount = 0
# transactions.write("|".join(transactionHeaders) + "\n") # Can't have the header if we take transactions from every file
for sourceNode, targets in edges.items():
sourceNodesCount += 1
if sourceNodesCount % batchSize == 1: log(label + ": generating transactions for source node " + str(sourceNodesCount) + ", transaction count: " + str(totalNumberOfTransactions))
targetNodesCount = 0
for targetNode, transactionsCount in targets.items():
targetNodesCount += 1
for _ in range(0, transactionsCount):
t = Transaction(sourceNode, targetNode)
batch.append(t.toRow(transactionHeaders))
totalNumberOfTransactions += 1
if len(batch) > batchSize:
writeBatch(transactions, batch)
batch = []
if len(batch) != 0:
writeBatch(transactions, batch)
log(label + ": TOTAL: generating transactions for source node " + str(sourceNodesCount) + ", transaction count: " + str(totalNumberOfTransactions))
def add_transaction(budget: Budget) -> bool:
name = input("Name of the transaction: ")
try:
outflow = float(input("Outflow amount: ") or 0.)
if outflow == 0.:
inflow = float(input("Inflow amount: "))
else:
inflow = 0.
category_id = int(input("Category id: "))
valid_ids = []
for parent in budget.parent_categories:
for category in parent.categories:
valid_ids.append(category.id)
if category_id not in valid_ids:
print("Invalid category ID!")
return False
provided_date = input("Date of the transaction (YYYY-MM-DD): ")
if provided_date:
receipt_date = datetime.strptime(provided_date, '%Y-%m-%d').date()
else:
receipt_date = datetime.today().date()
payee_name = input("Name of payee/payer: ")
except ValueError:
print("You provided an incorrect value!")
return False
transaction = Transaction(name=name,
payee_name=payee_name,
amount_inflow=inflow,
amount_outflow=outflow,
category_id=category_id,
receipt_date=receipt_date)
session.add(transaction)
session.commit()
print("The transaction has been added!")
return True
def callback(ch, method, properties, body):
data: InputJson = InputJson(**json.loads(body))
print(" [x] Received %r" % body)
# Update balances of customers, on average below code takes 0.46 sec
# Source: https://stackoverflow.com/questions/54365873/sqlalchemy-update-multiple-rows-in-one-transaction
bal_ = getattr(Balance, data.currency)
session.query(Balance).filter(
Balance.customer_id.in_([
data.customer_id, data.customer_id_to
])).update(
{
bal_:
case(
{
data.customer_id:
bal_ - data.amount, # getattr(Balance, data.currency)
data.customer_id_to: bal_ + data.amount
},
value=Balance.customer_id)
},
synchronize_session=False)
# Commented another method which first of all does Select and then inside Python changes values and then updates
# values. On average below code takes 0.76 sec
"""
new_bal: List[Balance] = session.query(Balance).filter(
(Balance.customer_id == data.customer_id) | (Balance.customer_id == customer_id_to)).all()
for bal in new_bal:
if bal.customer_id == data.customer_id:
setattr(bal, currency, getattr(bal, currency) - amount)
else:
setattr(bal, currency, getattr(bal, currency) + amount)
"""
# Create transaction and add it to Transactions table
new_transaction = Transaction(customer_id_from=data.customer_id,
customer_id_to=data.customer_id_to,
**{data.currency: data.amount})
session.add_all([new_transaction])
session.commit()
ch.basic_ack(
delivery_tag=method.delivery_tag
) # Answer to RabbitMQ that all is successful => Message is marked as acknowledged
parent_id=parent_instance.id))
session.add_all(category_list)
session.commit()
# ADD TRANSACTIONS
# ------------------------------
transaction_list = []
# loop through categories
for category_instance in session.query(Category).order_by(Category.id):
for i in range(randint(1, 10)):
transaction_list.append(
Transaction(name="Transakcja",
payee_name="Nazwa sklepu / płatnika",
amount_inflow=0.00,
amount_outflow=round(uniform(0.0, 800.0), 2),
category_id=category_instance.id,
receipt_date=date.today()))
session.add_all(transaction_list)
session.commit()
# ADD CATEGORY BUDGETS
# ------------------------------
month = datetime.now().month
year = datetime.now().year
day = datetime.now().day
category_budgets_list = []
import pprint
import os
import psycopg2
import openpyxl
from DBConnection import DBConnection
from dotenv import load_dotenv, find_dotenv
from models.Transaction import Transaction
conn = DBConnection().get_connection()
pp = pprint.PrettyPrinter(indent=4)
wb = openpyxl.load_workbook('golden_one_example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
obs = []
for row in range(2, sheet.max_row + 1):
transaction_date = sheet['A' + str(row)].value
amount = sheet['B' + str(row)].value
description = sheet['C' + str(row)].value
running_total = sheet['D' + str(row)].value
tran = Transaction(transaction_date, amount, description, running_total)
obs.append(
Transaction(transaction_date, amount, description, running_total))
tran.save(conn)
conn.close()
