Python:PostgreSQLデータベースの操作(DB API 2.0を使用)

昨日は「Python:PostgreSQLデータベースを操作する」という文でPyGreSQLモジュールを使ってPostgreSQLデータベースの操作を実現しました.今日はもう一つのpython共通のデータベースアクセスインターフェースDB API 2.0を使って同じ操作を実現します.それは異なるデータベースに対して統一的なアクセスインターフェースを提供しています.DB API 2.0についてもっと紹介しています.参照してください.http://www.python.org/dev/peps/pep-0249/
   実現を見てみます.まずDB API 2.0インターフェースを提供するモジュールpycopg 2をダウンロードします.http://initd.org/psycopg/download/このモジュールAPIマニュアルのアドレスは以下の通りです.http://initd.org/psycopg/docs/index.html
一、実現:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

#     psycopg2  
import logging
import logging.config
import 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 logger

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