Loading table to mysql with csvsql fails

[yaron-idan]

I'm trying to load a table to mysql using the following command -
csvsql -e "utf8" --db mysql://root:root@localhost/transport?charset=utf8 --insert /tmp/routes.txt
The command fails with the following error -

(_mysql_exceptions.OperationalError) (1059, "Identifier name 'route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_color' is too long") [SQL: u'\nCREATE TABLE routes (\n\t`route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_color` VARCHAR(76) NOT NULL\n)\n\n']

This seems strange to me as I'm loading a few other tables using the same method and all of them upload successfully, even though the column identifier of those tables is longer.

This is the header line of the failed file I'm trying to upload -
route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_color

And this is an example of a longer line that uploads just fine -
agency_id,agency_name,agency_url,agency_timezone,agency_lang,agency_phone,agency_fare_url

[jpmckinney]

My guess is that it's not inferring a comma as the field separator. What if you run the command with csvsql --no-inference ...?

[yaron-idan]

I tried it... seemed to make no difference. I can upload the files in question and the commands I'm using it helps.

[jpmckinney]

Can you try with the latest csvkit?

pip install --upgrade -e git+git://github.com/onyxfish/csvkit.git@master#egg=csvkit

[yaron-idan]

The installation fails with the following error - Python2/_regex.c:46:20: fatal error: Python.h: No such file or directory (and an enormous error stack surrounding it)

[jpmckinney]

What version of Python are you running? python --version

[yaron-idan]

2.7.3. After upgrading to 2.7.12 the installation was successful but running the same command resulted in the same error.

[jpmckinney]

Can you upload the file (or a sample) and the commands?

[yaron-idan]

Sure.
This is the command I'm running - csvsql -e "utf8" --db mysql://root:root@localhost/transport?charset=utf8 --insert /tmp/routes.txt

And this is a sample of the file causing the error (It's only the first few rows, but the same error is raised using it too) -

route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_color
12517,2,,באר שבע-צפון-באר שבע<->דימונה-דימונה,23042-1-0,2,
12518,2,,דימונה-דימונה<->באר שבע-צפון-באר שבע,23042-2-0,2,
13869,2,,חיפה מרכז-חיפה<->רחובות-רחובות,24034-1-2,2,
13870,2,,חיפה מרכז-חיפה<->רחובות-רחובות,24034-1-3,2,
13871,2,,רחובות-רחובות<->חיפה מרכז-חיפה,24034-2-3,2,
13872,2,,רחובות-רחובות<->חיפה מרכז-חיפה,24034-2-4,2,
12478,2,,תל אביב מרכז-תל אביב יפו<->מודיעין מרכז-מודיעין מכבים רעות,26025-1-0,2,
12479,2,,מודיעין מרכז-מודיעין מכבים רעות<->תל אביב מרכז-תל אביב יפו,26025-2-0,2,

The file contains hebrew characters, but other files containing chars like this did not cause the error, so I don't think this the cause of the error.

[jpmckinney]

Hmm, I don't get an error in either Python 2 or 3.

[yaron-idan]

That's mighty strange. I'm getting the error on a vagrant machine, maybe I'll send you the vagrantfile? That's almost certain to reproduce the error.

[jpmckinney]

That might work - I haven't used Vagrant much.

[jpmckinney]

Closing as can't reproduce, though if a Vagrantfile (or other steps to reproduce) are provided, I can re-open.