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..

What are your column names in the table, and are you explicitly defining your column names in your insert statement? — Silas Ray
– Silas Ray, Commented Nov 6, 2012 at 21:33
yes, explicitly defining, im thinking of doing something like this : — Matt
– Matt, Commented Nov 6, 2012 at 21:48
for each tuple in my list set a NaN value to =MySQLdb.FIELD_TYPE.NULL — Matt
– Matt, Commented Nov 6, 2012 at 21:48

Oscar Pérez · Accepted Answer · 2014-06-03 07:28:08Z

3

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.

answered Jun 3, 2014 at 7:28

Oscar Pérez

4,3971 gold badge20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1