varchar mysql errors about pygtfs

[jarondl]

Hi @anthonymobile ,
You are doing nothing wrong. So far I have tested on SQLite and PostgreSQL, and not on MySQL, and that is why this bug was not discovered.
However, this raises a design issue. We use SQLAlchemy's Unicode[1][2]. In SQLite the varchar length is undefined, and in PostgreSQL unnecessary, while MySQL demands that we put a length. I couldn't find any string length constraint in the GTFS reference [3].

We can choose one of the following options.

1. Announce that PyGTFS is not MySQL compatible, and tell users to pick a different database.
2. Pick a length (255 is a popular choice), and apply it all the time. (Should we truncate?)
3. Pick a length but only when the database requires it.

What do you think?

BTW, cool project!
https://github.com/anthonymobile/pi_transitsign

[1] http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Unicode
[2] http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.String
[3] https://developers.google.com/transit/gtfs/reference

[anthonymobile]

thanks... i think for me at least this is not needed. i was looking at mysql because of performance issues on a Raspberry Pi and i refactored my code so that is moot.

however, i was thinking about this a bit. it seems kind of crazy to -have- to load the entire GTFS database into memory. is there a way to select a subset of the full database through a different sqlalchemy query without substantially modifying the pygtfs code?

[jarondl]

I am not sure what you mean by that. You can store your database on disk with SQLite or PostgreSQL, and then the amount of memory used depends on your database settings.
You can also store it in-memory in SQLite, but that does seem crazy for large GTFS datasets.

If you mean you want to put only a subset of the CSV files in your database, then that is way tougher to implement in pygtfs. You can filter the files yourself before putting them in the database.

[jarondl]

To clarify my latest comment: You can use the included gtfs2db script to translate your zip file to sqlite:

gtfs2db append  gtfs.zip  db.sqlite

Then you can use the sqlite database in pygtfs:

sched = pygtfs.Schedule("db.sqlite")

The nice thing here is that sqlite is quite smart about loading only the relevant stuff that you need.
If you are really capped by your memory use, you can always do your full preprocessing somewhere else and store only what you need on your Pi.