当前位置: 首页 > news >正文

【Python进阶】Python中的数据库交互:使用SQLite进行本地数据存储

1、数据持久化与访问效率

数据持久化是指程序运行过程中产生的数据能够长期保存,即使程序关闭或系统重启后仍可读取和修改。通过数据库,我们可以确保数据持久化的同时,实现数据的快速访问。例如,银行系统需要实时更新账户余额,并保证在高并发环境下数据的一致性,这就需要依赖数据库的事务处理能力和高效的索引机制。

实例解析: 考虑一个简单的图书管理系统,用户可以添加、查询、修改和删除书籍信息。我们借助SQLite数据库实现数据持久化,以下是一个创建书籍表并插入数据的Python代码片段:

import sqlite3# 连接到SQLite数据库(如果不存在则自动创建)
conn = sqlite3.connect('library.db')# 创建游标对象
cursor = conn.cursor()# 定义书籍表结构
cursor.execute('''CREATE TABLE IF NOT EXISTS Books(ID INTEGER PRIMARY KEY,Title TEXT,Author TEXT,Publisher TEXT)''')# 插入书籍数据
book_data = ('001', 'Python Programming', 'John Doe', 'Tech Publisher')
cursor.execute("INSERT INTO Books VALUES (?, ?, ?, ?)", book_data)# 提交事务
conn.commit()# 关闭数据库连接
conn.close()

这段代码展示了如何通过Python内置的sqlite3模块与SQLite数据库交互,完成数据的持久化存储。同时,SQLite支持快速查找和更新数据,对于频繁查询和修改的需求提供了有力的支持。

2、数据库系统概览

2.1 关系型数据库与非关系型数据库

数据库系统作为现代应用的基础架构,主要分为关系型数据库(Relational Database)和非关系型数据库(NoSQL Database)两大阵营。关系型数据库遵循关系模型理论,数据以表格形式存储,并通过预定义的关系来建立不同表格之间的联系,如MySQL、PostgreSQL及本章将重点探讨的SQLite。

2.1.1 SQL与NoSQL数据库对比

SQL(Structured Query Language)是关系型数据库的标准语言,其核心特点是强一致性、事务处理以及高度结构化的数据存储。SQL数据库通过预先设定好的表格结构(Schema),确保了数据的一致性和完整性,适用于高度结构化、复杂事务处理的应用场景。

相比之下,NoSQL数据库放弃了严格的表格结构约束,更适合处理大量非结构化或半结构化数据,具有水平扩展性强、高性能读写等特点。常见的NoSQL数据库包括MongoDB(文档型)、Cassandra(列族型)和Redis(键值型)。然而,NoSQL数据库通常不支持完整的ACID(原子性、一致性、隔离性、持久性)事务,而是采取BASE(Basically Available, Soft-state, Eventually Consistent)模型,以牺牲一定程度的数据一致性换取更高的可用性和扩展性。

2.2 SQLite数据库简介

2.2.1 SQLite的特点与适用场景

SQLite是一款轻量级、零配置、服务器less的嵌入式SQL数据库引擎,它的文件型数据库设计使得它可以被轻松地内置于各种应用程序中,无需单独部署数据库服务器。SQLite非常适合小型、单用户、移动设备或客户端应用,例如手机APP、桌面软件和个人数据存储。

独立性:SQLite数据库就是一个单一的文件,易于携带和备份。
嵌入式:SQLite直接与应用程序集成,不需要额外的数据库服务器进程。
性能:尽管是轻量级数据库,但SQLite依然具备强大的性能表现,尤其在读取密集型任务上表现出色。

2.2.2 SQLite与其它数据库系统的比较

相比MySQL、PostgreSQL等传统关系型数据库,SQLite在资源占用、部署简易度等方面具有显著优势。然而,对于多用户、高并发环境下的企业级应用,SQLite可能不是最佳选择,因为其并发控制能力有限,且不支持多用户同时写入同一数据库文件。

下面展示一段SQLite数据库的基本操作示例,帮助读者直观感受SQLite的使用方式:

import sqlite3# 连接到SQLite数据库(如果不存在则创建)
conn = sqlite3.connect('my_database.db')# 创建游标对象用于执行SQL命令
cursor = conn.cursor()# 创建一个名为Users的新表
cursor.execute('''CREATE TABLE Users(Id INTEGER PRIMARY KEY AUTOINCREMENT,Username TEXT NOT NULL UNIQUE,Email TEXT NOT NULL UNIQUE)''')# 插入一条用户数据
cursor.execute("INSERT INTO Users (Username, Email) VALUES (?, ?)", ('Alice', 'alice@example.com'))# 提交事务
conn.commit()# 查询所有用户数据
cursor.execute("SELECT * FROM Users")
print(cursor.fetchall())# 关闭数据库连接
conn.close()

这段Python代码演示了如何使用sqlite3模块创建SQLite数据库、定义表格、插入数据以及查询数据的过程,体现了SQLite简单易用的特性。通过深入了解SQLite与其他数据库系统的异同,开发者可以根据项目需求灵活选择合适的数据库技术,有效应对各类数据存储挑战。

3、Python与SQLite的集成

3.1 Python标准库:sqlite3

在Python世界中,SQLite得到了官方标准库sqlite3的全力支持,使得开发者能够便捷地在Python程序中利用SQLite进行本地数据存储和管理。

3.1.1 安装与引入sqlite3模块

Python自带sqlite3模块,无需额外安装即可直接使用。只需在Python脚本中通过import sqlite3语句引入该模块,就可以开始SQLite数据库之旅。

import sqlite3

3.2 创建与管理SQLite数据库

3.2.1 连接SQLite数据库

当要与SQLite数据库交互时,首先需要建立连接。连接既可以指向一个新的数据库文件,也可以打开一个已经存在的SQLite数据库。

3.2.1.1 创建新数据库

新建数据库非常简单,只要在连接字符串中指定数据库名称即可。如果对应的文件不存在,sqlite3会自动创建新的数据库文件。

# 创建并连接到新的SQLite数据库(如果不存在,则创建)
conn = sqlite3.connect('new_database.db')
3.2.1.2 打开已存在的数据库

若数据库文件已经存在,只需提供正确的路径即可打开。

# 打开已存在的SQLite数据库
existing_conn = sqlite3.connect('/path/to/existing_database.db')
3.2.2 数据库连接与游标的使用
数据库连接:连接对象(如conn)是与SQLite数据库交互的主要途径,负责开启和管理整个数据库会话。
游标:在连接对象上调用cursor()方法可以获取一个游标对象,它是执行SQL命令和获取查询结果的接口。通过游标执行SQL语句,提交事务,以及处理结果集。
# 获取游标对象
cursor = conn.cursor()# 执行一条SQL语句(例如创建一个新表)
cursor.execute("CREATE TABLE my_table (id INTEGER PRIMARY KEY, name TEXT)")# 提交事务,确保更改生效
conn.commit()

3.2.3 数据库事务控制与ACID属性

SQLite遵循ACID原则,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。在Python中,可以通过数据库连接对象的begin()、commit()和rollback()方法来进行事务控制。

try:# 开始一次事务conn.begin()cursor.execute("INSERT INTO my_table (name) VALUES (?)", ('Alice',))cursor.execute("INSERT INTO my_table (name) VALUES (?)", ('Bob',))# 提交事务,确认更改conn.commit()
except Exception as e:# 如果发生错误,回滚事务conn.rollback()print(f"Transaction failed: {e}")

4、SQLite数据库操作实践

4.1 建立表格

4.1.1 SQL语法与数据类型

在SQLite中,创建表格需使用SQL的Data Definition Language(DDL)语句——CREATE TABLE。表格由列名、数据类型和约束条件构成,例如,我们可以创建一个学生信息表格:

CREATE TABLE Students (id INTEGER PRIMARY KEY,name TEXT NOT NULL,age INTEGER,gender CHAR(1),major TEXT
);

这里定义了一个名为Students的表格,包含五列:id作为主键,使用整数类型;name为必填项,类型为文本;age为整数类型;gender为单字符长度文本,用于表示性别;major也是文本类型,用来记录学生的专业。

4.1.2 使用Python执行DDL命令

在Python中,通过sqlite3模块的游标对象执行DDL命令。以下是如何使用Python创建上述Students表格的例子:

import sqlite3# 连接到SQLite数据库(如果不存在则创建)
conn = sqlite3.connect('students_db.sqlite3')# 创建游标对象
cursor = conn.cursor()# 创建表格的SQL语句
create_table_sql = """
CREATE TABLE IF NOT EXISTS Students (id INTEGER PRIMARY KEY,name TEXT NOT NULL,age INTEGER,gender CHAR(1),major TEXT
)
"""# 执行SQL语句
cursor.execute(create_table_sql)# 提交事务
conn.commit()# 关闭数据库连接
conn.close()

4.2 插入数据

4.2.1 INSERT INTO语句

向表格中插入数据,我们需要使用INSERT INTO语句。例如,为Students表格插入一条学生记录:

INSERT INTO Students (id, name, age, gender, major) VALUES (1, 'Alice', 20, 'F', 'Computer Science');

4.2.2 Python实现数据插入示例

在Python中,同样通过游标对象执行INSERT INTO语句:

# 重新连接数据库并创建游标
conn = sqlite3.connect('students_db.sqlite3')
cursor = conn.cursor()# 准备插入的数据
student_data = (1, 'Alice', 20, 'F', 'Computer Science')# 插入数据的SQL语句
insert_student_sql = "INSERT INTO Students (id, name, age, gender, major) VALUES (?, ?, ?, ?, ?)"# 执行SQL插入数据
cursor.execute(insert_student_sql, student_data)# 提交事务
conn.commit()# 关闭数据库连接
conn.close()

4.3 查询数据

4.3.1 SELECT基本语法

查询数据主要通过SELECT语句实现,可以选择特定列或者所有列,还可以添加WHERE子句进行条件筛选。例如,查询所有年龄大于18岁的学生信息:

SELECT * FROM Students WHERE age > 18;

4.3.2 Python执行查询并处理结果集

在Python中执行查询并处理结果:

# 重新连接并创建游标
conn = sqlite3.connect('students_db.sqlite3')
cursor = conn.cursor()# 查询年龄大于18岁的学生
select_students_sql = "SELECT * FROM Students WHERE age > 18"
cursor.execute(select_students_sql)# 获取所有查询结果
students_over_18 = cursor.fetchall()for student in students_over_18:print(f"Student ID: {student[0]}, Name: {student[1]}, Age: {student[2]}, Gender: {student[3]}, Major: {student[4]}")# 关闭数据库连接
conn.close()

4.4 更新与删除数据

4.4.1 UPDATE与DELETE操作

更新数据使用UPDATE语句,如将学生Alice的年龄改为21岁:

UPDATE Students SET age = 21 WHERE name = 'Alice';

删除数据使用DELETE语句,比如删除所有计算机科学专业的学生记录:

DELETE FROM Students WHERE major = 'Computer Science';

4.4.2 Python操作UPDATE与DELETE实例

在Python中执行更新和删除操作:

# 更新操作
update_age_sql = "UPDATE Students SET age = 21 WHERE name = 'Alice'"
cursor.execute(update_age_sql)
conn.commit()# 删除操作
delete_major_sql = "DELETE FROM Students WHERE major = 'Computer Science'"
cursor.execute(delete_major_sql)
conn.commit()# 最后别忘了关闭连接
conn.close()

5、高级SQLite功能与Python应用

5.1 视图与索引

5.1.1 创建与使用视图

视图是一种虚拟表,它并不实际存储数据,而是基于其他表或视图的结果集定义的一种查询。视图简化了复杂查询,并提供了数据抽象层。在SQLite中创建视图可通过CREATE VIEW语句实现。例如,假设我们有一个员工表Employees,可以创建一个只显示薪资超过5000的员工视图:

CREATE VIEW HighSalaryEmployees AS
SELECT * FROM Employees
WHERE Salary > 5000;

然后在Python中,可以像查询普通表一样查询这个视图:

cursor.execute("SELECT * FROM HighSalaryEmployees")
high_salary_employees = cursor.fetchall()
for employee in high_salary_employees:print(employee)

5.1.2 创建与管理索引提升查询性能

索引是数据库中为了加速查询而建立的一种数据结构。在SQLite中,创建索引可以显著提高查询性能,尤其是对大表进行检索时。以下是如何创建一个索引的示例,假设我们在Employees表的LastName列上创建一个索引:

CREATE INDEX idx_LastName ON Employees (LastName);

Python中并不直接涉及索引的创建,但在查询时,SQLite会自动识别并利用已存在的索引来加快查询速度。

5.2 多表关联查询与JOIN操作

5.2.1 INNER JOIN, LEFT JOIN等操作符

在SQLite中,JOIN操作允许我们跨多个表查询数据。例如,假设有两个表Departments和Employees,它们通过DepartmentID关联:

# -- INNER JOIN: 只返回两个表中匹配的行
SELECT E.Name, D.DepartmentName
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.ID;
# -- LEFT JOIN: 返回左表的所有行,即使右表没有匹配
SELECT E.Name, D.DepartmentName
FROM Employees E
LEFT JOIN Departments D ON E.DepartmentID = D.ID;

在Python中执行JOIN查询:

inner_join_sql = """
SELECT E.Name, D.DepartmentName
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.ID
"""
cursor.execute(inner_join_sql)
result_set = cursor.fetchall()left_join_sql = """
SELECT E.Name, D.DepartmentName
FROM Employees E
LEFT JOIN Departments D ON E.DepartmentID = D.ID
"""
cursor.execute(left_join_sql)
left_result_set = cursor.fetchall()

5.2.2 在Python中实现JOIN查询

Python代码会执行SQL语句并处理JOIN查询结果,如上所示。

5.3 存储过程与触发器

5.3.1 SQLite中的存储过程定义与调用

存储过程是一组预先编译好的SQL语句集合,可以在数据库中作为一个单元进行调用。SQLite虽支持基本的函数定义,但对于复杂存储过程的支持相对较弱。以下是一个简单的自定义函数示例:

CREATE FUNCTION total_salaries() RETURNS REAL AS $$
SELECT SUM(Salary) FROM Employees;
$$ LANGUAGE SQL;

在Python中调用此存储过程:

cursor.execute("SELECT total_salaries()")
total_salary = cursor.fetchone()[0]
print(f"Total salaries: {total_salary}")

5.3.2 触发器的创建与作用

触发器是在特定数据库操作(如INSERT、UPDATE或DELETE)发生时自动执行的一段SQL代码。例如,创建一个触发器以在插入新员工时自动更新部门总人数:

CREATE TRIGGER UpdateDeptCount
AFTER INSERT ON Employees
BEGINUPDATE DepartmentsSET EmployeeCount = EmployeeCount + 1WHERE ID = NEW.DepartmentID;
END;

尽管SQLite支持触发器,但在Python中并不直接涉及到触发器的调用,触发器会在相应数据库操作发生时自动执行。

6、Python中SQLite的实际应用场景

6.1 日志记录与数据分析

6.1.1 使用SQLite存储应用程序日志

在软件开发过程中,日志记录是至关重要的一步,它可以帮助开发者追踪和排查潜在的问题。SQLite因其轻量、无需服务器配置的特点,非常适合用于存储和查询应用程序日志。例如,我们可以创建一个Logs表来记录错误、警告和信息级别的日志条目:

import sqlite3# 连接到SQLite数据库
conn = sqlite3.connect('app_logs.db')
cursor = conn.cursor()# 创建日志表
cursor.execute("""CREATE TABLE Logs (LogID INTEGER PRIMARY KEY AUTOINCREMENT,Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,LogLevel TEXT,Message TEXT)
""")# 插入日志条目示例
log_entry = ("ERROR", "Failed to connect to server.")
cursor.execute("INSERT INTO Logs (LogLevel, Message) VALUES (?, ?)", log_entry)
conn.commit()# 查询特定级别日志
cursor.execute("SELECT * FROM Logs WHERE LogLevel=?", ("ERROR",))
error_logs = cursor.fetchall()
for log in error_logs:print(log)# 关闭数据库连接
conn.close()

6.1.2 Pandas与SQLite结合进行本地数据分析

Pandas是一个强大的数据处理库,可以很好地与SQLite配合进行本地数据分析。例如,我们可以将CSV数据导入SQLite数据库,然后使用SQL查询进行数据清洗和分析,再将结果转换成DataFrame以便进一步可视化或建模。

import pandas as pd
import sqlite3# 将CSV数据加载到DataFrame中
df = pd.read_csv('data.csv')# 连接到SQLite数据库并创建表
conn = sqlite3.connect('data_analysis.db')
df.to_sql('MyTable', conn, if_exists='replace', index=False)# 使用SQL查询数据
query = "SELECT * FROM MyTable WHERE ColumnA > 50"
df_from_db = pd.read_sql_query(query, conn)# 在Python中进行数据分析
grouped_df = df_from_db.groupby('Category').agg({'ColumnB': 'sum'})# 显示结果或进一步操作
print(grouped_df)# 关闭数据库连接
conn.close()

6.2 小型桌面应用的数据存储

6.2.1 嵌入式应用案例分析

在小型桌面应用中,如个人理财软件或待办事项列表,SQLite因其零配置、轻量级和嵌入式的特性成为理想的数据存储方案。例如,一个待办事项应用可能会创建一个Todos表来存储用户的任务清单:

# 创建Todos表
cursor.execute("""CREATE TABLE Todos (TodoID INTEGER PRIMARY KEY,Title TEXT NOT NULL,Description TEXT,DueDate DATE,Completed BOOLEAN DEFAULT FALSE)
""")# 插入、查询和更新任务示例
add_todo = ("Buy groceries", "Get milk and bread", "2023-03-01")
cursor.execute("INSERT INTO Todos (Title, Description, DueDate) VALUES (?, ?, ?)", add_todo)
conn.commit()# 查询未完成的任务
cursor.execute("SELECT * FROM Todos WHERE Completed = 0")
pending_todos = cursor.fetchall()# 标记任务已完成
complete_todo_id = 1
cursor.execute("UPDATE Todos SET Completed = 1 WHERE TodoID = ?", (complete_todo_id,))
conn.commit()

6.3 移动应用开发中的SQLite

6.3.1 Android与iOS平台上的SQLite应用

SQLite在移动应用开发领域也广泛应用,特别是在Android和iOS平台上。开发人员可以直接在应用程序内部嵌入SQLite数据库,以实现离线数据存储和同步功能。例如,在Android应用中,可以使用SQLiteOpenHelper类来创建和管理数据库:

public class AppDatabaseHelper extends SQLiteOpenHelper {private static final String DATABASE_NAME = "mobile_app.db";private static final int DATABASE_VERSION = 1;public AppDatabaseHelper(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {String createTableQuery = "CREATE TABLE UserPreferences (PreferenceKey TEXT PRIMARY KEY, PreferenceValue TEXT)";db.execSQL(createTableQuery);}// 其他数据库操作方法...
}

在iOS应用中,可以使用SQLite.swift库来操作SQLite数据库:

import SQLitelet db = try! Connection("path/to/mobile_app.db")let users = Table("Users")
let id = Expression<Int64>("id")
let name = Expression<String>("name")// 创建表
try! db.run(users.create { t int.column(id, primaryKey: true)t.column(name)
})// 插入数据
let newUser = users.insert(name <- "John Doe")
try! db.run(newUser)// 查询数据
for user in try! db.prepare(users) {print("\(user[id]), \(user[name])")
}

7、最佳实践与常见问题解决方案

7.1 数据库设计原则

7.1.1 正确设计数据库模式以避免数据冗余

在设计SQLite数据库时,应遵循一些基本原则以减少数据冗余和提高数据一致性。例如,可以通过规范化设计,将实体间的重复数据转化为外键关联,避免存储重复的信息。设想一个电商应用中的订单系统,用户信息不应在订单表中重复存储,而是通过用户ID关联到用户表。

CREATE TABLE Users (UserID INTEGER PRIMARY KEY,FirstName TEXT,LastName TEXT,Email TEXT UNIQUE
);CREATE TABLE Orders (OrderID INTEGER PRIMARY KEY,UserID INTEGER,ProductID INTEGER,Quantity INTEGER,OrderDate DATE,FOREIGN KEY (UserID) REFERENCES Users(UserID),FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

7.1.2 性能优化策略

索引优化:合理创建索引可以大幅提升查询性能。对于经常出现在WHERE子句中的字段,应为其创建索引。例如,如果我们频繁根据用户名查询用户信息,应在Users表的FirstName和LastName字段上创建索引。

CREATE INDEX idx_Users_FirstName ON Users (FirstName);
CREATE INDEX idx_Users_LastName ON Users (LastName);

查询优化:尽量减少JOIN操作的数量和范围,避免全表扫描,合理利用LIMIT和OFFSET限制查询结果数量,以及避免在WHERE子句中对TEXT类型的字段进行模糊查询。

7.2 Python操作SQLite时的异常处理

7.2.1 错误处理与回滚机制

在Python中,使用sqlite3模块与SQLite交互时,应当正确处理可能出现的异常,并适时使用事务管理确保数据的一致性。

import sqlite3conn = sqlite3.connect('example.db')
try:# 开启事务conn.execute('BEGIN TRANSACTION;')# 执行一系列SQL命令conn.execute("INSERT INTO Users VALUES (?, ?, ?, ?)", (1, 'Alice', 'Smith', 'alice@example.com'))conn.execute("INSERT INTO Orders VALUES (?, ?, ?, ?, ?)", (1, 1, 10, '2022-01-01'))# 提交事务conn.commit()
except sqlite3.Error as e:# 发生错误时回滚事务conn.rollback()print(f"An error occurred: {e}")
finally:# 关闭数据库连接conn.close()

7.3 数据迁移与备份恢复

7.3.1 数据库的导出与导入方法

将SQLite数据库导出为SQL文件,可以方便地进行数据迁移或备份:

import sqlite3
import os# 导出数据库到.sql文件
with sqlite3.connect('source.db') as source_conn:with open('database_dump.sql', 'w') as f:for line in source_conn.iterdump():f.write("%s\n" % line)# 导入.sql文件到新的数据库
with open('database_dump.sql', 'r') as f:sql_commands = f.read()with sqlite3.connect('target.db') as target_conn:target_conn.executescript(sql_commands)

7.3.2 SQLite数据库的备份与恢复操作

除了导出为SQL文件,还可以直接复制SQLite数据库文件实现备份。恢复时只需将备份文件覆盖原数据库文件即可。同时,SQLite还提供了.backup命令实现在线备份,但这需要通过命令行工具或其他支持该命令的API进行操作。


http://www.mrgr.cn/news/71186.html

相关文章:

  • pytorch官方FasterRCNN代码详解
  • 《关于加大信用教育力度的建议书》
  • Python 快速入门(上篇)❖ Python基础知识
  • 【SLAM文献阅读】基于概率模型的视觉SLAM动态检测与数据关联方法
  • 线性回归模型预测房价
  • 数字图像处理(2):Verilog基础语法
  • 【蓝桥等考C++真题】蓝桥杯等级考试C++组第13级L13真题原题(含答案)-小蓝的目标
  • 国标GB28181-2022平台EasyGBS国标GB28181软件在GB/T28181协议中的加密措施
  • 2024要如何对公司CAD图纸进行加密?十种方法加密保护图纸!
  • GitHub4.2k Java开源神器一个免费的基于JNA的Java操作系统和硬件信息库
  • 重磅!Pubmed停止更新了?
  • 反沙箱 反虚拟机的一些笔记
  • 【Linux实践2】实验三:死锁的避免
  • 系统架构师-考点梳理
  • uniapp vuex的使用
  • Transformer(三):论文 Attention Is All You Need
  • 【2024最新】渗透测试工具大全(超详细),收藏这一篇就够了!
  • 【comfyui教程】comfyui攻略:故障报错应对指南!
  • 神经网络算法
  • 基于51单片机俄罗斯方块游戏—可暂停
  • 安卓全屏实现
  • 【锁】聊一聊ReentrantLock 和 Synchronized 的区别
  • 丹摩征文活动|CogVideoX-2b:从安装到上线,轻松搞定全过程!
  • QML —— 圆形波浪进度条控件(附上源码)
  • docker save 和 docker load介绍
  • 常用的8款电脑加密软件分享|电脑办公文件加密软件推荐!