Error on converting dataframe to SQL - Pandas

Question

I am getting this error: DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Not all parameters were used in the SQL statement. when trying to convert my dataframe to sql

My connection variable:

con = mysql.connector.connect(
  host="****",
  port="****",
  database="*****",
  user="*****",
  password="*****"
)

My try to convert it to sql:

df.to_sql('menageiro2',con)

Note: I am using:

import pandas as pd
import sqlalchemy
import mysql.connector

You leaked your password so make sure you change that. The error means that the sql query expects input for the name bind variable. Can you post a self-contained example to demonstrate the issue? Otherwise I cannot debug this for you. Others may be able to of course. — user9706
– user9706, Commented Feb 27, 2021 at 3:03
stackoverflow.com/help/minimal-reproducible-example Pictures are better than nothing but that means if I try to run your code, I have to transcribe it to text. It's better if you post an actual minimal example of a program that exhibit the issue. That said, see my answer below (my current best guess). — user9706
– user9706, Commented Feb 27, 2021 at 3:13

user9706 · Accepted Answer · 2021-02-27 03:10:40Z

3

The reference says con: sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection. You appear to be passing in a mysql connection instead of a SQLAlchamy engine (that you connected to MySQL):

con = sqlalchemy.create_engine(
'mysql+mysqlconnector://<user>:<password>@<host>:<port>/<default_db>...')

answered Feb 27, 2021 at 3:10

user9706

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

3 Comments

Davi Cheli Miquelim Over a year ago

Do I have to take off "< >"?

Davi Cheli Miquelim Over a year ago

Thanks a lot man! That was the problem. Do you know why do I have to use SQLAlchemy engine?

user9706 Over a year ago

That was the choice of whoever wrote the pandas to_sql() function. SQLAlchamy probably works as comparability layer, so they can support more database without having to write a bunch per database specific code.

Collectives™ on Stack Overflow

Error on converting dataframe to SQL - Pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related