error when trying to insert values into MySQL table with python

Question

I'm trying to insert this row of data (i parse from a list of tuples)

(0L, u'2012-11-06T16:23:36-05:00', 0L, None, 23759918L, u'baseline', u'0 to 100', nan, 105114L, 2009524L, True, u'charge', u'Charge')

into a mySQL database table, but i seem to have problems with the null value (NaN), because i receive this error:

OperationalError: (1054, "Unknown column 'nan' in 'field list'")

I've tried making columns nullable in mySQL, and also making all text fields, but still no dice..

• pydsigner: Do something else with the Not a Number float.
• Silas Ray: What are your column names in the table, and are you explicitly defining your column names in your insert statement?
• Matt: yes, explicitly defining, im thinking of doing something like this :
• Matt: for each tuple in my list set a NaN value to =MySQLdb.FIELD_TYPE.NULL

Answer 1

The problem is that nan is not a valid value for a MySQL column. It is complaining just about that. Your values string should be:

(0L, u'2012-11-06T16:23:36-05:00', 0L, None, 23759918L, u'baseline', u'0 to 100', null, 105114L, 2009524L, True, u'charge', u'Charge')

To achieve this, add some logic to your script to change all nan values to NULL string.