Chinaunix首页 | 论坛 | 博客
  • 博客访问: 476816
  • 博文数量: 148
  • 博客积分: 4424
  • 博客等级: 上校
  • 技术积分: 1211
  • 用 户 组: 普通用户
  • 注册时间: 2010-08-25 21:50
文章分类

全部博文(148)

文章存档

2012年(89)

2011年(20)

2010年(39)

分类: Python/Ruby

2012-01-14 12:46:27

原文链接:http://www.cnblogs.com/iLdf/archive/2008/07/18/1246363.html

今天学习用Python访问数据库,以前用ADO习惯了,所以先找个封装了ADO的模块来试试。
adodbapi是用python封装ADO的数据库访问模块,adodbapi接口完全符合Python DB-API2.0规范,简单易用。
以下是自己学习过程中写的一个例程,使用了查询、建表、插入数据、执行存储过程等功能,基本上涵盖了日常数据库编程所需的功能。

#coding=utf-8

import adodbapi

class DBTestor:
    
def __init__(self):
         self.conn
= None
        
    
def __del__(self):
        
try:
             self.conn.close()
        
except:
            
pass
        
    
def connectDB(self, connectString):
         self.conn
= adodbapi.connect(connectString)
        
    
def closeDB(self):
         self.conn.close()
        
    
def fielddict(self, cursor):
         dict
= {}
         i
= 0
        
for field in cursor.description:
             dict[field[0]]
= i
             i
+= 1
        
return dict
    
    
def testCommand(self):
         u
"测试执行SQL命令,及参数、事务"
         cursor
= self.conn.cursor()
         sql
= """if exists (select * from sysobjects where id = object_id(N'Demo_Table') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
                     Drop Table Demo_Table;
                 CREATE TABLE Demo_Table (
                    ID int IDENTITY (1, 1) NOT NULL ,
                    Name varchar(50) NOT NULL Default('')
                    PRIMARY KEY   CLUSTERED
                    (
                        [ID ]
                    )
                );
"""
         cursor.execute(sql)

         sql
= """INSERT INTO Demo_Table (Name) VALUES (?);"""
         cursor.execute(sql, (
"jame",))
         sql
= """INSERT INTO Demo_Table (Name) VALUES (?);"""
         cursor.execute(sql, (
"jame2",))

         sql
= """SELECT @@Identity;"""
         cursor.execute(sql)
        
print "Inserted new record's ID = %s" % cursor.fetchone()[0]
        
         cursor.close()
        
        
#默认对数据库进行修改后必须要提交事务,否则关闭数据库时会回滚
         self.conn.commit()

    
    
def testQuery(self):
         u
"测试查询功能,通过序号和字段名读取数据"
         cursor
= self.conn.cursor()
         cursor.execute(
"SELECT * FROM authors")
        
try:
             fields
= self.fielddict(cursor)
             row
= cursor.fetchone()
            
while row != None:
                
print "%s: %s %s" % (row[0], row[fields['au_fname']], row[fields['au_fname']])
                 row
= cursor.fetchone()                    
        
finally:
             cursor.close()
            

    
def testStoreProc(self):
         u
"测试存储过程功能"
         cursor
= self.conn.cursor()
         sql
= """if exists (select * from sysobjects where id = object_id(N'insert_data_demo') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                     Drop Procedure insert_data_demo;
"""
         cursor.execute(sql)
         sql
= """CREATE PROCEDURE INSERT_DATA_Demo
                     @Name varchar(50),
                     @ID int output
                  AS
                     INSERT INTO Demo_Table (Name) VALUES (@Name);
                     Select @ID = @@Identity;
"""
         cursor.execute(sql)
        
         (name, id)
= cursor.callproc("insert_data_demo", ("tom", 0))                
        
print "Inserted new record's ID = %i" % id
        
         sql
= """SELECT * FROM Demo_Table;"""
         cursor.execute(sql)
        
print cursor.fetchall()        
         cursor.close()
        
         self.conn.commit()
        
                
if __name__ == "__main__":
     test
= DBTestor()
     test.connectDB(
"Provider=SQLOLEDB.1;Persist Security Info=True;Password=;User ID=sa;Initial Catalog=pubs;Data Source=.")
    
try:
         test.testQuery()
         test.testCommand()
         test.testStoreProc()        
    
finally:
         test.closeDB()













































程序说明:
1. 因为源代码有中文,所以要在第一行加上 #coding=utf-8
2. adodbapi默认在Connection的__init__里面开启事务BeginTrans(),然后在close方法里面回滚事务 RollbackTrans()。因此对数据库进行修改之后要调用Connection.commit()提交事务才会保存数据。这一点与ADO的默认行 为不一致,需要注意。
3. cursor.fetch*()方法返回的是tuple,只能通过序号读取字段值,如果要通过字段名访问数据,需要将字段名映射为序号。测试代码里面的fielddict方法利用cursor.description属性实现了此映射过程。

总结:
感觉Python DB-API2.0接口使用是很简单和方便的,只不过接口功能稍弱了点,比如没有MoveFirst等功能,不能多次遍历单个结果记录集,而且不直接支持使用字段名访问数据,需要做转换。

参考资料:
1. PEP-0249, Python Database API Specification V 2.0
2.
3.
阅读(2160) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~