Question 21356 in Mysql Question posted in
The official MySQL documentation can be found here.

Issue while trying to select record in mysql using Python

Error Message

You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use
near ‘%s’ at line 1

MySQL Database Table

CREATE TABLE `tblorders` (
  `order_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `order_number` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `tblorders`
  ADD PRIMARY KEY (`order_id`),
  ADD UNIQUE KEY `order_number` (`order_number`);

ALTER TABLE `tblorders`
  MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

Code

mydb = mysql.connector.connect(host = "localhost", user = "root", password = "", database = "mydb")
        
mycursor = mydb.cursor()
sql = "Select order_id from tblorders where order_number=%s"        
val = ("1221212")
mycursor.execute(sql, val)

Am I missing anything?

Tags:

2

Answers


  1. 0

    You must pass a list or a tuple as the arguments, but a tuple of a single value is just a scalar in parentheses.

    Here are some workarounds to ensure that val is interpreted as a tuple or a list:

    sql = "Select order_id from tblorders where order_number=%s"        
val = ("1221212",)
mycursor.execute(sql, val)

sql = "Select order_id from tblorders where order_number=%s"        
val = ["1221212"]
mycursor.execute(sql, val)

    This is a thing about Python that I always find weird, but it makes a kind of sense.

    Login or Signup to reply.
  2. 0

    In case you want to insert data you have to modify your SQL. Use INSERT instead of SELECT like this:

    INSERT INTO tblorders (order_number) VALUES ("122121");

    That statement will add new record to the table. Besides, in MariaDB you need to use ? instead of %s that works on Mysql database.

    sql = "INSERT INTO tblorders (order_number) VALUES (?);"
val = "1231231"
mycursor.execute(sql, [val])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search