Fix %s sql query in python

Question

Python refuses to execute an sql query

I want to execute an insert sql query with python language in Pycharm. An error is launched after executing the code below:

import mysql.connector

stock = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="admin2020",
  database="stock"
)

sql = "INSERT INTO produit(code, nom, prix_unitaire, tva, quantite) VALUES(%s,%s,%f,%f,%d)"
valeurs = ("LAM","lampe",0.9,0.19,10)
mycursor = stock.cursor()
mycursor.execute(sql, valeurs)

The error message is:

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

due to dbapi only %s is allowed

Vova
– Vova

2019-08-25 09:00:48 +00:00
Commented Aug 25, 2019 at 9:00 — Vova
– Vova, Commented Aug 25, 2019 at 9:00

Eugene Yarmash · Accepted Answer · 2019-08-25 08:58:34Z

1

You're confusing %s used as a parameter marker for a query, and %s (%d, %f, etc) used in Python string formatting. In your queries you should use just %s, the database driver will format the query for you:

sql = ("INSERT INTO produit(code, nom, prix_unitaire, tva, quantite) "
       "VALUES (%s, %s, %s, %s, %s)")
valeurs = ("LAM", "lampe", 0.9, 0.19, 10)
mycursor.execute(sql, valeurs)

edited Aug 25, 2019 at 8:58

answered Aug 23, 2019 at 11:01

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

Comments

Primit · Accepted Answer · 2019-08-23 12:03:23Z

0

Try this one

sql = "INSERT INTO produit(code, nom, prix_unitaire, tva, quantite) VALUES(%s,%s,%f,%f,%d)"%("LAM","lampe",0.9,0.19,10)

    mycursor = stock.cursor()
    mycursor.execute(sql)

answered Aug 23, 2019 at 12:03

Primit

8657 silver badges13 bronze badges

Collectives™ on Stack Overflow

Fix %s sql query in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related