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")
