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