#!/usr/bin/env python
# -*- coding: utf-8 -*-

#导入日志及SQLite3模块
import logging
import logging.config
import sqlite3

#日志配置文件名
LOG_FILENAME = 'logging.conf'

#日志语句提示信息
LOG_CONTENT_NAME = 'sqlite_log'

#SQLite数据库名称
DB_SQLITE_PATH = ".\\db\\sqlite_pytest.db"

def log_init(log_config_filename, logname):
    '''
    Function:日志模块初始化函数
    Input：log_config_filename:日志配置文件名
           lognmae:每条日志前的提示语句
    Output: logger
    author: socrates
    date:2012-02-11
    '''
    logging.config.fileConfig(log_config_filename)
    logger = logging.getLogger(logname)
    return logger

def operate_sqlite3_tbl_product():
    '''
    Function:操作SQLITE3数据库函数
    Input：NONE
    Output: NONE
    author: socrates
    date:2012-02-11
    '''  
    sqlite_logger.debug("operate_sqlite3_tbl_product enter...") 
    
    #连接数据库  
    try:
        sqlite_conn = sqlite3.connect(DB_SQLITE_PATH)
    except sqlite3.Error, e:
         print 'conntect sqlite database failed.'
         sqlite_logger.error("conntect sqlite database failed, ret = %s" % e.args[0])    
         return    
     
    sqlite_logger.info("conntect sqlite database(%s) succ." % DB_SQLITE_PATH) 
     
    #获取游标 
    sqlite_cursor = sqlite_conn.cursor()
    
    #删除表
    sql_desc2 = "DROP TABLE IF EXISTS tbl_product3;"
    try:
        sqlite_cursor.execute(sql_desc2)
    except sqlite3.Error, e:
         print 'drop table failed'
         sqlite_logger.error("drop table failed, ret = %s" % e.args[0])
         sqlite_cursor.close()
         sqlite_conn.close()      
         return
    sqlite_conn.commit()    
    
    sqlite_logger.info("drop table(tbl_product3) succ.") 
    
    #建表
    sql_desc = '''CREATE TABLE tbl_product3(
    i_index INTEGER PRIMARY KEY,
    sv_productname VARCHAR(32)
    );'''
    try:
        sqlite_cursor.execute(sql_desc)
    except sqlite3.Error, e:
         print 'drop table failed.'
         sqlite_logger.error("drop table failed, ret = %s" % e.args[0])
         sqlite_cursor.close()
         sqlite_conn.close()    
         return    
    sqlite_conn.commit()
    
    sqlite_logger.info("create table(tbl_product3) succ.") 
    
    #插入记录
    sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"
    try:
        sqlite_cursor.execute(sql_desc)
    except sqlite3.Error, e:
        print 'insert record failed.'
        sqlite_logger.error("insert record failed, ret = %s" % e.args[0])  
        sqlite_cursor.close()
        sqlite_conn.close()    
        return    
    sqlite_conn.commit()
    
    sqlite_logger.info("insert record into table(tbl_product3) succ.") 
    
    #查询记录
    sql_desc = "SELECT * FROM tbl_product3;"    
    sqlite_cursor.execute(sql_desc)
    for row in sqlite_cursor:
        print row
        sqlite_logger.info("%s", row) 
    
    #关闭游标和数据库句柄    
    sqlite_cursor.close()
    sqlite_conn.close()
    
    sqlite_logger.debug("operate_sqlite3_tbl_product leaving...") 

if __name__ == '__main__': 
    
    #初始化日志系统
    sqlite_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME)   
    
    #操作数据库
    operate_sqlite3_tbl_product()
    

[2012-02-12 12:13:52,131  sqlite_log]DEBUG:  operate_sqlite3_tbl_product enter... (test_log.py:39)
[2012-02-12 12:13:52,147  sqlite_log]INFO:  conntect sqlite database(.\db\sqlite_pytest.db) succ. (test_log.py:49)
[2012-02-12 12:13:52,147  sqlite_log]INFO:  drop table(tbl_product3) succ. (test_log.py:66)
[2012-02-12 12:13:52,240  sqlite_log]INFO:  create table(tbl_product3) succ. (test_log.py:83)
[2012-02-12 12:13:52,365  sqlite_log]INFO:  insert record into table(tbl_product3) succ. (test_log.py:97)
[2012-02-12 12:13:52,365  sqlite_log]INFO:  (1, u'apple') (test_log.py:104)
[2012-02-12 12:13:52,365  sqlite_log]DEBUG:  operate_sqlite3_tbl_product leaving... (test_log.py:110)

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\socrates.WINXP-DUANYX>cd /d E:\Study\学习\工作程序\py_
test\src\db

E:\Study\学习\工作程序\py_test\src\db>sqlite3.exe sqlite_pytest.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
tbl_product3
sqlite> select * from tbl_product3;
1|apple
sqlite> .quit

E:\Study\学习\工作程序\py_test\src\db>