CODEWITHSUNDEEP
pythonsqlpython-3.x
Xzatar
Xzatar

Reputation: 67

Not all parameters were used in the SQL statement error

I would like to get names from one db and initiate a new table with it. I want to add more analysis, but this is my starting point where I'm already struggling and I have no idea where I made the mistake.

mydb = db_login()

# get team
team = pd.read_sql('SELECT * FROM team', con=mydb)
names = team.name.to_list()

this will output something like ['name1', 'name2' ...]

mycursor = mydb.cursor()
mycursor.execute("DROP TABLE IF EXISTS team_analyse")
mycursor.execute("CREATE TABLE team_analyse (name VARCHAR(50))") #todo add all needed columns
sqlFormula = "INSERT INTO team_analyse (name) VALUES (%s)" #todo initial team commit
mycursor.executemany(sqlFormula, names)
mydb.commit()

In the end I get the following error:

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Upvotes: 1

Views: 601

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You should be using a single INSERT INTO ... SELECT here:

INSERT INTO team_analyse (name)
SELECT name
FROM team

Your updated Python script:

mycursor = mydb.cursor()
sql = """INSERT INTO team_analyse (name)
         SELECT name
         FROM team"""
mycursor.execute(sql)

The error message you are seeing is telling you that you passed a list as the parameters to bind to the statement, but not all parameters were used. Actually, the single insert statement you were trying to run only has a single parameter. But in any case, it is unnecessary to bring the result set from the team table into Python's memory. Instead, use my suggestion and let MySQL do the heavy lifting.

Upvotes: 2

Related Questions