Python SQLite3操作

SQLite是一个轻量级数据库，Python通过sqlite3模块提供了调用接口，本文通过一个简单例子，对sqlite3模块的基本接口使用方法进行简单的说明。

1. 模块导入

import sqlite3

2. 创建连接

导入模块后，开始连接数据库，首先定义存储数据库文件的名称，本例中是meter.db，存放在当前目录下面。第一次调用时文件不存在，就会创建meter.db文件，函数返回一个connect对象。数据库接口容易发生一些错误，为了保证程序不会崩溃，需要增加异常处理。

self.db_name = "meter.db"
self.db_error = DB_SUCCESS

try:
    self.db_connect = sqlite3.connect(self.db_name)
except Exception as e:
    info = traceback.format_exc()
    print(info)
    self.db_connect = None
    self.db_error = DB_CONNECT_FAILED

支持RAM数据，使用方式如下：

# 在内存中创建数据库
con = sqlite3.connect(":memory:")

3.创建数据库表

基本流程是固定的，首先通过connect对象获取游标对象cursor，通过cursor执行数据的建表语句，本例中建立了db_info、check_setting和check_result表，然后调用commit方法，最后关闭cursor。

cursor = self.db_connect.cursor()
cursor.execute(
    "CREATE TABLE IF NOT EXISTS db_info(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, version INT, time LONG)"
)

cursor.execute(
    "CREATE TABLE IF NOT EXISTS check_setting(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, meter_no TEXT NOT NULL UNIQUE, type TEXT, min REAL NOT NULL, max REAL NOT NULL)"
)

cursor.execute(
    "CREATE TABLE IF NOT EXISTS check_result(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, meter_no TEXT NOT NULL, check_no TEXT NOT NULL, value REAL NOT NULL, time LONG)"
)
self.db_connect.commit()
cursor.close()

4. 插入数据库表数据

基本流程是固定的，首先通过connect对象获取游标对象cursor，通过cursor执行插入表数据的语句，本例中插入数据有四列，对应的sql语句“check_result(meter_no, check_no, value, time) VALUES(?,?,?,?)”，传入参数组织为list对象，然后调用commit方法，最后关闭cursor。

cursor = self.db_connect.cursor()
exe_sql = "INSERT INTO check_result(meter_no, check_no, value, time) VALUES(?,?,?,?)"
values = [no, check_no, check_value, time.time()]
cursor.execute(exe_sql, values)
self.db_connect.commit()
cursor.close()

5.查询数据库数据

首先通过connect对象获取游标对象cursor，组织sql语句，通过fetchall接口获取查询结果，最后关闭cursor。

cursor = self.db_connect.cursor()
projection = ["meter_no", "type", "min", "max"]
exe_sql = "SELECT " + ",".join(projection) \
          + " from check_setting order by meter_slice ASC, meter_column ASC, meter_row ASC"
cursor.execute(exe_sql)
result = cursor.fetchall()
cursor.close()

连表查询举例

cursor = self.db_connect.cursor()
exe_sql = \
    "SELECT check_result.check_no, check_setting.meter_no, check_setting.type, check_setting.min, \
     check_setting.max, check_result.value, check_result.time from check_result inner join check_setting  \
     on check_result.meter_no=check_setting.meter_no where check_result.check_no=?"
cursor.execute(exe_sql, [check_no])
result = cursor.fetchall()
cursor.close()

6. 删除数据库记录

首先通过connect对象获取游标对象cursor，组织sql语句，然后执行commit接口，最后关闭cursor。

cursor = self.db_connect.cursor()
exe_sql = "DELETE FROM check_result WHERE meter_no=?"
cursor.execute(exe_sql, [delete_no])
self.db_connect.commit()
cursor.close()

7. 更新数据库记录

首先通过connect对象获取游标对象cursor，组织sql语句，然后执行commit接口，最后关闭cursor。

cursor = self.db_connect.cursor()
exe_sql = "UPDATE check_result SET value=?"
cursor.execute(exe_sql, [check_value])
self.db_connect.commit()
cursor.close()

8. 删除表

cursor = self.db_connect.cursor()
exe_sql = "DROP TABLE check_result"
cursor.execute(exe_sql)
self.db_connect.commit()
cursor.close()

9. 数据库升级

没有找到sqlite3提供的数据库版本的升级方法，我通常使用创建db_info表，在里面保存数据库的版本号，每次启动的时候，对比当前应用的版本号和存储的数据库文件的版本号，根据版本号信息，对已经存在的数据库的相关表信息进行升级。