_mysql_exceptions error(1064, "check the manual that corresponds to your MySQL server version for the right syntax to use near 'default) VALUES

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default) VALUES ('5', '0', '0', 'integer', '', '', '1', '', 'Base Parameters', '' at line 1")

What you are seeing is a fragment of an INSERT statement. It isn't showing you the whole INSERT statement, it cuts it off. You said you think it is not reading the '1' in the ROWNUM field of your input data, but you are misinterpreting the error message.

It's just a coincidence that you see two single-quotes next to each other in the error message. The error message is formatted like this:

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...' at line 1")

Where ... will be a fragment of the long SQL statement, starting with the first token that confused the parser, and continuing in this case for 80 characters. This 80-character fragment is:

default) VALUES ('5', '0', '0', 'integer', '', '', '1', '', 'Base Parameters', '

It's purely by accident that the 80th character is a single-quote, and then the next character in the error message is also a single-quote. It is not an empty string in place of the value '1' you expected to be read from the input. In fact, I assume it is reading the data value from the input.

So the problem reported in the error is that you're using the SQL reserved word DEFAULT as a column name. This Python script is not delimiting it. So the appearance of the reserved word in the INSERT statement confuses the parser.

I believe you can fix this in the Python script by formatting the column names inside back-ticks in the INSERT statement:

def get_insert(table, header):    """Generate the SQL for inserting rows    """    field_names = ', '.join('`%s`' % col for col in header)    field_markers = ', '.join('%s' for col in header)    return 'INSERT INTO %s (%s) VALUES (%s);' % \        (table, field_names, field_markers)

You could alternatively edit your input CSV file to avoid using SQL reserved words in the column names defined in the header.

@BillKarwin, When I used Django admin page to see the same table that was loaded in mysql db (after it was modified to take DEFAULT as a field name), it was throwing the "string index out of range" error. I couldn't pinpoint to the exact location where it is throwing the error. Is it because of the len(header) code in the main function?