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 # import driver my_db_connection = MySQLDatabase('test_employees', 'root', 'Ciaran03', 'localhost') kwargs={"where":"emp_no LIKE '1000%'", "limit":"10" } wheres={"emp_no":"= 10001"} print my_db_connection.select('employees',['first_name', 'last_name', 'emp_no'] ,**kwargs) my_db_connection.delete('employees', **wheres) print "\n" print"New List" print my_db_connection.select('employees',['first_name', 'last_name', 'emp_no'] ,**kwargs)
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] # Select all orders for that person orders = db.select('orders', named_tuples=True, where="person_id=%s" % person.id) # Print out each of the records for order in orders: print order # Execute the delete function without # `id='=1'` argument to see what happens db.delete('orders', person_id="=%s" % person.id) # Select all the order records for that # person again, so we can the effect it will # have orders = db.select('orders', where="person_id=%s" % person.id) # This won't actually print out # anything because all the records # have been deleted causing # the select to return an empty list for order in orders: print order
print columns # Get all the records from # the people table all_records = db.select('people') print "All records: %s" % str(all_records) # Get all of the records from # the people table but only the # `id` and `first_name` columns column_specific_records = db.select('people', ['id', 'first_name']) print "Column specific records: %s" % str(column_specific_records) # Select data using the WHERE clause where_expression_records = db.select('people', ['first_name'], where="first_name='John'") print "Where Records: %s" % str(where_expression_records) # Select data using the WHERE clause and # the JOIN clause joined_records = db.select('people', ['first_name'], where="people.id=3", join="orders ON people.id=orders.person_id") print "Joined records: %s" % str(joined_records) # Delete a record from the database db.delete('orders', id="=3") # We can also use multiple WHERE clauses! db.delete('orders', id=">4", amount=">1")