def get_company_names():
"""
return company names
"""
session, connection = connect_universe()
companies_query = session.query(Company.company_name).statement
companies = pd.read_sql(companies_query, connection)
connection.close()
return list(companies['company_name'])
def query_company():
"""
returns the company table from universe db
"""
session, connection = connect_universe()
companies_query = session.query(Company).statement
companies = pd.read_sql(companies_query, connection)
connection.close()
return companies
def get_company_ids():
"""
returns a list of insurance company ids
"""
session, connection = connect_universe()
companies_query = session.query(Company.company_id).statement
companies = pd.read_sql(companies_query, connection)
connection.close()
return list(companies['company_id'])
def query_population():
"""
return the person table from universe db
"""
session, connection = connect_universe()
query = session.query(PersonTable).statement
population = pd.read_sql(query, connection)
connection.close()
return population
def query_banks():
"""
returns the bank table from universe db
"""
session, connection = connect_universe()
banks_query = session.query(BankTable).statement
banks = pd.read_sql(banks_query, connection)
connection.close()
return banks
def query_bank_id(bank_name):
"""
takes a bank name and returns the id of that bank
"""
session, connection = connect_universe()
id_query = session.query(
BankTable.bank_id).filter(BankTable.bank_name == bank_name).statement
bank_id = pd.read_sql(id_query, connection).iat[0, 0]
connection.close()
return bank_id
def query_person(person_id):
"""
returns the person table filtered on one person in universe db
"""
session, connection = connect_universe()
person_query = session.query(PersonTable).filter(
PersonTable.person_id == person_id).statement
person = pd.read_sql(person_query, connection)
connection.close()
return person
def __register(self):
# populate universe company record
insurer_table = pd.DataFrame([[self.name]], columns=['bank_name'])
session, connection = connect_universe()
insurer_table.to_sql('bank',
connection,
index=False,
if_exists='append')
bank_id = query_bank_id(self.name)
return bank_id
def query_events_by_report_date(report_date):
session, connection = connect_universe()
event_query = session.query(Event). \
filter(Event.report_date == report_date). \
statement
events = pd.read_sql(event_query, connection)
connection.close()
return events
def query_incomes(person_ids):
"""
takes a list of person ids and returns the incomes for each person
"""
session, connection = connect_universe()
income_query = session.query(PersonTable.person_id,
PersonTable.income).statement
incomes = pd.read_sql(income_query, connection)
incomes = incomes[incomes['person_id'].isin(person_ids)]
connection.close()
return incomes
