Python使用PyGreSQL操作PostgreSQL数据库教程

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