def update(transaction): sql = "UPDATE transactions SET (amount, merchant_id, tag_id) = (%s, %s, %s) WHERE id = %s" values = [ transaction.amount, transaction.merchant.id, transaction.tag.id, transaction.id ] print(values) run_sql(sql, values)
def save(transaction): transaction.convert_to_pennies() sql = "INSERT INTO transactions (amount, merchant_id, tag_id) VALUES (%s, %s, %s) RETURNING id" values = [transaction.amount, transaction.merchant.id, transaction.tag.id] results = run_sql(sql, values) # pdb.set_trace() transaction.id = results[0]['id'] return transaction
def select_all(): merchants = [] sql = "SELECT * FROM merchants" results = run_sql(sql) for row in results: merchant = Merchant(row['name'], row['id']) merchants.append(merchant) return merchants
def select_all(): tags = [] sql = "SELECT * FROM tags" results = run_sql(sql) for row in results: tag = Tag(row['category'], row['id']) tags.append(tag) return tags
def select(id): tag = None sql = "SELECT * FROM tags WHERE id = %s" values = [id] result = run_sql(sql, values)[0] if result is not None: tag = Tag(result['category'], result['id']) return tag
def select(id): merchant = None sql = "SELECT * FROM merchants WHERE id = %s" values = [id] result = run_sql(sql, values)[0] if result is not None: merchant = Merchant(result['name'], result['id']) return merchant
def merchants(tag): merchants = [] sql = "SELECT merchants.* FROM merchants INNER JOIN transactions ON transactions.merchant_id = merchants.id WHERE tag_id = %s" values = [tag.id] results = run_sql(sql, values) for row in results: merchant = Merchant(row['name'], row['id']) merchants.append(merchant) return merchants
def select(id): transaction = None sql = "SELECT * FROM transactions WHERE id = %s" values = [id] result = run_sql(sql, values)[0] if result is not None: merchant = merchant_repository.select(result['merchant_id']) tag = tag_repository.select(result['tag_id']) transaction = Transaction(result['amount'], merchant, tag, result['id']) return transaction
def tags(merchant): tags = [] sql = "SELECT tags.* FROM tags INNER JOIN transactions ON transactions.tag_id = tags.id WHERE merchant_id = %s" values = [merchant.id] results = run_sql(sql, values) for row in results: tag = Tag(row['category'], row['id']) tags.append(tag) return tags
def select_all(): transactions = [] sql = "SELECT * FROM transactions" results = run_sql(sql) for row in results: merchant = merchant_repository.select(row['merchant_id']) tag = tag_repository.select(row['tag_id']) transaction = Transaction(row['amount'], merchant, tag, row['id']) transactions.append(transaction) return transactions
def delete_all(): sql = "DELETE FROM merchants" run_sql(sql)
def total_transactions_by_tag(): sql = "SELECT tag_id, SUM (amount) AS totals FROM transactions GROUP BY tag_id ORDER BY totals DESC" totals = run_sql(sql) return totals
def delete_all(): sql = "DELETE FROM tags" run_sql(sql)
def total_transactions_by_merchant(): sql = "SELECT merchant_id, SUM (amount) AS totals FROM transactions GROUP BY merchant_id ORDER BY totals DESC" totals = run_sql(sql) return totals
def save(tag): sql = "INSERT INTO tags (category) VALUES (%s) RETURNING id" values = [tag.category] results = run_sql(sql, values) tag.id = results[0]['id'] return tag
def tag(transaction): sql = "SELECT * FROM tags WHERE id = %s" values = [transaction.tag.id] results = run_sql(sql, values)[0] tag = Tag(results['category'], results['id']) return tag
def delete(id): sql = "DELETE FROM transactions WHERE id = %s" values = [id] run_sql(sql, values)
def update(merchant): sql = "UPDATE merchants SET (name) = (%s) WHERE id = %s" values = [merchant.name, merchant.id] run_sql(sql, values)
def delete_all(): sql = "DELETE FROM transactions" run_sql(sql)
def delete(id): sql = "DELETE FROM merchants WHERE id = %s" values = [id] run_sql(sql, values)
def merchant(transaction): sql = "SELECT * FROM merchants WHERE id = %s" values = [transaction.merchant.id] results = run_sql(sql, values)[0] merchant = Merchant(results['name'], results['id']) return merchant
def save(merchant): sql = "INSERT INTO merchants (name) VALUES (%s) RETURNING id" values = [merchant.name] results = run_sql(sql, values) merchant.id = results[0]['id'] return merchant
def total_transactions(): sql = "SELECT SUM (amount) FROM transactions" result = run_sql(sql) # pdb.set_trace() total_as_pennies = result[0][0] return total_as_pennies / 100
def update(tag): sql = "UPDATE tags SET (category) = (%s) WHERE id = %s" values = [tag.category, tag.id] run_sql(sql, values)