Python run_sql Examples

Example #1

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)

Example #2

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

Example #3

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

Example #4

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

Example #5

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

Example #6

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

Example #7

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

Example #8

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

Example #9

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

Example #10

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

Example #11

def delete_all():
    sql = "DELETE FROM merchants"
    run_sql(sql)

Example #12

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

Example #13

def delete_all():
    sql = "DELETE FROM tags"
    run_sql(sql)

Example #14

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

Example #15

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

Example #16

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

Example #17

def delete(id):
    sql = "DELETE FROM transactions WHERE id = %s"
    values = [id]
    run_sql(sql, values)

Example #18

def update(merchant):
    sql = "UPDATE merchants SET (name) = (%s) WHERE id = %s"
    values = [merchant.name, merchant.id]
    run_sql(sql, values)

Example #19

def delete_all():
    sql = "DELETE FROM transactions"
    run_sql(sql)

Example #20

def delete(id):
    sql = "DELETE FROM merchants WHERE id = %s"
    values = [id]
    run_sql(sql, values)

Example #21

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

Example #22

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

Example #23

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

Example #24

def update(tag):
    sql = "UPDATE tags SET (category) = (%s) WHERE id = %s"
    values = [tag.category, tag.id]
    run_sql(sql, values)