Python connecting dataframe to MySQL - issue with NaN [mysql-connector]

Question

I am using Python to insert some columns from a pandas dataframe to MySQL. I'm using pandas 15.2. and mysql.connector.

import mysql.connector
cte = mysql.connector.connect(user=xxxxxxxxx, password=xxxxxxx, host=xxxxxx)
cursor = cte.cursor()

for index, row in pets.iterrows():
    home_pets = (row['cats'], row['dogs'], row['goldfish'], row['rabbits'], row['mice'], row['parrots'])   
    add_pets = """  
                            INSERT INTO pets 
                            (cats, dogs, goldfish, rabbits, mice, parrots) 
                            VALUES (%s, %s, %s, %s, %s, %s)
                 """
    cursor.execute(add_pets, home_pets)
    cte.commit()

One of my integer columns has 'NaN' values in it. I've just added this and as everything was working before am sure that this is the column causing issues. This is a varchar(5) (was int(5) ) column and is not set to "NOT NULL".
Edit: This column (parrots) contains numbers 0 to 5 as responses. It also has "NaN" where the respondent didn't answer the question.

I'm getting the following error message when trying to insert the data in the table:

ProgrammingError                          Traceback (most recent call last)
<ipython-input-54-59a05bb50318> in <module>()
      6                                 VALUES (%s, %s, %s, %s, %s, %s)
      7                      """
----> 8     cursor.execute(add_pets, home_pets)
      9     cte.commit()

C:\Python27\lib\site-packages\mysql\connector\cursor.pyc in execute(self,     operation, params, multi)
    505             self._executed = stmt
    506             try:
--> 507                 self._handle_result(self._connection.cmd_query(stmt))
    508             except errors.InterfaceError:
    509                 if self._connection._have_next_result:  # pylint: disable=W0212

C:\Python27\lib\site-packages\mysql\connector\connection.pyc in cmd_query(self, query)
    720         if not isinstance(query, bytes):
    721             query = query.encode('utf-8')
--> 722         result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
    723 
    724         if self._have_next_result:

C:\Python27\lib\site-packages\mysql\connector\connection.pyc in _handle_result(self, packet)
        638             return self._handle_eof(packet)
        639         elif packet[4] == 255:
    --> 640             raise errors.get_exception(packet)
        641 
        642         # We have a text result set

ProgrammingError: 1054 (42S22): Unknown column 'nan' in 'field list'

I saw this question but they are not using mysql.connector. Python Pandas write to sql with NaN values.

I would prefer to keep the int column as int and not have to change it to object if possible (and then go back and forth every time I read the data into Python from my MySQL db. Is this possible? Appreciate your assistance.

Hi @AndyHayden , I didn't. I'm very inexperienced at using MySQL so am still learning. Would you recommend this over mysql.connector? I guess I would need to install SQLAlchemy? — gincard, May 01 '15 at 04:46
How have you created pets and whats the data(or a small subset that gives the error)? and you don't need so many tags. — lxx, May 01 '15 at 04:52
Yes, definitely worth it, see http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries — Andy Hayden, May 01 '15 at 05:01
Either change the column data type to something that can handle numbers and NAN or format the data to suit before inserting it. eg remove the NAN. if x == 'NAN': x='' or add another column for no response — lxx, May 01 '15 at 05:19
Re the above comments on .to_sql(), here is some code on how to do it: https://gist.github.com/atandy/495786403effb9df9642 — tandy, May 01 '15 at 05:21
Thank you very much - I'm now using to_sql, @tandy - your code was very helpful, have managed to create a db using python and am now adding elements from my dataframe to new tables. Much much quicker than using mysql.connector. Am very grateful for your help. — gincard, May 01 '15 at 06:26

score 0 · Answer 1 · answered Nov 24 '18 at 07:03

You have put your add_pets query in docstring which is for special commenting and this can be your issue. Switching it to normal quotation mark can solve the issue.

add_pets = "INSERT INTO pets (cats, dogs, goldfish, rabbits, mice, parrots) VALUES (%s, %s, %s, %s, %s, %s)"

Python connecting dataframe to MySQL - issue with NaN [mysql-connector]

1 Answers1