Python SQL Date insert issue

Question

I am trying to insert a date inside the orderDate column in my sql database, which is of datatype date

date = (start_date.strftime("%Y-%m-%d"))

testData.cur.execute("INSERT INTO customerOrders (orderDate) VALUES %s", (date,))
testData.cn.commit()

Here is the value of the variable 'data':

2019-01-01

Here is the data type of the date:

class 'str'

I am subject to the error of:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2019-01-01'' at line 1

score 0 · Answer 1 · answered Jan 21 '20 at 17:25

0

Add simple quotes in %s. MySQL needs to know that '2019-01-01' is a date type input.

INSERT INTO customerOrders (orderDate) VALUES '%s'

answered Jan 21 '20 at 17:25

ThRnk

575
2
19

Do note: OP is attempting parameterization which uses the same symbol (`%s`) as Python's older string formatting method, `%`, which is [no longer recommended](https://stackoverflow.com/a/13452357/1422451). Usually, you do not quote parameters. – Parfait Jan 21 '20 at 17:44

score 0 · Answer 2 · answered Jan 21 '20 at 17:41

Consider parameterization of exact date time variable and avoid string formatting. Also, in SQL, VALUES clause requires parentheses enclosure around numbers or literals:

testData.cur.execute("INSERT INTO customerOrders (orderDate) VALUES (%s)", 
                     (start_date,))
testData.cn.commit()

Python SQL Date insert issue

2 Answers2