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"],
["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 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