【Python百日进阶-Web开发-Peewee】Day245 - 数据库 SQLite

6.4 使用 SQLite

要连接到 SQLite 数据库，我们将使用SqliteDatabase. 第一个参数是包含数据库的文件名，或 ':memory:'用于创建内存数据库的字符串。在数据库文件名之后，您可以指定列表或编译指示或任何其他任意sqlite3 参数。

sqlite_db = SqliteDatabase('my_app.db', pragmas={'journal_mode': 'wal'})

class BaseModel(Model):
    """A base model that will use our Sqlite database."""
    class Meta:
        database = sqlite_db

class User(BaseModel):
    username = TextField()
    # etc, etc

Peewee 包含一个SQLite 扩展模块，它提供了许多 SQLite 特定的功能，例如全文搜索、 json 扩展支持等等。如果您想使用这些很棒的功能，请使用SqliteExtDatabase模块中的playhouse.sqlite_ext：

from playhouse.sqlite_ext import SqliteExtDatabase

sqlite_db = SqliteExtDatabase('my_app.db', pragmas={
    'journal_mode': 'wal',  # WAL-mode.
    'cache_size': -64 * 1000,  # 64MB cache.
    'synchronous': 0})  # Let the OS manage syncing.

6.4.1 参数声明

PRAGMASQLite 允许通过语句（SQLite 文档）对许多参数进行运行时配置 。这些语句通常在创建新的数据库连接时运行。要针对新连接运行一个或多个PRAGMA语句，可以将它们指定为字典或包含 pragma 名称和值的 2 元组列表：

db = SqliteDatabase('my_app.db', pragmas={
    'journal_mode': 'wal',
    'cache_size': 10000,  # 10000 pages, or ~40MB
    'foreign_keys': 1,  # Enforce foreign-key constraints
})

PRAGMA 也可以使用 pragma()方法或对象上公开的特殊属性动态配置SqliteDatabase：

# Set cache size to 64MB for *current connection*.
db.pragma('cache_size', -1024 * 64)

# Same as above.
db.cache_size = -1024 * 64

# Read the value of several pragmas:
print('cache_size:', db.cache_size)
print('foreign_keys:', db.foreign_keys)
print('journal_mode:', db.journal_mode)
print('page_size:', db.page_size)

# Set foreign_keys pragma on current connection *AND* on all
# connections opened subsequently.
db.pragma('foreign_keys', 1, permanent=True)

注意力

使用该方法设置的 Pragma
pragma()，默认情况下，在连接关闭后不会持续存在。要将编译指示配置为在打开连接时运行，请指定permanent=True.

笔记

PRAGMA 设置的完整列表、它们的含义和可接受的值可以在 SQLite 文档中找到： http:
//sqlite.org/pragma.html

6.4.2 推荐设置

以下设置是我与 SQLite 一起用于典型 Web 应用程序数据库的设置。

参数	推荐设置	解释
journal_mode	沃尔	让读者和作家共存
cache_size	-1 * data_size_kb	以 KiB 为单位设置页面缓存大小，例如 -32000 = 32MB
foreign_keys	1	强制外键约束
ignore_check_constraints	0	强制 CHECK 约束
synchronous	0	让操作系统处理 fsync（谨慎使用）
使用上述选项的示例数据库：

db = SqliteDatabase('my_app.db', pragmas={
    'journal_mode': 'wal',
    'cache_size': -1 * 64000,  # 64MB
    'foreign_keys': 1,
    'ignore_check_constraints': 0,
    'synchronous': 0})

6.4.3 用户自定义函数

SQLite 可以使用用户定义的 Python 代码进行扩展。该类 SqliteDatabase支持三种类型的用户定义扩展：

• 函数 - 接受任意数量的参数并返回单个值。
• 聚合 - 聚合来自多行的参数并返回单个值。
• 排序规则 - 描述如何对某些值进行排序。

笔记

如需更多扩展支持，请参阅模块SqliteExtDatabase中的。playhouse.sqlite_ext

用户定义函数示例：

db = SqliteDatabase('analytics.db')

from urllib.parse import urlparse

@db.func('hostname')
def hostname(url):
    if url is not None:
        return urlparse(url).netloc

# Call this function in our code:
# The following finds the most common hostnames of referrers by count:
query = (PageView
         .select(fn.hostname(PageView.referrer), fn.COUNT(PageView.id))
         .group_by(fn.hostname(PageView.referrer))
         .order_by(fn.COUNT(PageView.id).desc()))

示例用户定义的聚合：

from hashlib import md5

@db.aggregate('md5')
class MD5Checksum(object):
    def __init__(self):
        self.checksum = md5()

    def step(self, value):
        self.checksum.update(value.encode('utf-8'))

    def finalize(self):
        return self.checksum.hexdigest()

# Usage:
# The following computes an aggregate MD5 checksum for files broken
# up into chunks and stored in the database.
query = (FileChunk
         .select(FileChunk.filename, fn.MD5(FileChunk.data))
         .group_by(FileChunk.filename)
         .order_by(FileChunk.filename, FileChunk.sequence))

示例排序规则：

@db.collation('ireverse')
def collate_reverse(s1, s2):
    # Case-insensitive reverse.
    s1, s2 = s1.lower(), s2.lower()
    return (s1 < s2) - (s1 > s2)  # Equivalent to -cmp(s1, s2)

# To use this collation to sort books in reverse order...
Book.select().order_by(collate_reverse.collation(Book.title))

# Or...
Book.select().order_by(Book.title.asc(collation='reverse'))

示例用户定义的表值函数（请参阅TableFunction 和table_function）以获取更多详细信息：

from playhouse.sqlite_ext import TableFunction

db = SqliteDatabase('my_app.db')

@db.table_function('series')
class Series(TableFunction):
    columns = ['value']
    params = ['start', 'stop', 'step']

    def initialize(self, start=0, stop=None, step=1):
        """
        Table-functions declare an initialize() method, which is
        called with whatever arguments the user has called the
        function with.
        """
        self.start = self.current = start
        self.stop = stop or float('Inf')
        self.step = step

    def iterate(self, idx):
        """
        Iterate is called repeatedly by the SQLite database engine
        until the required number of rows has been read **or** the
        function raises a `StopIteration` signalling no more rows
        are available.
        """
        if self.current > self.stop:
            raise StopIteration

        ret, self.current = self.current, self.current + self.step
        return (ret,)

# Usage:
cursor = db.execute_sql('SELECT * FROM series(?, ?, ?)', (0, 5, 2))
for value, in cursor:
    print(value)

# Prints:
# 0
# 2
# 4

有关更多信息，请参阅：

• SqliteDatabase.func()
• SqliteDatabase.aggregate()
• SqliteDatabase.collation()
• SqliteDatabase.table_function()
• 有关更多 SQLite 扩展，请参阅SQLite 扩展

6.4.4 设置事务的锁定模式

SQLite 事务可以以三种不同的模式打开：

• Deferred（默认）- 仅在执行读取或写入时获取锁。第一次读取创建共享锁 ，第一次写入创建保留锁。因为锁的获取被推迟到实际需要时，另一个线程或进程可能会创建一个单独的事务并在当前线程上的 BEGIN 执行后写入数据库。
• Immediate -立即获取保留锁 。在这种模式下，没有其他数据库可以写入数据库或打开立即或独占事务。但是，其他进程可以继续从数据库中读取。
• Exclusive - 打开一个独占锁 ，防止所有（除了未提交的读取）连接访问数据库，直到事务完成。
  指定锁定模式的示例：

db = SqliteDatabase('app.db')

with db.atomic('EXCLUSIVE'):
    do_something()


@db.atomic('IMMEDIATE')
def some_other_function():
    # This function is wrapped in an "IMMEDIATE" transaction.
    do_something_else()

有关更多信息，请参阅 SQLite锁定文档。要了解有关 Peewee 中事务的更多信息，请参阅管理事务 文档。

6.4.5 APSW，高级 SQLite 驱动程序

Peewee 还附带了一个使用apsw 的备用 SQLite 数据库，一个高级 sqlite 驱动程序，一个高级 Python SQLite 驱动程序。有关 APSW 的更多信息，请 访问 APSW 项目网站。APSW 提供特殊功能，例如：

• 虚拟表、虚拟文件系统、Blob I/O、备份和文件控制。
• 可以跨线程共享连接，而无需任何额外的锁定。
• 事务由您的代码显式管理。
• Unicode 被正确处理。
• APSW 比标准库 sqlite3 模块更快。
• 将几乎整个 SQLite C API 暴露给您的 Python 应用程序。
  如果您想使用 APSW，请使用APSWDatabaseapsw_ext 模块中的：

from playhouse.apsw_ext import APSWDatabase

apsw_db = APSWDatabase('my_app.db')