Malformed date content causes Pandas write to SQL to fail

[andrewsage]

If the date column in the source spreadsheet does not contain a valid date then Pandas to_sql is crashing.

https://github.com/CodeTheCity/historical_harbour_arrivals_api/blob/ed1ce9953da432293191f0d84923eba46fe26cd9/create_database.py#L95

Here is the traceback:

Traceback (most recent call last):
  File "./create_database.py", line 95, in <module>
    df.to_sql('arrivals', con, if_exists='replace', index = False)
  File "/usr/local/lib/python3.7/dist-packages/pandas/core/generic.py", line 2663, in to_sql
    method=method,
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py", line 521, in to_sql
    method=method,
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py", line 1734, in to_sql
    table.insert(chunksize, method)
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py", line 755, in insert
    exec_insert(conn, keys, chunk_iter)
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py", line 1464, in _execute_insert
    conn.executemany(self.insert_statement(), data_list)
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Something needs to be written that cleans these rouge values out before import.

[andrewsage]

This is still an active issue despite several rewrites and attempts to pre-process the imported data.

The import process is failing before the remapped cargo and activity columns are created and this is causing rendering errors on the website.