[Tutor] pyodbc/date values in MS Access

[Peter Otten]

Albert-Jan Roskam wrote:

> Hi,
> 
> I'm using pyodbc (Python 2.5) to insert records in an MS Access database.
> For security reasons, question marks should be used for string replacement
> [*]. The standard %s would make the code vulnerable to sql code injection.
> Problem is, string replacement in the Good Way somehow doesn't work when
> the values are dates. Below, snippet #1 does not work (Access says the
> inserted value is not consistent with the defined datatype), but #2 does.
> I tried various other ways (ie. DateValue, CDate, etc.) but none of them
> works. Is there a solution for this?
> 
> [*] see http://code.google.com/p/pyodbc/wiki/GettingStarted --> under
> 'Parameters'
> 
> ### 1
> sql = "INSERT INTO tblSomeTable (myDate) VALUES (?);"
> cursor.execute(sql, "#01/01/2010#")


(1) Try providing the date in ISO format "yyyy-mm-dd"

"2010-01-01"

or (even better if supported) as a date value

from datetime import date
date(2010, 1, 1)

(2) Wrap the value into a tuple which I think is required by the Python 
DBAPI.

cursor.execute(sql, ("2010-01-01",))
cursor.execute(sql, (date(2010, 1, 1),))

Peter