每日一模块:sqlite3

之前用python写了一个北上资金每日交易数查询的应用，需要每天从网上爬取其股票交易的数据，存储的话我们常见的可以选择存到Mogodb，mysql等数据库，也可以存储到excel这些文档中，但Mogodb这些数据库对于小白需要一定的安装与配置成本，而存储到excel中呢，随着数据的越来越大，加载起来会非常不方便，因此找了下既不需要安装又在性能不错的环境下完美支持python，就选择了SQlite。今天就介绍下python如何使用sqlite。

1. 什么是SQLite?

SQLite是一个进程内的库，实现了自给自足的、跨平台的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。

就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。

2. SQLite3支持的数据类型

SQLite3支持以下数据类型：

1. NULL：空值。
2. INTEGER：整型，可以是1、2、3、4、6或8字节，具体取决于存储的数字范围大小。
3. REAL：浮点数字，存储为8-byte IEEE浮点数。
4. TEXT：字符串文本，存储时不限长度。
5. BLOB：二进制对象，存储时不限大小。

尽管SQLite3主要支持上述五种数据类型，但它实际上也接受其他数据类型，如varchar(n)、char(n)、decimal(p,s)、smallint、float、double、time等。然而，当这些数据类型在SQLite3中进行运算或保存时，它们会被转换为上述的五种主要数据类型之一。

例如，BOOLEAN类型在SQLite3中并不存在，但如果你尝试存储一个布尔值，它会被转换为整型0（表示False）或1（表示True）。同样，decimal(p,s)类型的数值在SQLite3中会被转换为REAL或INTEGER类型，具体取决于其数值范围和精度。

3. 连接到SQLite数据库

sqlite3是python的内置模块，所以无需安装。

首先，你需要创建一个到SQLite数据库的连接。如果数据库不存在，SQLite会自动创建一个。

import sqlite3

# 连接到SQLite数据库
# 数据库文件是example.db
# 如果文件不存在，会自动在当前目录创建
conn = sqlite3.connect('example.db')

4. 创建一个游标对象

游标对象让你能够执行所有的SQL命令。

# 创建一个游标对象cursor
cursor = conn.cursor()

5. 创建部门表

接下来，使用游标对象来创建一个名为departments的表。

# 创建一个部门表
cursor.execute('''
    CREATE TABLE departments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
''')

# 提交事务以保存表结构
conn.commit()

6. 插入部门数据

使用游标对象来插入一些部门数据。

# 插入一些部门数据
cursor.execute("INSERT INTO departments (name, description) VALUES (?, ?)", ('Engineering', 'Software development team'))
cursor.execute("INSERT INTO departments (name, description) VALUES (?, ?)", ('HR', 'Human Resources'))

# 提交事务以保存数据
conn.commit()

sqlite3 中使用? 作为占位符，它会被后面的元组中的值替换。

7. 查询部门数据

使用游标对象来执行一个查询并获取部门数据。

# 查询所有部门
cursor.execute("SELECT * FROM departments")

# 获取查询结果并遍历打印
results = cursor.fetchall()
for row in results:
    print(row)

8. 遍历查询结果

在上面的示例中，我们使用cursor.fetchall()来获取查询结果的所有行。但是，如果你的查询结果包含大量数据，这可能会消耗大量内存。在这种情况下，你可以使用cursor.fetchone()或cursor.fetchmany(size)来逐行或逐批获取结果。

rows = cursor.execute("SELECT * FROM departments")
while True:
    row = rows.fetchone()
    if row is None:
        break
    # 处理行数据...

或者：

rows = cursor.execute("SELECT * FROM departments")
chunk_size = 10
while True:
    chunk = rows.fetchmany(chunk_size)
    if not chunk:
        break
    # 处理数据块...

9. 更新部门数据

使用游标对象来更新一个部门的数据。

# 更新部门描述
cursor.execute("UPDATE departments SET description = ? WHERE name = ?", ('IT Support', 'Engineering'))

# 提交事务以保存更改
conn.commit()

10. 删除部门数据

使用游标对象来删除一个部门。

# 删除一个部门（这里我们假设删除ID为1的部门）
cursor.execute("DELETE FROM departments WHERE id = ?", (1,))

# 提交事务以保存更改
conn.commit()

11. 关闭连接

当你完成所有操作后，关闭数据库连接。

# 关闭连接
conn.close()

12. 使用上下文管理器（可选）

你还可以使用Python的with语句来自动管理数据库连接的打开和关闭，以及事务的提交或回滚。

import sqlite3

# 使用with语句自动管理连接
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    
    # ... 执行一些数据库操作 ...
    
    # 提交事务（在with块结束时会自动提交或回滚）
    # conn.commit()  # 这一步在with块中通常是可选的

# 连接在with语句块结束时自动关闭

13. 错误处理

在数据库操作中，错误处理是非常重要的。你可以使用try...except语句来捕获并处理可能发生的异常。

import sqlite3

try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # ... 执行一些数据库操作 ...
    conn.commit()
except sqlite3.Error as e:
    conn.rollback()
    print(f"An error occurred: {e.args[0]}")
finally:
    if conn:
        conn.close()