Python 跟 ActiveX Data Objects (ADO)

首先这一页主要参考Python and ActiveX Data Objects (ADO),但是这一页说是Bill Wilkinson的姊妹页,Bill的这一页是讨论有关Python跟Microsoft’s Data Access Objects (DAO)的,现在已经找不到这一页,但是如果你查ActiveX Data Objects (ADO)维基的话,你会注意到‘ADO被设计来继承微软早期的资料存取物件层,包括RDO (Remote Data Objects) 和DAO(Data Access Objects)。’,所以说好像也不用知道Python跟DAO的关系了,你就单看这一篇应该就够了,所以ㄚ琪试着了解、翻译并测试连至Access、MSSQL、MYSQL跟DB2看看,是不是可以Work。

原本的原文页打算提供一个使用Python透过微软的ActiveX Data Objects (ADO)来连结到MS Access 2000,但是我手上已经没有这个版本了,现在只有Microsoftc Office Access 2003 SP3测试,虽然现在的版本已经到了2010年了,哈哈,我们公司落后了七年,但是看起来应该不会有很大的差异才是,如果有再请你提出了,当然我还会测其他的资料库,不过Access优先。有几个原因你会想要这么做,ADO 较轻巧而且它可能比DAO还有点快,不像DAO那样它可以容易地用在其他的资料库(SQL Server、Oracle、MySQL等等),所以我才想在我有使用的资料库来做测试,它也可以跟XML及text文字档使用,以及任何其他的资料,而且微软支援ADO的时间也会比DAO久。
要让这一篇文章变得有意义,你应该稍微了解Python,而且你已安装了PythonWin IDE跟Windows扩充,有一篇相关的文章[python]找不到mfc71.dll你可以参考, 可能要有MS Access也会比较有用,如果你没有我还真不知道怎么写下去。


  1. 执行MakePy
  2. DSN
  3. 开启纪录集(Recordset)
  4. 看看纪录集
  5. 新增新的纪录
  6. 资料表资讯
  7. 关闭连结
  8. 使用SQL跟ADO
  9. 获取纪录笔数
  10. 关于Python跟ADO的常见问题解答。

  1. 第一件要做的事就是执行makepy应用程式,这不是必须的,但是它可以改善速度并且让我们在PythonWin IDE中生活较容易,可以从PythonWin工具选单中选择COM Makepy Utility来做,我看看有没有图可以贴,哈哈有:
  2. 2010-11-05_114819
  3. 在公司的阿公级的电脑上跑不动说?太神奇了,风扇拼命大声地转,放弃了,只好转进到Server上跑,Server是Windows 2003的Server版,安装pywin32-214.win32-py2.5.exe试跑看看,ok!搞定了!
  4. 2010-11-05_133242
  5. 有看到一些讯息吧,应该是成功了!先看到这,无法等待的,就先看下面的英文资讯吧!

  1. Next you will need a Data Source Name [DSN] and a connection object. [I tend to use DSN-Less connection strings (as opposed to system DSNs as it improves performance and makes code portable)]
    For MS Access you can just copy the DSN below. For other databases, or for more advanced options like passwords and the like go to [Control Panel | Administrative Tools | Data Sources (ODBC)]. From there you can set up a system DSN. You can then either use it as a system DSN, or copy it (it is just a text file) into a string and make a DSN-Less connection string. You might also do a search for DSN-Less connection strings on the web. Better yet, here are some samples for SQL Server, Access, FoxPro, Oracle , Oracle, Access, SQL Server, and finally MySQL.

  2.         >>> import win32com.client
            >>> conn = win32com.client.Dispatch(r'ADODB.Connection')
            >>> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
            >>> conn.Open(DSN)

    With these set up access to the database is fairly straight forward.

  3. The next task is to open a Recordset. For fancy stuff like joins and whatnot, you can use SQL SELECT * FROM table_name type statements, or just use the table name surrounded by brackets []
            >>> rs = win32com.client.Dispatch(r'ADODB.Recordset')
            >>> rs_name = 'MyRecordset'
            >>> rs.Open('[' + rs_name + ']', conn, 1, 3)

    [The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic and are well beyond the scope of this tutorial. I typically use these settings as defaults, but your mileage may vary. Pick up a book on ADO for details.]

  4. With the recordset open you can examine the field names/indices like …
            >>> flds_dict = {}
            >>> for x in range(rs.Fields.Count):
            ...     flds_dict[x] = rs.Fields.Item(x).Name

    A field’s type and size are returned by …

            >>> print rs.Fields.Item(1).Type
            202 # 202 is a text field
            >>> print rs.Fields.Item(1).DefinedSize
            50  # 50 Characters

  5. Actual work can be done as well. Adding new records to recordsets can be done both with INSERT INTO (see below) statements, or directly using the AddNew() and Update() methods.
            >>> rs.AddNew()
            >>> rs.Fields.Item(1).Value = 'data'
            >>> rs.Update()

    These values can be also be returned.

            >>> x = rs.Fields.Item(1).Value
            >>> print x

    So, if one wants to create a new Record, and know what number an AutoNumber field has generated for it without having to query the database …

            >>> rs.AddNew()
            >>> x = rs.Fields.Item('Auto_Number_Field_Name').Value
            # x contains the AutoNumber
            >>> rs.Fields.Item('Field_Name').Value = 'data'
            >>> rs.Update()

  6. You can get a list of the Tables in a Database using ADO.
            >>> oCat = win32com.client.Dispatch(r'ADOX.Catalog')
            >>> oCat.ActiveConnection = conn
            >>> oTab = oCat.Tables
            >>> for x in oTab:
            ...     if x.Type == 'TABLE':
            ...         print x.Name

  7. Close the connection. Notice that to close this connection the ‘C’ is upper case, whereas to close a file opened with python the ‘c’ is lower case.
            >>> conn.Close()

  8. To use SQL to INSERT or UPDATE data, use a Connection object directly.
            >>> conn = win32com.client.Dispatch(r'ADODB.Connection')
            >>> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
            >>> sql_statement = "INSERT INTO [Table_Name]
                                 ([Field_1], [Field_2]) VALUES ('data1', 'data2')"
            >>> conn.Open(DSN)
            >>> conn.Execute(sql_statement)
            >>> conn.Close()

  9. Here is a last example that often seems to be a sticking point with ADO. Generally, if one wants to get the RecordCount of a table, one must move through all of the records counting them along the way like …
            >>> # See example 3 above for the set-up to this
            >>> rs.MoveFirst()
            >>> count = 0
            >>> while 1:
            ...     if rs.EOF:
            ...         break
            ...     else:
            ...         count = count + 1
            ...         rs.MoveNext()

    Aside from being horribly inefficient, if the recordset is empty, moving to the first record will generate an error. ADO provides a way to correct this. Before opening the recordset, set the CursorLocation to 3. After opening the recordset, the recordcount will be available.

            >>> rs.Cursorlocation = 3 # don't use parenthesis here
            >>> rs.Open('SELECT * FROM [Table_Name]', conn) # be sure conn is open
            >>> rs.RecordCount # no parenthesis here either

    [Again, the 3 is a constant.]

This really just scratches the surface of ADO, but it should help getting connected from Python. For anything more than just simple database scripting it is worth looking into the object model. Here are some links that might be helpful.