Experiences on Windows and MySQL

[Bertalan Fodor]

Hi everyone,

I just thought I would let you know that I tried gtfsdb today on
Windows and MySQL and share my experiences.

Here are the issues I've encountered:

1. I've read in an install document to install python 2.5, but gtfsdb
requires at least python 2.6 because of the String.format method.

2. The simplest way to install the MySQL connector is to download a
Windows binary from here: http://www.lfd.uci.edu/~gohlke/pythonlibs/

3. MySQL requires a max length for VARCHAR fields which was missing. I
circumvented this by hard coding this value in SQLAlchemy. For this I
had to edit c:\Program Files (x86)\Python2\Lib\site-packages
\sqlalchemy-0.7.3-py2.7.egg\sqlalchemy\dialects\mysql\base.py and
modify the code around line 1650. This is my new code:

def visit_VARCHAR(self, type_):
#if type_.length:
# return self._extend_string(type_, {}, "VARCHAR(%d)" %
type_.length)
#else:
# raise exc.InvalidRequestError(
# "VARCHAR requires a length on dialect %s" %
# self.dialect.name)
# If length is not specified, let's set it to 255
if not type_.length:
type_.length = 127 # Don't make these fields too big,
it can cause problems when creating the keys
return self._extend_string(type_, {}, "VARCHAR(%d)" %
type_.length)

4. There was a duplicate entry issue in MySQL because of the rows that
had 0 as a value for auto increment fields (MySQL interprets giving 0
for these field as an instruction to automatically set the value),
which I solved by running the following SQL command:

SET GLOBAL sql_mode='NO_AUTO_VALUE_ON_ZERO'


I hope this helps other developers when taking the first step.

Cheers,
Bertalan Fodor

PS: Huge thanks to the developer(s) for making this software! I don't
know if I'm ever going to pursue my plans with GTFS databases, but
converting the data into a relational database helped me a lot to
understand the format.