示例#1
0
    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)" \
示例#2
0
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
示例#3
0
    # 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
示例#7
0
# 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)





示例#8
0
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)




示例#9
0
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
示例#11
0
文件: main.py 项目: mullaned/DB_tests
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










示例#12
0
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
示例#14
0
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
示例#15
0
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
示例#16
0
					   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)" \
示例#17
0
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
示例#18
0
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
示例#19
0
	`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))
示例#20
0
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')

示例#21
0
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")
示例#22
0
	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,