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 ~]$