Python3: sqlite3 模块 - SQLite3 数据库操作

梦里梦外; 2021-09-21 23:08 521阅读 0赞

本文链接: [https://blog.csdn.net/xietansheng/article/details/115559160][https_blog.csdn.net_xietansheng_article_details_115559160]

[Python3 学习笔记（目录）][Python3]

Python 官方文档:

*  sqlite3 模块: [sqlite3 — DB-API 2.0 interface for SQLite databases][sqlite3 _ DB-API 2.0 interface for SQLite databases]

# 1. 主要对象 #

sqlite3 模块主要对象:

*  数据库连接对象: [sqlite3.Connection][]
 *  游标对象: [sqlite3.Cursor][]

使用`sqlite3`模块，先创建`Connection`数据库连接对象，再创建`Cursor`游标对象，然后调用`cursor.execute()`方法执行 SQL 语句。

## 1.1 Connection ##

连接数据库方法: [sqlite3.connect(database, …)][sqlite3.connect_database_]

# 连接 SQLite 数据, 
    # database 是准备打开的数据库文件路径, 
    # database 也可以传 ":memory:" 在内存中打开一个数据库。
    # 默认返回 Connection 对象
    sqlite3.connect(database[, timeout, detect_types, 
                    isolation_level, check_same_thread,  
                    factory, cached_statements, uri])

> SQLite 原生只支持 5 种类型：TEXT、INTEGER、REAL、BLOB、NULL。  
> 如果想用其它类型，必须自己添加相应的支持。

`sqlite3.Connection`类常用属性和方法:

# 获取/设置 当前默认的隔离级别
    Connection.isolation_level
    
    # 获取游标, 返回 Cursor 实例
    Connection.cursor(factory=Cursor)
    
    # 提交当前事务。如果没有调用该方法，则从上一次 commit() 以来的所有变化在 其他数据库连接 上都不可见。
    Connection.commit()
    
    # 回滚到上一次 commit() 以来的所有数据库的改变
    Connection.rollback()
    
    # 关闭数据库连接。注意，该方法不会自动 commint() 
    Connection.close()
    
    # 备份数据库，target 为 Connection 实例
    Connection.backup(target, *, pages=-1, progress=None, 
                      name="main", sleep=0.250)
    
    # 执行 SQL 语句的快捷方法，Connection 上直接执行 SQL 语句是 非标准的，
    # 它会先调用 cursor() 方法创建一个游标对象后再执行 SQL 语句。
    Connection.execute(sql[, parameters])
    Connection.executemany(sql[, parameters])
    Connection.executescript(sql_script)

> [Python3 SQLite3 的「事务控制」说明][Python3 SQLite3]

## 1.2 Cursor ##

`sqlite3.Cursor`类常用属性和方法:

# 执行单条 SQL 语句，可参数化语句（参数值使用占位符），支持两种占位符: 问号 和 命名占位符。
    # 例如: cursor.execute("insert into people values (?, ?)", (who, age))
    Cursor.execute(sql[, parameters])
    
    # 一条 SQL 语句，使用多个参数序列，多次执行。
    # 例如: params = [(who0, age0), (who1, age1), ...]
    # cursor.executemany("insert into people values (?, ?)", params)
    Cursor.executemany(sql, seq_of_parameters)
    
    # 执行 SQL 脚本，非标准的便捷方法。可用于一次执行多条 SQL 语句，
    # 它会首先执行一条 COMMIT 语句，再执行 SQL 脚本。
    # 例如: cursor.executescript("create ...; insert ...; select ...;")
    Cursor.executescript(sql_script)
    
    # 执行完一条查询的 SQL 语句后，获取该条 SQL 语句查询的结果集的下一行，
    # 返回一个单独序列，例如: (who1, age1)，没有更多可用数据时返回 None。
    Cursor.fetchone()
    
    # 执行完一条查询的 SQL 语句后，获取该条 SQL 语句查询结果集的 size 行，
    # 返回一个列表，例如: [(who1, age1), (who2, age2)]，没有更多可用行时将返回一个空列表。
    # 此方法是基于 size 参数尝试获取指定行，如果获取不到指定行数，实际结果可能返回较少的行数。
    Cursor.fetchmany(size=cursor.arraysize)
    
    # 执行完一条查询的 SQL 语句后，获取该条 SQL 语句查询的所有结果集（剩余所有行），
    # 返回一个列表，例如: [(who1, age1), (who2, age2), ...]，没有可用行时将返回一个空列表。
    Cursor.fetchall()
    
    # 该只读属性将提供上一次 SQL 查询的列名称。为了与 Python DB API 保持兼容，
    # 它会为每个列返回一个 7 元组，每个元组的最后 6 个条目均为 None。
    # 例如: (
    # ('name', None, None, None, None, None, None), 
    # ('age', None, None, None, None, None, None)
    # )
    # 对于没有任何匹配行的 SELECT 语句同样会设置该属性。
    Cursor.description
    
    # 立即关闭 cursor
    Cursor.close()

# 2. SQL 语句操作: CREATE / INSERT / SELECT / UPDATE / DELETE #

import sqlite3
    
    # 连接数据库
    conn = sqlite3.connect(":memory:")
    
    # 获取游标
    cursor = conn.cursor()
    
    # 创建表
    cursor.execute(""" create table if not exists Person( id integer primary key not null, name text not null, age integer not null ) """)
    
    # 插入数据
    cursor.execute("insert into Person(name, age) values(?, ?)", ("Tom1", 25))
    cursor.executemany("insert into Person(name, age) values(?, ?)",
                       [("Tom2", 26), ("Tom3", 27), ("Tom4", 28)])
    
    # 查询数据
    cursor.execute("select * from Person")
    print(cursor.description)       # (('id', None, ...), ('name', ...), ('age', ...))
    print(cursor.fetchall())        # [(1, 'Tom1', 25), (2, 'Tom2', 26), ...]
    
    # 更新数据
    cursor.execute("update Person set name=?, age=? where name=?", ("TOM111", 18, "Tom1"))
    cursor.execute("select * from Person where name=?", ("TOM111",))
    print(cursor.fetchone())        # (1, 'TOM111', 18)
    
    # 删除数据
    cursor.execute("delete from Person where id=?", (3,))
    cursor.execute("select * from Person where age>=?", (26,))
    print(cursor.fetchall())        # [(2, 'Tom2', 26), (4, 'Tom4', 28)]
    
    # 关闭游标
    cursor.close()
    
    # 提交事务
    conn.commit()
    # 关闭数据库连接
    conn.close()

执行结果:

(('id', None, None, None, None, None, None), ('name', None, None, None, None, None, None), ('age', None, None, None, None, None, None))
    [(1, 'Tom1', 25), (2, 'Tom2', 26), (3, 'Tom3', 27), (4, 'Tom4', 28)]
    (1, 'TOM111', 18)
    [(2, 'Tom2', 26), (4, 'Tom4', 28)]

# 3. 通过「列名称」访问查询结果数据 #

import sqlite3
    
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()
    
    cursor.execute(""" create table if not exists Person( id integer primary key not null, name text not null, age integer not null ) """)
    cursor.execute("insert into Person(name, age) values(?, ?)", ("Tom1", 25))
    
    # 使用 sqlite3.Row 作为行工厂,
    # SELECT 查询结果的行数据将以 sqlite3.Row 实例的方式返回
    cursor.row_factory = sqlite3.Row
    
    # 执行 select 查询语句
    cursor.execute("select * from Person")
    # 遍历结果
    while True:
        row = cursor.fetchone()         # 获取下一行
        if row is None:
            break
        print(row)                      # <sqlite3.Row object>
        print(row.keys())               # 所有列名称: ['id', 'name', 'age']
        print(row[0])                   # 通过「索引」访问结果值
        print(row["id"])                # 通过「列名称」访问结果值（名称不区分大小写）
        assert row[1] == row["name"]
        assert row[2] == row["age"]
    
    # 也可以直接迭代 cursor, 迭代返回的元素为 Row 实例
    cursor.execute("select * from Person")
    for row in cursor:
        print(row)                      # <sqlite3.Row object>
    
    cursor.close()
    conn.commit()
    conn.close()

# 4. SQLite 和 Python 数据类型转换 #

SQLite 原生支持如下的 5 种类型: `NULL`, `INTEGER`, `REAL`, `TEXT`, `BLOB`

因此可以将以下 Python 类型发送到 SQLite 而不会出现任何问题:

SAVE DATA
    -------------------------------------------
    Python          -->          SQLite
    -------------------------------------------
    None             |           NULL
    int              |           INTEGER
    float            |           REAL
    str              |           TEXT
    bytes            |           BLOB
    -------------------------------------------

SQLite 类型默认转换为 Python 类型的方式:

READ DATA
    -------------------------------------------
    SQLite          -->          Python
    -------------------------------------------
    NULL             |           None
    INTEGER          |           int
    REAL             |           float
    TEXT             |           str
    BLOB             |           bytes
    -------------------------------------------

# 5. 数据库备份 #

将现有数据库复制到另一个数据库中：

import sqlite3
    
    conn1 = sqlite3.connect("test1.db")
    conn2 = sqlite3.connect("test2.db")
    
    # 将数据库 test1.db 复制到 test2.db 中（如果 test2.db 已存在, 将被覆盖）
    conn1.backup(conn2)
    conn2.commit()
    
    conn1.close()
    conn2.close()

将现有数据库复制到临时副本中：

import sqlite3
    
    conn1 = sqlite3.connect("test1.db")
    conn2 = sqlite3.connect(":memory:")
    
    conn1.backup(conn2)
    conn2.commit()
    
    conn1.close()
    conn2.close()

# 6. 使用 Connection 作为上下文管理器 #

连接对象`Connection`支持使用`with`用来作为上下文管理器，它可以自动提交或者回滚事务。如果出现异常，事务会被回滚`conn.rollback()`；否则，事务会被提交`conn.commit()`。

import sqlite3
    
    conn = sqlite3.connect(":memory:")
    conn.execute("create table Person(id integer primary key, name text unique)")
    
    # 成功后, conn.commit() 会自动调用
    with conn:
        conn.execute("insert into Person(name) values (?)", ("Tom",))
    
    # 当 with 块以异常结束后会自动调用 conn.rollback(), 并且异常仍然会被抛出, 必须手动捕获异常。
    try:
        with conn:
            conn.execute("insert into Person(name) values (?)", ("Tom",))
    except sqlite3.IntegrityError as e:
        print("Error: " + str(e))       # Error: UNIQUE constraint failed: Person.name
    
    # 作为上下文管理器的连接对象只提交或回滚事务, 还需要手动关闭连接对象
    conn.close()

[https_blog.csdn.net_xietansheng_article_details_115559160]: https://blog.csdn.net/xietansheng/article/details/115559160
[Python3]: https://blog.csdn.net/xietansheng/article/details/109266600
[sqlite3 _ DB-API 2.0 interface for SQLite databases]: https://docs.python.org/zh-cn/3/library/sqlite3.html
[sqlite3.Connection]: https://docs.python.org/zh-cn/3/library/sqlite3.html#sqlite3.Connection
[sqlite3.Cursor]: https://docs.python.org/zh-cn/3/library/sqlite3.html#cursor-objects
[sqlite3.connect_database_]: https://docs.python.org/zh-cn/3/library/sqlite3.html#sqlite3.connect
[Python3 SQLite3]: https://docs.python.org/zh-cn/3/library/sqlite3.html#sqlite3-controlling-transactions