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')) # Get all the available tables for # our database and print them out tables = db.get_available_tables() print tables
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')) # Get all the available columns for our # orders table and print them out columns = db.get_columns_for_table('orders') print columns
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')) # 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
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 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)
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
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')) # Get all te available tables for # our database and print them out. tables = db.get_available_tables() print tables columns = db.get_columns_for_table('articles') print columns
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')) #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)" " AS full_name", "SUM(amount)" " AS total_spend" ], named_tuples=True,
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 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')) # 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('articles') print columns
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')) # Get all the available columns for our # articles table and print them out columns = db.get_columns_for_table('articles') print columns
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 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")
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')) # 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
from database.mysql import MySQLDatabase from settings import db_config from prettytable import PrettyTable db = MySQLDatabase(db_config.get('db_name'), db_config.get('user'), db_config.get('pass'), db_config.get('host')) def league_table(division, season, **kwargs): collect_data = db.league_table(division, season, **kwargs) if season < '1976-77': tie_breaker = 'GA' else: tie_breaker = 'GD' output = PrettyTable(field_names=[ 'Pos', 'Team', 'P', 'W', 'D', 'L', 'F', 'A', tie_breaker, 'Pts', '+/-' ]) base_number = 0 for entry in collect_data: base_number += 1 record = list(entry) record.insert(0, base_number) output.add_row(record) print output def team_results(team, season): collect_data = db.team_results(team, season) output = PrettyTable( field_names=['Game', 'Date', 'Opponent', 'Ven.', 'Res.', 'F', 'A']) base_number = 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')) # 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
# coding=utf-8 from database.mysql import MySQLDatabase from settings import database if __name__ == "__main__": db = MySQLDatabase(database.get('name'), database.get('username'), database.get('password'), database.get('host')) # print db.get_available_tables() # print print db.get_columns_for_table('people') print print db.get_columns_for_table('profiles') print print db.get_columns_for_table('orders') print # # select a tables worth of data # 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 # use CONCAT and SUM to get a persons full name and total spend # people = db.select('people',
from database.mysql import MySQLDatabase my_db = MySQLDatabase(database_name='employees_class_example', username='******', password='******', host='localhost') # 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}
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
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')) # 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")
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')) # Get all te available tables for # our database and print them out. tables = db.get_available_tables() print tables columns = db.get_columns_for_table('articles') 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 teh 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
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")
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')) # Get all the available columns for our # profiles table and print them out columns = db.get_columns_for_table('profiles') print columns
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
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 """ if __name__ == "__main__": db = MySQLDatabase(db_config.get('db_name'), db_config.get('user'), db_config.get('pass'), db_config.get('host')) """ # Get all the records from # the people table results = db.select('people') print "Selecting Rows" for row in results: print row # Selecting columns with named tuples results = db.select('people', columns=['id', 'first_name'], named_tuples=True) print "Selecting Columns" 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"],
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)
["CONCAT(people.first_name, ' ', people.second_name) AS full_name", "min(amount) AS minimum"], True, join="people on people.id = orders.person_id", where="person_id=%s" % person_id)) def mass_order_update(first_name, amount): """Updates all the specified users orders with specified amount""" person_id = str((db.select("people", ['id'], False, where="first_name='%s'"% first_name))[0][0]) db.update("orders", where="person_id=%s" % person_id, amount=amount) if __name__ == "__main__": db = MySQLDatabase(DbSettings.get('database_name'), DbSettings.get('username'), DbSettings.get('password'), DbSettings.get('host')) # Example # print(db.select("people", # ["CONCAT(people.first_name, ' spends') AS First_Name, AVG(amount) AS Average"], # True, # join="orders on people.id = orders.person_id", # where="person_id=1")) # person_average("han") # add_person("Bob","Hope", "1/01/1910") # add_profile("Bob", "1 w. washington") # add_orders("Bob", ["12.50", "13.50"]) # person_min("Bob") # mass_order_update("Bob", "20.02")
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')) 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
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')) # 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:
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')) # 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)
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] # 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 see the effect it will # have orders = db.select('orders', where="person_id=%s" % person.id)
# coding=utf-8 from database.mysql import MySQLDatabase from settings import database if __name__ == "__main__": db = MySQLDatabase(database.get('name'), database.get('username'), database.get('password'), database.get('host')) # print db.get_available_tables() # print print db.get_columns_for_table('people') print print db.get_columns_for_table('profiles') print print db.get_columns_for_table('orders') print # # select a tables worth of data # 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 # use CONCAT and SUM to get a persons full name and total spend # people = db.select('people', # columns=["concat(first_name, ' ', second_name) as full_name", # "SUM(amount) as total_spend"],
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")
from flask import Flask from flask import render_template from database.mysql import MySQLDatabase from database.settings_db import db_config from database.settings_db import db_config_local app = Flask(__name__) is_heroku = os.environ.get("IS_HEROKU", None) if is_heroku: db = MySQLDatabase( db_config.get("db_name"), db_config.get("user"), db_config.get("pass"), db_config.get("host") ) else: db = MySQLDatabase( db_config_local.get("db_name"), db_config_local.get("user"), db_config_local.get("pass"), db_config_local.get("host") ) #REUSUABLE FUNCTIONS def data_table(): """ use this function to refer to the table name in all queries below NOTE: time_spent, the_year and month_number are number data types in the DD