Python: PostgreSQL database operations (using DB api2.0)

Yesterday, I used the pygresql module in the article "Python: Operating PostgreSQL databases (using pygresql)" to perform operations on PostgreSQL databases, today, we use another common Python database access interface dB api2.0 to perform the same operation. In fact, we use dB api2.0 in the article "Python: Operating embedded database SQLite, we can see that it provides a unified access interface for different databases.
Api2.0 Introduction Please refer to http://www.python.org/dev/peps/pep-0249/

First, download the module psycopg2 that provides the DB api2.0 interface.

I. Implementation:

#! /Usr/bin/ENV Python #-*-coding: UTF-8-*-# import logs and psycopg2 module import loggingimport logging. configimport psycopg2 # 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 output: logger Author: Socrates Date: ''' logging before each log. 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-13 ''' pgdb_logger.debug ("operate_effec_tbl_product enter... ") # connect to the database try: pgdb_conn = psycopg2.connect (Database = 'kevin _ test', user = 'dyx1024', password = '000000', host = '000000. 168.230.128 ') 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. ") pg_cursor = pgdb_conn.cursor () # Delete the table SQL _desc =" Drop table if exists tbl_product3; "try: pg_cursor.execute (SQL _desc) limit t exception, E: print 'drop table failed' pgdb_logger.error ("Drop table failed, ret = % s" % E. ARGs [0]) pg_cursor.close () pgdb_conn.close () return pgdb_conn.commit () 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: pg_cursor.execute (SQL _desc) Foreign t exception, E: print 'create table failed' pgdb_logger.error ("create table failed, ret = % s" % E. ARGs [0]) pg_cursor.close () pgdb_conn.close () return pgdb_conn.commit () pgdb_logger.info ("create table (tbl_product3) succ. ") # insert record SQL _desc =" insert into tbl_product3 (sv_productname) values ('apple') "try: pg_cursor.execute (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]) pg_cursor.close () pgdb_conn.close () return pgdb_conn.commit () pgdb_logger.info ("insert record into table (tbl_product3) succ. ") # query table method 1 SQL _desc =" select * From tbl_product3 "try: pg_cursor.execute (SQL _desc) failed t exception, E: print 'select record from Table tbl_product3 failed 'pgdb_logger.error ("select record from Table tbl_product3 failed, ret = % s" % E. ARGs [0]) pg_cursor.close () pgdb_conn.close () return for row in pg_cursor: Print row pgdb_logger.info ("% s", row) print '* 20 # query table method 2 SQL _desc = "select * From tbl_test_port" try: pg_cursor.execute (SQL _desc) failed t exception, E: print 'select record from Table tbl_test_port failed 'pgdb_logger.error ("select record from Table tbl_test_port failed, ret = % s" % E. ARGs [0]) pg_cursor.close () pgdb_conn.close () return for row in rows (): Print row pgdb_logger.info ("% s", row) # Close database connection pg_cursor.close () 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 ()

Ii. test:

1. Command Line result:

(None, 'apple')********************(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)

2. Log File results:

[2012-02-14 00:12:06,358  pg_log]DEBUG:  operate_postgre_tbl_product enter... (db_postgre.py:36)[2012-02-14 00:12:06,453  pg_log]INFO:  conntect postgre database(kevin_test) succ. (db_postgre.py:46)[2012-02-14 00:12:06,467  pg_log]INFO:  drop table(tbl_product3) succ. (db_postgre.py:62)[2012-02-14 00:12:06,483  pg_log]INFO:  create table(tbl_product3) succ. (db_postgre.py:79)[2012-02-14 00:12:06,483  pg_log]INFO:  insert record into table(tbl_product3) succ. (db_postgre.py:93)[2012-02-14 00:12:06,483  pg_log]INFO:  (None, 'apple') (db_postgre.py:108)[2012-02-14 00:12:06,483  pg_log]INFO:  (1, 2, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (2, 3, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (3, 5, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (5, 0, 1) (db_postgre.py:124)[2012-02-14 00:12:06,483  pg_log]INFO:  (7, 18, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (8, 8, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (9, 7, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (10, 21, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (11, 23, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (12, 29, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (4, 3000, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]INFO:  (6, 1999, 1) (db_postgre.py:124)[2012-02-14 00:12:06,500  pg_log]DEBUG:  operate_sqlite3_tbl_product leaving... (db_postgre.py:130)