Python using Pygresql to manipulate PostgreSQL database tutorials

PostgreSQL is a powerful open source relational database that uses Python to implement common operations for the open source database PostgreSQL, with the following brief introduction to the development process:

First, environmental information:

1. Operating system:

RedHat Enterprise Linux 4
Windows XP SP2

2. Database:

PostgreSQL8.3

3. Development tools:

Eclipse+pydev+python2.6+pygresql (PG module available)

4. Description:

A, the PostgreSQL database runs on Redhat Linux, and Windows also installs Pgadmin (the client that accesses the PostgreSQL server).
B, Pygresql (ie PG) module download path and API manual: http://www.pygresql.org/
Pygresql module point this site download

Second, the configuration:

1. Add the following subdirectory under the Pgadmin installation path to the system environment variable:

E:\Program Files\postgresql\8.3\lib

E:\Program Files\postgresql\8.3\bin

2. Copy the DLL files under Python installation directory C:\Python26\Lib\site-packages\pywin32_system32 to C:\WINDOWS\system32

3, Note: If you skip the above two steps, the import PG will be error, and will waste a long time to get it done.

Third, the implementation of the program:

#!/usr/bin/env python#-*-coding:utf-8-*-#导入日志及pg模块import loggingimport logging.configimport pg# Log Profile name Log_filename = ' logging.conf ' #日志语句提示信息LOG_CONTENT_NAME = ' Pg_log ' def log_init (Log_config_filename, logname): ' Function: Log module initialization function input:log_config_filename: Log configuration file name Lognmae: Prompt statement before each log 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: Operation PG database function Input:none output:none author:socrates date:2012-02-12 ' pgdb_l Ogger.debug ("operate_postgre_tbl_product enter ...") #连接数据库 Try:pgdb_conn = pg.connect (dbname = ' kevin_test ', hos t = ' 192.168.230.128 ', user = ' dyx1024 ', passwd = ' 888888 ') except Exception, E:print E.args[0] Pgdb_logger.erro R ("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 fail    ed, 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 tabl E 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, re t =%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 PG Db_logger.info ("%s", row) #查询表2 Sql_desc = "SELECT * from Tbl_test_port" for row in Pgdb_conn.query (SQL_DESC). DiC TResult (): Print row Pgdb_logger.info ("%s", row) #关闭数据库连接 Pgdb_conn.close () pgdb_logger.debug ("Ope Rate_sqlite3_tbl_product leaving ... ") if __name__ = = ' __main__ ': #初始化日志系统 Pgdb_logger = Log_init (Log_filename, LOG_CO   Ntent_name) #操作数据库 operate_postgre_tbl_product ()

Four, test:

1. After running the command line printing results:

{' 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 ':, ' 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 ': +, ' I_index ': 10} {' I_status ': 1, ' i_port ': +, ' I_index ': 11} {' I_status ': 1, ' I_port ': $, ' I_index ': 12} {' I_status ': 1, ' i_port ': +, ' I_index ': 4} {' I_status ': 1, ' I_port ': 1999, ' I_index ': 6}

2. log file contents:

[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 ':, ' 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_fu nc.py:97) [2012-02-12 18:09:53,819 pg_log]info: {' i_status ': 1, ' i_port ': +, ' I_index ': Ten} (TEST_FUNC.PY:97) [ 2012-02-12 18:09:53,819 pg_log]info: {' i_status ': 1, ' i_port ':, ' I_index ': one} (test_func.py:97) [2012-02-12 18:09:53,819 pg_log]info: {' i_status ': 1, ' I_port ': $, ' I_index ': (test_func.py:97) [2012-02-12 18:09:53,819 Pg_log] INFO: {' i_status ': 1, ' i_port ': +, ' 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 View Results:

[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 |   |    1    8 |   8 |    1    9 |   7 |    1   |   |    1   |   |    1   |   |    1    4 |  |    1    6 |  1999 |    

