db = MySQLDatabase(db_config.get('db_name'), db_config.get('user'),
db_config.get('pass'), db_config.get('host'))
# Get all the available tables for
# our database annd print them out.
tables = db.get_available_tables()
print tables
# Get all the available columns for our
# articles table and print them out
columns = db.get_columns_for_table('people')
print columns
# Get all the records from
# the people table
results = db.select('people')
for row in results:
print row
# 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)" \
import random
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'))
# Select person from people table using
# CONCAT to get their full name and MIN
# to get their minimum amount spent
person = db.select('people', columns=["CONCAT(first_name, ' ', second_name)" \
" AS full_name", "MIN(amount)" \
" AS min_spend"],
named_tuples=True, where="people.first_name='April'",
join="orders ON people.id=orders.person_id")
print person
# 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")
"""
"""Challenge A
Using the AVG(), select a person from your people table and get
the average amount they spend and, at the same time, create a column
that reads, [first_name] spends . Then print out the columns
to provide the answers in the terminal."""
people = db.select('people',
columns=['first_name', "AVG(amount) AS avg_spend"],
named_tuples=True,
where="people.id=1",
join="orders ON people.id=orders.person_id")
print "CHALLENGE A"
for person in people:
print person.first_name, "spends", person.avg_spend
""" Challenge B:
Create a new person in the people table and add in a
profile row and two orders of random value."""
#db.insert('people', first_name="Bruce", second_name="Wayne", DOB='STR_TO_DATE("20/09/1983", "%d/%m/%Y")')
results = db.select('people')
print "Selecting Rows"
for row in results:
print row
db_config.get('pass'),
db_config.get('host'))
# Get all the available tables for
# our database annd print them out.
tables = db.get_available_tables()
print tables
# Get all the available columns for our
# articles table and print them out
columns = db.get_columns_for_table('people')
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
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]
# 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
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'))
# Retrieve the first_name column and get the average
# amount spent where the person.id = 1
people = db.select('people', columns=["first_name", "AVG(amount)"
" AS average_spent"],
named_tuples=True, where="people.id=1",
join="orders ON people.id=orders.person_id")
# Print the results in a format that
# looks like - "<first_name> spends <average_amount>"
for person in people:
print person.first_name, "spends", person.average_spent
# my_tables = my_db.get_available_tables()
# print my_tables
#
# table_columns = my_db.get_columns_for_table('titles')
# print table_columns
#
#
#
# employee = my_db.select('employees', named_tuples=True)[0]
#
# print employee
kwargs= {'where':'emp_no=10002'}
results = my_db.select('employees', ['first_name'], **kwargs)
print results
kwargs = {'where': "emp_no=1000%",
'join':'orders ON people.id=orders.person_id',
'order_by': 'orders.amount desc',
'limit':2}
results2 = my_db.select('people', columns=['', ''], named_tuples=True, **kwargs)
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)
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 people table and get the average amount they spend,
# create a column that reads "First name spends ..."
people = db.select('people',
columns=["first_name", "AVG(amount)"
" AS average_spent"],
named_tuples=True,
where="people.id=2",
join="orders ON people.id=orders.person_id")
for person in people:
print person.first_name, "spends", person.average_spent
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 using the limit clause
limited_results = db.select('orders', limit='5')
print "--------------------------------------"
print "First 5 Results"
print "--------------------------------------"
# iterate over the list of results
for result in limited_results:
print result
print "--------------------------------------"
# Limit the results to 10
limited_results = db.select('orders', limit='10')
print "First 10 results"
print "--------------------------------------"
for result in limited_results:
print result
from database.mysql import MySQLDatabase
my_db = MySQLDatabase('juanexp','root','ihi8Neru')
#results1 = my_db.get_available_tables()
#results2 = my_db.get_columns_for_table('employees')
kwrgs = {'join': "titles on titles.emp_no = employees.emp_no",
'where': "employees.first_name='Gino'",
'order by': "employees.last_name",
'limit': "2"}
results3 = my_db.select('employees', columns=["concat(first_name, ' ', last_name) as Name", "concat(employees.emp_no) as Employee_Number"], named_tuples=True,**kwrgs)
#print results1
#print results2
print results3
print tables
# Get all the available columns for our
# articles table and print them out
columns = db.get_columns_for_table('articles')
print columns
columns = db.get_columns_for_table('profiles')
print columns
columns = db.get_columns_for_table('orders')
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
import random
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 person from people table using
# CONCAT to get their full name and MIN
# to get their minimum amount spent
person = db.select('people', columns=["CONCAT(first_name, ' ', second_name)"
" AS full_name", "MIN(amount)"
" AS min_spend"],
named_tuples=True, where="people.first_name='April'",
join="orders ON people.id=orders.person_id")
print person
from database.mysql import MySQLDatabase
my_db_connection = MySQLDatabase('employees_db',
'root',
'root')
my_tables = my_db_connection.get_available_tables()
my_col = my_db_connection.get_columns_for_table('dept_emp')
kwrgs = {'where': "emp_no-2"}
results = my_db_connection.select('dept_manager', columns=['em', 'first_name'], named_tuples=True, **kwrgs)
print results
print my_tables
print my_col
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'))
descending_results = db.select('orders', order_desc='amount')
print "--------------------------------------"
print "Descending Results -"
print "--------------------------------------"
for result in descending_results:
print result
ascending_results = db.select('orders', order_asc='amount')
print "--------------------------------------"
print "Ascending Results -"
print "--------------------------------------"
for result in ascending_results:
print result
db_config.get('pass'),
db_config.get('host'))
# Get all the available tables for
# our database annd print them out.
tables = db.get_available_tables()
print tables
# Get all the available columns for our
# articles table and print them out
columns = db.get_columns_for_table('people')
print columns
# Get all the records from
# the people table
results = db.select('people')
for row in results:
print row
# 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)" \
db = MySQLDatabase(db_config.get('db_name'), db_config.get('user'),
db_config.get('pass'), db_config.get('host'))
# Get all the available tables for
# our database annd print them out.
tables = db.get_available_tables()
print tables
# Get all the available columns for our
# articles table and print them out
columns = db.get_columns_for_table('people')
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
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 using the limit clause
limited_results = db.select('orders', limit='5')
print "--------------------------------------"
print "First 5 Results"
print "--------------------------------------"
# iterate over the list of results
for result in limited_results:
print result
print "--------------------------------------"
# Limit the results to 10
limited_results = db.select('orders', limit='10')
print "First 10 results"
print "--------------------------------------"
for result in limited_results:
print result
`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
db.insert('orders',
person_id="%s" % april.id,
amount="%s" % random.randint(2, 18))
import MySQLdb as _mysql
# To connect to we need to import the module and use it to create a connection instance
from database.mysql import MySQLDatabase
employees02_db = MySQLDatabase('employees02_db', 'root', '123root', 'localhost')
my_tables = employees02_db.get_available_tables()
print my_tables
my_select = employees02_db.select('employees')
print my_select
kwargs = {'where': "gender=M",
'order_by': 'emp_no'}
results = employees02_db.select('employees', columns=["concat('first_name', '' , 'last_name') as full_name"],
named_tuples=True, **kwargs)
print kwargs
'''
db = _mysql.connect(db='employees02_db',
host='localhost',
user='******',
passwd='123root')
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")
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
db.insert('orders', person_id="%s" % april.id,
amount="%s" % random.randint(2, 18))
db.insert('orders', person_id="%s" % april.id,
