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

Question

I'm new to the python. Currently, I have a python code with me to insert new data to my Microsoft SQL Database and I do it like this as below:

import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=DESKTOP-H7KQUT1;"
                      "Database=SAOS1;"
                      "Trusted_Connection=yes;")
cursor = cnxn.cursor()


print ('Inserting a new row into table')
#Insert Query
tsql = "INSERT INTO attendance (Atd_Date, Atd_InTime, Atd_OutTime, SID) VALUES (?,?,?,?);"
with cursor.execute(tsql,'2018/11/14','07:11:34','14:32:11','18010321'):
    print ('Successfuly Inserted!')

However, I have an Arduino project on the other side which is recording student attendance using fingerprint. Serial monitor will be displaying output when a match is found. My arduino code is:

void setup()
   {
     ....
   }
void loop()
   {
     ...
     //Found a match
     Serial.println("{'SID':"+ String(finger.fingerID) +",'Time':"+ String(timeString) +"}");
   }

Sample output will be like this:

{'SID':1,'Time':07:11:13}

I would like to catch this output from serial, and let my python code fetch this output and store it into MSSQL Database. I refer to this website So, I do it like this:

import pyodbc
import serial
import time
import datetime
import ast
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=DESKTOP-H7KQUT1;"
                      "Database=SAOS1;"
                      "Trusted_Connection=yes;")
cursor = cnxn.cursor()

#initial serial port
arduino = serial.Serial('COM4', 9600, timeout=.1)
#fetch data from serial
data = arduino.readline()[:-2].decode("utf-8")
if data!="":

        SID = ast.literal_eval(data)['SID']
        Atd_InTime = ast.literal_eval(data)['Time']

print ('Inserting a new row into table')
#Insert Query
tsql = "INSERT INTO attendance (Atd_Date, Atd_InTime, Atd_OutTime, SID) VALUES (?,?,?,?);"
with cursor.execute(tsql,'2018/11/14','Time','14:32:11','SID'):
    print ('Successfuly Inserted!')

But I failed to do it.I am not really sure is this the correct way to do it. Besides, I am getting this error:

line 24, in with cursor.execute(tsql,'2018/11/14','Time','14:32:11','SID'): Conversion failed when converting date and/or time from character string.

Similar [pyodbc-dataerror](https://stackoverflow.com/questions/37418527/pyodbc-dataerror-22018-22018-microsoftodbc-sql-server-driversql-serv). Please remove your **duplicate** code and show your `CREATE TABLE ...` instead. — stovfl, Nov 17 '18 at 10:16
Relevant [cant-insert-date-and-time-to-sql-server-via-pyodbc](https://stackoverflow.com/questions/24491421/cant-insert-date-and-time-to-sql-server-via-pyodbc) — stovfl, Nov 17 '18 at 10:22
@stovfl I think it's not only can't insert date/time data to table. The whole idea and python code have problem. I think my second python code above has other problem as well. By the way, thank for your info — Xavier, Nov 17 '18 at 10:55

score 0 · Answer 1 · answered Nov 17 '18 at 18:30

cursor.execute(tsql,'2018/11/14','Time','14:32:11','SID')

The second parameter value (third argument to the .execute method) is the string literal 'Time'. SQL Server is complaining that it cannot convert that string to a valid time value.

If you tweak your Arduino code to return

{'SID':1,'Time':'07:11:13'}

then you could just use

cursor.execute(tsql,'2018/11/14',Atd_InTime,'14:32:11',SID)

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

1 Answers1