Problem Inserting data into MS Access database using ADO via Python

Question

[Edit 2: More information and debugging in answer below...]

I'm writing a python script to export MS Access databases into a series of text files to allow for more meaningful version control (I know - why Access? Why aren't I using existing solutions? Let's just say the restrictions aren't of a technical nature).

I've successfully exported the full contents and structure of the database using ADO and ADOX via the comtypes library, but I'm getting a problem re-importing the data.

I'm exporting the contents of each table into a text file with a list on each line, like so:

[-9, u'No reply']
[1, u'My home is as clean and comfortable as I want']
[2, u'My home could be more clean or comfortable than it is']
[3, u'My home is not at all clean or comfortable']

And the following function to import the said file:

import os
import sys
import datetime
import comtypes.client as client
from ADOconsts import *
from access_consts import *

class Db:
    def create_table_contents(self, verbosity = 0):
        conn = client.CreateObject("ADODB.Connection")
        rs = client.CreateObject("ADODB.Recordset")
        conn.ConnectionString = self.new_con_string
        conn.Open()
        for fname in os.listdir(self.file_path):
            if fname.startswith("Table_"):
                tname = fname[6:-4]
                if verbosity > 0:
                    print "Filling table %s." % tname
                conn.Execute("DELETE * FROM [%s];" % tname)
                rs.Open("SELECT * FROM [%s];" % tname, conn,
                        adOpenDynamic, adLockOptimistic)
                f = open(self.file_path + os.path.sep + fname, "r")
                data = f.readline()
                print repr(data)
                while data != '':
                    data = eval(data.strip())
                    print data[0]
                    print rs.Fields.Count
                    rs.AddNew()
                    for i in range(rs.Fields.Count):
                        if verbosity > 1:
                            print "Into field %s (type %s) insert value %s." % (
                                rs.Fields[i].Name, str(rs.Fields[i].Type),
                                data[i])
                        rs.Fields[i].Value = data[i]
                    data = f.readline()
                    print repr(data)
                    rs.Update()
                rs.Close()
        conn.Close()

Everything works fine except that numerical values (double and int) are being inserted as zeros. Any ideas on whether the problem is with my code, eval, comtypes, or ADO?

Edit: I've fixed the problem with inserting numbers - casting them as strings(!) seems to solve the problem for both double and integer fields.

However, I now have a different issue that had previously been obscured by the above: the first field in every row is being set to 0 regardless of data type... Any ideas?

Answer 1

And found an answer.

    rs = client.CreateObject("ADODB.Recordset")

Needs to be:

    rs = client.CreateObject("ADODB.Recordset", dynamic=True)

Now I just need to look into why. Just hope this question saves someone else a few hours...