| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
openkk
13年前发布

使用PyGreSQL操作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安装和配置不熟悉,请参考本博客中以下几篇文章:

                  《RedHat Linux上安装PostgreSQL》

                  《PostgreSQL服务端监听设置及客户端连接方法》

                  《PostgreSQL数据库创建、删除方法》

                   更多PostgreSQL相关知识请访问从我的博客专栏:PostgreSQL系列

三、程序实现:

#!/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 ~]$ 
来自:http://blog.csdn.net/dyx1024/article/details/7252816

 本文由用户 openkk 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1329049528655.html
PostgreSQL 数据库服务器