当前位置：Gxlcms > Python > Python使用PyGreSQL操作PostgreSQL数据库教程

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

时间：2021-07-01 10:21:17 帮助过：68人阅读

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时将会报错，并且会浪费较长时间才能搞定。

三、程序实现：

  1. #!/usr/bin/env python
  2. # -*- coding: utf-8 -*-
  3. #导入日志及pg模块
  4. import logging
  5. import logging.config
  6. import pg
  7. #日志配置文件名
  8. LOG_FILENAME = 'logging.conf'
  9. #日志语句提示信息
  10. LOG_CONTENT_NAME = 'pg_log'
  11. def log_init(log_config_filename, logname):
  12.   '''
  13.   Function:日志模块初始化函数
  14.   Input：log_config_filename:日志配置文件名
  15.       lognmae:每条日志前的提示语句
  16.   Output: logger
  17.   author: socrates
  18.   date:2012-02-12
  19.   '''
  20.   logging.config.fileConfig(log_config_filename)
  21.   logger = logging.getLogger(logname)
  22.   return logger
  23. def operate_postgre_tbl_product():
  24.   '''
  25.   Function:操作pg数据库函数
  26.   Input：NONE
  27.   Output: NONE
  28.   author: socrates
  29.   date:2012-02-12
  30.   ''' 
  31.   pgdb_logger.debug("operate_postgre_tbl_product enter...") 
  32.   
  33.   #连接数据库 
  34.   try:
  35.     pgdb_conn = pg.connect(dbname = 'kevin_test', host = '192.168.230.128', user = 'dyx1024', passwd = '888888')
  36.   except Exception, e:
  37.      print e.args[0]
  38.      pgdb_logger.error("conntect postgre database failed, ret = %s" % e.args[0])  
  39.      return  
  40.    
  41.   pgdb_logger.info("conntect postgre database(kevin_test) succ.") 
  42.     
  43.   #删除表
  44.   sql_desc = "DROP TABLE IF EXISTS tbl_product3;"
  45.   try:
  46.     pgdb_conn.query(sql_desc)
  47.   except Exception, e:
  48.     print 'drop table failed'
  49.     pgdb_logger.error("drop table failed, ret = %s" % e.args[0])
  50.     pgdb_conn.close() 
  51.     return
  52.   
  53.   pgdb_logger.info("drop table(tbl_product3) succ.") 
  54.  
  55.   #创建表
  56.   sql_desc = '''CREATE TABLE tbl_product3(
  57.     i_index INTEGER,
  58.     sv_productname VARCHAR(32)
  59.     );'''
  60.   try:  
  61.     pgdb_conn.query(sql_desc)
  62.   except Exception, e:
  63.     print 'create table failed'
  64.     pgdb_logger.error("create table failed, ret = %s" % e.args[0])
  65.     pgdb_conn.close() 
  66.     return    
  67.   
  68.   pgdb_logger.info("create table(tbl_product3) succ.") 
  69.    
  70.   #插入记录  
  71.   sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"
  72.   try:
  73.     pgdb_conn.query(sql_desc)
  74.   except Exception, e:
  75.     print 'insert record into table failed'
  76.     pgdb_logger.error("insert record into table failed, ret = %s" % e.args[0])
  77.     pgdb_conn.close() 
  78.     return  
  79.    
  80.   pgdb_logger.info("insert record into table(tbl_product3) succ.")   
  81.    
  82.   #查询表 1    
  83.   sql_desc = "select * from tbl_product3"
  84.   for row in pgdb_conn.query(sql_desc).dictresult():
  85.     print row
  86.     pgdb_logger.info("%s", row) 
  87.  
  88.   #查询表2    
  89.   sql_desc = "select * from tbl_test_port"
  90.   for row in pgdb_conn.query(sql_desc).dictresult():
  91.     print row 
  92.     pgdb_logger.info("%s", row)    
  93.    
  94.   #关闭数据库连接   
  95.   pgdb_conn.close()    
  96.   pgdb_logger.debug("operate_sqlite3_tbl_product leaving...") 
  97. if __name__ == '__main__': 
  98.   
  99.   #初始化日志系统
  100.   pgdb_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME)  
  101.   
  102.   #操作数据库
  103.   operate_postgre_tbl_product()
  104.

四、测试：

1、运行后命令行打印结果：

  1. {'sv_productname': 'apple', 'i_index': None}
  2. {'i_status': 1, 'i_port': 2, 'i_index': 1}
  3. {'i_status': 1, 'i_port': 3, 'i_index': 2}
  4. {'i_status': 1, 'i_port': 5, 'i_index': 3}
  5. {'i_status': 1, 'i_port': 0, 'i_index': 5}
  6. {'i_status': 1, 'i_port': 18, 'i_index': 7}
  7. {'i_status': 1, 'i_port': 8, 'i_index': 8}
  8. {'i_status': 1, 'i_port': 7, 'i_index': 9}
  9. {'i_status': 1, 'i_port': 21, 'i_index': 10}
  10. {'i_status': 1, 'i_port': 23, 'i_index': 11}
  11. {'i_status': 1, 'i_port': 29, 'i_index': 12}
  12. {'i_status': 1, 'i_port': 3000, 'i_index': 4}
  13. {'i_status': 1, 'i_port': 1999, 'i_index': 6}

2、日志文件内容：

  1. [2012-02-12 18:09:53,536 pg_log]DEBUG: operate_postgre_tbl_product enter... (test_func.py:36)
  2. [2012-02-12 18:09:53,772 pg_log]INFO: conntect postgre database(kevin_test) succ. (test_func.py:46)
  3. [2012-02-12 18:09:53,786 pg_log]INFO: drop table(tbl_product3) succ. (test_func.py:58)
  4. [2012-02-12 18:09:53,802 pg_log]INFO: create table(tbl_product3) succ. (test_func.py:73)
  5. [2012-02-12 18:09:53,802 pg_log]INFO: insert record into table(tbl_product3) succ. (test_func.py:85)
  6. [2012-02-12 18:09:53,802 pg_log]INFO: {'sv_productname': 'apple', 'i_index': None} (test_func.py:91)
  7. [2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 2, 'i_index': 1} (test_func.py:97)
  8. [2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 3, 'i_index': 2} (test_func.py:97)
  9. [2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 5, 'i_index': 3} (test_func.py:97)
  10. [2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 0, 'i_index': 5} (test_func.py:97)
  11. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 18, 'i_index': 7} (test_func.py:97)
  12. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 8, 'i_index': 8} (test_func.py:97)
  13. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 7, 'i_index': 9} (test_func.py:97)
  14. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 21, 'i_index': 10} (test_func.py:97)
  15. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 23, 'i_index': 11} (test_func.py:97)
  16. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 29, 'i_index': 12} (test_func.py:97)
  17. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 3000, 'i_index': 4} (test_func.py:97)
  18. [2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 1999, 'i_index': 6} (test_func.py:97)
  19. [2012-02-12 18:09:53,819 pg_log]DEBUG: operate_sqlite3_tbl_product leaving... (test_func.py:101)

3、psql查看结果：

  1. [root@kevin ~]# su - postgres
  2. [postgres@kevin ~]$ psql -U dyx1024 -d kevin_test
  3. psql (8.4.2)
  4. Type "help" for help.
  5. kevin_test=# \dt
  6.         List of relations
  7.  Schema |   Name   | Type |   Owner   
  8. --------+---------------+-------+----------------
  9.  public | tbl_product3 | table | dyx1024
  10.  public | tbl_test_port | table | pg_test_user_3
  11. (2 rows)
  12. kevin_test=# select * from tbl_product3;
  13.  i_index | sv_productname 
  14. ---------+----------------
  15.      | apple
  16. (1 row)
  17. kevin_test=# select * from tbl_test_port;
  18.  i_index | i_port | i_status 
  19. ---------+--------+----------
  20.     1 |   2 |    1
  21.     2 |   3 |    1
  22.     3 |   5 |    1
  23.     5 |   0 |    1
  24.     7 |   18 |    1
  25.     8 |   8 |    1
  26.     9 |   7 |    1
  27.    10 |   21 |    1
  28.    11 |   23 |    1
  29.    12 |   29 |    1
  30.     4 |  3000 |    1
  31.     6 |  1999 |    1
  32. (12 rows)
  33. kevin_test=# \q
  34. [postgres@kevin ~]$

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！