Python: PostgreSQL database operations (using pygresql)

Yesterday I used python to write a method for operating embedded database SQLite. Today I used python to write common operations for PostgreSQL, an open source database that I am familiar with. The development process is as follows:

I. Environment 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 is provided)

4. Description:

A. PostgreSQL databases run on Redhat Linux, while pgadmin is also installed on Windows (clients accessing the PostgreSQL server ).

B. pygresql (PG) module download path and API Manual: http://www.pygresql.org/

Ii. Configuration:

1. Add the following sub-directories under the pgadmin installation path to the system environment variables:

E: \ Program Files \ PostgreSQL \ 8.3 \ Lib

E: \ Program Files \ PostgreSQL \ 8.3 \ bin

2. Copy the DLL file in the python installation directory c: \ python26 \ Lib \ Site-packages \ pywin32_system32 to C: \ windows \ system32.

3. Description:

A. if you skip the preceding two steps, an error will be reported during PG import, which takes a long time to complete.

B. If you are not familiar with PostgreSQL installation and configuration, refer to the following articles in this blog:

RedHat install PostgreSQL on Linux

PostgreSQL server listener settings and Client Connection Methods

PostgreSQL database creation and deletion Methods

For more information about PostgreSQL, visit my blog: PostgreSQL Series

3. Program Implementation:

#! /Usr/bin/ENV Python #-*-coding: UTF-8-*-# import logs and PG module import loggingimport logging. configimport PG # log configuration file name log_filename = 'logging. conf '# log statement prompt message 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: the 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_effec_tbl_product (): ''' function: Operation PG database function input: None output: None Author: Socrates Date: 2012-02-12 ''' pgdb_logger.debug ("operate_pai_tbl_product enter... ") # connect to the database try: pgdb_conn = PG. connect (dbname = 'kevin _ test', host = '2017. 168.230.128 ', user = 'dyx1024', passwd = '000000') failed t 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. ") # Delete the table SQL _desc =" Drop table if exists tbl_product3; "try: pgdb_conn.query (SQL _desc) failed t 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. ") # create a table SQL _desc = ''' create table tbl_product3 (I _index integer, sv_productname varchar (32); ''' try: pgdb_conn.query (SQL _desc) Before t 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. ") # insert record SQL _desc =" insert into tbl_product3 (sv_productname) values ('apple') "try: pgdb_conn.query (SQL _desc) failed t 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. ") # query Table 1 SQL _desc =" select * From tbl_product3 "for row in pgdb_conn.query (SQL _desc ). dictresult (): Print row pgdb_logger.info ("% s", row) # query Table 2 SQL _desc = "select * From tbl_test_port" for row in pgdb_conn.query (SQL _desc ). dictresult (): Print row pgdb_logger.info ("% s", row) # Close the database connection pgdb_conn.close () pgdb_logger.debug ("operate_sqlite3_tbl_product leaving... ") if _ name _ = '_ main _': # initialize the log system pgdb_logger = log_init (log_filename, log_content_name) # operate the database operate_effec_tbl_product ()

Iv. test:

1. Run the following command to print the result:

{'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. 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_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. View Psql 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 |     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 ~]$