Tutorial on using PyGreSQL to operate a PostgreSQL database in Python

This article mainly introduces how to use PyGreSQL to operate PostgreSQL databases in Python. If you need it, refer to PostgreSQL, which is a powerful open-source relational database, this article uses python to implement common operations on the open source database PostgreSQL. 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/
Click here to download the PyGreSQL module.

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. Note: If you skip the preceding two steps, an error will be reported during import of pg, which will take a long time to complete.

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