INSERT INTO Access DB with Python/pyodbc

Question

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?

Answer 1

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.