def bulk_csv_upload(upload_type):
populate_queries = [
'call sp_populate_dim_parcel();', 'call sp_populate_fact_sales();',
'call sp_populate_sales_upload_amount_errors();'
]
truncate = 'TRUNCATE TABLE sales_upload'
table = 'sales_upload'
# Get file from data folder
path = './data'
extension = 'csv'
file = os.path.join(path, '{}.{}'.format(upload_type, extension))
conn = create_connection()
cur = conn.cursor()
cur.execute(truncate)
copy_sql = """
COPY {table} FROM stdin WITH CSV HEADER
DELIMITER as ','
""".format(table=table)
with open(file, 'r') as f:
cur.copy_expert(sql=copy_sql, file=f)
conn.commit()
[cur.execute(query) for query in populate_queries]
conn.commit()
cur.close()
def parcel_site_information_upload(parcel_id, parcel_detail_data, sale_data):
conn = create_connection()
cur = conn.cursor()
#'TRUNCATE TABLE stg_parcel_site_details',
truncate = [
'TRUNCATE TABLE stg_sale_history',
'DELETE FROM stg_parcel_site_details sg WHERE parcel_id in (select parcel_id from dim_parcel where municipality is not null)'
]
[cur.execute(query) for query in truncate]
# Add to parcel details table
insert = insert_parcel_site_details.format(
parcel_id=parcel_id,
site_location=parcel_detail_data['Site Location'].replace("'", "''"),
legal_description=parcel_detail_data['Legal Description'].replace(
"'", "''"),
municipality=parcel_detail_data['Municipality'],
school_district=parcel_detail_data['School District'],
property_type=parcel_detail_data['Property Type'],
)
cur.execute(insert)
# Add to sales table
[
cur.execute("""INSERT INTO stg_sale_history(
parcel_id,
owner,
sale_date,
sale_price
)
VALUES (
'{parcel_id}',
'{owner}',
'{sale_date}',
'{sale_price}'
)
""".format(parcel_id=parcel_id,
owner=entry['owner'].replace("'", "''"),
sale_date=entry['sale_date'],
sale_price=entry['sale_price']))
for entry in sale_data['owner_history']
]
populate_queries = ['call sp_populate_fact_sales_history();']
[cur.execute(query) for query in populate_queries]
conn.commit()
def residential_details_upload(data_dict, exclude):
# Upload to database
conn = create_connection()
cur = conn.cursor()
truncate = 'TRUNCATE TABLE stg_parcel_residential_details'
cur.execute(truncate)
insert = insert_residential_details.format(
parcel_id=data_dict['parcel_id'],
card=data_dict['Card'],
class_input=data_dict['Class'],
grade=data_dict['Grade'],
cdu=data_dict['CDU'],
style=data_dict['Style'],
acres=data_dict['Acres'],
year_built_effective_year=data_dict['Year Built / Effective Year'],
remodeled_year=data_dict['Remodeled Year'],
base_area=data_dict['Base Area'],
finished_bsmt_area=data_dict['Finished Bsmt Area'],
number_of_stories=data_dict['Number of Stories'],
exterior_wall=data_dict['Exterior Wall'],
basement=data_dict['Basement'],
physical_condition=data_dict['Physical Condition'],
heating=data_dict['Heating'],
heat_fuel_type=data_dict['Heating Fuel Type'],
heating_system=data_dict['Heating System'],
attic_code=data_dict['Attic Code'],
fireplaces=data_dict['Fireplaces: 1 Story/2 Story'],
parking=data_dict['Parking'],
total_rooms=data_dict['Total Rooms'],
full_baths=data_dict['Full Baths'],
half_baths=data_dict['Half Baths'],
total_fixtures=data_dict['Total Fixtures'],
additional_fixtures=data_dict['Additional Fixtures'],
bed_rooms=data_dict['Bed Rooms'],
family_room=data_dict['Family Room'],
living_units=data_dict['Living Units'])
cur.execute(insert)
populate_queries = ['call sp_populate_dim_parcel_residential_details();']
[cur.execute(query) for query in populate_queries]
conn.commit()
def get_parcels_without_detail_data(missing_data_query):
"""
Query database and find all parcels without detail entries
Args:
missing_data_query (either residential or parcel details)
"""
query = get_missing_data[missing_data_query]
conn = None
try:
conn = create_connection()
cur = conn.cursor()
cur.execute(query)
records = [r[0] for r in cur.fetchall()]
conn.commit()
cur.close()
return records
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def residential_details_upload(data_dict, exclude):
"""
Upload residential details to database
Args:
data_dict: a dictionary required for upload
exclude: Some entries do not have residential details, this allows us to bypass the scraping
"""
# Not every entry has residential detail data if it does not insert dummy data into the database to stop from searching again
if (not data_dict) or list(data_dict.keys())[0] != exclude:
conn = create_connection()
cur = conn.cursor()
truncate = 'TRUNCATE TABLE stg_parcel_residential_details'
cur.execute(truncate)
insert = """INSERT INTO stg_parcel_residential_details
(parcel_id)
VALUES ('{parcel_id}')""".format(
parcel_id=data_dict['parcel_id'])
cur.execute(insert)
populate_queries = [
'call sp_populate_dim_parcel_residential_details();'
]
[cur.execute(query) for query in populate_queries]
conn.commit()
print('No data available for parcel {parcel_id}'.format(
parcel_id=data_dict['parcel_id']))
else:
try:
conn = create_connection()
cur = conn.cursor()
truncate = 'TRUNCATE TABLE stg_parcel_residential_details'
cur.execute(truncate)
insert = insert_residential_details.format(
parcel_id=data_dict['parcel_id'],
card=data_dict['Card'],
class_input=data_dict['Class'],
grade=data_dict['Grade'],
cdu=data_dict['CDU'],
style=data_dict['Style'],
acres=data_dict['Acres'],
year_built_effective_year=data_dict[
'Year Built / Effective Year'],
remodeled_year=data_dict['Remodeled Year'],
base_area=data_dict['Base Area'],
finished_bsmt_area=data_dict['Finished Bsmt Area'],
number_of_stories=data_dict['Number of Stories'],
exterior_wall=data_dict['Exterior Wall'],
basement=data_dict['Basement'],
physical_condition=data_dict['Physical Condition'],
heating=data_dict['Heating'],
heat_fuel_type=data_dict['Heating Fuel Type'],
heating_system=data_dict['Heating System'],
attic_code=data_dict['Attic Code'],
fireplaces=data_dict['Fireplaces: 1 Story/2 Story'],
parking=data_dict['Parking'],
total_rooms=data_dict['Total Rooms'],
full_baths=data_dict['Full Baths'],
half_baths=data_dict['Half Baths'],
total_fixtures=data_dict['Total Fixtures'],
additional_fixtures=data_dict['Additional Fixtures'],
bed_rooms=data_dict['Bed Rooms'],
family_room=data_dict['Family Room'],
living_units=data_dict['Living Units'])
cur.execute(insert)
populate_queries = [
'call sp_populate_dim_parcel_residential_details();'
]
[cur.execute(query) for query in populate_queries]
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)