Python使用PyGreSQL操作PostgreSQL数据库教程
PostgreSQL是一款功能强大的开源关系型数据库，本文使用python实现了对开源数据库PostgreSQL的常用操作，其开发过程简介如下：
一、环境信息：
1、操作系统：
RedHatEnterpriseLinux4
WindowsXPSP2
2、数据库：
PostgreSQL8.3
3、开发工具:
Eclipse+Pydev+python2.6+PyGreSQL(提供pg模块)
4、说明：
a、PostgreSQL数据库运行于RedHatLinux上，Windows下也要安装pgAdmin（访问PostgreSQL服务器的客户端）。
b、PyGreSQL(即pg)模块下载路径及API手册：http://www.pygresql.org/
PyGreSQL模块点此本站下载
二、配置：
1、将pgAdmin安装路径下以下子目录添加到系统环境变量中:
E:\ProgramFiles\PostgreSQL\8.3\lib
E:\ProgramFiles\PostgreSQL\8.3\bin
2、将python安装目录C:\Python26\Lib\site-packages\pywin32_system32下的dll文件拷贝到C:\WINDOWS\system32
3、说明：如果跳过以上两步，在importpg时将会报错，并且会浪费较长时间才能搞定。
三、程序实现：
#!/usr/bin/envpython
#-*-coding:utf-8-*-
#导入日志及pg模块
importlogging
importlogging.config
importpg
#日志配置文件名
LOG_FILENAME='logging.conf'
#日志语句提示信息
LOG_CONTENT_NAME='pg_log'
deflog_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)
returnlogger
defoperate_postgre_tbl_product():
'''
Function:操作pg数据库函数
Input：NONE
Output:NONE
author:socrates
date:2012-02-12
'''
pgdb_logger.debug("operate_postgre_tbl_productenter...")
#连接数据库
try:
pgdb_conn=pg.connect(dbname='kevin_test',host='192.168.230.128',user='dyx1024',passwd='888888')
exceptException,e:
printe.args[0]
pgdb_logger.error("conntectpostgredatabasefailed,ret=%s"%e.args[0])
return
pgdb_logger.info("conntectpostgredatabase(kevin_test)succ.")
#删除表
sql_desc="DROPTABLEIFEXISTStbl_product3;"
try:
pgdb_conn.query(sql_desc)
exceptException,e:
print'droptablefailed'
pgdb_logger.error("droptablefailed,ret=%s"%e.args[0])
pgdb_conn.close()
return
pgdb_logger.info("droptable(tbl_product3)succ.")
#创建表
sql_desc='''CREATETABLEtbl_product3(
i_indexINTEGER,
sv_productnameVARCHAR(32)
);'''
try:
pgdb_conn.query(sql_desc)
exceptException,e:
print'createtablefailed'
pgdb_logger.error("createtablefailed,ret=%s"%e.args[0])
pgdb_conn.close()
return
pgdb_logger.info("createtable(tbl_product3)succ.")
#插入记录
sql_desc="INSERTINTOtbl_product3(sv_productname)values('apple')"
try:
pgdb_conn.query(sql_desc)
exceptException,e:
print'insertrecordintotablefailed'
pgdb_logger.error("insertrecordintotablefailed,ret=%s"%e.args[0])
pgdb_conn.close()
return
pgdb_logger.info("insertrecordintotable(tbl_product3)succ.")
#查询表1
sql_desc="select*fromtbl_product3"
forrowinpgdb_conn.query(sql_desc).dictresult():
printrow
pgdb_logger.info("%s",row)
#查询表2
sql_desc="select*fromtbl_test_port"
forrowinpgdb_conn.query(sql_desc).dictresult():
printrow
pgdb_logger.info("%s",row)
#关闭数据库连接
pgdb_conn.close()
pgdb_logger.debug("operate_sqlite3_tbl_productleaving...")
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-1218:09:53,536pg_log]DEBUG:operate_postgre_tbl_productenter...(test_func.py:36)
[2012-02-1218:09:53,772pg_log]INFO:conntectpostgredatabase(kevin_test)succ.(test_func.py:46)
[2012-02-1218:09:53,786pg_log]INFO:droptable(tbl_product3)succ.(test_func.py:58)
[2012-02-1218:09:53,802pg_log]INFO:createtable(tbl_product3)succ.(test_func.py:73)
[2012-02-1218:09:53,802pg_log]INFO:insertrecordintotable(tbl_product3)succ.(test_func.py:85)
[2012-02-1218:09:53,802pg_log]INFO:{'sv_productname':'apple','i_index':None}(test_func.py:91)
[2012-02-1218:09:53,802pg_log]INFO:{'i_status':1,'i_port':2,'i_index':1}(test_func.py:97)
[2012-02-1218:09:53,802pg_log]INFO:{'i_status':1,'i_port':3,'i_index':2}(test_func.py:97)
[2012-02-1218:09:53,802pg_log]INFO:{'i_status':1,'i_port':5,'i_index':3}(test_func.py:97)
[2012-02-1218:09:53,802pg_log]INFO:{'i_status':1,'i_port':0,'i_index':5}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':18,'i_index':7}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':8,'i_index':8}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':7,'i_index':9}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':21,'i_index':10}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':23,'i_index':11}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':29,'i_index':12}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':3000,'i_index':4}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]INFO:{'i_status':1,'i_port':1999,'i_index':6}(test_func.py:97)
[2012-02-1218:09:53,819pg_log]DEBUG:operate_sqlite3_tbl_productleaving...(test_func.py:101)
3、psql查看结果：
[root@kevin~]#su-postgres [postgres@kevin~]$psql-Udyx1024-dkevin_test psql(8.4.2) Type"help"forhelp. kevin_test=#\dt Listofrelations Schema|Name|Type|Owner --------+---------------+-------+---------------- public|tbl_product3|table|dyx1024 public|tbl_test_port|table|pg_test_user_3 (2rows) kevin_test=#select*fromtbl_product3; i_index|sv_productname ---------+---------------- |apple (1row) kevin_test=#select*fromtbl_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 (12rows) kevin_test=#\q [postgres@kevin~]$