Issue while trying to select record in mysql using Python-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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])