ProgrammingError("%s can not be used with MySQL" % s)

user_12241196 0
Viewed 3400

I want to insert the dataframe into table1 using the code below:

def insert(df):
    host='###'
    name='###'
    password='###'
    database='###'
    conn = pymysql.connect(host, user=name,passwd=password, db=database, connect_timeout=5,cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "INSERT INTO table1 (col1, col2, col3, col4, col5 ) VALUES (%s,%s,%s,%s,%s)"
    cur.execute( sql, (row[0], row[1], row[2], row[3], row[4]))
    conn.commit()
    cur.close()
    conn.close()

    return

for row in dataframe.itertuples():
    insert(row)

The error is showing below:

    cur.execute( sql, (row[0], row[1], row[2], row[3], row[4]))
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 161, in execute
    query = self.mogrify(query, args)
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 140, in mogrify
    query = query % self._escape_args(args, conn)
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 115, in _escape_args
    return tuple(conn.literal(arg) for arg in args)
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 115, in <genexpr>
    return tuple(conn.literal(arg) for arg in args)
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 469, in literal
    return self.escape(obj, self.encoders)
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 462, in escape
    return converters.escape_item(obj, self.charset, mapping=mapping)
  File "/usr/local/lib/python3.7/site-packages/pymysql/converters.py", line 26, in escape_item
    val = encoder(val, mapping)
  File "/usr/local/lib/python3.7/site-packages/pymysql/converters.py", line 58, in escape_float
    raise ProgrammingError("%s can not be used with MySQL" % s)
NameError: name 'ProgrammingError' is not defined

Anyone know what was wrong and how to solve the error?

mysql-python python
3 Answers

You have to check for all of the values like inf or NaN. You can place something like this on the end of the df processing to avoid such problems:

report = report.replace([np.inf, -np.inf], np.nan)
report = report.fillna(0)

Of course if you don't mind, that these values will be represented as zeros, which may be wrong in many situations. Then you have to find some other representation.

user_73544160

ProgrammingError is not a built in error. It needs to be defined before you can use it. If its supposed to be defined by pymysql you need to import it with from pymysql import ProgrammingError.

user_115912380

Solve the problem by checking that the data did not have NaN.

user_142173930
Related