利用python操作access，sql server数据库

本文主要介绍了python如何利用 ADO访问windows平台下的数据库，比如access,sql server.

(译者注：作者原文用的IDE是pythonwin,但我的pytonwin重装了两遍了就是用不起来，就算写个helloworld运行也崩掉，妈的，只好用IDLE了)

Table of Contents

 

1. Running MakePy
2. DSN
3. Opening a Recordset
4. Looking at the Recordset
5. Adding new Records
6. Database Table Information
7. Closing Connections
8. Using SQL with ADO
9. Getting the RecordCount
10. Common Questions and Answers about Python and ADO.

 

 

1. 首先要做的就是运行makepy组件。这不是必须的，但是它可以improves speed and makes life in the PythonWin IDE that much easier. 从pythonWin的菜单里选择com makepy Utility，然后选择 Microsoft ActiveX Data object 2.5 Library.
2. 下一步我们需要一个DSN（data source name）和一个connection 对象，对于access可以直接拷贝下面的字符串，对其他的数据库或者要设置一些高级选项，可以去[控制面板 | 管理工具 | 数据源 ]。在那里，我们可以建立一个系统DSN，或者把它（它只是一个文本文件）作为字符串拷贝进剪贴板，也可以建立一个DNS-less connection string。我们也可以在网上搜索其他数据库的DNS-less connection string，比如：sql server,access,foxPro,oracle, 还有 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)
   有了这些设置，我们访问数据库就是易如反掌了。

3. 下一步的任务是打开一个recordset。对于一些有趣的东西比如joins或者类似的，我们可以用select * from talbename 类型的声明，或者只用一个中括号把表明括起来。

   >>> rs = win32com.client.Dispatch(r'ADODB.Recordset')
   >>> rs_name = 'MyRecordset'
   >>> rs.Open('[' + rs_name + ']', conn, 1, 3)
   

    对于参数1和3，分别表示：adOpenKeyset and adLockOptimistic 。对他们的解释已经超出了本指南的范围，请参考相关资料。
4. 有了打开的recordset我们就可以遍历字段：                                                                                           >>> flds_dict = {}
   >>> for x in range(rs.Fields.Count):
   ...     flds_dict[x] = rs.Fields.Item(x).Name
   字段的类型和大小可以这样得到：                                                                                                       >>> print rs.Fields.Item(1).Type
    202 # 202 is a text field
   >>> print rs.Fields.Item(1).DefinedSize                                                                                       50  # 50 Characters

添加新的记录可以用insert语句，或者直接调用AddNew() or Update()方法：                                             >>> 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
        

        >>> conn.Close()
        

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

 

 

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.

To use SQL to INSERT or UPDATE data, use a Connection object directly.

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

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

 

 

 

Python编程语言的出现，带给开发人员非常大的好处。我们可以利用这样一款功能强大的面向对象开源语言来轻松的实现许多特定功能需求。比如Python操作Access数据库的功能实现等等。在Python操作Access数据库之前，首先,你应安装了Python和Python for Windows extensions。

Python操作Access数据库步骤之1、建立数据库连接

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)

Python操作Access数据库步骤之2、打开一个记录集

rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs_name = 'MyRecordset'#表名
rs.Open('[' + rs_name + ']', conn, 1, 3)

Python操作Access数据库步骤之3、对记录集操作

rs.AddNew()
rs.Fields.Item(1).Value = 'data'
rs.Update()

Python操作Access数据库步骤之4、用SQL来插入或更新数据

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

Python操作Access数据库步骤之5、遍历记录

rs.MoveFirst()
count = 0
while 1:
if rs.EOF:
break
else:
countcount = count + 1
rs.MoveNext()

注意：如果一个记录是空的，那么将指针移动到第一个记录将导致一个错误，因为此时recordcount是无效的。解决的方法是：打开一个记录集之前，先将Cursorlocation设置为3，然后再打开记录集，此时recordcount将是有效的。例如：

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

以上就是我们对Python操作Access数据库步骤的相关介绍。