def add_new_accident_file(data):
for ix, row in data.iterrows():
# Insert a Person in the person table
new_accident = Accident(
report_number=row.REPORT_NUMBER,
report_seq_num=row.REPORT_SEQ_NO,
dot_number=row.DOT_NUMBER,
accident_date=row.REPORT_DATE,
state=row.REPORT_STATE,
fatalities=row.FATALITIES,
injuries=row.INJURIES,
tow_away=row.TOW_AWAY,
hazmat=row.HAZMAT_RELEASED,
trafficway_description=row.TRAFFICWAY_DESC,
access_control=row.ACCESS_CONTROL_DESC,
road_surface_cond=row.ROAD_SURFACE_CONDITION_DESC,
weather=row.WEATHER_CONDITION_DESC,
light_cond=row.LIGHT_CONDITION_DESC,
vin=row.VEHICLE_ID_NUMBER,
license_num=row.VEHICLE_LICENSE_NUMBER,
license_state=row.VEHICLE_LICENSE_STATE,
severity_weight=row.SEVERITY_WEIGHT,
time_weight=row.TIME_WEIGHT,
citation_issued=row.CITATION_ISSUED_DESC,
sequence_num=row.SEQ_NUM,
)
session.add(new_accident)
session.commit()
def add_vin(vin):
# check if we already have the vin
exists = session.query(Vehicle.vin).filter_by(vin=vin).scalar() is not None
if exists:
return
else:
# if not retrieve the vin from the vpic
vin_data = pyvpic.Vehicle(vin)
vin_data.decode()
if vin_data.message == 'Results returned successfully':
# new vehicle object
new_vehicle = Vehicle(
vin = vin,
make = vin_data.listing['Make'],
model = vin_data.listing['Model'],
year = vin_data.listing['Model Year'],
plant_city = vin_data.listing['Plant City'],
plant_state = vin_data.listing['Plant State'],
plant_country = vin_data.listing['Plant Country'],
engine = vin_data.listing['Engine Manufacturer'],
body_class = vin_data.listing['Body Class'],
brake_system = vin_data.listing['Brake System Type'],
drive_type = vin_data.listing['Drive Type'],
notes = ' ',
manufacturer= vin_data.listing['Manufacturer Id']
)
session.add(new_vehicle)
session.commit()
'/home/lxpollara/mcmis crash_data/crash_carrier_cols.csv')
file_loc = "/home/lxpollara/mcmis crash_data/CrashCarrier_01012017_11082017.txt"
data = pd.read_csv(file_loc, sep='\t', header=None)
data.columns = columns.Field.values
length = str(len(data))
data.fillna('N', inplace=True)
for ix, row in data.iterrows():
session.add(
AccidentCarrier(crash_carrier_id=row['CRASH_CARRIER_ID '],
crash_id=row['CRASH_ID '],
carrier_name_source=row['CARRIER_SOURCE_CODE '],
carrier_name=row['CRASH_CARRIER_NAME '],
carrier_street=row['CRASH_CARRIER_STREET '],
carrier_city=row['CRASH_CARRIER_CITY '],
carrier_city_code=row['CRASH_CARRIER_CITY_CODE '],
carrier_state=row['CRASH_CARRIER_STATE '],
carrier_zip=row['CRASH_CARRIER_ZIP_CODE '],
colonia=row['CRASH_COLONIA '],
prefix=row['PREFIX '],
docket_num=row['DOCKET_NUMBER '],
interstate=row['CRASH_CARRIER_INTERSTATE '],
no_id_flag=row['NO_ID_FLAG '],
state_num=row['STATE_NUMBER '],
state_issuing_num=row['STATE_ISSUING_NUMBER ']))
if ix % 1000 == 0:
session.commit()
print str(ix + 1) + ' out of ' + length
print str(ix + 1) + ' out of ' + length
session.commit()
session.add(
AccidentMaster(
crash_id=row['CRASH_ID '],
report_state=row['REPORT_STATE '],
report_num=row['REPORT_NUMBER '],
report_date=row['REPORT_DATE '],
report_time=row['REPORT_TIME '],
report_sequence_num=row['REPORT_SEQ_NO '],
dot_num=row['DOT_NUMBER '],
status_code=row['CI_STATUS_CD '],
final_status_date=row['FINAL_STATUS_DATE '],
location=row['LOCATION '],
city_code=row['CITY_CODE '],
city=row['CITY '],
state=row['STATE '],
county_code=row['COUNTY_CODE '],
truck_bus_ind=row['TRUCK_BUS_IND '],
trafficway_id=row['TRAFFICWAY_ID '],
road_access=row['ACCESS_CONTROL_ID '],
road_surface=row['ROAD_SURFACE_CONDITION_ID '],
cargo_body_type=row['CARGO_BODY_TYPE_ID '],
gross_vehicle_weight_range=row['GVW_RATING_ID '],
vin=row['VEHICLE_IDENTIFICATION_NUMBER '],
license_num=row['VEHICLE_LICENSE_NUMBER '],
license_state=row['VEHICLE_LIC_STATE '],
hazmat=row['VEHICLE_HAZMAT_PLACARD '],
weather_cond=row['WEATHER_CONDITION_ID '],
vehicle_config=row['VEHICLE_CONFIGURATION_ID '],
light_cond=row['LIGHT_CONDITION_ID '],
hazmat_release=row['HAZMAT_RELEASED '],
reporting_agency=row['AGENCY '],
officer_badge_num=row['OFFICER_BADGE '],
vehicles_involved=row['VEHICLES_IN_ACCIDENT '],
fatalities=row['FATALITIES '],
injuries=row['INJURIES '],
tow_away=row['TOW_AWAY '],
federally_recordable=row['FEDERAL_RECORDABLE '],
state_recordable=row['STATE_RECORDABLE '],
safetynet_software_version=row['SNET_VERSION_NUMBER '],
safetynet_sequence_id=row['SNET_SEQUENCE_ID '],
transaction_code=row['TRANSACTION_CODE '],
transaction_date=row['TRANSACTION_DATE '],
upload_byte=row['UPLOAD_FIRST_BYTE '],
upload_dot_num=row['UPLOAD_DOT_NUMBER '],
upload_search_indicator=row['UPLOAD_SEARCH_INDICATOR '],
upload_date=row['UPLOAD_DATE '],
census_search_date=row['CENSUS_SEARCH_DATE '],
add_date=row['ADD_DATE '],
change_date=row['CHANGE_DATE '],
changed_by_user=row['CHANGE_BY_USER '],
changed_by_application=row['CHANGE_BY_APPL '],
safetynet_input_date=row['SNET_INPUT_DATE '],
))
from sql_alchemy_engine import session
from create_db import Accident
import pandas as pd
columns = pd.read_csv('/home/lxpollara/mcmis crash_data/crash_event_cols.csv')
file_loc = "/home/lxpollara/mcmis crash_data/CrashEvent_01012017_11082017.txt"
data = pd.read_csv(file_loc, sep='\t', header=None)
data.columns = columns.Field.values
length = str(len(data))
data.fillna('None', inplace=True)
for ix, row in data.iterrows():
session.add(Accident(
crash_event_id = row['CRASH_EVENT_ID '],
crash_id = row['CRASH_ID '],
sequence_num = row['SEQ_NO '],
event_id = row['EVENT_ID '],
event_other_desc = row['EVENT_OTHER_DESC '],
))
if ix%1000 == 0:
session.commit()
print str (ix + 1) + ' out of ' + length
print str(ix+1) + ' out of ' + length
session.commit()
vins_clean = set(vins_clean) - set(
[v[0] for v in session.query(Vehicle.vin).distinct() if v[0] is not None])
n = 0
for v in vins_clean:
try:
vin_data = pyvpic.Vehicle(v)
vin_data.decode()
except ValueError:
pass
if vin_data.message == 'Results returned successfully':
# new vehicle object
new_vehicle = Vehicle(
vin=v,
make=vin_data.listing['Make'],
model=vin_data.listing['Model'],
year=vin_data.listing['Model Year'],
plant_city=vin_data.listing['Plant City'],
plant_state=vin_data.listing['Plant State'],
plant_country=vin_data.listing['Plant Country'],
engine=vin_data.listing['Engine Manufacturer'],
body_class=vin_data.listing['Body Class'],
brake_system=vin_data.listing['Brake System Type'],
drive_type=vin_data.listing['Drive Type'],
notes=' ',
manufacturer=vin_data.listing['Manufacturer Id'])
session.add(new_vehicle)
n += 1
session.commit()