2

I am using Pyodbc and am trying to insert values into a Microsoft 2013 Access Database. However, everytime I run the code I get an error on the execute line:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)')

Does anyone have any ideas how to fix this? I have put my code below.

conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Database2.accdb;")
cur=conn.cursor()
sql="""INSERT INTO Invoices (LINE TYPE,INVOICE NUMBER) VALUES (?,?)"""
cur.execute(sql,("Line","41243"))
Air
  • 8,274
  • 2
  • 53
  • 88
Matt Owens
  • 23
  • 1
  • 3

2 Answers2

4

Columns with spaces in the name need to be surrounded with brackets:

sql="""INSERT INTO Invoices ([LINE TYPE],[INVOICE NUMBER]) VALUES (?,?)"""

If you have control over the table design, I'd consider removing the spaces from the column names, replacing them with underscores (LINE_TYPE) or using pascal case (LineType).

Bryan
  • 17,112
  • 7
  • 57
  • 80
2

The syntax error is in your SQL:

INSERT INTO Invoices (LINE TYPE,INVOICE NUMBER) VALUES (?,?)

I've yet to encounter a DBMS that allows you to reference columns whose names contain spaces without specially delimiting the column name. In Access SQL, as in SQL Server, the delimiters used are square brackets; so you would have to write:

INSERT INTO Invoices ([LINE TYPE], [INVOICE NUMBER]) VALUES (?,?)

The SQL standard uses double quotes, but most vendors aren't particularly bothered about conforming to the standard. See also: SQL standard to escape column names?

Community
  • 1
  • 1
Air
  • 8,274
  • 2
  • 53
  • 88