CODEWITHSUNDEEP
pythonmysql
jeyshree
jeyshree

Reputation: 59

format specifoers not replaced by values in select query passed to mysql from python

i tried to query sql database from python. when i harcode the values of filter directly in string and pass i as query,query is proper. however when i use format specifiers like "%s" i am facing issues.

code is as follows

[import tkinter
import tkinter.messagebox
from tkinter import *
import mysql.connector

top = tkinter.Tk()

#database connecion
from mysql.connector import errorcode
try:
    cnx =mysql.connector.connect(host='localhost',user='root',password='1234')
    cur = cnx.cursor()
    query="CREATE DATABASE IF NOT EXISTS prj_db"
    cur.execute(query)
    cnx =mysql.connector.connect(host='localhost',database='prj_db',user='root',password='1234')
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("no db")
  else:
    print(err)
else:
    cur = cnx.cursor()  
    query = ("CREATE TABLE IF NOT EXISTS product_det_2"
            "(P_id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,"
            "P_name CHAR(20) DEFAULT '' NOT NULL,"
            "Drawing_num CHAR(20) DEFAULT '' NOT NULL,"
            "Price INT(4) UNSIGNED ZEROFILL NOT NULL,"
            "PRIMARY KEY(P_id))")
    cur.execute(query)
query = ("SELECT * FROM product_det_2")
cur.execute(query)
print(query)
for (P_id, P_name, Drawing_num,Price) in cur:
      print("{}, {}, {},{}".format(P_id, P_name, Drawing_num,Price))
print("*********************************")
query = ("SELECT * FROM product_det_2 WHERE P_id=2")
print(query)
cur.execute(query)
for (P_id, P_name, Drawing_num,Price) in cur:
      print("{}, {}, {},{}".format(P_id, P_name, Drawing_num,Price))
print("*********************************")
a="2"
query = ("SELECT * FROM product_det_2 WHERE P_id=%s")
print(query)
cur.execute(query,a)
for (P_id, P_name, Drawing_num,Price) in cur:
      print("{}, {}, {},{}".format(P_id, P_name, Drawing_num,Price))
cnx.commit()
top.mainloop()][1]

here if i give %d as format specifier and a=2 even then i am facing error. please let me know where i am going wrongenter image description here

Upvotes: 0

Views: 56

Answers (1)

mhawke
mhawke

Reputation: 87084

The second argument to cursor.execute() should be a tuple or a dictionary. This applies even if there is a single parameter to be inserted into the query. Try this:

cur.execute(query, (a,))

Here a tuple containing a single element is passed to execute().

Upvotes: 1

Related Questions