from database.mysql import MySQLDatabase
from settings import db_config
if __name__ == "__main__":
"""
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'))
# Insert a new person into the people table
db.insert('people',
first_name="April",
second_name="ONeil",
DOB='STR_TO_DATE("02-10-1984", "%d-%m-%Y")')
# Retrieve the new person from the people table
april = db.select('people', ["id", "first_name"],
where='first_name="April"',
named_tuples=True)
# We only need the first entry in the list
april = april[0]
# Insert into the profiles table using the
# id of the 'april' person
db.insert('profiles', person_id="%s" % april.id, address="New York City")
# Insert into the orders table using the
# id of the person and generate a random
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")
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'))
#create a new person in the people table
db.insert('people',
first_name="Jane",
second_name="Brooks",
DOB='STR_TO_DATE("26-03-1955", "%d-%m-%Y")')
#add in a profile row
db.insert('profiles', person_id='9', address="Millhouse")
#add two orders of random value
db.insert('orders', person_id='9', amount="25")
db.insert('orders', person_id='9', amount="35")
# 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")
if __name__ == "__main__":
"""
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'))
# Insert a new person into the people table
db.insert('people', first_name="April", second_name="ONeil",
DOB='STR_TO_DATE("02-10-1984", "%d-%m-%Y")')
# Retrieve the new person from the people table
april = db.select('people', ["id", "first_name"], where='first_name="April"',
named_tuples=True)
# We only need the first entry in the list
april = april[0]
# Insert into the profiles table using the
# id of the 'april' person
db.insert('profiles', person_id="%s" % april.id,
address="New York City")
# Insert into the orders table using the
# id of the person and generate a random
# integer for the amount column
