CODEWITHSUNDEEP
pythonwindowsms-accesspyodbc
user12345
user12345

Reputation: 417

python Pyodbc, cant create a field named "Date" in the table

The problem is that I can't create a field named Date (I think because its a type) Any ideas how to do that?

from pyodbc import connect
# database connect
conn = connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ=test.mdb')
cursor = conn.cursor()
# sql query execute
query = "create table MyTable(name varchar(30), age integer , Date date)"
cursor.execute(query)
# commit changes             
conn.commit()
conn.close()

Error:

Traceback (most recent call last):
  File "\Path\to\myscript\test.py", line 9, in <module>
    cursor.execute(query)
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition. (-3553) (SQLExecDirectW)')

Environment: Windows 7 64bit, Python 2.7 pyodbc-3.0.6.win-amd64-py2.7

Upvotes: 2

Views: 1265

Answers (3)

Bryan
Bryan

Reputation: 17693

DATE is a reserved word in Access (and other software).

Try wrapping the Date column name with square brackets [], or better yet, come up with a different column name.

...
query = "create table MyTable(name varchar(30), age integer , [Date] date)"
...

Upvotes: 6

Graeme Perrow
Graeme Perrow

Reputation: 57238

You're right, you can't create a column with the same name as type. Some RDBMS's will allow you to do this if you quote it, i.e. "..age integer, "Date" date)" (or as others have said, backticks or square brackets) but then you have to quote it in all your queries as well. Better to avoid that.

Note that this isn't a python problem, it's a problem with the database server (which appears to be MS Access). (And to be accurate, it's not a problem with the database server, that's just how it works. It just has nothing to do with python.)

Upvotes: 1

sean
sean

Reputation: 3985

Surround the name in backticks the ` symbol. But, I would highly suggest changing the name to something else to prevent typos from happening.

Upvotes: 3

Related Questions