Python: SQL Server insert multiple values with datetime (Conversion failed when converting date and/or time from character string)

Question

I'm trying to insert multiple rows in a database table (SQL server) in python:

def write_to_database(values):
    conn =  pyodbc.connect("Driver={SQL Server};"
                        "Server=xxxx.database.windows.net;"
                        f"Database={database};"
                        "UID=user;"
                        "PWD=password;")
    cursor = conn.cursor()
    cursor.executemany("insert into KeyFigures (DateTime, Parameter, CommulativeTime, Value) values (?,?,?,?)", values)
    conn.commit()
    return()

date = datetime.datetime.now()
atom = date.strftime("%Y-%M-%d") + " " + date.strftime("%H:%M:%S") + ".4526800"

values = ([datetime.datetime.now().isoformat(), 1, "NULL", 2],[datetime.datetime.now().isoformat(), 1, "NULL", 47],[datetime.datetime.now().isoformat(), 1, "NULL", 78])

write_to_database(values)

I tried multiple formats of datetime, string combinations etc. e.g.:

datetime.datetime.now().isoformat() 
atom
"2020-02-23 11:30:53.4526800"
"2020-02-23T11:30:53.4526800"

but i keep recieving the same error:

line 50, in write_to_database
    cursor.executemany("insert into KeyFigures (DateTime, Parameter, CommulativeTime, Value) values (?,?,?,?)", values)
pyodbc.DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

in SSMS the folowing works:

INSERT INTO KeyFigures (DateTime, Parameter, CommulativeTime, Value) VALUES ('2020-02-23 11:30:53.4526800',2,null,21)

how can I solve this error?

***** edit ****

@Mogo thank you very much. this was already very helpfull, but does not work in my code. I still receive the same error. I also tried to insert a single row and this piece of code works (with execute instead of executemany):

def write_to_database(date,parameter,cummulativeTime,value):
    conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
                        "Server=xxxx.database.windows.net;"
                        f"Database={database};"
                        "UID=user;"
                        "PWD=password;")
    with conn.cursor() as cursor:
        cursor.execute(f"INSERT INTO dbo.KeyFigures (DateTime, Parameter, CommulativeTime, Value) values ('{date}',{parameter},{cummulativeTime},{value})")
        conn.commit()
    return()


date = datetime.datetime.now()

write_to_database(date, 1, "NULL", 43)

it doesnt work without date between quotes. Is this also the problem with the executemany? when i put the questionmark between qoutes ('?' or '?') gives the error that there are only 3 parameters given instead of 4.

Don't convert them to a string, pass the parameter to the cursor as a strongly typed data and time value and python handles it fine. — Keming Lamurnogo
– Keming Lamurnogo, Commented Mar 23, 2021 at 16:09
Also, you should really be using the ODBC Driver for SQL Server — Keming Lamurnogo
– Keming Lamurnogo, Commented Mar 23, 2021 at 16:14
Don't use dynamic SQL to inject column values. Use INSERT … VALUES (?, ?, … and pass the parameter values in a tuple (for .execute) or a list of tuples (for .executemany). — Gord Thompson
– Gord Thompson, Commented Mar 24, 2021 at 13:16

Keming Lamurnogo · Accepted Answer · 2021-03-23 16:29:53Z

2

As I mentioned in my comment, if you use a strongly type data type (so don't convert it to a string), python and pyodbc will handle this gracefully. I also, however, recommend updating to the ODBC Driver for SQL Server rather than using the old Native SQL Server Driver. I also put the cursor into a with so that it is close gracefully.

For a table I created with the definition below this worked fine, and inserted 2 rows, with the correct date and time values:

CREATE TABLE dbo.TestDateTable (i int,dt datetime2(7));

import datetime, pyodbc

def write_to_database(values):
    conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
                          "Server=xxxx.database.windows.net;"
                          "Database={database};"
                          "UID=user;"
                          "PWD=password;")
    with conn.cursor() as cursor:
        cursor.executemany("INSERT INTO dbo.TestDateTable (i,dt) VALUES (?,?);", values)
        conn.commit()
    return()

date = datetime.datetime.now()
values = ([1,date],[2,date])

write_to_database(values)

edited Mar 23, 2021 at 16:29

answered Mar 23, 2021 at 16:24

Keming Lamurnogo

1211 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gord Thompson Over a year ago

Good suggestions. Note, however, that using a context manager (with block) does not automatically close the cursor when it exits. Details here.

Keming Lamurnogo Over a year ago

Thanks @GordThompson. Would you therefore recommend an explicit cursor.close() outside/inside of the with? Seems instead, however, that the conn.commit() is redundant; unless I am reading the link incorrectly.

Gord Thompson Over a year ago

The issue is discussed at some length here.

Loes Visser Over a year ago

@Mogo, I editted my start post. I tried your code, but does not work.

Keming Lamurnogo Over a year ago

@LoesVisser your attempt doesn't parametrise; you're injecting. That is a major security flaw. Have a look at my answer again. If you were injecting then yes, you would have to quote the value, but you shouldn't be injecting because it's a security flaw.

Collectives™ on Stack Overflow

Python: SQL Server insert multiple values with datetime (Conversion failed when converting date and/or time from character string)

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related