Python使用PyGreSQL操作PostgreSQL數(shù)據(jù)庫教程

更新時(shí)間：2014年07月30日 16:32:50 投稿：shichen2014

這篇文章主要介紹了Python使用PyGreSQL操作PostgreSQL數(shù)據(jù)庫,需要的朋友可以參考下

PostgreSQL是一款功能強(qiáng)大的開源關(guān)系型數(shù)據(jù)庫，本文使用python實(shí)現(xiàn)了對開源數(shù)據(jù)庫PostgreSQL的常用操作，其開發(fā)過程簡介如下：

一、環(huán)境信息：

1、操作系統(tǒng)：

RedHat Enterprise Linux 4
Windows XP SP2

2、數(shù)據(jù)庫：

PostgreSQL8.3

3、開發(fā)工具:

Eclipse+Pydev+python2.6+PyGreSQL(提供pg模塊)

4、說明：

a、PostgreSQL數(shù)據(jù)庫運(yùn)行于RedHat Linux上，Windows下也要安裝pgAdmin（訪問PostgreSQL服務(wù)器的客戶端）。
b、PyGreSQL(即pg)模塊下載路徑及API手冊：http://www.pygresql.org/
PyGreSQL模塊點(diǎn)此本站下載

二、配置：

1、將pgAdmin安裝路徑下以下子目錄添加到系統(tǒng)環(huán)境變量中:

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時(shí)將會(huì)報(bào)錯(cuò)，并且會(huì)浪費(fèi)較長時(shí)間才能搞定。

三、程序?qū)崿F(xiàn)：

#!/usr/bin/env python
# -*- coding: utf-8 -*-

#導(dǎo)入日志及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:日志模塊初始化函數(shù)
  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數(shù)據(jù)庫函數(shù)
  Input：NONE
  Output: NONE
  author: socrates
  date:2012-02-12
  ''' 
  pgdb_logger.debug("operate_postgre_tbl_product enter...") 
  
  #連接數(shù)據(jù)庫 
  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.") 
 
  #創(chuàng)建表
  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)    
   
  #關(guān)閉數(shù)據(jù)庫連接   
  pgdb_conn.close()    
  pgdb_logger.debug("operate_sqlite3_tbl_product leaving...") 

if __name__ == '__main__': 
  
  #初始化日志系統(tǒng)
  pgdb_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME)  
  
  #操作數(shù)據(jù)庫
  operate_postgre_tbl_product()

四、測試：

1、運(yùn)行后命令行打印結(jié)果：

{'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、日志文件內(nèi)容：

[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查看結(jié)果：

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

您可能感興趣的文章: