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
