`sqlite3` — SQLite 数据库的 DB-API 2.0 接口 ¶

源代码： Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

sqlite3 module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249 , and requires SQLite 3.7.15 or newer.

This document includes four main sections:

教程 teaches how to use the sqlite3 模块。
参考 describes the classes and functions this module defines.
How-to guides details how to handle specific tasks.
解释 provides in-depth background on transaction control.

另请参阅

https://www.sqlite.org
https://www.w3schools.com/sql/
PEP 249 - 数据库 API 规范 2.0

教程 ¶

In this tutorial, you will create a database of Monty Python movies using basic sqlite3 functionality. It assumes a fundamental understanding of database concepts, including cursors and transactions .

First, we need to create a new database and open a database connection to allow sqlite3 to work with it. Call sqlite3.connect() to to create a connection to the database tutorial.db in the current working directory, implicitly creating it if it does not exist:

import sqlite3
con = sqlite3.connect("tutorial.db")

返回的 Connection 对象 con represents the connection to the on-disk database.

In order to execute SQL statements and fetch results from SQL queries, we will need to use a database cursor. Call con.cursor() to create the Cursor :

cur = con.cursor()

Now that we’ve got a database connection and a cursor, we can create a database table movie with columns for title, release year, and review score. For simplicity, we can just use column names in the table declaration – thanks to the flexible typing feature of SQLite, specifying the data types is optional. Execute the CREATE TABLE statement by calling cur.execute(...) :

cur.execute("CREATE TABLE movie(title, year, score)")

We can verify that the new table has been created by querying the sqlite_master table built-in to SQLite, which should now contain an entry for the movie table definition (see The Schema Table for details). Execute that query by calling cur.execute(...) , assign the result to res ，和调用 res.fetchone() to fetch the resulting row:

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

We can see that the table has been created, as the query returns a tuple containing the table’s name. If we query sqlite_master for a non-existent table spam , res.fetchone() 将返回 None :

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

Now, add two rows of data supplied as SQL literals by executing an INSERT statement, once again by calling cur.execute(...) :

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

INSERT statement implicitly opens a transaction, which needs to be committed before changes are saved in the database (see Transaction control for details). Call con.commit() on the connection object to commit the transaction:

con.commit()

We can verify that the data was inserted correctly by executing a SELECT query. Use the now-familiar cur.execute(...) to assign the result to res ，和调用 res.fetchall() to return all resulting rows:

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

The result is a list of two tuple s, one per row, each containing that row’s score 值。

Now, insert three more rows by calling cur.executemany(...) :

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.

预告 ? placeholders are used to bind data to the query. Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks (见 How to use placeholders to bind values in SQL queries 了解更多细节)。

We can verify that the new rows were inserted by executing a SELECT query, this time iterating over the results of the query:

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

Each row is a two-item tuple of (year, title) , matching the columns selected in the query.

Finally, verify that the database has been written to disk by calling con.close() to close the existing connection, opening a new one, creating a new cursor, then querying the database:

>>> con.close()
>>> new_con = sqlite3.connect("tutorial.db")
>>> new_cur = new_con.cursor()
>>> res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")
>>> title, year = res.fetchone()
>>> print(f'The highest scoring Monty Python movie is {title!r}, released in {year}')
The highest scoring Monty Python movie is 'Monty Python and the Holy Grail', released in 1975

You’ve now created an SQLite database using the sqlite3 module, inserted data and retrieved values from it in multiple ways.

另请参阅

How-to guides for further reading:
解释 for in-depth background on transaction control.

参考 ¶

Module functions ¶

sqlite3. connect ( database , timeout = 5.0 , detect_types = 0 , isolation_level = 'DEFERRED' , check_same_thread = True , factory = sqlite3.Connection , cached_statements = 128 , uri = False ) ¶

参数

database ( 像路径对象 ) – The path to the database file to be opened. Pass ":memory:" to open a connection to a database that is in RAM instead of on disk.
timeout ( float ) – How many seconds the connection should wait before raising an exception, if the database is locked by another connection. If another connection opens a transaction to modify the database, it will be locked until that transaction is committed. Default five seconds.
detect_types ( int ) – Control whether and how data types not natively supported by SQLite are looked up to be converted to Python types, using the converters registered with register_converter() . Set it to any combination (using | , bitwise or) of PARSE_DECLTYPES and PARSE_COLNAMES to enable this. Column names takes precedence over declared types if both flags are set. Types cannot be detected for generated fields (for example max(data) ), even when the detect_types parameter is set; str will be returned instead. By default ( 0 ), type detection is disabled.
isolation_level ( str | None ) – The isolation_level of the connection, controlling whether and how transactions are implicitly opened. Can be "DEFERRED" (默认)， "EXCLUSIVE" or "IMMEDIATE" ; or None to disable opening transactions implicitly. See Transaction control for more.
check_same_thread ( bool ) – If True (default), only the creating thread may use the connection. If False , the connection may be shared across multiple threads; if so, write operations should be serialized by the user to avoid data corruption.
factory ( Connection ) – A custom subclass of Connection to create the connection with, if not the default Connection 类。
cached_statements ( int ) – The number of statements that sqlite3 should internally cache for this connection, to avoid parsing overhead. By default, 128 statements.
uri ( bool ) – If set to True , database is interpreted as a URI with a file path and an optional query string. The scheme part must be "file:" , and the path can be relative or absolute. The query string allows passing parameters to SQLite, enabling various How to work with SQLite URIs .

Return type

Connection

引发审计事件 sqlite3.connect 采用自变量 database .

引发审计事件 sqlite3.connect/handle 采用自变量 connection_handle .

3.4 版新增： uri 参数。

3.7 版改变： database can now also be a 像路径对象 , not only a string.

3.10 版新增： sqlite3.connect/handle auditing event.

sqlite3. complete_statement ( statement ) ¶

This function may be useful during command-line input to determine if the entered text seems to form a complete SQL statement, or if additional input is needed before calling execute() .

sqlite3. enable_callback_tracebacks ( flag , / ) ¶

sqlite3. register_adapter ( type , adapter , / ) ¶

sqlite3. register_converter ( typename , converter , / ) ¶

sqlite3. PARSE_COLNAMES ¶

sqlite3. PARSE_DECLTYPES ¶

sqlite3. SQLITE_OK ¶
sqlite3. SQLITE_DENY ¶
sqlite3. SQLITE_IGNORE ¶

sqlite3. apilevel ¶

sqlite3. paramstyle ¶

sqlite3. sqlite_version ¶

sqlite3. sqlite_version_info ¶

sqlite3. threadsafety ¶

SQLite threading mode	threadsafety	SQLITE_THREADSAFE	DB-API 2.0 meaning
single-thread	0	0	Threads may not share the module
multi-thread	1	2	Threads may share the module, but not connections
serialized	3	1	Threads may share the module, connections and cursors

sqlite3. version ¶

sqlite3. version_info ¶

Python 类型	SQLite 类型
`None`	`NULL`
`int`	`INTEGER`
`float`	`REAL`
`str`	`TEXT`
`bytes`	`BLOB`

SQLite 类型	Python 类型
`NULL`	`None`
`INTEGER`	`int`
`REAL`	`float`
`TEXT`	depends on `text_factory` , `str` 默认情况下
`BLOB`	`bytes`

`sqlite3` — SQLite 数据库的 DB-API 2.0 接口 ¶

教程 ¶

参考 ¶

Module functions ¶

Module constants ¶

Connection objects ¶

Cursor objects ¶

Row objects ¶

Blob objects ¶

PrepareProtocol objects ¶

异常 ¶

SQLite 和 Python 类型 ¶

默认适配器和转换器 ¶

How-to guides ¶

How to use placeholders to bind values in SQL queries ¶

How to adapt custom Python types to SQLite values ¶

How to write adaptable objects ¶

How to register adapter callables ¶

How to convert SQLite values to custom Python types ¶

Adapter and converter recipes ¶

How to use connection shortcut methods ¶

How to use the connection context manager ¶

How to work with SQLite URIs ¶

解释 ¶

Transaction control ¶

内容表

上一话题

下一话题

本页

快速搜索

sqlite3 — SQLite 数据库的 DB-API 2.0 接口 ¶

教程 ¶

参考 ¶

Module functions ¶

Module constants ¶

Connection objects ¶

Cursor objects ¶

Row objects ¶

Blob objects ¶

PrepareProtocol objects ¶

异常 ¶

SQLite 和 Python 类型 ¶

默认适配器和转换器 ¶

How-to guides ¶

How to use placeholders to bind values in SQL queries ¶

How to adapt custom Python types to SQLite values ¶

How to write adaptable objects ¶

How to register adapter callables ¶

How to convert SQLite values to custom Python types ¶

Adapter and converter recipes ¶

How to use connection shortcut methods ¶

How to use the connection context manager ¶

How to work with SQLite URIs ¶

解释 ¶

Transaction control ¶

内容表

上一话题

下一话题

本页

快速搜索

`sqlite3` — SQLite 数据库的 DB-API 2.0 接口 ¶