Python how to insert data from dataframe to MySQL

Question

I get row of datafram and insert to mysql with this code, res is data I want to insert to mysql.

res = df.loc[df.ID == l_id]
mycursor = mydb.cursor()

sql = "INSERT INTO log (id, user, number, state, j_id) VALUES (%s, %s, %s, %s, %s )"
val = [(None, res['User'], res['Pages'] , res['State'], l_id )]

mycursor.executemany(sql, val)
mydb.commit()

print(mycursor.rowcount, "was inserted.") 

mycursor.close()

when I run it show error like this.

ProgrammingError: Failed processing format-parameters; Python 'series' cannot be converted to a MySQL type

How to add data from dataframe to MySQL?

Answer 1

Try this:

val = [(None, res['User'].item(), res['Pages'].item() , res['State'].item(), l_id )]

The res['User'] by itself returns a pandas object which you can't pass as mysql arguments. You need to extract the value of the pandas object.

Answer 2

You can use to_dict(). JFYI: item() deprecated 0.25.0.

df = pd.DataFrame({
    'User': ('first', 'sec',),
    'ID': (1, 2,),
    'Pages': (1, 2,),
    'State': ('active', 'not active',),
})


l_id = 1
users = df.loc[df.ID == l_id].to_dict('records')
print(users)  # [{'User': 'first', 'ID': 1, 'Pages': 1, 'State': 'active'}]

mycursor = mydb.cursor()
sql = "INSERT INTO log (id, user, number, state, j_id) VALUES (%s, %s, %s, %s, %s )"
# insert first user from list
val = (None, users[0]['User'], users[0]['Pages'], users[0]['State'], l_id)
mycursor.execute(sql, val)
mydb.commit()

Hope this helps.

Answer 3

Consider using to_records with executemany. The resulting numpy record arrays append may migrate like list of tuples into SQL.

Also, instead of appending None to render as NULL, simply leave out the column in append query. And why not use the data frame's ID which would be same as the variable l_id? And in case your data frame contains other unneeded columns for table insert, use reindex to subset columns.

mycursor = mydb.cursor()

# FILTER ROWS AND COLUMNS
res = df[df.ID == l_id].reindex(['User', 'Pages', 'State', 'ID'], axis=1)

sql = "INSERT INTO log (user, number, state, j_id) VALUES (%s, %s, %s, %s)"

mycursor.executemany(sql, res.to_records(index=False))         
myconn.commit()

Alternatively, import via list of tuples:

mycursor.executemany(sql, res.apply(tuple, axis=1).tolist())   
myconn.commit()