问题通过Python使用ADO将数据插入MS Access数据库

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

[编辑2：以下答案中的更多信息和调试...]

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

我正在编写一个python脚本，将MS Access数据库导出为一系列文本文件，以便进行更有意义的版本控制（我知道 - 为什么Access？为什么我不使用现有的解决方案？让我们说限制不是技术性的）。

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.

我已经通过comtypes库使用ADO和ADOX成功导出了数据库的全部内容和结构，但是我在重新导入数据时遇到了问题。

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?

除了将数值（double和int）作为零插入外，一切正常。关于问题是否与我的代码，eval，comtypes或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.

编辑：我已经修复了插入数字的问题 - 将它们转换为字符串（！）似乎解决了double和integer字段的问题。

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?

但是，我现在有一个以前被上面模糊的不同问题：无论数据类型如何，每一行中的第一个字段都被设置为0 ...任何想法？

3 个解决方案

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

现在我只需要研究一下原因。希望这个问题可以节省几个小时......

#2

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?

data [i]被视为字符串吗？如果在设置rs.Fields [i] .Value时专门将其强制转换为int / double会发生什么？

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

另外，在设置后打印出rs.Fields [i] .Value的内容会发生什么？

#3

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

每个“Inserted ...”行中的最后一个值是在调用rs.Update（）之前调用rs.Fields [i] .Value的结果。 “After ...”行显示调用rs.Update（）后调用rs.Fields [i] .Value的结果。

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.

如您所见，在您提交结果之前结果是可靠的，然后......不是。