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 loggingimport logging.configimport 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 loggerdef 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_testpsql (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 ~]$