【脚本语言系列】关于Python数据库访问ActiveXDataObject，你需要知道的事

如何使用ActiveXDataObject

# -*- coding: utf-8 -*-
import win32com.client

conn = win32com.client.Dispatch("ADODB.Connection")
dsn = "Provider = OraOLEDB.Oracle;PLSQLRSet=1;Password=tiger;UserID=scott;DataSource=ORCL"
conn.Open(dsn)
rs = win32com.client.Dispatch("ADODB.Recordset")
sql = '''select a.ename NAME,a.job JOB, a.sal SALARY, c.dname DNAME, b.ename MANAGE
        from emp a, emp b, dept c
        where a.sal > 2500
        and a.deptno = c.deptno
        and a.mgr = b.empno(+)
        order by a.sal'''
rs.Open(sql, conn)
rs.MoveFirst()
li = list()
while not rs.EOF:
    d = dict()
    for x in range(rs.Fields.Count):
        key = rs.Field.Item(x).Name
        value = rs.Field.Item(x).Value
        d.setdefault(key, value)
    li.append(d)
    print rs.Fields("SALARY").Value, rs.Fields("JOB").Value,\
            rs.Fields("DNAME").Value, rs.Fields("NAME").Value,\
            rs.Fields("MANAGE").Value
    rs.MoveNext()
for d in li:
    for key in d.keys():
        print key.encode("gb2312"), d.get(key),
    print
conn.Close()

----------------------------------------------------------------------

com_error                            Traceback (most recent call last)

<ipython-input-2-0b996462d598> in <module>()
      4 conn = win32com.client.Dispatch("ADODB.Connection")
      5 dsn = "Provider = OraOLEDB.Oracle;PLSQLRSet=1;Password=tiger;UserID=scott;DataSource=ORCL"
----> 6 conn.Open(dsn)
      7 rs = win32com.client.Dispatch("ADODB.Recordset")
      8 sql = '''select a.ename NAME,a.job JOB, a.sal SALARY, c.dname DNAME, b.ename MANAGE


c:\python27\lib\site-packages\win32com\client\dynamic.pyc in Open(self, ConnectionString, UserID, Password, Options)


c:\python27\lib\site-packages\win32com\client\dynamic.pyc in _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args)
    285 
    286         def _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args):
--> 287                 result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
    288                 return self._get_good_object_(result, user, resultCLSID)
    289 


com_error: (-2147352567, '\xb7\xa2\xc9\xfa\xd2\xe2\xcd\xe2\xa1\xa3', (0, u'ADODB.Connection', u'\u672a\u627e\u5230\u63d0\u4f9b\u7a0b\u5e8f\u3002\u8be5\u7a0b\u5e8f\u53ef\u80fd\u672a\u6b63\u786e\u5b89\u88c5\u3002', u'C:\\Windows\\HELP\\ADO270.CHM', 1240655, -2146824582), None)

什么是ActiveXDataObject

ActiveX Data Object(ADO)是微软提供的连接数据库的接口。ADO的访问速度比DAO更快。
ADO可以应用于文本文件,Excel,Word等文件和Oracle,SQL Server, Access, MySQL等文件。