Add pandas Dataframe to MySQL

Question

I am trying to add a section of dataframe to mySQL database and I am getting an error on my syntax

#connection to database
conn = mysql.connector.connect(host='localhost', user='root', passwd='passed')
cur = conn.cursor() #create cursor

# Insert DataFrame records one by one.
for index, row in final_df.iterrows():
    cur.execute("INSERT IGNORE INTO player ([full_name], [first_name], [last_name], [name_FIBA_format], [dob], [age], [height], [real_gm_profile], [game_log_url]) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)", (row['full_name'], row['first_name'], row['last_name'], row['name_FIBA_format'], row['dob'], row['age'], row['height'], row['real_gm_profile'], row['game_log_url']));

conn.commit()
conn.close()

The error message I get is

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 487, in cmd_query
    self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[full_name], [first_name], [last_name], [name_FIBA_format], [dob], [age], [heigh' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "connect_db_player_profile_update.py", line 157, in <module>
    cur.execute("INSERT IGNORE INTO player ([full_name], [first_name], [last_name], [name_FIBA_format], [dob], [age], [height], [real_gm_profile], [game_log_url]) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)", (row['full_name'], row['first_name'], row['last_name'], row['name_FIBA_format'], row['dob'], row['age'], row['height'], row['real_gm_profile'], row['game_log_url']));
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 264, in execute
    result = self._cnx.cmd_query(stmt, raw=self._raw,
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 491, in cmd_query
    raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[full_name], [first_name], [last_name], [name_FIBA_format], [dob], [age], [heigh' at line 1

Any help would be greatly appreciated as been going round in circles for two weeks now. The Dataframe columns match with the the table in the database. I am using Python3, Pandas and MySQL community edition

Thank you

Can you try to insert without brackets around your columns like insert into player (full_name,first_name,... — A.B
– A.B, Commented Sep 23, 2020 at 21:56

Parfait · Accepted Answer · 2020-09-23 21:57:48Z

1

MySQL does not use square brackets, [...] for column identifiers but backticks. Consider also using executemany converting all rows to list of values avoiding the iterrows loop. Below reindex ensures column subset and order.

sql = """INSERT IGNORE INTO player (`full_name`, `first_name`, `last_name`, 
                                    `name_FIBA_format`, `dob`, `age`, `height`, 
                                    `real_gm_profile`, `game_log_url`)
         VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""

final_df = final_df.reindex(['full_name', 'first_name', 'last_name', 'name_FIBA_format', 
                             'dob', 'age', 'height', 'real_gm_profile', 'game_log_url'],
                            axis='columns')

cur.executemany(sql, final_df.to_numpy().tolist())
conn.commit()

answered Sep 23, 2020 at 21:57

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

3 Comments

A.B Over a year ago

itseems to be the issue, I was also thinking that i never used brackets with mysql

Adam Sewell Over a year ago

Thank you @Parfait I really appreciate you taking the time to answer my question. I know it was probably fairly basic but know I have got my head around it will help me massively going forward!

Parfait Over a year ago

Great to hear and glad to help. Interestingly, in your previous post you did use backticks. Only double quotes are the standard in SQL for table/column identifiers, all other symbols are specific to database: backticks for MySQL; square brackets for SQL Server / Sybase; both for SQLite / MS Access; only double quotes for PostgreSQL / Oracle / DB2).

A.B · Accepted Answer · 2020-09-23 21:50:49Z

0

Edited, if you use sqlalchemy module for connecting to mysql, like

 import pandas as pd
 import mysql.connector
 from sqlalchemy import create_engine

 engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)

you can use to_sql method on the dataframe

 df.to_sql("player",con=engine)

to insert specific column, you might select only those columns

edited Sep 23, 2020 at 21:50

answered Sep 23, 2020 at 21:45

A.B

20.5k3 gold badges43 silver badges74 bronze badges

Collectives™ on Stack Overflow

Add pandas Dataframe to MySQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related