def get_wages():
with engine.connect() as conn:
result = conn.execute(
"SELECT employees.ename ,employees.salary * hours.hours AS Wages FROM hours JOIN employees ON employees.eid=hours.employees_eid WHERE day >= '1998/10/9' AND day <= '1998/10/23';"
)
rows = result.fetchall()
return rows
def drop_tables(engine, exclude_tables):
meta = MetaData(engine)
meta.reflect(bind=engine)
connection = engine.connect()
for tbl in reversed(meta.sorted_tables):
if tbl.name not in exclude_tables:
connection.execute(DropTable(tbl))
def get_daily_inventory(
): # daily profit = profit [revenue - fix cost] by boat
with engine.connect() as conn:
result = conn.execute(
"SELECT r.bid, SUM(r.cost) - SUM(c.cost) AS profit from cost_record c JOIN reserves r ON c.boat_id=r.bid WHERE r.day='1998/10/10' GROUP BY r.bid;"
)
rows = result.fetchall()
return rows
def stmt6():
with engine.connect() as conn:
result = conn.execute(
"select s.sname from sailors s where s.sid not in (select r.sid from reserves r where r.bid in (select b.bid from boats b where b.color = 'red'));"
)
rows = result.fetchall()
result = []
for r in rows:
result.append(r[0])
return result
def custom_assert(raw_query, api_query):
#Create a list from connect().execute() to compare with Query objects
raw_list = []
api_list = []
with engine.connect() as conn:
result = conn.execute(raw_query)
for x in result:
raw_list.append(x)
print(raw_list)
for x in api_query:
api_list.append(x)
print(api_list)
assert raw_list == api_list
def custom_assert(raw_query, api_query):
#convert ResultProxy result from excute() to list to compare with Query object
raw_list = []
api_list = []
with engine.connect() as conn:
result = conn.execute(raw_query)
for x in result:
raw_list.append(x)
#print(raw_list)
for x in api_query:
api_list.append(x)
#print(api_list)
assert raw_list == api_list
def stmt2():
with engine.connect() as conn:
result = conn.execute(
"select b.bid, b.bname, count(*) from reserves r join boats b on b.bid = r.bid where not b.bid = 0 group by bid;"
)
rows = result.fetchall()
result_bid = []
result_bname = []
result_count = []
for r in rows:
result_bid.append(r[0])
result_bname.append(r[1])
result_count.append(r[2])
return result_bid, result_bname, result_count
def gen_report():
tables = [Patient, Encounter, Observation, Procedure]
with Session() as session:
for table in tables:
qry = session.query(func.count(table.id))
count = num_records(qry)
table_name = table.__tablename__.title()
print(f'Number of {table_name}s : {count}')
print("-------")
# Patients by Gender
genders = session.query(distinct(Patient.gender)).all()
for gender in genders:
# gender is a tuple w/ one entry, so we need to use gender[0]
qry = session.query(func.count(
Patient.gender)).filter(Patient.gender == gender[0])
count = num_records(qry)
print(f'Number of {gender[0]} Patients : {count}')
print("-------")
# Popular Procedures
print("The top 10 types of procedures (and their counts) :")
with engine.connect() as con:
rows = con.execute(
"""select type_code, count(type_code) from procedure
group by type_code order by count(type_code) desc
limit 10;""")
for row in rows:
print(f'{row[0]}: {row[1]}')
print("-------")
print("The most popular day of the week when encounters occurred")
rows = con.execute(
"""select extract(dow from start_date), count(id)
from encounter group by 1 order by 2 desc;""")
result = [(row[0], row[1]) for row in rows]
print(f'{calendar.day_name[int(result[0][0])]}: {result[0][1]}')
print("The least popular day of the week when encounters occurred")
print(f'{calendar.day_name[int(result[6][0])]}: {result[6][1]}')
default=func.unix_timestamp()),
Column('_modified', INTEGER(unsigned=True), nullable=False,
default=func.unix_timestamp(), onupdate=func.unix_timestamp()),
mysql_engine = 'InnoDB',
mysql_charset = 'utf8',
)
mods = map(lambda x: dict(
app_label=x.split('_')[0], model=x.split('_', 1)[1]),
metadata.tables.keys())
if __name__ == '__main__':
'''
from sqlalchemy import create_engine
from base import engine, DATABASE_SETTING
test_engine = '%s://%s:%s@%s' % \
(DATABASE_SETTING['drivername'], DATABASE_SETTING['username'], DATABASE_SETTING['password'], DATABASE_SETTING['host'])
conn = create_engine(test_engine).connect()
conn.execute('commit')
str_db_exist = "select `table_schema` from `information_schema`.`tables` where `table_schema`='valor_test';"
if conn.execute(str_db_exist).rowcount == 0:
conn.execute('create database `valor_test`;')
conn.close()
'''
from base import engine
metadata.create_all(engine)
conn = engine.connect()
conn.execute(site_model.insert(), mods)
for r in rows:
result.append(r[0])
return result
def test_stmt6():
expected = [
"brutus", "andy", "rusty", "jit", "zorba", "horatio", "art", "vin",
"bob"
]
result = stmt6()
assert sorted(expected) == sorted(result)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
# load in all data entries from data.txt
with open('./data.txt', 'r') as fp:
for line in fp:
line = line.strip('\n')
with engine.connect() as conn:
conn.execute(line)
test_stmt2()
test_stmt6()
session = Session()
session.commit()
session.close()
INTEGER(unsigned=True),
nullable=False,
default=func.unix_timestamp(),
onupdate=func.unix_timestamp()),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
mods = map(lambda x: dict(app_label=x.split('_')[0], model=x.split('_', 1)[1]),
metadata.tables.keys())
if __name__ == '__main__':
'''
from sqlalchemy import create_engine
from base import engine, DATABASE_SETTING
test_engine = '%s://%s:%s@%s' % \
(DATABASE_SETTING['drivername'], DATABASE_SETTING['username'], DATABASE_SETTING['password'], DATABASE_SETTING['host'])
conn = create_engine(test_engine).connect()
conn.execute('commit')
str_db_exist = "select `table_schema` from `information_schema`.`tables` where `table_schema`='valor_test';"
if conn.execute(str_db_exist).rowcount == 0:
conn.execute('create database `valor_test`;')
conn.close()
'''
from base import engine
metadata.create_all(engine)
conn = engine.connect()
conn.execute(site_model.insert(), mods)
def stmt1():
with engine.connect() as conn:
result = conn.execute("select price_num from prices where pid = 1;")
row = result.first()
return row[0]
from base import Base,engine,Session,qreuz_table
import sqlalchemy as db
from sqlalchemy import String
import json
session = Session()
connection = engine.connect()
# defining queries task 1
query = db.select([db.func.count(qreuz_table.columns.hit_id)])
query_2 = db.select([db.func.count(qreuz_table.columns.user_agent.distinct())])
query_3= db.select([qreuz_table.columns.session_id,
db.func.count(qreuz_table.columns.session_id).label('qty')]).group_by(qreuz_table.columns.session_id
).order_by(db.desc('qty'))
# executing queries
result_1 = connection.execute(query).scalar()
result_2 = connection.execute(query_2).scalar()
result_3 = connection.execute(query_3)
result_3 = list(result_3.fetchall()[0])[1]
# printing results
# print('Number of hits: ' + str(result_1))
# print('Number of unique entries for user_agent: '+ str(result_2))
# print('Most occurence of session_id: '+ str(result_3))
entries = session.query(qreuz_table.columns.ad_ids).all()
counter = 0
for entry in entries: