Replicating .accdb database tables with python pyodbc
Question: Novice python user here. There are other ways to achieve this, but I believe
this is my best option. I maintain an MS Access 2007 database (.accdb) that is
used on offline TabletPCs in the field to collect data. When arriving back at
the office, the user can re-connect to the server. I'm trying to utilize the
pyodbc module to loop through the tables and rows and insert records from the
offline field database into the server "master" database. The selection part
of the script appears to be working to grab the records in the offline field
database and put them into a dictionary for later use. Recommendations based
on the original post now has the insert part looping through the dictionary
and creating parameter-based sql for inserting the records. However, the code
throws the following error after two records are inserted into the first table
in the loop. The next sql string is for the next table - so all records are
inserted into the first table correctly and the error occurs when moving to
the next table.

    
    
    Error: ('HY010', '[HY010] [Microsoft][ODBC Driver Manager] Function sequence error (0) (SQLFetch)')
    

I read error information here but don't know what to make of it:
<http://msdn.microsoft.com/en-
us/library/windows/desktop/ms712424(v=vs.85).aspx>

    
    
    import pyodbc
    
    otherDbaseDict = {}
    
    connOtherDbase = pyodbc.connect(("Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
                                     "DBQ=C:\\other.accdb;"))
    otherDbaseTables = connOtherDbase.cursor().tables()
    
    counter = 0
    
    for tblOther in otherDbaseTables:
        if tblOther.table_name.startswith("tbl"): #ignores MS sys tables
            nameOther = tblOther.table_name
            cursor = connOtherDbase.cursor()
            selectSQL = 'SELECT * FROM {}'.format(nameOther) #generate SQL select syntax
            cursor.execute(selectSQL)
            rows = cursor.fetchall()
            for row in rows:
                counter = counter + 1 #counter digit used to create unique key, since table names repeat
                otherDbaseDict.update({nameOther+str(counter):row})
    
    connMainDbase = pyodbc.connect(("Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
                                    "DBQ=C:\\main.accdb;"))
    mainDbaseTables = connMainDbase.cursor().tables()
    
    #beargle2
    for tblMain in mainDbaseTables:
        if tblMain.table_name.startswith("tbl"):
            nameMain = tblMain.table_name
            # get all column names with list comprehension
            columns = [row.column_name for row in cursor.columns(table=nameMain)]
            for k, v in otherDbaseDict.iteritems():
                if nameMain in k:
                    # build dynamic sql
                    sql = 'INSERT into {0}({1}) values ({2})'
                    # add question mark placeholders, one for each column
                    # value to insert
                    sql = sql.format(nameMain, ','.join(columns),
                                     ','.join(len(columns) * '?'))
                    #print sql
                    cursor = connMainDbase.cursor()
                    # execute parameterized insert
                    cursor.execute(sql, v)
                    connMainDbase.commit()
    

Any pointers on ways to reconcile the error? Was just thinking, is this a
cursor issue? Do I need to "reset/refresh" or whatever it may be called after
each connMainDbase.commit() or before switching tables? Execute dies once it
finishes with the first table. Looking into it but comments welcome...


Answer: The ProgrammingError is occuring because you have both single and double
quotes around the `insertSQL` string.

The reason you have only one field is that you are still within the `for` loop
block that is adding field names to the list. You need to unindent before
constructing the `insertSQL` string, so your last few lines should be:

    
    
    for row in cursor.columns(table=nameMain):
        fieldName = str(row.column_name)
        fields.append(fieldName)
    insertSQL = 'INSERT into {0}({1}) values ({2})'.format(nameMain,fields,v)
    cursor.execute(insertSQL)
    connMainDbase.commit()
    

However, that is still not going to give you valid SQL because you have your
field names within single quotes and square brackets, and double parentheses
around your values. I think the simplest way to fix this is to convert the
list & tuple to strings first:

    
    
    insertSQL = 'INSERT into {0}({1}) values ({2})'.format(nameMain,', '.join(fields),', '.join(v))
    

Finally, it's best practice to pass your values to the `execute` method as
parameters so that you use a parameterized query (it might not matter a great
deal in your use case, but you might as well follow best practice anyway). To
do this you need to have ? in place of each value that needs to be added to
the query which you can do by replacing `' ,'.join(v)` with `'
,'.join('?'*len(v))` and passing the `v` tuple as the second argument to
`execute`.

You should end up with:

    
    
    for row in cursor.columns(table=nameMain):
        fieldName = str(row.column_name)
        fields.append(fieldName)
        insertSQL = 'INSERT into {0}({1}) values ({2})'.format(nameMain,', '.join(fields),', '.join('?'*len(v)))
    cursor.execute(insertSQL, v)
    connMainDbase.commit()
    

