def fetch_tables():
engine = create_mysql_engine(CREDS)
result = engine.execute(
"SELECT table_name FROM information_schema.tables where table_schema = '{}'"
.format(DB)).fetchall()
tables = [row[0] for row in result]
return tables
def execute_sql_from_files(credentials, files):
engine = create_mysql_engine(credentials)
for file in files:
statements = process_sql_statements(join(CONFIG['ddl_directory'],
file))
for statement in statements:
engine.execute(statement)
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 test_setup():
engine = create_mysql_engine('./tests/test_creds.yml')
engine.execute('CREATE DATABASE IF NOT EXISTS testdb;')
engine.execute('USE testdb')
engine.execute('DROP TABLE IF EXISTS elec_usage;')
engine.execute('''CREATE TABLE IF NOT EXISTS elec_usage (
invoice_id VARCHAR(16) PRIMARY KEY,
statement_number VARCHAR(10),
account_number VARCHAR(10),
bill_month DATE,
acctg_month DATE,
service_period_start DATE,
service_period_stop DATE,
rebill VARCHAR(1),
billed_khw FLOAT,
peak_kw FLOAT,
supply_charges FLOAT,
udc_charges FLOAT,
total_charges FLOAT
);''')
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 test_connection_made_via_mysql_engine():
engine = create_mysql_engine('./tests/test_creds.yml')
assert type(engine.table_names()) is list, "Connection to database failed."
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)