Python:操作嵌入式数据库SQLite
去年写过一篇通过C语言操作SQLite数据库的文章《SQLITE学习笔记一(打开、操作及关闭数据库，C程序实现)》，最近在学习python,所以使用ptyhon实现了一下，实现不多描述了，代码中的注释已经非常详细了。直接贴上来。
- 实现：
#!/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 = ".dbsqlite_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(.dbsqlite_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)
3.通过命令行查看：
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:Documents and Settingssocrates.WINXP-DUANYX>cd /d E:Study学习工作程序py_
testsrcdb
E:Study学习工作程序py_testsrcdb>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_testsrcdb>