uploading data with dates to MS Access database with pyodbc

Question

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.

Date is a reserved word, you need to escape it to use it as column name. — mata
– mata, Commented Jan 21, 2016 at 16:25
@mata that was it, all of the three versions above work once Date is renamed. Thanks. — tpoh
– tpoh, Commented Jan 21, 2016 at 17:11

Gord Thompson · Accepted Answer · 2016-01-21 17:26:23Z

3

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 ...

answered Jan 21, 2016 at 17:26

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aleyn · Accepted Answer · 2016-01-21 16:24:49Z

0

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#.

answered Jan 21, 2016 at 16:24

Aleyn

11 bronze badge

Collectives™ on Stack Overflow

uploading data with dates to MS Access database with pyodbc

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related