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!')