def write(self):
print(
f'Writing {len(self.rows):,} rows to {IpedsCharge.__tablename__} table in database.'
)
session = Session()
if len(self.rows) > 0:
try:
for row in self.rows:
_ = session.query(IpedsCharge).filter(
IpedsCharge.unitid == row.unitid,
IpedsCharge.date_key == row.date_key).delete(
synchronize_session=False)
session.bulk_save_objects(self.rows)
session.commit()
except Exception as e:
print(f'An error occurred:\n{str(e)}.')
session.rollback()
print('No changes were made to the database due to error.')
else:
print('Rows successfully written to database.')
else:
print('No rows were available to insert.')
session.close()
def create_element_api(id, id_owner, id_domain) -> str:
body = request.json
session = Session()
service = get_service(session, id)
owner = get_owner(session, id_owner)
domain = get_domain(session, id_domain)
if (service is not None) and (owner is not None) and (domain is not None)\
and (owner in service.owner) and (domain in owner.domain):
element = create_element(session, domain, body["name"],
body["information"], None,
datetime.datetime.now(), body["init_time"],
body["end_time"], body["price"])
element.url = "/service/%d/owner/%d/domain/%d/element/%d" % (
service.id, owner.id, domain.id, element.id)
element.reserved = False
session.commit()
response = element.get_dict()
session.close()
return json.dumps(response)
session.close()
return "ERROR"
def create_reservation_api(id, id_owner, id_domain, id_element) -> str:
body = request.json
session = Session()
service = get_service(session, id)
owner = get_owner(session, id_owner)
domain = get_domain(session, id_domain)
element = get_element(session, id_element)
client = get_client(session, body["id_client"])
if (element is not None) and (element.reserved == True):
session.close()
return "RESERVED"
if (service is not None) and (owner is not None) and (domain is not None)\
and (owner in service.owner) and (domain in owner.domain)\
and (client is not None) and (element in domain.element)\
and (client in service.client) and (element.reserved ==False):
reservation = create_reservation(session, service, client, element,
body["name"], body["information"],
None, datetime.datetime.now())
reservation.url = "/service/%d/client/%d/reservation/%d" % (
service.id, client.id, reservation.id)
session.commit()
response = reservation.get_dict()
session.close()
return json.dumps(response)
session.close()
return "ERROR"
def insert_user(username, state):
session = Session()
user = User(username, state)
res = session.query(User).filter(User.name == username).all()
if len(res) < 1:
session.add(user)
session.commit()
session.close()
else:
print('User already exists')
def create_service_api() -> str:
session = Session()
body = request.json
service = create_service(session, body['name'], body['information'], None,
datetime.datetime.now())
service.url = "/service/%d" % (service.id)
session.commit()
response = service.get_dict()
session.close()
return json.dumps(response)
def update_status(username, state):
session = Session()
res = session.query(User).filter(User.name == username).all()
if len(res) < 1:
user = User(username, state)
session.add(user)
session.commit()
session.close()
if len(res) == 1:
user = session.query(User).filter(User.name == username).first()
user.state = state
session.commit()
session.close()
def create_client_api(id)->str:
body = request.json
session = Session()
service = get_service(session, id)
if service is not None:
client = create_client(session, service, body['name'], body['information'],None,datetime.datetime.now())
client.url="/service/%d/client/%d" % (service.id,client.id)
session.commit()
response = client.get_dict()
session.close()
return json.dumps(response)
session.close()
return "ERROR"
def get_element_byid_api(id, id_element):
session = Session()
service = get_service(session, id)
element = get_element(session, id_element)
client_id = request.args.get('client_id')
client = get_client(session, client_id)
if (service is not None) and (element is not None):
#Get Reservation
if request.method == 'GET':
response = element.get_dict()
session.close()
return json.dumps(response)
#Make Reservation
elif request.method == 'POST' and (client is not None):
body = request.json
if isinstance(body["information"], dict):
body["information"] = json.dumps(body["information"])
reservation = create_reservation(session, service, client, element,
body["name"], body["information"],
None, datetime.datetime.now())
if reservation is not None:
reservation.url = "/service/%d/client/%d/reservation/%d" % (
service.id, client.id, reservation.id)
session.commit()
response = reservation.get_dict()
session.close()
return jsonify(response)
else:
session.close()
return "RESERVED"
#Delete element
delete_element(session, element=element)
session.close()
return "DELETED"
session.close()
return "ERROR"
def create_owner_api(id) -> str:
body = request.json
session = Session()
service = get_service(session, id)
if service is not None:
owner = create_owner(session, service,
body['name'], body['information'], None,
datetime.datetime.now())
owner.url = "/service/%d/owner/%d" % (service.id, owner.id)
session.commit()
response = owner.get_dict()
session.close()
return json.dumps(response)
session.close()
return "ERROR"
def write(self):
session = Session()
if len(self.rows) > 0:
try:
_ = session.query(self.rows[0].__class__).filter(self.rows[0].__class__.date_key==self.date_key).delete(synchronize_session=False)
session.bulk_save_objects(self.rows)
session.commit()
except Exception as e:
print(f'An error occurred:\n{str(e)}.')
session.rollback()
print('No changes were made to the database due to error.')
else:
print('Rows successfully written to database.')
else:
print('No rows were available to insert.')
session.close()
def setup_db():
Base.metadata.create_all(engine)
session = Session()
if session.query(User).first() == None:
# plain text password: admin
session.add(
User(
0, "admin",
"103ed64fd2ec3a053dd50bca44ddf7ed6cdeedf83963c44044b494ea69afa52e"
))
if session.query(Room).first() == None:
session.add(
Room(0, "Main Room", "Initial room you connect to.", 100, "admin"))
session.commit()
session.close()
def get_reservation_api(id, id_client, id_reservation) -> str:
session = Session()
service = get_service(session, id)
client = get_client(session, id_client)
reservation = get_reservation(session, id_reservation)
if (service is not None) and (client is not None)\
and (reservation is not None) and (client in service.client)\
and (reservation in client.reservation):
#Get Reservation
if request.method == 'GET':
response = reservation.get_dict()
session.close()
return json.dumps(response)
#Update Information
elif request.method == 'PUT':
body = request.json
if "information" in body.keys():
if isinstance(body["information"], dict):
body["information"] = json.dumps(body["information"])
reservation.information = body["information"]
session.commit()
response = reservation.get_dict()
session.close()
return jsonify(response)
#Delete reservation
delete_reservation(session, reservation=reservation)
session.close()
return "DELETED"
session.close()
return "ERROR"
def create_domain_api(id, id_owner) -> str:
body = request.json
session = Session()
service = get_service(session, id)
owner = get_owner(session, id_owner)
if (service is not None) and (owner is not None) and (owner
in service.owner):
domain = create_domain(session, owner, body['name'],
body['information'], None,
datetime.datetime.now())
domain.url = "/service/%d/owner/%d/domain/%d" % (service.id, owner.id,
domain.id)
session.commit()
response = domain.get_dict()
session.close()
return json.dumps(response)
session.close()
return "ERROR"
def save(self):
Session.add(self)
Session.commit()
def insert_word(eng_word):
session = Session()
word = Word(eng_word)
session.add(word)
session.commit()
session.close()
def insert_translate(translate_word):
session = Session()
translate = Translate(translate_word)
session.add(translate)
session.commit()
session.close()
def main():
for year in np.arange(args.first, args.last + 1):
try:
spec = f'data/nsf_{year}.pickle'
print(f'Reading data for fiscal year ending {year}...', end='', flush=True)
with open(spec, 'rb') as f:
herd = pickle.load(f)
except Exception as e:
print(f'ERROR.\nFile not downloaded properly.\n\n{str(e)}\n')
else:
print('DONE.')
# herd.info()
# set date key
date_key = f'{year}-06-30'
# modify data frame to apply needed fixes
herd['date_key'] = date_key
# convert id's and add missing values
herd.ncses_inst_id = herd.ncses_inst_id.fillna('XXXXXXXX')
# get unitid fixes
with open('data/inst_id_fixes.pickle', 'rb') as f:
fixes = pickle.load(f)
# apply fixes to unitid column, fill missing, and convert to integer
herd['unitid'] = herd.inst_id.map(fixes)
herd.unitid = np.where(herd.unitid.isna(), herd.ipeds_unitid, herd.unitid)
herd.unitid = herd.unitid.fillna(-1).astype(int)
herd = herd[herd.column.isin(['DOD',
'DOE',
'HHS',
'NASA',
'NSF',
'USDA',
'Other agencies',
'State and local government',
'Business',
'Institution funds',
'Nonprofit organziations'])]
herd['agency_key'] = item_recode(herd.column,
{'DOD': 'DOD',
'DOE': 'DOE',
'HHS': 'HHS',
'NASA': 'NAS',
'NSF': 'NSF',
'USDA': 'USD',
'Other agencies': 'OTH',
'State and local government': 'SLG',
'Business': 'BUS',
'Institution funds': 'INS',
'All other sources': 'OTH',
'Nonprofit organziations': 'NPO'},
'Unknown')
herd['funding_type'] = herd.questionnaire_no.str[:2]
herd = herd[herd.funding_type.isin(['09', '11'])]
herd['funding_type'] = item_recode(herd.funding_type, {'09': 'Federal', '11': 'Non-federal'}, 'Unknown')
herd['academic_field_key'] = herd.questionnaire_no.str[2:].str.strip()
herd = herd[herd.academic_field_key.isin(['A','B01','B02','B03','B04','B05','B06','B07','B08',
'B09','C01','C02','C03','C04','D01','D02','D03','D04',
'D05','E','F01','F02','F03','F04','F05','G','H01',
'H02','H03','H04','H05','I','J01','J02','J03','J04',
'J05','J06','J07','J08'])]
herd['data'] = herd.data.fillna(0) * 1000
# rename columns
herd = herd.rename(columns = {'data': 'expenditure'})
# de-duplicate items
keepers = ['inst_id',
'date_key',
'funding_type',
'agency_key',
'academic_field_key',
'ncses_inst_id',
'unitid',
'expenditure']
herd = herd[keepers].groupby(['inst_id',
'date_key',
'funding_type',
'agency_key',
'academic_field_key',
'ncses_inst_id',
'unitid']).sum().reset_index()
herd['expenditure'] = herd.expenditure.fillna(0)
# herd = herd.fillna(sql.null())
# insert data into dbo.survey_records
session = Session()
try:
print(f'Attempting to insert {herd.shape[0]:,} rows for {year} into {NsfHerdDetail.__tablename__}.')
record_deletes = session.query(NsfHerdDetail).filter(NsfHerdDetail.date_key==date_key).delete(synchronize_session=False)
session.bulk_insert_mappings(mapper = NsfHerdDetail,
mappings = herd.to_dict(orient='records'),
render_nulls = True)
except Exception as e:
session.rollback()
print(str(e))
print('No data were altered due to error.\n')
else:
session.commit()
print(f'\t{record_deletes:,} old records were deleted.')
print(f'\t{herd.shape[0]:,} new records were inserted.\n')
finally:
session.close()
session = None
print('All done!')
import numpy as np
import pandas as pd
from pandas import DataFrame
from sqlalchemy import sql
print("Reading faculty dimension data")
df = pd.read_csv('data/ipeds_faculty_dimension.csv')
session = Session()
try:
print('Populating dimension tables.')
# insert state data
record_deletes = session.query(IpedsFacultyDimension).delete(
synchronize_session=False)
df_inserts = session.bulk_insert_mappings(
mapper=IpedsFacultyDimension, mappings=df.to_dict(orient='records')),
print('\tFinished populating counties table.')
except Exception as e:
session.rollback()
print(
"\tAn error occurred and no data were changed in the database.\n\nError:\n{}"
.format(str(e)))
else:
session.commit()
print('\tChanges committed to database.')
session.close()
print("All Done.")
def main():
print("Reading carnegie_classes data")
labels = get_sheet('Labels')
labels.columns = labels.columns.str.strip().str.lower()
labels = labels.rename(
columns={
'variable': 'classification_id',
'label': 'classification',
'value': 'class_code',
'label.1': 'carnegie_class'
})
labels = labels[labels.class_code.notnull()]
labels = labels.ffill()
vals = get_sheet('Data')
# reshape from wide to long format
carnegie = pd.melt(vals,
id_vars=['UNITID'],
var_name='classification_id',
value_vars=[
'BASIC2005',
'BASIC2010',
'BASIC2015',
'BASIC2018',
'IPUG2018',
'IPGRAD2018',
'ENRPROFILE2018',
'UGPROFILE2018',
'SIZESET2018',
'CCE2015',
],
value_name='class_code')
carnegie = carnegie.rename(columns={'UNITID': 'unitid'})
carnegie = carnegie.merge(labels,
on=['class_code', 'classification_id'],
how='inner')
session = Session()
try:
print('Populating dimension tables.')
# insert state data
record_deletes = session.query(CarnegieClass).delete(
synchronize_session=False)
df_inserts = session.bulk_insert_mappings(
mapper=CarnegieClass, mappings=carnegie.to_dict(orient='records')),
print('\tFinished populating carnegie_classes table.')
except Exception as e:
session.rollback()
print(
"\tAn error occurred and no data were changed in the database.\n\nError:\n{}"
.format(str(e)))
print("\tNo changed made to database.\n")
else:
session.commit()
print('\tChanges committed to database.\n')
session.close()
print("All Done.")
def get_domain_byid_api(id, id_domain) -> str:
session = Session()
service = get_service(session, id)
domain = get_domain(session, id_domain)
if (service is not None) and (domain is not None):
if request.method == 'GET':
response = get_json_domain(domain)
session.close()
return response
#Reserve one of the available elements from domain
elif request.method == 'POST':
body = request.json
elems = get_domain_aval_elements(session, domain=domain)
if len(elems)!= 0 and\
set(["name", "information", "client"]).issubset(set(body.keys())):
#Get Client
client = get_client(session, body["client"])
if client is None:
session.close()
return "ERROR CLIENT"
#Get Element
element = get_element(session, elems[0])
if element is None:
session.close()
return "ERROR ELEMENT"
if isinstance(body["information"], dict):
body["information"] = json.dumps(body["information"])
#Create Reservation
reservation = create_reservation(session, service, client,
element, body["name"],
body["information"], None,
datetime.datetime.now())
if reservation is not None:
reservation.url = "/service/%d/client/%d/reservation/%d" % (
service.id, client.id, reservation.id)
session.commit()
response = reservation.get_dict()
response["price"] = element.price
session.close()
return jsonify(response)
else:
session.close()
return "ALREADY RESERVED"
else:
session.close()
return "ERROR"
#delete domain
delete_domain(session, domain=domain)
session.close()
return "DELETED"
session.close()
return "ERROR"
def reserve_seat():
user_id = request.args.get('usr_id') #Auth id
trip_id = request.args.get('trip_id')
body = request.json
session = Session()
if usr_exists(session, user_id) and\
trip_exists_id(session, trip_id) and\
set(["name", "information", "lat", "lon"]).issubset(set(body.keys())):
user = get_usr_by_idauth(session, user_id)
trip = get_trip(session, trip_id)
if trip.available:
#Make reservation.
url = URL_RESERVATION + str(BOOKING_SERVICE_ID) + "/domain/" + str(trip.id_domain_booking)
res_body = {
"client":user.id_client_booking,
"name":"reservation_from_"+user.name,
"information": "none"
}
r = requests.post(url,json=res_body)
r = r.json()
#Saves reservation Id
res_id = r["id"]
#Create Delayed Payment
pay_url = URL_PAYMENT + "/delayedPayment"
pay_bdy = {
"targetID" : IKER_MAIL,
"amount" : r['price'],
"briefDescription" : "None"
}
r = requests.post(pay_url, json=pay_bdy)
pay_response = r.json()
url = URL_RESERVATION + str(BOOKING_SERVICE_ID) + "/client/" +\
str(user.id_client_booking) + "/reservation/" + str(res_id)
#Updates reservation and gets final reservation info.
r = requests.put(url,json={"information":pay_response})
res = r.json()
res["token"] = pay_response["ttoken"]
#Analyses available elements and updates number of elements avaiable
r = requests.get(URL_RESERVATION + str(BOOKING_SERVICE_ID) + "/domain/" +\
str(trip.id_domain_booking) + "/get_aval_elems")
if len(r.json()) == 0:
trip.available = False
session.commit()
#Add sub-trip.
event = get_event(session, trip.id_event)
subtrip_bdy = {
"StartCoords" : [body["lat"], body["lon"]],
"EndCoords" : [event.lat, event.lon],
"TripId" : trip.id_iptf
}
r = requests.post(URL_TRIP_FOLLOWER + "add_subtrip",
json=subtrip_bdy)
if r.status_code != 200:
return "ERROR" #TODO
session.close()
return jsonify(res)
session.close()
return "TRIP UNAVAILABLE"
session.close()
return "ERROR"
def main():
for year in np.arange(args.first, args.last + 1):
try:
spec = f'data/nsf_{year}.pickle'
print(f'Reading data for fiscal year ending {year}...',
end='',
flush=True)
with open(spec, 'rb') as f:
herd = pickle.load(f)
except Exception as e:
print(f'ERROR.\nFile not downloaded properly.\n\n{str(e)}\n')
else:
print('DONE.')
# herd.info()
# set date key
date_key = f'{year}-06-30'
# modify data frame to apply needed fixes
herd['date_key'] = date_key
herd['med_sch_flag'] = herd.med_sch_flag.isin(
['T', 'TRUE', 'True', 'true', 't', 'Y', 'Yes', '1'])
herd['toi_code'] = herd.toi_code == 1
toc = {1: 'Public', 2: 'Private'}
herd['toc_code'] = item_recode(herd['toc_code'], toc, 'Unknown')
herd['inst_state_code'] = item_recode(herd['inst_state_code'],
state_fips, 0)
# convert id's and add missing values
herd.ncses_inst_id = herd.ncses_inst_id.fillna('XXXXXXXX')
# get unitid fixes
with open('data/inst_id_fixes.pickle', 'rb') as f:
fixes = pickle.load(f)
# apply fixes to unitid column, fill missing, and convert to integer
herd['unitid'] = herd.inst_id.map(fixes)
herd.unitid = np.where(herd.unitid.isna(), herd.ipeds_unitid,
herd.unitid)
herd.unitid = herd.unitid.fillna(-1).astype(int)
# select questionnaire_no's for institutional aggregate values
herd = herd[herd.questionnaire_no.isin([
'01.a', '01.b', '01.c', '01.d', '01.e', '01.f', '01.g', '04',
'NA_01', '15'
])]
# data are reported in thousands of dollars - make explicit
herd['data'] = np.where(herd.questionnaire_no == '15', herd.data,
herd.data.fillna(0) * 1000)
# add labels for personnel variables
herd.loc[(herd['questionnaire_no'] == '15') &
(herd['row'] == 'Principal investigators'),
'questionnaire_no'] = 'principal_investigators'
herd.loc[(herd['questionnaire_no'] == '15') &
(herd['row'] == 'Other personnel'),
'questionnaire_no'] = 'other_personnel'
herd.loc[(herd['questionnaire_no'] == '15') & (herd['row'] == 'Total'),
'questionnaire_no'] = 'research_personnel'
keepers = [
'inst_id', 'date_key', 'unitid', 'ncses_inst_id', 'inst_name_long',
'inst_state_code', 'toc_code', 'toi_code', 'med_sch_flag',
'questionnaire_no', 'data'
]
# long to wide
institutions = herd.pivot_table(index=[
'inst_id', 'date_key', 'unitid', 'ncses_inst_id', 'inst_name_long',
'inst_state_code', 'toc_code', 'toi_code', 'med_sch_flag'
],
columns='questionnaire_no',
values='data',
aggfunc=np.sum,
fill_value=0).reset_index()
# rename columns
institutions = institutions.rename(
columns={
'inst_name_long': 'institution_name',
'inst_state_code': 'state_fips',
'toc_code': 'control',
'toi_code': 'academic_institution',
'med_sch_flag': 'medical_school_flag',
'01.a': 'federal_government',
'01.b': 'state_and_local_government',
'01.c': 'business',
'01.d': 'nonprofit_organizations',
'01.e': 'institutional_funds',
'01.f': 'other_sources',
'01.g': 'total_rd_expenses',
'04': 'medical_school_expenses',
'NA_01': 'arra_funds'
})
# replace NaN with database-compliant nulls
institutions['research_personnel'] = np.where(
institutions.research_personnel == 0, None,
institutions.research_personnel)
# insert data into dbo.survey_records
session = Session()
try:
print(
f'Attempting to insert {institutions.shape[0]:,} rows for {year} into {NsfHerdInstitution.__tablename__}.'
)
record_deletes = session.query(NsfHerdInstitution).filter(
NsfHerdInstitution.date_key == date_key).delete(
synchronize_session=False)
session.bulk_insert_mappings(
mapper=NsfHerdInstitution,
mappings=institutions.to_dict(orient='records'),
render_nulls=True)
except Exception as e:
session.rollback()
print(str(e))
print('No data were altered due to error.\n')
else:
session.commit()
print(f'\t{record_deletes:,} old records were deleted.')
print(f'\t{institutions.shape[0]:,} new records were inserted.\n')
finally:
session.close()
session = None
print('All done!')
