from database.mysql import MySQLDatabase
from settings import db_config
"""
Retrieve the settings from the
`db_config` dictionary to connect to
our database so we can instantiate our
MySQLDatabase object
"""
db = MySQLDatabase(db_config.get('db_name'), db_config.get('user'),
db_config.get('pass'), db_config.get('host'))
# Select a person from the people table
person = db.select('people', named_tuples=True, where="id=2")[0]
print person
# Select all orders for that person
orders = db.select('orders',
named_tuples=True,
where="person_id=%s" % person.id)
print orders
# Iterate over each order
for order in orders:
print order
# Update the amount of each order
db.update('orders', where="id=%s" % order.id, amount="20.02")
# Selecting columns with named tuples
results = db.select('people',
columns=['id', 'first_name'], named_tuples=True)
for row in results:
print row.id, row.first_name
# We can also do more complex queries using `CONCAT`
# and `SUM`
people = db.select('people', columns=["CONCAT(first_name, ' ', second_name)" \
" AS full_name", "SUM(amount)" \
" AS total_spend"],
named_tuples=True, where="people.id=1",
join="orders ON people.id=orders.person_id")
for person in people:
print person.full_name, "spent ", person.total_spend
# Inserting an order
db.insert('orders', person_id="2", amount="120.00")
# Updating a person
person = db.select('people', named_tuples=True)[0]
db.update('profiles', where="person_id=%s" % person.id,
address="1a another street")
# Deleting a record
person = db.select('people',named_tuples=True)[0]
db.delete('orders', person_id="=%s" % person.id, id="=1")
columns=['id', 'first_name'],
named_tuples=True)
for row in results:
print row.id, row.first_name
# We can also do more complex queries using `CONCAT`
# and `SUM`
people = db.select('people', columns=["CONCAT(first_name, ' ', second_name)" \
" AS full_name", "SUM(amount)" \
" AS total_spend"],
named_tuples=True, where="people.id=1",
join="orders ON people.id=orders.person_id")
for person in people:
print person.full_name, "spent ", person.total_spend
# Inserting an order
db.insert('orders', person_id="2", amount="120.00")
# Updating a person
person = db.select('people', named_tuples=True)[0]
db.update('profiles',
where="person_id=%s" % person.id,
address="1a another street")
# Deleting a record
person = db.select('people', named_tuples=True)[0]
db.delete('orders', person_id="=%s" % person.id, id="=1")
`db_config` dictionary to connect to
our database so we can instantiate our
MySQLDatabase object
"""
db = MySQLDatabase(db_config.get('db_name'),
db_config.get('user'),
db_config.get('pass'),
db_config.get('host'))
# Select a person from the people table
person = db.select('people', named_tuples=True, where="id=2")[0]
print person
# Select all orders for that person
orders = db.select('orders', named_tuples=True, where="person_id=%s" % person.id)
print orders
# Iterate over each order
for order in orders:
print order
# Update the amount of each order
db.update('orders', where="id=%s" % order.id, amount="20.02")
# Select all the orders for that person again
new_orders = db.select('orders', named_tuples=True, where="person_id=%s" % person.id)
# Iterate over the orders and print
# out each one to ensure that the
# amount column has been updated.
for order in new_orders:
print order