CODEWITHSUNDEEP
pythonsql-serverdatetimesql-insertpyodbc
Loes Visser
Loes Visser

Reputation: 31

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

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.

Upvotes: 1

Views: 1293

Answers (1)

Mogo
Mogo

Reputation: 121

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)

Upvotes: 2

Related Questions