Coin163

首页 > 利用python操作access,sql server数据库 - 博客频道 - CSDN.NET

利用python操作access,sql server数据库 - 博客频道 - CSDN.NET

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

相关推荐:在Eclipse中连接SQL Server 2008数据库 - 博客频道 - CSDN.NET

本文转载自:Eclipse连接SQL Server 2008数据库 在连接数据库之前必须保证SQL Server 2008是采用SQL Server身份验证方式而不是windows身份验证方式。如果在安装时选用了后者,则重新设置如下: http://blog.163.com/jackie_howe/blog/static/19949134720122261

 来源: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 ...

相关推荐:[置顶] 黑马程序员 将Access数据库里的数据导入Sql server数据库里

---------------------- Windows Phone 7手机开发、 .Net培训、期待与您交流! ----------------------     前几天在黑马论坛上遇到一个网友求手机IP地址的数据,我便回复,网上如果找不到的话可以加我QQ,今晚下班回来,打开QQ给他传过去~结 果让他想不到,

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





     

    原文

     来源:http://john2007.javaeye.com/blog/309023   本文主要介绍了python如何利用 ADO访问windows平台下的数据库,比如access,sql server. (译者注:作者原文用的IDE是pythonwin,但我的pytonwi

    ------分隔线----------------------------