Inserting datetime into a MS SQL table using pyodbc

Question

I'm trying to insert a datetime value into a MS SQL Server table using pyodbc. If I do it manually, something like:

cursor.execute("""insert into currentvalue(value1,currentdatetime)
                                    values(55,'2014-06-27 16:42:48.533')""")

I have no problem at all, but when I try to do:

currenttime = str(datetime.datetime.now())
cursor.execute("""insert into currentvalue(value1,currentdatetime) 
                                    values(55,"""+ currenttime+")")

I got this error:

SQL server Incorrect syntax near '07' which i think is the number after the date and starting the time.

Also I tried this:

currenttime = "'"+str(datetime.datetime.now())+"'"

and now this error comes up:

Conversion failed when converting date and/or time from character string.

Use [parameters](https://code.google.com/p/pyodbc/wiki/GettingStarted#Parameters). — Bryan, Jun 27 '14 at 18:12

score 19 · Answer 1 · answered Jun 27 '14 at 18:14

19

Remove the datetime to string conversion and instead use parameters:

....
cursor.execute("insert into currentvalue (value1,currentdatetime) values(?,?)",
               (value1, datetime.datetime.now()))
....

answered Jun 27 '14 at 18:14

Bryan

17,112
7
57
80

score 7 · Answer 2 · answered Jan 30 '15 at 13:39

7

You can also use datetime.strftime() to convert datetime object to string in the way you need. str(datetime) is bad idea.

currenttime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f") cursor.execute("""insert into currentvalue(value1,currentdatetime) values(55,"""+ currenttime+")")

answered Jan 30 '15 at 13:39

user2232196

79
2
3

I think your solution not work and it is just like the questioner first attempt. You need at least a string escape. – Michele d'Amico Jan 30 '15 at 14:05

score -1 · Answer 3 · edited Apr 12 '23 at 09:04

-1

You can add single quotes:

currenttime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_statement = f"""
insert into currentvalue(value1,currentdatetime)
values(55,'{currenttime}')
""" 
cursor.execute()

edited Apr 12 '23 at 09:04

ChrisGPT was on strike

127,765
105
273
257

answered Apr 08 '23 at 03:04

TomN

1
2

Building SQL queries by sticking strings together [is a](https://xkcd.com/327/) [bad habit](https://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables) – ChrisGPT was on strike Apr 12 '23 at 09:06
1

@Chris Thank you for the feedback. I didn't know that I was writing vulnerable SQL injection code. Moving forward, I will keep that in mind. See: https://realpython.com/prevent-python-sql-injection/ for how to write safer code for SQL injection attacks. – TomN Apr 17 '23 at 16:27

Inserting datetime into a MS SQL table using pyodbc

3 Answers3

Linked