def state_univ(state_list):
"""Return A JSON Containing National University Data."""
output = []
db_operator = Database()
db_operator.check_database()
for state_abbr in state_list:
result_list = db_operator.get_state_univ(state_abbr=state_abbr)
result_list = [
dict(ID=result[0],
Name=result[1],
Ranking=result[2],
State=result[3],
City=result[4],
Type=result[5],
FoundYear=result[6],
Endowment=result[7],
TuitionAndFeesInState=result[8],
TuitionAndFeesOutOfState=result[9],
Enrollment=result[10],
MedianStartingSalary=result[11],
StudentFacultyRatio=result[12],
FemalePercentage=round(result[13] * 100, 1)
if result[13] is not None else None) for result in result_list
]
output += result_list
return json.dumps(output)
def __init__(self, app):
global session
session = Session()
self.app = app
self.db = Database()
self.db.destroy()
self.db.init()
def state_gdp(state_list):
"""Return A JSON Containing GDP Data."""
output = []
db_operator = Database()
db_operator.check_database()
for state_abbr in state_list:
result = db_operator.get_state_gdp(state_abbr=state_abbr)
result = dict(Name=result[0],
Y1997=result[1],
Y1998=result[2],
Y1999=result[3],
Y2000=result[4],
Y2001=result[5],
Y2002=result[6],
Y2003=result[7],
Y2004=result[8],
Y2005=result[9],
Y2006=result[10],
Y2007=result[11],
Y2008=result[12],
Y2009=result[13],
Y2010=result[14],
Y2011=result[15],
Y2012=result[16],
Y2013=result[17],
Y2014=result[18],
Y2015=result[19],
Y2016=result[20])
output.append(result)
return json.dumps(output)
def main():
if request.method == 'POST':
ttemail = request.form.get('email')
tpassword = request.form.get('password')
db = Database()
if db.isUserValid(ttemail, tpassword):
return redirect("/dashboard")
render_template('Login.html')
return render_template('Login.html')
def get_project_colors(cls):
cursor = Database.find_db()
# returns project colors by companies
cursor.execute("SELECT main_color FROM project WHERE name IS NOT NULL ORDER BY name;")
project_colors = []
for i in cursor.fetchall():
project_colors.append(list(i))
for i, j in enumerate(project_colors):
project_colors[i][0] = Database.hex_to_rgb(j[0])
return project_colors
class DatabaseMiddleware(object):
def __init__(self, app):
global session
session = Session()
self.app = app
self.db = Database()
self.db.destroy()
self.db.init()
def __call__(self, env, start):
return self.app(env, start)
def get_project_colors(cls):
cursor = Database.find_db()
# returns project colors by companies
cursor.execute("SELECT company_name, main_color FROM project ORDER BY company_name;")
colors = cursor.fetchall()
client_colors = []
for i in colors:
client_colors.append(list(i))
for i, j in enumerate(client_colors):
client_colors[i][1] = Database.hex_to_rgb(j[1])
return client_colors
def login():
db = Database()
if request.method == 'POST':
username = request.form.get("username")
password = request.form.get("password")
if db.isUserValid(username, password):
print("im okay")
return redirect("/page2")
else:
return redirect("/")
if request.method == 'GET':
return render_template('index.html')
def get_database():
db_path = "{}/songs.sqlite".format(os.path.expanduser('~/.lyrical'))
if os.path.isfile(db_path):
db = Database(db_path)
else:
try:
db = Database(db_path)
db.create_tables()
except e:
dialog = ModalDialog()
dialog.error('Database init error {}: {}'.format(e.errno, e.strerror))
raise
return db
def register():
db = Database()
if request.method == 'GET':
return render_template('reg.html')
if request.method == 'POST':
username = request.form.get("username")
email = request.form.get("email")
password = request.form.get("psw")
password_repeat = request.form.get("psw-repeat")
if password == password_repeat and password_repeat != "" and password != "":
db.insertUser(username, email, password)
return redirect("/login")
return render_template('reg.html')
def bar_state_univ(limit=20):
"""Bar Plot - States with Most National Universities"""
if limit not in list(range(1, 31)):
print('Invalid Input')
return
with Database() as db_operator:
db_operator.check_database()
result_list = db_operator.get_university_count(limit)
data = [Bar(x=[result[0] for result in result_list],
y=[result[1] for result in result_list],
marker=dict(line=dict(color='rgb(0,0,0)',
width=1.5)))]
layout = Layout(xaxis=dict(tickangle=-45,
tickfont=dict(size=10)),
yaxis=dict(tickfont=dict(size=15)),
title='Top {} States with Most National Universities'.format(limit))
fig = Figure(data=data, layout=layout)
try:
div = offplot(fig, show_link=False, output_type="div", include_plotlyjs=False)
return div
except PlotlyRequestError:
print('Account limit reached')
return '<div></div>'
def histogram_difference_tuition():
"""Plot Histogram Showing Difference between In-State and Out-of-State Tuition and Fees of Public Universities"""
with Database() as db_operator:
db_operator.check_database()
result_list = db_operator.get_tuition_difference()
data = [Bar(x=['0', '0 ~ 5,000', '5,000 ~ 10,000', '10,000 ~ 15,000', '15,000 ~ 20,000', '20,000 ~ 25,000',
'25,000 ~ 30,000', '> 30,000'],
y=[len([result for result in result_list if result == 0]),
len([result for result in result_list if 0 < result <= 5000]),
len([result for result in result_list if 5000 < result <= 10000]),
len([result for result in result_list if 10000 < result <= 15000]),
len([result for result in result_list if 15000 < result <= 20000]),
len([result for result in result_list if 20000 < result <= 25000]),
len([result for result in result_list if 25000 < result <= 30000]),
len([result for result in result_list if result > 30000])],
marker=dict(line=dict(color='rgb(0,0,0)',
width=1.5)))]
layout = Layout(xaxis=dict(tickfont=dict(size=15)),
yaxis=dict(tickfont=dict(size=15)),
title='Difference between In-State and Out-of-State Tuition and Fees of Public Universities')
fig = Figure(data=data, layout=layout)
try:
div = offplot(fig, show_link=False, output_type="div", include_plotlyjs=False)
return div
except PlotlyRequestError:
print('Account limit reached')
return '<div></div>'
def register():
db = Database()
if request.method == 'POST':
fullname = request.form.get('fulllname')
email = request.form.get('the_email')
phone_number = request.form.get('the_phone_number')
password = request.form.get('password')
confirmpass = request.form.get('confirm_pass')
if password != "" and confirmpass != "" and password == confirmpass:
db.insertUser(fullname, email, password)
print("done")
# return render_template('Register.html',errormessage="User added successfully")
return redirect('/')
return render_template('Register.html',
errormessage="Mismatch password")
return render_template('Register.html')
def getDatabase():
if 'db' not in g:
g.db = Database('bucket_list')
if g.db is None:
abort(400)
return g.db
def run_server():
with SimpleXMLRPCServer(("localhost", 9000), allow_none=True) as server:
server.register_instance(Database("contacts.db"))
print("Serving XML-RPC on localhost port 9000")
try:
server.serve_forever()
except KeyboardInterrupt:
print("Server interrupted, exiting")
def get_budget_by_project(cls):
cursor = Database.find_db()
# projects witout name are removed
cursor.execute("SELECT name, budget_value, budget_currency FROM project WHERE manager IS NOT NULL ORDER BY name;")
budget_list = []
for i in cursor.fetchall():
budget_list.append(list(i))
for i in budget_list:
i[1] = "%.0f" % (Project.currency_conversion(i[2]) * float(i[1]))
i[2] = "EUR"
return budget_list
def is_maintenance_requested(cls):
# returns list of boolean values
cursor = Database.find_db()
# projects without names are removed
cursor.execute("SELECT maintenance_requested FROM project WHERE name IS NOT NULL ORDER BY name")
maintenance_requested = []
for i in cursor.fetchall():
if i[0] == 'true':
maintenance_requested.append(True)
else:
maintenance_requested.append(False)
return maintenance_requested
def scatter_public_private(x_axis='Enrollment', y_axis='MedianStartingSalary'):
"""Scatter Plot - Clustering of Private & Public University"""
axis_range = ['FoundYear', 'Endowment(M)', 'TuitionAndFeesInState', 'TuitionAndFeesOutOfState', 'Enrollment',
'MedianStartingSalary', 'StudentFacultyRatio', 'FemalePercentage', 'Tuition Difference']
if x_axis not in axis_range:
x_axis = 'Enrollment'
if y_axis not in axis_range:
y_axis = 'MedianStartingSalary'
with Database() as db_operator:
db_operator.check_database()
result_list = db_operator.get_public_private(x_axis=x_axis, y_axis=y_axis)
trace1 = Scatter(x=[result[0] for result in result_list if result[2] == 'Private'],
y=[result[1] for result in result_list if result[2] == 'Private'],
text=[result[3] for result in result_list if result[2] == 'Private'],
marker=dict(size=12,
color='#258039'),
mode='markers',
name='Private')
trace2 = Scatter(x=[result[0] for result in result_list if result[2] == 'Public'],
y=[result[1] for result in result_list if result[2] == 'Public'],
text=[result[3] for result in result_list if result[2] == 'Public'],
marker=dict(size=12,
color='#F5BE41'),
mode='markers',
name='Public')
data = [trace1, trace2]
layout = Layout(xaxis=dict(tickfont=dict(size=15),
title=x_axis,
titlefont=dict(size=20)),
yaxis=dict(tickfont=dict(size=15),
title=y_axis,
titlefont=dict(size=20),
showline=True),
title='Clustering of Private & Public University',
titlefont=dict(size=25),
legend=dict(font=dict(size=20),
x=0.88,
y=0.97))
fig = Figure(data=data, layout=layout)
try:
div = offplot(fig, show_link=False, output_type="div", include_plotlyjs=False)
return div
except PlotlyRequestError:
print('Account limit reached')
return '<div></div>'
def scatter_university_gdp(input_phrase='Total Endowment (Million)'):
"""Scatter Plot - Relationship between GDP & National University Amount"""
input_range = ['Total Endowment (Million)', 'Average Endowment (Million)', 'Total Enrollment', 'Average Enrollment',
'Average Tuition in State', 'Average Tuition out of State', 'Average Median Starting Salary',
'Average Student Faculty Ratio', 'Average Female Percentage']
if input_phrase not in input_range:
input_phrase = 'Total Endowment (Million)'
with Database() as db_operator:
db_operator.check_database()
result_list = db_operator.get_university_gdp(additional_variable=input_phrase)
if input_phrase == 'Average Student Faculty Ratio':
text_format = '{0} ({1})<br>{2}: {3}:1'
elif input_phrase == 'Average Female Percentage':
text_format = '{0} ({1})<br>{2}: {3: 3.0%}'
else:
text_format = '{0} ({1})<br>{2}: {3}'
text = [text_format.format(result[1], result[0], input_phrase, result[3]) for result in result_list]
data = [Scatter(x=[result[2] for result in result_list],
y=[result[3] for result in result_list],
text=text,
marker=dict(sizeref=2. * max([result[4] for result in result_list]) / (100 ** 2),
size=[result[4] for result in result_list],
color=[result[4] for result in result_list],
colorscale='Viridis',
sizemode='area',
showscale=True,
colorbar=dict(title=input_phrase.replace(' (', '</br>(').replace(' ', '</br></br>'),
titlefont=dict(size=15),
tickfont=dict(size=15))),
mode='markers')]
layout = Layout(xaxis=dict(tickfont=dict(size=15),
title='National University',
titlefont=dict(size=20)),
yaxis=dict(tickfont=dict(size=15),
title='GDP of State (Million $)',
titlefont=dict(size=20)),
title='Relationship between GDP & National University',
titlefont=dict(size=25))
fig = Figure(data=data, layout=layout)
try:
div = offplot(fig, show_link=False, output_type="div", include_plotlyjs=False)
return div
except PlotlyRequestError:
print('Account limit reached')
return '<div></div>'
def state_abbr_table():
"""Initialize State Abbreviation Table"""
t_name = 'State_Abbr'
data_dict = get_state_abbr_data()
data_dict['DC'] = 'District of Columbia'
table_dict = dict(name=t_name,
column=[('Id', 'INTEGER'), ('StateAbbr', 'TEXT'),
('StateName', 'TEXT')],
key='Id')
statement = 'INSERT INTO {} VALUES (NULL, ?, ?)'.format(t_name)
with Database() as db_operator:
db_operator.create_table(table_dict)
db_operator.insert_data(list(data_dict.items()), statement)
def gdp_state_table():
"""Initialize GDP State Table"""
t_name = 'GDP_State'
column_name, column_data = get_gdp_data()
column_list = ['INT'] * (len(column_name) - 1)
table_dict = dict(name=t_name,
column=[('StateId', 'INTEGER')] +
list(zip(column_name[1:], column_list)))
statement = 'INSERT INTO {} VALUES ('.format(t_name)
statement += '(SELECT Id FROM State_Abbr WHERE StateName = ?)'
statement += ', ?' * (len(column_name) - 1)
statement += ')'
with Database() as db_operator:
db_operator.create_table(table_dict)
db_operator.insert_data([tuple(row) for row in column_data[1:]],
statement)
def mapbox_univ(state_abbr):
"""Plot National Universities on Map through Mapbox"""
with Database() as db_operator:
db_operator.check_database()
result_list = db_operator.get_gps_data(state_abbr=state_abbr)
lat_list = []
lon_list = []
name_list = []
for result in result_list:
if (result[1] is not None) and (result[2] is not None):
lat_list.append(result[1])
lon_list.append(result[2])
name_list.append(result[0])
axis_dict = get_axis_info(lat_list, lon_list)
data = Data([Scattermapbox(name="National Universities",
lat=lat_list,
lon=lon_list,
text=name_list,
mode="markers",
marker=Marker(size=10))])
layout = Layout(title="National Universities in " + state_abbr,
autosize=True,
hovermode="closest",
mapbox=dict(
accesstoken=mapbox_access_token,
bearing=0,
center=dict(lat=axis_dict["center_lat"],
lon=axis_dict["center_lon"]),
zoom=5,
pitch=0))
fig = dict(data=data, layout=layout)
try:
div = offplot(fig, show_link=False, output_type="div", include_plotlyjs=False)
return div
except PlotlyRequestError:
print('Account limit reached')
return '<div></div>'
def line_gdp(abbr_list):
"""Scatter Plot - GDP"""
# if len(abbr_list) > 10:
# print('Too Many States')
# return
with Database() as db_operator:
db_operator.check_database()
data = []
for state_abbr in abbr_list:
result = db_operator.get_state_gdp(state_abbr=state_abbr)
if result is None:
print('No Result')
continue
data.append(Scatter(x=list(range(1996, 2017)),
y=result[1:],
mode='lines+markers',
name=state_abbr))
layout = Layout(xaxis=dict(tickfont=dict(size=15),
title='Year',
titlefont=dict(size=20)),
yaxis=dict(tickfont=dict(size=15),
title='GDP',
titlefont=dict(size=20)),
title='GDP')
fig = Figure(data=data, layout=layout)
try:
div = offplot(fig, show_link=False, output_type="div", include_plotlyjs=False)
return div
except PlotlyRequestError:
print('Account limit reached')
return '<div></div>'
def national_university_table():
"""Initialize National University Table"""
t_name = 'National_University'
db_operator = Database()
table_dict = dict(name=t_name,
column=[('Id', 'INTEGER'),
('Name', 'TEXT'),
('Ranking', 'TEXT'),
('StateId', 'INT'),
('City', 'TEXT'),
('Type', 'TEXT'),
('FoundYear', 'TEXT'),
('Endowment(M)', 'REAL'),
('TuitionAndFeesInState', 'INT'),
('TuitionAndFeesOutOfState', 'INT'),
('Enrollment', 'INT'),
('MedianStartingSalary', 'INT'),
('StudentFacultyRatio', 'INT'),
('FemalePercentage', 'REAL'),
('Latitude', 'INT'),
('Longitude', 'INT')],
key='Id')
db_operator.create_table(table_dict)
statement = 'INSERT INTO {} VALUES (NULL'.format(t_name)
statement += ', ?' * 2
statement += ', (SELECT Id FROM State_Abbr WHERE StateAbbr = ?)'
statement += ', ?' * 12
statement += ')'
data_list = []
for nu_obj in get_all_national_university():
data_list.append((nu_obj.name, nu_obj.ranking, nu_obj.state, nu_obj.city,
nu_obj.type, nu_obj.found_year, nu_obj.endowment, nu_obj.tuition_in_state,
nu_obj.tuition_out_state, nu_obj.enrollment, nu_obj.median_salary,
nu_obj.student_faculty, nu_obj.female, nu_obj.lat, nu_obj.lng))
db_operator.insert_data(data_list, statement)
class Processor(object):
def __init__(self):
self.filer = FileHandler()
self.validator = Validator()
self.database = Database()
self.editor = Editor()
self.plotter = Plotter()
def add_data(self, fileloc):
self.database.empty_database()
self.filer.set_filepath(fileloc)
self.filer.load_file()
self.filer.strip_tags()
self.validator.set_raw_data(self.filer.export())
self.validator.parse_data()
self.database.add_people(self.validator.export_good_data())
def process_bad(self):
if self.validator.has_bad_data():
bad_data = self.validator.export_bad_data()
self.editor.set_raw(bad_data)
for data_string in bad_data:
(print("Bad data: \n" + data_string))
action = self.prompt_recursively_for_input()
self.editor.edit_or_delete(data_string, action)
print ("All bad data has been handled")
self.database.add_people(self.editor.export_good_data())
def prompt_recursively_for_input(self):
action = input("Press 'E' to edit the data, press 'D' to delete it.\n")
action = action.upper()
if action == 'E' or'D':
return action
else:
return self.prompt_recursively_for_input()
def set_file_path(self, new_path):
self.database.set_directory(new_path)
def get_file_path(self):
return self.database.get_directory()
def serialize(self, option):
self.database.serialize(option)
def deserialize(self, option):
self.database.empty_database()
self.database.deserialize(option)
def pie_bmi(self):
dist = self.database.get_bmi_distribution()
self.plotter.pie_bmi(dist["normal"], dist["overweight"], dist["obese"], dist["underweight"])
def pie_gender(self):
dist = self.database.get_gender_distribution()
self.plotter.pie_gender(dist["males"], dist["females"])
def scatter_sales(self):
sales_list = self.database.get_sales_ordered()
self.plotter.scatter_sales(sales_list)
def bar_bmi_vs_gender(self):
self.plotter.bar_bmi_vs_gender(self.database.get_male_bmi(),self.database.get_female_bmi() )
"""
Execute script
"""
from model import Database
# sys.path.append('/Users/admin/Developer/Python/Playground/Visual')
config = {
'user': '******',
'password': '******',
'host': 'localhost',
'database': 'MV'
}
db = Database(**config)
db.show_weeks_short_information()
print(db.show_week_full_information(28))
db.close()
@web.middleware
async def user_middleware(request: web.Request,
handler: Callable) -> web.Response:
session = await get_session(request)
if not session.empty:
session['last_visited'] = datetime.datetime.now().isoformat()
if session.get('user', None):
request["user"] = session['user']
request['last_visited'] = session['last_visited']
return await handler(request)
loop = asyncio.get_event_loop()
pg_pool = loop.run_until_complete(create_pool())
db = Database(pg_pool)
redis = loop.run_until_complete(get_redis_pool())
storage = RedisStorage(redis)
session_middleware = aiohttp_session.session_middleware(storage)
dir_path = os.path.dirname(os.path.realpath(__file__))
router = SwaggerRouter(search_dirs=[dir_path],
swagger_ui='/api/',
default_validate=True)
app = web.Application(router=router,
middlewares=[
session_middleware, error_middleware,
user_middleware, jsonify
])
def get_manager_names_and_companies(cls):
cursor = Database.find_db()
cursor.execute("SELECT manager, company_name FROM project WHERE manager IS NOT NULL ORDER BY name;")
managers = cursor.fetchall()
return managers
from view import View
from model import Database
#Open connection to database with the Database class from models
my_database = Database()
#Initialize View class from view
my_view = View()
running = True
#Run menu until the exit choice is selected
while running == True:
#output menu and take user input
user_input = my_view.menu()
#Insert item into shoppinglist
if user_input == "1":
#Get item name
list_item = my_view.takeInput("Enter the name of the item: ")
#Get amount of item
choosing = True
amount_of_item = ""
while choosing == True:
amount_of_item = my_view.takeInput(
"Enter how many of that item you want: ")
#CHeck if input is integer and convert to interger
if amount_of_item.isdigit() == True:
amount_of_item = int(amount_of_item)
choosing = False
def get_project_duedates(cls):
cursor = Database.find_db()
cursor.execute("SELECT duedate FROM project WHERE name IS NOT NULL ORDER BY name;")
project_duedates = []
return cursor.fetchall()
def __init__(self):
self.filer = FileHandler()
self.validator = Validator()
self.database = Database()
self.editor = Editor()
self.plotter = Plotter()
def get_client_and_number_of_projects(cls):
cursor = Database.find_db()
cursor.execute("""SELECT company_name, COUNT(company_name)
FROM project GROUP BY company_name ORDER BY company_name;""")
rows = cursor.fetchall()
return rows
def setUp(self):
"""Generate Database Operator"""
from model import Database
self.db = Database()
from flask import Flask,request, session, g, redirect, url_for
from model import Database
import os
from config import CONFIG
app = Flask(__name__)
app.config.from_object(CONFIG)
x = Database(app)
x.init_db()
@app.route('/')
def hello_world():
return 'Hello World!'
@app.route("/api",methods=["POST","GET"])
def api():
pass
if __name__ == '__main__':
app.run()
from flask import Flask, request
from flask_cors import CORS
from model import Database
app = Flask(__name__)
CORS(app, origins='*', supports_credentials=True)
db = Database()
@app.route("/databases")
def get_data_bases():
database_list = db.select_rows('SELECT datname FROM pg_database;')
print('database_list', database_list)
return database_list
@app.route("/databases/<dbname>/tables")
def get_table_list(dbname):
tables_list = db.select_rows(
"SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_catalog = '"
+ dbname + "' ORDER BY table_schema, table_name;",
dbname=dbname)
return tables_list
@app.route("/databases/<dbname>/schema/<schemaname>")
def get_schema(dbname, schemaname):
schema_table_list = db.select_rows(
"SELECT c.table_name, t.table_type, json_agg(json_build_object('column_name', c.column_name, 'data_type', c.data_type)) AS columns FROM information_schema.columns c JOIN information_schema.tables t ON c.table_name = t.table_name WHERE c.table_schema = '"
+ schemaname + "' GROUP BY c.table_name, t.table_type;",
from flask import Flask, request, session, g, redirect, url_for
from model import Database
import os
from config import CONFIG
app = Flask(__name__)
app.config.from_object(CONFIG)
x = Database(app)
x.init_db()
@app.route('/')
def hello_world():
return 'Hello World!'
@app.route("/api", methods=["POST", "GET"])
def api():
pass
if __name__ == '__main__':
app.run()