器→工具, 工具软件

轻量级数据库SQLite

钱魏Way · · 150 次浏览
!文章内容如有错误或排版问题，请提交反馈，非常感谢！

SQLite简介

SQLite是一个轻量级的、嵌入式的关系型数据库管理系统（RDBMS）。它的设计初衷是为了提供一个简便、快速、可靠的数据库解决方案，不需要独立的服务器进程即可运行，适合嵌入到各种应用程序中。

主要特性

  • 自包含：SQLite是一个自包含的库，所有的功能都打包在一个单独的文件中。它不需要额外的配置或依赖，可以直接在任何支持C的环境中编译和运行。
  • 无服务器：不同于MySQL、PostgreSQL等需要单独的服务器进程，SQLite不需要服务器。数据库操作直接在本地文件系统上进行，简化了部署和管理。
  • 零配置：无需复杂的安装和配置过程。SQLite数据库文件可以直接在文件系统中创建和访问，适合快速开发和原型设计。
  • 跨平台：SQLite是跨平台的，可以在几乎所有的操作系统上运行，包括Windows、Linux、macOS、iOS、Android等。
  • 事务支持：SQLite支持ACID（原子性、一致性、隔离性、持久性）事务特性，确保数据的完整性和安全性。
  • 丰富的数据类型支持：SQLite支持多种数据类型，包括整数、浮点数、文本、BLOB（大对象）等，尽管它使用动态类型系统。
  • 广泛的语言绑定：SQLite提供多种编程语言的绑定，包括C/C++、Python、Java、C#、PHP等，使其易于集成到不同的开发环境中。

典型应用场景

  • 嵌入式应用：由于其小巧和无服务器的特性，SQLite非常适合嵌入式设备和应用，如移动应用、浏览器扩展、嵌入式系统等。
  • 本地/客户端存储：SQLite常用于需要本地存储数据的应用程序中，例如桌面应用和移动应用，以便在无网络连接时也能访问数据。
  • 测试和原型设计：因为部署简单，SQLite是开发和测试环境的理想选择，特别是在需要快速原型设计和迭代的场景中。
  • 小型网站和应用：对于流量和数据量不大的小型网站和应用，SQLite提供了足够的功能和性能。

SQLite的局限性

  • 并发写入限制：SQLite使用数据库级别的锁机制来处理写操作，因此在高并发写入场景下可能会成为瓶颈。
  • 单用户写入：虽然支持多个读取者同时访问，但一次只能有一个写入者进行操作，这在高写入频率的应用中可能不够。
  • 功能限制：与更复杂的数据库系统相比，SQLite缺少一些高级功能，如复杂的权限管理、分布式数据库特性等。

总之，SQLite是一个功能强大、使用简单的数据库解决方案，特别适合嵌入式系统和需要轻量级数据库支持的应用程序。它的无服务器和零配置特性使其成为开发人员的热门选择。

SQLite的使用

SQLite支持的数据类型

SQLite的数据类型系统与传统的关系型数据库有所不同，它采用了一种称为”动态类型”（dynamic typing）的灵活系统。虽然在创建表时可以指定列的数据类型，但SQLite并不严格限制存储在列中的数据类型。以下是SQLite支持的主要数据类型：

  • NULL表示一个空值。可以用于表示数据缺失或未知的情况。
  • INTEGER用于存储有符号的整数，大小根据值的大小自动调整，可以是1、2、3、4、6或8字节。
  • REAL用于存储浮点数字，采用8字节的IEEE浮点数格式。
  • TEXT用于存储文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）进行存储。
  • BLOB用于存储二进制数据，存储的内容与输入的内容完全一致，不进行任何编码或转换。

类型关联（Type Affinity）

SQLite使用”类型关联”来决定如何处理不同的数据类型。即使指定了某个数据类型，SQLite仍然可以存储不同类型的数据。SQLite的类型关联分为以下几类：

  • INTEGER：如果列具有INTEGER类型关联，SQLite优先将数据视为整数。
  • REAL：如果列具有REAL类型关联，SQLite优先将数据视为浮点数。
  • TEXT：如果列具有TEXT类型关联，SQLite优先将数据视为字符串。
  • BLOB：如果列具有BLOB类型关联，数据将被存储为它的输入形式。
  • NUMERIC：如果列具有NUMERIC类型关联，SQLite会根据输入的格式自动选择INTEGER、REAL或TEXT类型。

类型推断

SQLite在插入数据时，会根据输入值的格式自动推断其数据类型。例如：

  • 如果插入的值是一个整数，SQLite将尝试将其存储为INTEGER。
  • 如果插入的值是一个浮点数，SQLite将尝试将其存储为REAL。
  • 如果插入的值是一个字符串（例如包含非数字字符），SQLite将尝试将其存储为TEXT。

SQLite的数据类型系统提供了灵活性，允许在列中存储不同类型的数据。这种灵活性适合多种应用场景，但在设计数据库架构时需要谨慎，以避免数据不一致的问题。了解SQLite的类型系统有助于更好地利用其功能和特性。

SQLite对SQL92标准的支持

SQLite是一个轻量级的数据库引擎，虽然它支持许多SQL92标准的特性，但并不完全支持整个标准。以下是SQLite对SQL92标准的一些支持情况：

支持的SQL92特性

  • 基本查询：支持基本的SELECT 查询，包括 WHERE、ORDER BY、GROUP BY、HAVING 等子句。
  • 数据操作语言(DML)：支持INSERT、UPDATE 和 DELETE 语句，用于数据的增删改操作。
  • 数据定义语言(DDL)：支持CREATE TABLE、ALTER TABLE 和 DROP TABLE 等语句，用于定义和修改数据库结构。支持 CREATE INDEX 和 DROP INDEX，用于索引的管理。
  • 事务控制：支持BEGIN TRANSACTION、COMMIT 和 ROLLBACK，用于事务的控制和管理。
  • 视图：支持CREATE VIEW 和 DROP VIEW，可以定义和删除视图。
  • 聚合函数：支持常见的聚合函数，如COUNT、SUM、AVG、MIN、MAX。
  • 连接操作：支持INNER JOIN、LEFT JOIN、CROSS JOIN 等连接操作。
  • 子查询：支持在SELECT、INSERT、UPDATE 和 DELETE 语句中使用子查询。
  • 窗口函数：自SQLite 3.25.0版本（2018年9月发布）开始，SQLite引入了对窗口函数的支持。

不完全支持或不支持的SQL92特性

  • 外键约束：虽然SQLite支持外键约束，但默认情况下是禁用的。需要通过PRAGMA foreign_keys=ON; 启用。
  • 复杂数据类型：不支持一些复杂的数据类型，如ENUM、SET、存储过程和触发器（虽然支持简单的触发器）。
  • 权限管理：SQLite没有用户管理和权限控制功能，因为它是一个嵌入式数据库，通常用于单用户或单应用场景。
  • 存储过程：不支持存储过程和函数的定义，这在某些复杂应用中可能是一个限制。
  • 某些高级SQL特性：不支持某些SQL92标准的高级特性，如RIGHT JOIN、FULL OUTER JOIN、CHECK约束的复杂表达式等。

SQLite提供了许多符合SQL92标准的基本功能，足以满足大多数嵌入式和小型应用的需求。然而，由于其设计目标是轻量级和嵌入式，SQLite在某些高级功能和特性上有所限制。因此，在选择SQLite作为数据库引擎时，需要根据具体应用场景评估其功能是否满足需求。

图形用户界面（GUI）客户端

  • DBBrowser for SQLite: 一个开源的、跨平台的SQLite数据库管理工具，提供简单易用的图形界面，可以执行SQL查询、查看和编辑表数据、管理数据库结构等。
  • SQLiteStudio: 一个免费的SQLite数据库管理工具，支持跨平台，提供丰富的功能，包括数据库管理、SQL查询、表格设计等。
  • DBeaver: 一个通用的数据库管理工具，支持多种数据库，包括SQLite。它提供了一个强大的图形界面，适合专业用户进行复杂的数据库管理任务。
  • DataGrip: 由JetBrains开发的数据库管理工具，支持多种数据库系统，包括SQLite。DataGrip提供智能的SQL编辑器和数据库管理功能。
  • Navicat for SQLite: 款商业数据库管理工具，提供强大的数据库设计和管理功能，支持多平台使用。

SQLite在Python中的使用

在Python中使用SQLite非常方便，因为Python的标准库中已经内置了一个名为sqlite3的模块，可以直接用于与SQLite数据库进行交互。

SQLite模块介绍

sqlite3是Python标准库中的一个模块，提供了对SQLite数据库的接口。SQLite是一个轻量级的嵌入式关系数据库管理系统，不需要独立的服务器进程，数据存储在一个文件中。sqlite3模块允许在Python程序中使用SQLite数据库，进行数据的持久化存储和管理。

主要功能

  • 数据库连接：建立与SQLite数据库的连接。
  • SQL执行：执行SQL查询和命令。
  • 事务管理：支持事务的提交和回滚。
  • 数据操作：插入、更新、删除和查询数据。
  • 游标操作：遍历查询结果集。

工作机制

  • 数据库连接：通过connect()创建与SQLite数据库的连接。
  • 游标操作：通过cursor()创建游标对象，使用游标执行SQL语句和获取结果。
  • 事务管理：使用commit()提交事务，使用Connection.rollback()撤销事务。
  • 数据存储：SQLite数据库存储在一个文件中，所有数据都保存在该文件内。

主要组件和函数：

sqlite3.connect()函数

sqlite3.connect()函数用于创建与SQLite数据库的连接。可以连接到一个数据库文件，如果文件不存在，将会创建一个新的数据库文件。

参数：

  • database：数据库文件的名称。如果文件不存在，将创建一个新的文件。
  • timeout：可选参数，指定连接超时时间（秒）。
  • detect_types：可选参数，用于检测类型的标志。
  • isolation_level：可选参数，指定事务隔离级别。
  • check_same_thread：可选参数，指定是否在同一线程中使用连接。
  • factory：可选参数，指定游标工厂。

返回值：返回一个连接对象。

Connection.cursor()方法

Connection.cursor()方法用于创建一个游标对象，通过游标可以执行SQL语句和获取查询结果。

返回值：返回一个游标对象。

Cursor.execute()方法

Cursor.execute()方法用于执行单条SQL语句。

参数：

  • sql：要执行的SQL语句。
  • parameters：可选参数，SQL语句中的参数值。

Cursor.executemany()方法

Cursor.executemany()方法用于执行多条SQL语句。

参数：

  • sql：要执行的SQL语句。
  • seq_of_parameters：参数序列，每个元素是一个参数元组。

Cursor.fetchall()方法

Cursor.fetchall()方法用于获取所有查询结果。

返回值：返回一个列表，其中每个元素是查询结果的一行。

Connection.commit()方法

Connection.commit()方法用于提交当前事务，将所有修改保存到数据库。

Connection.rollback()方法

Connection.rollback()方法用于回滚当前事务，撤销所有未提交的修改。

Connection.close()方法

Connection.close()方法用于关闭与数据库的连接。

SQLite使用示例

连接到数据库

首先，需要导入sqlite3模块并连接到一个SQLite数据库文件。如果数据库文件不存在，SQLite会自动创建一个新的数据库文件。

import sqlite3

# 连接到SQLite数据库（如果数据库不存在，则会自动创建）
connection = sqlite3.connect('example.db')

创建表

使用cursor对象执行SQL语句来创建表。

# 创建一个cursor对象
cursor = connection.cursor()

# 创建一个表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL
)
''')

# 提交事务
connection.commit()

插入数据

可以使用INSERT语句向表中插入数据。

# 插入数据
cursor.execute('''
INSERT INTO users (name, age) VALUES (?, ?)
''', ('Alice', 30))

# 提交事务
connection.commit()

查询数据

使用SELECT语句从表中查询数据。

# 查询数据
cursor.execute('SELECT * FROM users')

# 获取所有结果
rows = cursor.fetchall()

# 遍历并打印结果
for row in rows:
    print(row)

更新数据

可以使用UPDATE语句来更新表中的数据。

# 更新数据
cursor.execute('''
UPDATE users SET age=? WHERE name=?
''', (31, 'Alice'))

# 提交事务
connection.commit()

删除数据

使用DELETE语句从表中删除数据。

# 删除数据
cursor.execute('''
DELETE FROM users WHERE name=?
''', ('Alice',))

# 提交事务
connection.commit()

关闭连接

操作完成后，记得关闭数据库连接。

# 关闭cursor和连接
cursor.close()
connection.close()

使用上下文管理器

在Python中，使用with语句可以更优雅地管理数据库连接和资源，确保在块结束时自动关闭连接。

import sqlite3

with sqlite3.connect('example.db') as connection:
    cursor = connection.cursor()
    # 执行数据库操作
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

通过Python的sqlite3模块，可以轻松地与SQLite数据库进行交互。这个模块提供了执行SQL语句的基本功能，同时也支持事务处理和参数化查询，以确保应用程序的安全性和性能。

相关文章:
  1. PEP 249：Python数据库API规范v2.0
  2. Python如何连接数据库
  3. Hive SQL系统化学习
  4. Python实现的数据库
  5. 时序异常检测实战：酒店价格
数据库
Windows下数据库管理软件的选择
评分卡模型Toad简介
多智能体协作框架CrewAI

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注