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

Is data[i] being treated as a string? What happens if you specifically cast it as a int/double when you set rs.Fields[i].Value?

Also, what happens when you print out the contents of rs.Fields[i].Value after it is set?

Answer 2

Not a complete answer yet, but it appears to be a problem during the update. I've added some further debugging code in the insertion process which generates the following (example of a single row being updated):

Inserted into field ID (type 3) insert value 1, field value now 1.
Inserted into field TextField (type 202) insert value u'Blah', field value now Blah.
Inserted into field Numbers (type 5) insert value 55.0, field value now 55.0.
After update: [0, u'Blah', 55.0]

The last value in each "Inserted..." line is the result of calling rs.Fields[i].Value before calling rs.Update(). The "After..." line shows the results of calling rs.Fields[i].Value after calling rs.Update().

What's even more annoying is that it's not reliably failing. Rerunning the exact same code on the same records a few minutes later generated:

Inserted into field ID (type 3) insert value 1, field value now 1.
Inserted into field TextField (type 202) insert value u'Blah', field value now Blah.
Inserted into field Numbers (type 5) insert value 55.0, field value now 55.0.
After update: [1, u'Blah', 2.0]

As you can see, results are reliable until you commit them, then... not.

Answer 3

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...