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 loggingimport logging.configimport 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 loggerdef 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_testpsql (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 ~]$
新聞熱點
疑難解答
圖片精選
