Python:操作PostgreSQL数据库(使用DB API2.0)

mr923

　　昨天在《Python:操作PostgreSQL数据库(使用PyGreSQL)》一文中使用PyGreSQL模块实现了对PostgreSQL数据库的操作，今天使用另一种python通用的数据库访问接口DB API2.0来实现一下同样的操作，其实在《Python:操作嵌入式数据库SQLite》一文中我们使用的就是DB API2.0，我们可以看到，它对不同数据库提供了统一的访问接口，更多关于DBAPI2.0的介绍请参考http://www.python.org/dev/peps/pep-0249/
　　来看看实现，首先我们要下载提供DB API2.0接口的模块psycopg2,路径：http://initd.org/psycopg/download/，此模块API手册地址为：http://initd.org/psycopg/docs/index.html
　　一、实现：
　　#!/usr/bin/env python# -*- coding: utf-8 -*-#导入日志及psycopg2模块import loggingimport logging.configimport psycopg2#日志配置文件名LOG_FILENAME = 'logging.conf'#日志语句提示信息LOG_CONTENT_NAME = 'pg_log'def log_init(log_config_filename, logname):'''Function:日志模块初始化函数Input：log_config_filename:日志配置文件名lognmae:每条日志前的提示语句Output: loggerauthor: socratesdate:2012-02-13'''logging.config.fileConfig(log_config_filename)logger = logging.getLogger(logname)return loggerdef operate_postgre_tbl_product():'''Function:操作pg数据库函数Input：NONEOutput: NONEauthor: socratesdate:2012-02-13'''  pgdb_logger.debug("operate_postgre_tbl_product enter...") #连接数据库  try:pgdb_conn = psycopg2.connect(database = 'kevin_test', user = 'dyx1024', password = '888888', host = '192.168.230.128')except Exception, e:print e.args[0]pgdb_logger.error("conntect postgre database failed, ret = %s" % e.args[0])    return    pgdb_logger.info("conntect postgre database(kevin_test) succ.") pg_cursor = pgdb_conn.cursor()#删除表sql_desc = "DROP TABLE IF EXISTS tbl_product3;"try:pg_cursor.execute(sql_desc)except Exception, e:print 'drop table failed'pgdb_logger.error("drop table failed, ret = %s" % e.args[0])pg_cursor.close()pgdb_conn.close()  returnpgdb_conn.commit()pgdb_logger.info("drop table(tbl_product3) succ.") #创建表sql_desc = '''CREATE TABLE tbl_product3(i_index INTEGER,sv_productname VARCHAR(32));'''try:    pg_cursor.execute(sql_desc)except Exception, e:print 'create table failed'pgdb_logger.error("create table failed, ret = %s" % e.args[0])pg_cursor.close()pgdb_conn.close()  returnpgdb_conn.commit()      pgdb_logger.info("create table(tbl_product3) succ.") #插入记录   sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"try:pg_cursor.execute(sql_desc)except Exception, e:print 'insert record into table failed'pgdb_logger.error("insert record into table failed, ret = %s" % e.args[0])pg_cursor.close()pgdb_conn.close()  return    pgdb_conn.commit()pgdb_logger.info("insert record into table(tbl_product3) succ.")     #查询表方法一        sql_desc = "select * from tbl_product3"try:pg_cursor.execute(sql_desc)except Exception, e:print 'select record from  table tbl_product3 failed'pgdb_logger.error("select record from  table tbl_product3 failed, ret = %s" % e.args[0])pg_cursor.close()pgdb_conn.close()  return      for row in pg_cursor:print row pgdb_logger.info("%s", row)   print '*' * 20    #查询表方法二     sql_desc = "select * from tbl_test_port"try:pg_cursor.execute(sql_desc)except Exception, e:print 'select record from  table tbl_test_port failed'pgdb_logger.error("select record from  table tbl_test_port failed, ret = %s" % e.args[0])pg_cursor.close()pgdb_conn.close()  return  for row in pg_cursor.fetchall():print row pgdb_logger.info("%s", row)              #关闭数据库连接     pg_cursor.close()pgdb_conn.close()       pgdb_logger.debug("operate_sqlite3_tbl_product leaving...") if __name__ == '__main__': #初始化日志系统pgdb_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME)   #操作数据库operate_postgre_tbl_product()
二、测试：
　　1、命令行结果：
　　(None, 'apple')********************(1, 2, 1)(2, 3, 1)(3, 5, 1)(5, 0, 1)(7, 18, 1)(8, 8, 1)(9, 7, 1)(10, 21, 1)(11, 23, 1)(12, 29, 1)(4, 3000, 1)
2、日志文件结果：
　　[2012-02-14 00:12:06,358  pg_log]DEBUG:  operate_postgre_tbl_product enter... (db_postgre.py:36)[2012-02-14 00:12:06,453  pg_log]INFO:  conntect postgre database(kevin_test) succ. (db_postgre.py:46)[2012-02-14 00:12:06,467  pg_log]INFO:  drop table(tbl_product3) succ. (db_postgre.py:62)[2012-02-14 00:12:06,483  pg_log]INFO:  create table(tbl_product3) succ. (db_postgre.py:79)[2012-02-14 00:12:06,483  pg_log]INFO:  insert record into table(tbl_product3) succ. (db_postgre.py:93)[2012-02-14 00:12:06,483  pg_log]INFO:  (None, 'apple') (db_postgre.py:108)[2012-02-14 00:12:06,483  pg_log]INFO:  (1, 2, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (2, 3, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (3, 5, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (5, 0, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (7, 18, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (8, 8, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (9, 7, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (10, 21, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (11, 23, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (12, 29, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (4, 3000, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (6, 1999, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]DEBUG:  operate_sqlite3_tbl_product leaving... (db_postgre.py:130)