SQL syntax error when passing datetime into execute code

March 17, 2022

I’m working on sql project in python and I having some problem here seeking for help.
I have a list variable and I want to insert the datetime into sql

module: mysql-connector , IDE: vscode

here’s the variable

data_lst =[['2020-12-13 13:00:00',2,3,4,5]]
dttime = str(data_lst[z][0])

here’s the code #dttime is in datetime format

insert_data = "INSERT INTO"+ space + coin_lst[x] + space + \
        "(dttime,open_,close_,high_,low_) VALUES" + space + \
        "(" + dttime + "," + open_ +","+ close_ +"," + high_ +","+ low_+")"                                        
mycursor.execute(insert_data)

error

raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
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 '13:00:00,2,3,4,5)'

#I tried solving by adding single quotes but not working , please help , Thank you:)

>Solution :

The issue with the syntax should be apparent if you display the SQL string after building it.

INSERT INTO tablename (dttime,open_close_high_low_) VALUES(13:00:00,2,3,4,5)

Date and time values in SQL are strings. They need single-quotes to delimit them. So it should look like the following:

INSERT INTO tablename (dttime,open_close_high_low_) VALUES('13:00:00',2,3,4,5)

This leads to a general tip: It’s easier to debug SQL by looking at the final SQL, instead of looking at your code that builds the SQL string.

Another tip is to modernize your method of building SQL by using f-strings, multi-line strings, and SQL query parameters.

Example:

insert_data = f"""INSERT INTO {coin_lst[x]} (dttime,open_,close_,high_,low_) 
    VALUES (%s, %s, %s, %s)"""
params = (dttime, open_, close_, high_,)
mycursor.execute(insert_data, params)

It’s hard to get SQL syntax formatted correctly when it’s mixed with all the string-concatenation Python syntax. This is likely to cause mistakes when you forget the quotes and parentheses and so on. So it’s better to use Python syntax that helps you avoid such mistakes.

It’s also worthwhile to use query parameters so you only need to put %s in your SQL query, and then the values for those placeholders are passed separately in a tuple, as shown above. Don’t put the %s placeholder inside single-quotes, even for string literals. This is handled automatically by the execute() function.

By modernizing your code, you’ll make fewer mistakes!