What python object-type is compatible with the ms-access date/time field?-CodePudding

I have a piece of code that tries to insert a datetime.datetime into an ms-access datebase's date/time (not extended date/time) field, yet I keep getting the data type mismatch error message. So my question is, which type do I use for this?

I tried using this code

    print(type(timeCET))
    >>>
    datetime.datetime

and

    crsr.execute("insert into WeatherAnalisis(Time_UTC) values ('timeCET.timedate')")
    cnxn.commit()
    >>>>
    pyodbc.DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (-3030) (SQLExecDirectW)')

But it doesn't seem to work

CodePudding user response：

Whilst the original question does not specify where the variable timeCET originates from, once can see that it is a datetime from its type.

The problem seems to be one of matching types.

Given this, here is how to convert to a string:


import datetime as dt


# create a datetime
t = dt.datetime.utcnow()
print(t)
print(type(t))


# now convert to string
u = t.strftime("%m/%d/%Y, %H:%M:%S")
print(u)
print(type(u))

This is the output:

2022-03-26 17:00:52.998699
<class 'datetime.datetime'>
03/26/2022, 17:00:52
<class 'str'>

The original type (for variable t) was datetime.

This is now changed (for variable u) to string.

This can now be parsed into the database (or changed to a format that works).

CodePudding user response：

You directly included the name of your datetime object as a quoted literal in SQL statement. As a result, Access throws an error since your are trying to insert a literal string value 'timeCET.timedate' (the name and not value of your actual datetime variable) into datetime column.

Instead, consider SQL parameterization. (Do not concatenate as single quotes will not work to enclose dates in Access SQL).

# PREPARED STATEMENT WITH QMARK
sql = "insert into WeatherAnalisis (Time_UTC) values (?)"

# EXECUTE QUERY WITH BINDED PARAM
crsr.execute(sql, timeCET)
cnxn.commit()