Get error ProgrammingError when insert to MySQL database using Python

Question

I have a dataframe that have about 200M rows with example like this:

Date         tableName    attributeName
29/03/2019   tableA       attributeA
....

and I want to save the dataframe to a table in MySQL database. This is what I've tried to insert the dataframe to table:

def insertToTableDB(tableName,dataFrame):
    mysqlCon = mysql.connector.connect(host='localhost',user='root',passwd='')
    cursor = mysqlCon.cursor()
    for index, row in dataFrame.iterrows():
        myList =[row.Date, row.tableName, row.attributeName]
        query = "INSERT INTO `{0}`(`Date`, `tableName`, `attributeName`) VALUES (%s,%s,%s);".format(tableName)
        cursor.execute(query,myList)
        print(myList)
    try:
        mysqlCon.commit()
        cursor.close()        
        print("Done")
        return tableName,dataFrame
    except:
        cursor.close()
        print("Fail")

This code successful when I inserted a dataframe that have 2M rows. But, when I inserted dataframe that have 200M rows, I got error like this:

File "C:\Users\User\Anaconda3\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
self._handle_result(self._connection.cmd_query(stmt))

File "C:\Users\User\Anaconda3\lib\site-packages\mysql\connector\connection.py", line 553, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))

File "C:\Users\User\Anaconda3\lib\site-packages\mysql\connector\connection.py", line 442, in _handle_result
raise errors.get_exception(packet)

ProgrammingError: Unknown column 'nan' in 'field list'

My dataframe doesn't have 'nan' value. Could someone help me to solve this problem?

Thank you so much.

@tawab_shakeel yes, of course. I already update the question — elisa
– elisa, Commented Jul 29, 2019 at 6:31
put for loop in try block or use if after for loop to check whether all expected fields are available in dataframe, i think one of your column holding nan (i.e. not a number) — Shanteshwar Inde
– Shanteshwar Inde, Commented Jul 29, 2019 at 6:38

Danrley Pereira · Accepted Answer · 2020-12-15 22:39:43Z

2

replace everywhere 'NaN' for the string 'empty':

df = df.replace(np.nan, 'empty')

Remember to:

import numpy as np

answered Dec 15, 2020 at 22:39

Danrley Pereira

1,41618 silver badges26 bronze badges

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

1 Comment

wfolkerts Over a year ago

this is not what you want, it should say NULL.

tawab_shakeel · Accepted Answer · 2019-07-29 06:42:52Z

1

try these steps

drop rows containing nan using dropna
Filter rows which not contains nan in string.
Convert nan into None

df.dropna(inplace=True)

df[(df['Date']!='nan') & (df['tableName']!='nan') &(df['attributeName']!='nan')]

df1 = df.where((pd.notnull(df)), None)

answered Jul 29, 2019 at 6:42

tawab_shakeel

3,75912 silver badges26 bronze badges

1 Comment

Tengerye Over a year ago

The third one does not work.

T.S. · Accepted Answer · 2022-08-31 23:38:38Z

0

df = df.astype(str) solves the problem for me - assuming you've already set up your table schema

edited Aug 31, 2022 at 23:38

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

answered Aug 26, 2022 at 17:54

hq2nguye

1

Collectives™ on Stack Overflow

Get error ProgrammingError when insert to MySQL database using Python

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related