Problem Inserting data into MS Access database using ADO via Python

Question

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

I am writing a python script to export MS Access databases to a number of text files to provide more meaningful version control (I know why Access? Why do not I use existing solutions? The restrictions are not technical in nature).

I have successfully exported the full contents and structure of the database using ADO and ADOX through the comtypes library, but I have a problem with re-importing the data.

I export the contents of each table to a text file with a list in each row, for example:

[-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 specified 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 numeric values ​​(double and int) are inserted as zeros. Any ideas on whether the issue is related to my code, eval, comtypes or ADO?

Edit: I fixed the problem with introducing numbers - to distinguish them, since strings (!) Seem to solve the problem for both two and integer fields.

However, I now have another problem that was previously hidden above: the first field in each row is set to 0 regardless of the data type ... Any ideas?

Answer 1

And found the answer.

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

Must be:

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

Now I just need to find out why. I just hope this question saves someone else a few hours ...

Answer 2

Is data[i] handled like a string? What happens if you configured it as int / double when installing rs.Fields[i].Value ?

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

Answer 3

Not a complete answer, but it seems to be a problem during the upgrade. I added additional debugging code during the insertion process, which generates the following (an example of updating a single line):

 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 on each line of "Inserted ..." is the result of calling rs.Fields [i] .Value before calling rs.Update (). The line "After ..." shows the results of calling rs.Fields [i]. The value after rs.Update () is called.

What is even more annoying is that it does not fail reliably. Re-running the same code in the same entries after a few minutes was 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, the results are reliable until you fix them, then ... no.