Python:操作PostgreSQL資料庫(使用DB API2.0)

   昨天在《Python:操作PostgreSQL資料庫(使用PyGreSQL)》一文中使用PyGreSQL模組實現了對PostgreSQL資料庫的操作，今天使用另一種python通用的資料庫提供者DB API2.0來實現一下同樣的操作，其實在《Python:操作嵌入式資料庫SQLite》一文中我們使用的就是DB API2.0，我們可以看到，它對不同資料庫提供了統一的提供者，更多關於DB
API2.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: logger    author: socrates    date:2012-02-13    '''    logging.config.fileConfig(log_config_filename)    logger = logging.getLogger(logname)    return loggerdef operate_postgre_tbl_product():    '''    Function:操作pg資料庫函數    Input：NONE    Output: NONE    author: socrates    date: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()          return    pgdb_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()          return    pgdb_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)