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，但是我手上已經沒有這個版本了，現在只有Microsoft© 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也會比較有用，如果你沒有我還真不知道怎麼寫下去。

第一件要做的事就是執行makepy應用程式，這不是必須的，但是它可以改善速度並且讓我們在PythonWin IDE中生活較容易，可以從PythonWin工具選單中選擇COM Makepy Utility來做，我看看有沒有圖可以貼，哈哈有：
在公司的阿公級的電腦上跑不動說？太神奇了，風扇拼命大聲地轉，放棄了，只好轉進到Server上跑，Server是Windows 2003的Server版，安裝pywin32-214.win32-py2.5.exe試跑看看，ok！搞定了！
有看到一些訊息吧，應該是成功了！先看到這，無法等待的，就先看下面的英文資訊吧！

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.

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

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

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

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
        'data'

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

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

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

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

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
        186

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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoobjmod.asp

http://www.activeserverpages.ru/ADO/dadidx01_1.htm

感謝你看到這裡，很快就可以離開了，但最好的獎勵行動就是按一下幫我分享或留言，感恩喔～

點我分享到Facebook

一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Python 跟 ActiveX Data Objects (ADO)

感謝你看到這裡，很快就可以離開了，但最好的獎勵行動就是按一下幫我分享或留言，感恩喔～

精選相關文章

發佈留言取消回覆

Python 跟 ActiveX Data Objects (ADO)

感謝你看到這裡，很快就可以離開了，但最好的獎勵行動就是按一下幫我分享或留言，感恩喔～

精選相關文章

發佈留言 取消回覆

發佈留言取消回覆