在《Python:操作PostgreSQL數據庫(使用PyGreSQL)》(見 http://www.linuxidc.com/Linux/2012-02/54214.htm)一文中使用PyGreSQL模塊實現了對PostgreSQL數據庫的操作,今天使用另一種python通用的數據庫訪問接口DB API2.0來實現一下同樣的操作,其實在《Python:操作嵌入式數據庫SQLite》(見 http://www.linuxidc.com/Linux/2012-02/54213.htm)一文中我們使用的就是DB API2.0,我們可以看到,它對不同數據庫提供了統一的訪問接口,更多關於DB API2.0的介紹請參考http://www.python.org/dev/peps/pep-0249/
來看看實現,首先我們要下載提供DB API2.0接口的模塊psycopg2,路徑:http://initd.org/psycopg/download/,此模塊API手冊地址為:http://initd.org/psycopg/docs/index.html
一、實現:
[python]
- #!/usr/bin/env python
- # -*- coding: utf-8 -*-
-
- #導入日志及psycopg2模塊
- import logging
- import logging.config
- import psycopg2
-
- #日志配置文件名
- LOG_FILENAME = 'logging.conf'
-
- #日志語句提示信息
- LOG_CONTENT_NAME = 'pg_log'
-
- def log_init(log_config_filename, logname):
- '''''
- Function:日志模塊初始化函數
- Input:log_config_filename:日志配置文件名
- lognmae:每條日志前的提示語句
- Output: logger
- author: socrates
- date:2012-02-13
- '''
- logging.config.fileConfig(log_config_filename)
- logger = logging.getLogger(logname)
- return logger
-
- def operate_postgre_tbl_product():
- '''''
- Function:操作pg數據庫函數
- Input:NONE
- Output: NONE
- author: socrates
- date:2012-02-13
- '''
- pgdb_logger.debug("operate_postgre_tbl_product enter...")
-
- #連接數據庫
- try:
- pgdb_conn = psycopg2.connect(database = 'kevin_test', user = 'dyx1024', password = '888888', host = '192.168.230.128')
- except 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()
-
- #刪除表
- sql_desc = "DROP TABLE IF EXISTS tbl_product3;"
- try:
- pg_cursor.execute(sql_desc)
- except 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.")
-
- #創建表
- sql_desc = '''''CREATE TABLE tbl_product3(
- i_index INTEGER,
- sv_productname VARCHAR(32)
- );'''
- try:
- pg_cursor.execute(sql_desc)
- except 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.")
-
- #插入記錄
- sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"
- try:
- pg_cursor.execute(sql_desc)
- except 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.")
-
- #查詢表方法一
- sql_desc = "select * from tbl_product3"
- try:
- pg_cursor.execute(sql_desc)
- except 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
- #查詢表方法二
- sql_desc = "select * from tbl_test_port"
- try:
- pg_cursor.execute(sql_desc)
- except 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 pg_cursor.fetchall():
- print row
- pgdb_logger.info("%s", row)
-
- #關閉數據庫連接
- pg_cursor.close()
- pgdb_conn.close()
-
- pgdb_logger.debug("operate_sqlite3_tbl_product leaving...")
-
- if __name__ == '__main__':
-
- #初始化日志系統
- pgdb_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME)
-
- #操作數據庫
- operate_postgre_tbl_product()
-