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