db support -- MS Sql Server 2017: multi-byte (utf-8) issues with Netherlands GTFS route names on Python 2.7

[BTollison]

gtfs feed: http://gtfs.ovapi.nl/new/gtfs-nl.zip

using ms sql server 2017

C:\WINDOWS\system32> gtfsdb-load --database_url mssql+pyodbc://USER:PASS@gtfs_nl http://gtfs.ovapi.nl/new/gtfs-nl.zip
23:19:35,325 DEBUG [gtfsdb.model.gtfs] GTFS.load: http://gtfs.ovapi.nl/new/gtfs-nl.zip
23:19:42,966 DEBUG [gtfsdb.model.route] RouteType.load (0 seconds)
23:19:42,976 DEBUG [gtfsdb.model.stop_feature] StopFeatureType.load (0 seconds)
23:19:42,980 DEBUG [gtfsdb.model.feed_info] FeedInfo.load (0 seconds)
23:19:43,009 DEBUG [gtfsdb.model.agency] Agency.load (0 seconds)
23:19:43,009 DEBUG [gtfsdb.model.calendar] Calendar.load (0 seconds)
******************23:19:58,115 DEBUG [gtfsdb.model.calendar] CalendarDate.load (15 seconds)
Traceback (most recent call last):
File "c:\python27\lib\runpy.py", line 174, in _run_module_as_main
"main", fname, loader, pkg_name)
File "c:\python27\lib\runpy.py", line 72, in run_code
exec code in run_globals
File "C:\Python27\Scripts\gtfsdb-load.exe_main.py", line 9, in
File "c:\python27\lib\site-packages\gtfsdb\scripts.py", line 33, in gtfsdb_load
database_load(args.file, **kwargs)
File "c:\python27\lib\site-packages\gtfsdb\api.py", line 21, in database_load
gtfs.load(db, **kwargs)
File "c:\python27\lib\site-packages\gtfsdb\model\gtfs.py", line 34, in load
cls.load(db, **load_kwargs)
File "c:\python27\lib\site-packages\gtfsdb\model\base.py", line 83, in load
db.engine.execute(table.insert(), records)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2064, in execute
return connection.execute(statement, *multiparams, **params)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 945, in execute
return meth(self, multiparams, params)
File "c:\python27\lib\site-packages\sqlalchemy\sql\elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1189, in _execute_context
context)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1405, in _handle_dbapi_exception
util.reraise(*exc_info)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1159, in _execute_context
context)
File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line 467, in do_executemany
cursor.executemany(statement, parameters)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 1: ordinal not in range(128)

[fpurcell]

GTFS for The Netherlands loads for me with the Postgres / Postgis database:
bin/gtfsdb-load --database_url postgresql://postgres@localhost:5432 --is_geospatial http://gtfs.ovapi.nl/new/gtfs-nl.zip

Not sure what's needed to support the MS database, but willing to accept code fix (assuming they don't break postgis) via a pull request.

Since this looks like a UTF-8 problem, you might also try gtfsdb Python 3.x (v3 supposedly solves a lot of these issues). Running gtfsdb with python 3.x might open another can of worms currently (e.g., there's a 3.x upgrade pull-request sitting in the project now; not sure it works, since it was unaccepted due to formatting issues).

[fpurcell]

I've got a TODO item to add some route info involving multi-byte names from gtfs-nl to gtfsdb's test data, since the actual gtfs-nl.zip is a bit big for testing.