PostgreSQL是一款功能强大的开源关系型数据库，本文使用python实现了对开源数据库PostgreSQL的常用操作，其开发过程简介如下：

一、环境信息：

   1、操作系统：

        RedHat Enterprise Linux 4
        Windows XP SP2

  2、数据库：

        PostgreSQL8.3

  3、 开发工具:

        Eclipse+Pydev+python2.6+PyGreSQL(提供pg模块)

  4、说明：

        a、PostgreSQL数据库运行于RedHat Linux上，Windows下也要安装pgAdmin（访问PostgreSQL服务器的客户端）。
        b、PyGreSQL(即pg)模块下载路径及API手册：http://www.pygresql.org/
 PyGreSQL模块点此本站下载

二、配置：

       1、将pgAdmin安装路径下以下子目录添加到系统环境变量中:

             E:\\Program Files\\PostgreSQL\\8.3\\lib

             E:\\Program Files\\PostgreSQL\\8.3\\bin

       2、将python安装目录C:\\Python26\\Lib\\site-packages\\pywin32_system32下的dll文件拷贝到C:\\WINDOWS\\system32

       3、说明：如果跳过以上两步，在import pg时将会报错，并且会浪费较长时间才能搞定。

三、程序实现：

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

#导入日志及pg模块
import logging
import logging.config
import pg

#日志配置文件名
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-12
  \'\'\'
  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-12
  \'\'\' 
  pgdb_logger.debug(\"operate_postgre_tbl_product enter...\") 
  
  #连接数据库 
  try:
    pgdb_conn = pg.connect(dbname = \'kevin_test\', host = \'192.168.230.128\', user = \'dyx1024\', passwd = \'888888\')
  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.\") 
    
  #删除表
  sql_desc = \"DROP TABLE IF EXISTS tbl_product3;\"
  try:
    pgdb_conn.query(sql_desc)
  except Exception, e:
    print \'drop table failed\'
    pgdb_logger.error(\"drop table failed, ret = %s\" % e.args[0])
    pgdb_conn.close() 
    return
  
  pgdb_logger.info(\"drop table(tbl_product3) succ.\") 
 
  #创建表
  sql_desc = \'\'\'CREATE TABLE tbl_product3(
    i_index INTEGER,
    sv_productname VARCHAR(32)
    );\'\'\'
  try:  
    pgdb_conn.query(sql_desc)
  except Exception, e:
    print \'create table failed\'
    pgdb_logger.error(\"create table failed, ret = %s\" % e.args[0])
    pgdb_conn.close() 
    return    
  
  pgdb_logger.info(\"create table(tbl_product3) succ.\") 
   
  #插入记录  
  sql_desc = \"INSERT INTO tbl_product3(sv_productname) values(\'apple\')\"
  try:
    pgdb_conn.query(sql_desc)
  except Exception, e:
    print \'insert record into table failed\'
    pgdb_logger.error(\"insert record into table failed, ret = %s\" % e.args[0])
    pgdb_conn.close() 
    return  
   
  pgdb_logger.info(\"insert record into table(tbl_product3) succ.\")   
   
  #查询表 1    
  sql_desc = \"select * from tbl_product3\"
  for row in pgdb_conn.query(sql_desc).dictresult():
    print row
    pgdb_logger.info(\"%s\", row) 
 
  #查询表2    
  sql_desc = \"select * from tbl_test_port\"
  for row in pgdb_conn.query(sql_desc).dictresult():
    print row 
    pgdb_logger.info(\"%s\", row)    
   
  #关闭数据库连接   
  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、运行后命令行打印结果：

{\'sv_productname\': \'apple\', \'i_index\': None}
{\'i_status\': 1, \'i_port\': 2, \'i_index\': 1}
{\'i_status\': 1, \'i_port\': 3, \'i_index\': 2}
{\'i_status\': 1, \'i_port\': 5, \'i_index\': 3}
{\'i_status\': 1, \'i_port\': 0, \'i_index\': 5}
{\'i_status\': 1, \'i_port\': 18, \'i_index\': 7}
{\'i_status\': 1, \'i_port\': 8, \'i_index\': 8}
{\'i_status\': 1, \'i_port\': 7, \'i_index\': 9}
{\'i_status\': 1, \'i_port\': 21, \'i_index\': 10}
{\'i_status\': 1, \'i_port\': 23, \'i_index\': 11}
{\'i_status\': 1, \'i_port\': 29, \'i_index\': 12}
{\'i_status\': 1, \'i_port\': 3000, \'i_index\': 4}
{\'i_status\': 1, \'i_port\': 1999, \'i_index\': 6}

2、日志文件内容：

[2012-02-12 18:09:53,536 pg_log]DEBUG: operate_postgre_tbl_product enter... (test_func.py:36)
[2012-02-12 18:09:53,772 pg_log]INFO: conntect postgre database(kevin_test) succ. (test_func.py:46)
[2012-02-12 18:09:53,786 pg_log]INFO: drop table(tbl_product3) succ. (test_func.py:58)
[2012-02-12 18:09:53,802 pg_log]INFO: create table(tbl_product3) succ. (test_func.py:73)
[2012-02-12 18:09:53,802 pg_log]INFO: insert record into table(tbl_product3) succ. (test_func.py:85)
[2012-02-12 18:09:53,802 pg_log]INFO: {\'sv_productname\': \'apple\', \'i_index\': None} (test_func.py:91)
[2012-02-12 18:09:53,802 pg_log]INFO: {\'i_status\': 1, \'i_port\': 2, \'i_index\': 1} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {\'i_status\': 1, \'i_port\': 3, \'i_index\': 2} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {\'i_status\': 1, \'i_port\': 5, \'i_index\': 3} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {\'i_status\': 1, \'i_port\': 0, \'i_index\': 5} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 18, \'i_index\': 7} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 8, \'i_index\': 8} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 7, \'i_index\': 9} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 21, \'i_index\': 10} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 23, \'i_index\': 11} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 29, \'i_index\': 12} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 3000, \'i_index\': 4} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {\'i_status\': 1, \'i_port\': 1999, \'i_index\': 6} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]DEBUG: operate_sqlite3_tbl_product leaving... (test_func.py:101)

3、psql查看结果：

[root@kevin ~]# su - postgres
[postgres@kevin ~]$ psql -U dyx1024 -d kevin_test
psql (8.4.2)
Type \"help\" for help.

kevin_test=# \\dt
        List of relations
 Schema |   Name   | Type |   Owner   
--------+---------------+-------+----------------
 public | tbl_product3 | table | dyx1024
 public | tbl_test_port | table | pg_test_user_3
(2 rows)

kevin_test=# select * from tbl_product3;
 i_index | sv_productname 
---------+----------------
     | apple
(1 row)

kevin_test=# select * from tbl_test_port;
 i_index | i_port | i_status 
---------+--------+----------
    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
    6 |  1999 |    1
(12 rows)

kevin_test=# \\q
[postgres@kevin ~]$