Using pyodbc to insert rows into a MS Access MDB, how do I escape the paramaters?

Question

Hi,

I'm using pyodbc to talk to a legacy Access 2000 .mdb file.

I've got a cursor, and am trying to execute this:

c.execute("INSERT INTO [Accounts] ([Name], [TypeID], [StatusID], [AccountCat], [id]) VALUES (?, ?, ?, ?, ?)", [u'test', 20, 10, 4, 2])

However, doing so results in

pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented  (106) (SQLBindParameter)')

I understand I can simply change the question marks to the literal values, but in my experience proper escaping of strings across databases is... not pretty.

PHP and mysql team up to bring mysql_real_escape_string and friends, but I can't seem to find pyodbc's function for escaping values.

If you could let me know what the recommended way of inserting this data (from python) is, that'd be very helpful. Alternatively, if you have a python function to escape the odbc strings, that would also be great.

Thanks for the help.

Answer 1

Well, apparently this is just Access yelling at me again.

Previously, I had been inserting python integers. Now, I have tried to insert python longs.

When inserting a long as a parameter, I get

pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented  (106) (SQLBindParameter)')

Adding this code to my function just before calling the execute fixes it:

    for k, v in new.items():
        new[k] = int(v) if isinstance(v, long) else v

IOW, replacing longs with ints works fine. Go Access, what a descriptive error.

Strictly speaking, I don't think this is a bug in pyodbc, but rather a gotcha when dealing with this particular driver. Thanks for the help anyway.