INSERT INTO Access DB with Python/pyodbc

ms-access, odbc, pyodbc, python, sql

Solution

Access generally accepts either `'` or `"` as text delimiters in SQL statements. However, I seem to recall the Access ODBC driver has problems with `"`, at least sometimes.

So try it with single quotes.

SQL = """INSERT INTO AssetDetails
    (
        [Reporting Account Number],
        [As Of Date],
        [Asset Type],
        [Security Description 1],
        [Shares/Par],
        [Base Price],
        [Base Cost],
        CUSIP,
        Ticker,
        ISIN,
        SEDOL
    ) 
VALUES
    (
        'ABCD99020002',
        '1/31/2010',
        'CASH & CASH EQUIVALENTS',
        'INTEREST RECEIVABLE',
        '0.000',
        '1.00',
        '1,171,069.04',
        '',
        '',
        '',
        ''
    );"""

My hunch is that either that will work or you will get a different error.

Problem

I'm trying to insert some csv data into an Access 2007 DB using Python/pyodbc. I tested out a working append query in Access that selects the headers and one row of values. ``` INSERT INTO AssetDetails ( [Reporting Account Number], [As Of Date], [Asset Type], [Security Description 1], [Shares/Par], [Base Price], [Base Cost], CUSIP, Ticker, ISIN, SEDOL ) VALUES ("ABCD99020002", "1/31/2010", "CASH & CASH EQUIVALENTS", "INTEREST RECEIVABLE", "0.000", "1.00", "1,171,069.04", "", "", "", ""); ``` Running this query in pyodbc yields: ``` ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Invalid bracketing of name '[0.000]'. (-1002) (SQLExecDirectW)") ``` To address this problem, I thought it was possible that it was the quotes around the numbers that was causing the trouble. I moved on to the following query: ``` SQL = '''INSERT INTO AssetDetails([Reporting Account Number], [As Of Date], [Asset Type], [Security Description 1], [Shares/Par], [Base Price], [Base Cost], CUSIP, Ticker, ISIN, SEDOL) VALUES ("AKPF99020002", "1/31/2010", "CASH & CASH EQUIVALENTS", "INTEREST RECEIVABLE", 0.00, 1.00, 1171069.04, "", "", "", "");''' ``` While I'm on it, I might as well removed the comma between every 3 digits on the 1171069.04 number. This gives an error ``` Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5. (-3010) (SQLExecDirectW)') ``` I did some digging and it appears that this error occurs when there's nonexistent columns being referenced. Perhaps this indicates bad column formatting? So I stripped things down to a very simple query to see whether it is possible to `INSERT` anything into my Access table, with just one field (none of the fields are required). ``` cur.execute('INSERT INTO AssetDetails("SEDOL") VALUES ("123412")') ``` This also yields ``` Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)') ``` I've also tried the above without quotes around SEDOL, or with brackets... I'm slowly going insane... any ideas?

Original source