How to use to_sql in pandas

I'm trying to get to the bottom of what I thought would be a simple problem: exporting a dataframe in Pandas into a mysql database.

There is a scraper that collates data in pandas to save the csv format

**title, summary, url** #header
  abc,   summary, some_url

But I'd like to send the dataframe directly to a mysql database with the same three-column format.

My code so far is:

import mysql.connector

# Connect to the database
conn = mysql.connector.connect(user='root', password='somepassword', host='localhost', port='3306', database='db')

# Write the DataFrame to the database
df.to_sql(name='table_name', con=conn, if_exists='replace', index=False)

# Close the connection
conn.close()

But this returns an error message of:

pandas.errors.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Not all parameters were used in the SQL statement

How do I fix this?

UPDATE:

I have read that I might have to use sqlalchemy but I really want to stick with pandas' solution if possible.

Solution

You can still use pandas solution, but you have to use sqlalchemy.create_engine instead of mysql.connector.connect, since to_sql expects "sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection" as con argument. See reference and examples there. This should be working fine:

import sqlalchemy

# Connect to the database
conn = sqlalchemy.create_engine(
'mysql+mysqlconnector://root:somepassword@localhost:3306/db')

# Write the DataFrame to the database
df.to_sql(name='table_name', con=conn, if_exists='replace', index=False)

# Close the connection
conn.close()