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.