CODEWITHSUNDEEP
pythondatesql-insertms-access-2013pyodbc
tpoh
tpoh

Reputation: 361

uploading data with dates to MS Access database with pyodbc

I'm trying to insert data with a date to MS Access using the pyodbc library in Python. My table has 3 fields: Date (Date/Time), 1Y (Number), and 2Y (Number). I know the problem is not a connection issue since I'm able to insert successfully without the Date field. For example, this works:

cursor.execute("insert into test(1Y,2Y) values (?,?)",'3','4')

Now including the Date, I've tried:

cursor.execute("insert into test(Date,1Y,2Y) values (?,?,?)",'2010-01-01','3','4')
cursor.execute("insert into test(Date,1Y,2Y) values (?,?,?)",date(2010,1,1),'3','4')
cursor.execute("insert into test(Date,1Y,2Y) values (?,?,?)",'1/1/2010','3','4')

For the above examples with Date, I receive the following error: ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)')

I'm using Python 2.7, MS Access 2013, pyodbc 2.7. Thanks.

Upvotes: 4

Views: 1388

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123399

To use a reserved word like Date as a column or table name in Access SQL it must be enclosed in square brackets, e.g., 

INSERT INTO TableName ([Date]) VALUES ...

Upvotes: 3

Aleyn
Aleyn

Reputation: 1

This may not be applicable for Python if it handles qualifying fields for you, however, in some other languages, to insert Date values in Access tables you need to qualify them with # characters, i.e. #01/21/2016#.

Upvotes: 0

Related Questions