这里(http://www.linuxidc.com/Linux/2012-02/54213.htm)写了使用Python写了操作嵌入式数据库SQLite的方法,今天用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/
二、配置:
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、说明:
a. 如果跳过以上两步,在import pg时将会报错,浪费较长时间才搞定。
b. 如果对PostgreSQL安装和配置不熟悉,请参考本博客中以下几篇文章:
三、程序实现:
[python] view plaincopyprint?
#!/usr/bin/env python
# -*- 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_product enter...")
#连接数据库
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("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)
exceptException, 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)
exceptException, 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)
exceptException, 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"
forrowinpgdb_conn.query(sql_desc).dictresult():
printrow
pgdb_logger.info("%s", row)
#查询表2
sql_desc ="select * from tbl_test_port"
forrowinpgdb_conn.query(sql_desc).dictresult():
printrow
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()