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

• A.B: Can you try to insert without brackets around your columns like insert into player (full_name,first_name,...

Answer 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()

• A.B: itseems to be the issue, I was also thinking that i never used brackets with mysql
• Adam Sewell: 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: 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).

Answer 2

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