CODEWITHSUNDEEP
pythonms-access
Bernardo Cascao
Bernardo Cascao

Reputation: 25

Syntax error when inserting into 2003 MDB file with Pyodbc

I am trying to write 2003 mdb files from scratch. I already have a file with the tables and column names (I have 112 columns). In my attempt I read lines from a pandas DataFrame (named sections in my code) and append those lines to the mdb file. But, when using the pyodbc INSERT INTO syntax it gave me this error:

ProgrammingError: ('42000', "[42000] [Microsoft][Driver ODBC Microsoft Access] Expression syntax error 'Equatorial-TB-BG-CA_IRI-1.0_SNP-1.0_ACA-0_ESAL-1000'. (-3100) (SQLExecDirectW)")

here is my code:

for k in range(len(sections)):
    cols = tuple(list(sections.columns))
    vals = tuple(list(sections.iloc[k]))
    action = 'INSERT INTO SECTIONS {columns} VALUES {values}'.format(columns = str(cols).replace("'",""), values = str(vals).replace("'",""))
    cursor.execute(action)
    conn.commit()

Does anyone know why I am having this kind of problem?

Upvotes: 0

Views: 127

Answers (1)

Parfait
Parfait

Reputation: 107567

Actually, this is not an Access specific error but a general SQL error where your string literals are not properly enclosed with quotes. Therefore, the Access engine assumes they are named fields further complicated by the hyphens where engine assumes you are running a subtraction expression.

To demonstrate the issue, see below filling in for your unknown values. Notice the string items passed in VALUES are not quoted:

sections_columns = ['database', 'tool']
cols = tuple(list(sections_columns))

sections_vals = ['ms-access', 'pandas']
vals = tuple(list(sections_vals))

action = 'INSERT INTO SECTIONS {columns} VALUES {values}'.\
          format(columns = str(cols).replace("'",""), values = str(vals).replace("'",""))

print(action)
# INSERT INTO SECTIONS (database, tool) VALUES (ms-access, pandas)

Now, you could leave in the single quotes you replace in str(vals):

action = 'INSERT INTO SECTIONS {columns} VALUES {values}'.\
          format(columns = str(cols).replace("'",""), values = str(vals))

print(action)
# INSERT INTO SECTIONS (database, tool) VALUES ('ms-access', 'pandas')

But even better, consider parameterizing the query with qmark placeholders and passing the values as parameters (second argument of cursor.execute(query, params)). This avoids any need to quote or unquote string or numeric values:

# MOVED OUTSIDE LOOP AS UNCHANGING OBJECTS
cols = tuple(sections.columns)            # REMOVED UNNEEDED list()
qmarks = tuple(['?' for i in cols])       # NEW OBJECT

action = 'INSERT INTO SECTIONS {columns} VALUES {qmarks}'.\
          format(columns = str(cols).replace("'",""), qmarks = str(qmarks))
# INSERT INTO SECTIONS (col1, col2, col3, ...) VALUES (?, ?, ?...)

for k in range(len(sections)):        
    vals = list(sections.iloc[k])         # REMOVED tuple()

    cursor.execute(action, vals)          # EXECUTE PARAMETERIZED QUERY
    conn.commit()

Even much better, avoid any looping with executemany of DataFrame.values.tolist() using a prepared statement:

# PREPARED STATEMENT
cols = tuple(sections.columns)
qmarks = tuple(['?' for i in cols])

action = 'INSERT INTO SECTIONS {columns} VALUES {qmarks}'.\
          format(columns = str(cols).replace("'",""), qmarks = str(qmarks))

# EXECUTE PARAMETERIZED QUERY
cursor.executemany(action, sections.values.tolist())   
conn.commit()

Upvotes: 2

Related Questions