Exemplo n.º 1
0
def parse_to_db(db, filename):
    """Reads a csv and imports the data into a database."""
    # The indices in the csv of different data
    vehicle_id_index = 8
    license_plate_index = 9
    location_index = 4
    date_index = 0
    agency_index = 6
    picture_index = 13
    description_index = 11

    validator_form = IncidentReportForm()

    with open(filename, 'rb') as csv_file:
        reader = csv.reader(csv_file)
        columns = reader.next()

        for i, row in enumerate(reader, start=2):  # i is the row number

            address_text = row[location_index]
            coords = geocode(address_text)

            # Ignore rows that do not have correct geocoding
            if coords[0] is None or coords[1] is None:
                print_error(i, 'Failed to geocode "{:s}"'.format(address_text))

            # Insert correctly geocoded row to database
            else:
                loc = Location(
                    latitude=coords[0],
                    longitude=coords[1],
                    original_user_text=address_text)
                db.session.add(loc)

                time1, time2 = parse_start_end_time(date_index, row)

                # Assign correct agency id
                agency_name = row[agency_index].rstrip()
                if agency_name.upper() == 'OTHER':
                    agency_name = row[agency_index + 1].rstrip()
                agency = Agency.get_agency_by_name(agency_name)

                # Create new agency object if not in database
                if agency is None:
                    agency = Agency(name=agency_name)
                    agency.is_public = True
                    agency.is_official = False
                    db.session.add(agency)
                    db.session.commit()

                vehicle_id_text = row[vehicle_id_index].strip()
                license_plate_text = row[license_plate_index].strip()

                # If the license plate is too short, just ignore it
                if len(strip_non_alphanumeric_chars(license_plate_text)) < 3:
                    license_plate_text = ''

                # Validate all the fields
                validate_field = functools.partial(
                    validate_field_partial,
                    form=validator_form,
                    row_number=i
                )

                errors = 0

                if not validate_field(
                    field=validator_form.vehicle_id,
                    data=vehicle_id_text
                ):
                    errors += 1

                if not validate_field(
                    field=validator_form.description,
                    data=row[description_index]
                ):
                    errors += 1

                if not validate_field(
                    field=validator_form.picture_url,
                    data=row[picture_index]
                ):
                    errors += 1

                if errors == 0:
                    vehicle_id_text = strip_non_alphanumeric_chars(
                        vehicle_id_text)
                    license_plate_text = strip_non_alphanumeric_chars(
                        license_plate_text)

                    incident = IncidentReport(
                        vehicle_id=vehicle_id_text if len(vehicle_id_text) > 0
                        else None,
                        license_plate=license_plate_text if
                        len(license_plate_text) > 0 else None,
                        location=loc,
                        date=time1,
                        duration=time2 - time1,
                        agency=agency,
                        picture_url=row[picture_index],
                        description=row[description_index],
                        send_email_upon_creation=False,
                    )
                    db.session.add(incident)

        db.session.commit()
        return columns
 def __call__(self, form, field):
     stripped = strip_non_alphanumeric_chars(field.data)
     if not (self.min_length <= len(stripped) <= self.max_length):
         raise ValidationError(self.message)
Exemplo n.º 3
0
def parse_to_db(db, filename):
    """Reads a csv and imports the data into a database."""
    # The indices in the csv of different data

    date_index = 0
    location_index = 1
    description_index = 6
    injuries_index = 7
    pedestrian_num_index = 2
    bicycle_num_index = 4
    automobile_num_index = 3
    other_num_index = 5
    picture_index = 8
    contact_name_index = 9
    contact_phone_index = 10
    contact_email_index = 11

    validator_form = IncidentReportForm()

    with open(filename, 'r') as csv_file:
        reader = csv.reader(csv_file)
        # columns = reader.next()

        for i, row in enumerate(reader, start=2):  # i is the row number

            address_text = row[location_index]
            coords = geocode(address_text)

            # Ignore rows that do not have correct geocoding
            if coords[0] is None or coords[1] is None:
                print_error(i, 'Failed to geocode "{:s}"'.format(address_text))

            # Insert correctly geocoded row to database
            else:
                loc = IncidentLocation(latitude=coords[0],
                                       longitude=coords[1],
                                       original_user_text=address_text)
                db.session.add(loc)

                time1, time2 = parse_start_end_time(date_index, row)

                pedestrian_num_text = row[pedestrian_num_index].strip()
                bicycle_num_text = row[bicycle_num_index].strip()
                automobile_num_text = row[automobile_num_index].strip()
                other_num_text = row[other_num_index].strip()

                contact_name_text = row[contact_name_index].strip()
                contact_phone_text = row[contact_phone_index].strip()
                contact_email_text = row[contact_email_index].strip()

                # Validate all the fields
                validate_field = functools.partial(validate_field_partial,
                                                   form=validator_form,
                                                   row_number=i)

                errors = 0

                if not validate_field(field=validator_form.description,
                                      data=row[description_index]):
                    errors += 1

                if not validate_field(field=validator_form.picture_url,
                                      data=row[picture_index]):
                    errors += 1

                if errors == 0:
                    pedestrian_num_text = strip_non_alphanumeric_chars(
                        pedestrian_num_text)
                    bicycle_num_text = strip_non_alphanumeric_chars(
                        bicycle_num_index)
                    automobile_num_text = strip_non_alphanumeric_chars(
                        automobile_num_index)
                    other_num_text = strip_non_alphanumeric_chars(
                        other_num_index)

                    contact_name_text = strip_non_alphanumeric_chars(
                        contact_name_index)
                    contact_phone_text = strip_non_alphanumeric_chars(
                        contact_phone_index)
                    contact_email_text = strip_non_alphanumeric_chars(
                        contact_email_index)

                    incident = Incident(
                        date=time1,
                        pedestrian_num=int(pedestrian_num_text)
                        if len(pedestrian_num_text) > 0 else 0,
                        bicycle_num=int(bicycle_num_text)
                        if len(bicycle_num_text) > 0 else 0,
                        automobile_num=int(automobile_num_text)
                        if len(automobile_num_text) > 0 else 0,
                        other_num=int(other_num_text)
                        if len(other_num_text) > 0 else 0,
                        description=row[description_index],
                        injuries=row[injuries_index],
                        picture_url=row[picture_index],
                        contact_name=contact_name_text
                        if len(contact_name_text) > 0 else None,
                        contact_phone=int(contact_phone_text)
                        if len(contact_phone_text) > 0 else None,
                        contact_email=contact_email_text
                        if len(contact_email_text) > 0 else None,
                    )
                    db.session.add(incident)

        db.session.commit()
        return columns
 def __call__(self, form, field):
     stripped = strip_non_alphanumeric_chars(field.data)
     if not (self.min_length <= len(stripped) <= self.max_length):
         raise ValidationError(self.message)