def natural_gas_data_to_db(filepath):
'''
Sends natural_gas data to a database table named elec_usage.
Parameters
----------
filepath: (string) filepath for file containing natural_gas utility data.
Returns
-------
None
'''
assert type(filepath) == str, 'Please provide a file path as a string.'
engine = create_mysql_engine(CREDS)
ngas_data = preprocess_natural_gas(filepath)
data_to_db(ngas_data, 'ngas_usage', engine)
def electricity_data_to_db(filepath):
'''
Sends electricity data to a database table named elec_usage.
Parameters
----------
filepath: (string) filepath for file containing electricity utility data.
Returns
-------
None
'''
assert type(filepath) == str, 'Please provide a file path as a string.'
engine = create_mysql_engine(CREDS)
elec_data = preprocess_electricity(filepath)
data_to_db(elec_data, 'elec_usage', engine)
def elec_accounts_to_db(filepath):
'''
Sends electricity accounts data to database table named elec_accounts.
Parameters
----------
filepath: (string) filepath for file containing electricity accounts data.
Returns
-------
None
'''
assert type(filepath) == str, 'Please provide a file path as a string.'
engine = create_mysql_engine(CREDS)
elec_accounts = read_data(filepath, 'other')
elec_accounts = elec_accounts.drop_duplicates()
elec_accounts['account_number'] = elec_accounts['account_number'].apply(
lambda x: restore_leading_zeros(x, 10))
data_to_db(elec_accounts, 'elec_accounts', engine)
def ngas_accounts_to_db(filepath):
'''
Sends natural gas accounts data to database table named ngas_accounts.
Parameters
----------
filepath: (string) filepath for file containing natural gas account data.
Returns
-------
None
'''
assert type(filepath) == str, 'Please provide a file path as a string.'
engine = create_mysql_engine(CREDS)
ngas_accounts = read_data(filepath, 'gas_accounts')
ngas_accounts = ngas_accounts.drop_duplicates()
ngas_accounts['account_number'] = ngas_accounts['account_number'].apply(
lambda x: restore_leading_zeros(x, 13))
ngas_accounts['ert_number'] = ngas_accounts['ert_number'].apply(
lambda x: restore_leading_zeros(x, 9))
data_to_db(ngas_accounts, 'ngas_accounts', engine)
def test_data_sends_to_database():
engine = create_mysql_engine('./tests/test_creds.yml')
df = pd.DataFrame([['a', 'b'], [1, 2]], columns=['first', 'second'])
data_to_db(df, 'sample', engine)
result = pd.read_sql(sql='SELECT * FROM sample;', con=engine)
assert list(result.columns) == ['first', 'second']
def buildings_data_to_db(filepath):
assert type(filepath) == str, 'Please provide a file path as a string.'
engine = create_mysql_engine(CREDS)
buildings = read_data(filepath, 'buildings')
buildings = buildings.replace('NV', math.nan)
data_to_db(buildings, 'buildings', engine)
def test_send_elec_data_to_database():
engine = create_mysql_engine('./tests/test_creds.yml')
data = preprocess_electricity(
'/home/vidal/Projects/cityofchicago/2FM/data/energy/energy.xlsx')
data_to_db(data, 'elec_usage', engine)