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

 来源：http://john2007.javaeye.com/blog/309023

 

本文主要介绍了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.

Python代码

       >>> 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 类型的声明，或者只用一个中括号把表明括起来。

Python代码

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