Python uses Pygresql to manipulate PostgreSQL database tutorials _python

PostgreSQL is a powerful open source relational database, this article uses Python to achieve the open source database PostgreSQL Common operations, the development process is as follows:

I. 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 (clients that access PostgreSQL servers).
B, Pygresql (ie PG) module download path and API manual: http://www.pygresql.org/
Pygresql module Point Download this site

Second, the configuration:

1, add the following subdirectories 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, the Python installation directory C:\Python26\Lib\site-packages\pywin32_system32 under the DLL file copy to C:\WINDOWS\system32

3, Description: If you skip the above two steps, in the import of PG will be an error, and will waste a long time to finish.

Third, the realization of the program:

#!/usr/bin/env python #-*-coding:utf-8-*-#导入日志及pg模块 Import Logging Import Logging.config Import PG #日志配置文件名 LOG_FI Lename = ' logging.conf ' #日志语句提示信息 log_content_name = ' Pg_log ' def log_init (Log_config_filename, logname): ' Functi On: Log module initialization function input:log_config_filename: Log profile filename 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 Logger def op
  Erate_postgre_tbl_product (): ' function: Operation PG database function 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 ("C Onntect 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_product 
   
  3) succ. ") #插入记录 Sql_desc = "INSERT into Tbl_product3 (sv_productname) VALUES (' Apple ')" Try:pgdb_conn.query (SQL_DESC) exc EPT Exception, e:print ' Insert record to 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_d ESC). Dictresult (): Print row Pgdb_logger.info ("%s", row) #关闭数据库连接 pgdb_conn.close () pgdb_log Ger.debug ("operate_sqlite3_tbl_product leaving ...") if __name__ = = ' __main__ ': #初始化日志系统 Pgdb_logger = Log_init (L
  
 Og_filename, Log_content_name) #操作数据库 operate_postgre_tbl_product ()

Four, test:

1, after the operation of 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 ': ten}
{' I_status ': 1, ' i_port ': ', ' I_index ': one}
{' I_status ': 1, ' I_port ': Don, ' I_index ':
{' I_status ': 1, ' i_port ': 3000, ' I_index ': 4}
{' I_status ': 1, ' I_port ': 1999, ' I_index ': 6}

2, log file content:

[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_inde X ': 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_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 ': 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: [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:0 9: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 Ug:operate_sqlite3_tbl_product leaving ...

 (test_func.py:101)

3, Psql View results:

[Root@kevin ~]# su-postgres
[Postgres@kevin ~]$ psql-u dyx1024-d kevin_test psql
(8.4.2)
Type ' help ' for H ELP.

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 One
   |   |    1
   |   |    1
    4 |  3000 |    1
    6 |  1999 |    1
(rows)

kevin_test=# \q
[Postgres@kevin ~]$