sqlite3 --- SQLite 數(shù)據(jù)庫(kù) DB-API 2.0 接口模塊?

模塊函數(shù)和常量?

sqlite3.apilevel?

String constant stating the supported DB-API level. Required by the DB-API. Hard-coded to "2.0".

sqlite3.paramstyle?

String constant stating the type of parameter marker formatting expected by the sqlite3 module. Required by the DB-API. Hard-coded to "qmark".

備注

The sqlite3 module supports both qmark and numeric DB-API parameter styles, because that is what the underlying SQLite library supports. However, the DB-API does not allow multiple values for the paramstyle attribute.

sqlite3.version?

這個(gè)模塊的版本號(hào)，是一個(gè)字符串。不是 SQLite 庫(kù)的版本號(hào)。

sqlite3.version_info?

這個(gè)模塊的版本號(hào)，是一個(gè)由整數(shù)組成的元組。不是 SQLite 庫(kù)的版本號(hào)。

sqlite3.sqlite_version?

使用中的 SQLite 庫(kù)的版本號(hào)，是一個(gè)字符串。

sqlite3.sqlite_version_info?

使用中的 SQLite 庫(kù)的版本號(hào)，是一個(gè)整數(shù)組成的元組。

sqlite3.threadsafety?

Integer constant required by the DB-API 2.0, stating the level of thread safety the sqlite3 module supports. This attribute is set based on the default threading mode the underlying SQLite library is compiled with. The SQLite threading modes are:

1. Single-thread: In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.

2. Multi-thread: In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

3. Serialized: In serialized mode, SQLite can be safely used by multiple threads with no restriction.

The mappings from SQLite threading modes to DB-API 2.0 threadsafety levels are as follows:

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

在 3.11 版更改: Set threadsafety dynamically instead of hard-coding it to 1.

sqlite3.PARSE_DECLTYPES?

這個(gè)常量可以作為 connect() 函數(shù)的 detect_types 參數(shù)。

設(shè)置這個(gè)參數(shù)后，sqlite3 模塊將解析它返回的每一列申明的類型。它會(huì)申明的類型的第一個(gè)單詞，比如“integer primary key”，它會(huì)解析出“integer”，再比如“number(10)”，它會(huì)解析出“number”。然后，它會(huì)在轉(zhuǎn)換器字典里查找那個(gè)類型注冊(cè)的轉(zhuǎn)換器函數(shù)，并調(diào)用它。

sqlite3.PARSE_COLNAMES?

這個(gè)常量可以作為 connect() 函數(shù)的 detect_types 參數(shù)。

設(shè)置此參數(shù)可使得 SQLite 接口解析它所返回的每一列的列名。 它將在其中查找形式為 [mytype] 的字符串，然后將 'mytype' 確定為列的類型。 它將嘗試在轉(zhuǎn)換器字典中查找 'mytype' 條目，然后用找到的轉(zhuǎn)換器函數(shù)來(lái)返回值。 在 Cursor.description 中找到的列名并不包括類型，舉例來(lái)說(shuō)，如果你在你的 SQL 中使用了像 'as "Expiration date [datetime]"' 這樣的寫法，那么我們將解析出在第一個(gè) '[' 之前的所有內(nèi)容并去除前導(dǎo)空格作為列名：即列名將為 "Expiration date"。

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])?

連接 SQLite 數(shù)據(jù)庫(kù) database。默認(rèn)返回 Connection 對(duì)象，除非使用了自定義的 factory 參數(shù)。

database 是準(zhǔn)備打開的數(shù)據(jù)庫(kù)文件的路徑（絕對(duì)路徑或相對(duì)于當(dāng)前目錄的相對(duì)路徑），它是 path-like object。你也可以用 ":memory:" 在內(nèi)存中打開一個(gè)數(shù)據(jù)庫(kù)。

當(dāng)一個(gè)數(shù)據(jù)庫(kù)被多個(gè)連接訪問(wèn)的時(shí)候，如果其中一個(gè)進(jìn)程修改這個(gè)數(shù)據(jù)庫(kù)，在這個(gè)事務(wù)提交之前，這個(gè) SQLite 數(shù)據(jù)庫(kù)將會(huì)被一直鎖定。timeout 參數(shù)指定了這個(gè)連接等待鎖釋放的超時(shí)時(shí)間，超時(shí)之后會(huì)引發(fā)一個(gè)異常。這個(gè)超時(shí)時(shí)間默認(rèn)是 5.0（5秒）。

isolation_level 參數(shù)，請(qǐng)查看 Connection 對(duì)象的 isolation_level 屬性。

SQLite 原生只支持5種類型：TEXT，INTEGER，REAL，BLOB 和 NULL。如果你想用其它類型，你必須自己添加相應(yīng)的支持。使用 detect_types 參數(shù)和模塊級(jí)別的 register_converter() 函數(shù)注冊(cè)**轉(zhuǎn)換器** 可以簡(jiǎn)單的實(shí)現(xiàn)。

detect_types 默認(rèn)為 0 (即關(guān)閉，不進(jìn)行類型檢測(cè))，你可以將其設(shè)為任意的 PARSE_DECLTYPES 和 PARSE_COLNAMES 組合來(lái)啟用類型檢測(cè)。 由于 SQLite 的行為，生成的字段類型 (例如 max(data)) 不能被檢測(cè)，即使在設(shè)置了 detect_types 形參時(shí)也是如此。 在此情況下，返回的類型為 str。

默認(rèn)情況下，check_same_thread 為 True，只有當(dāng)前的線程可以使用該連接。 如果設(shè)置為 False，則多個(gè)線程可以共享返回的連接。 當(dāng)多個(gè)線程使用同一個(gè)連接的時(shí)候，用戶應(yīng)該把寫操作進(jìn)行序列化，以避免數(shù)據(jù)損壞。

默認(rèn)情況下，當(dāng)調(diào)用 connect 方法的時(shí)候，sqlite3 模塊使用了它的 Connection 類。當(dāng)然，你也可以創(chuàng)建 Connection 類的子類，然后創(chuàng)建提供了 factory 參數(shù)的 connect() 方法。

詳情請(qǐng)查閱當(dāng)前手冊(cè)的 SQLite 與 Python 類型 部分。

The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead. If you want to explicitly set the number of statements that are cached for the connection, you can set the cached_statements parameter. The currently implemented default is to cache 128 statements.

If uri is True, database is interpreted as a URI with a file path and an optional query string. The scheme part must be "file:". The path can be a relative or absolute file path. The query string allows us to pass parameters to SQLite. Some useful URI tricks include:

# Open a database in read-only mode.
con = sqlite3.connect("file:template.db?mode=ro", uri=True)

# Don't implicitly create a new database file if it does not already exist.
# Will raise sqlite3.OperationalError if unable to open a database file.
con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)

# Create a shared named in-memory database.
con1 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
con2 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
con1.executescript("create table t(t); insert into t values(28);")
rows = con2.execute("select * from t").fetchall()

More information about this feature, including a list of recognized parameters, can be found in the SQLite URI documentation.

引發(fā)一個(gè) 審計(jì)事件 sqlite3.connect，附帶參數(shù) database。

引發(fā)一個(gè) 審計(jì)事件 sqlite3.connect/handle，附帶參數(shù) connection_handle。

在 3.4 版更改: 增加了 uri 參數(shù)。

在 3.7 版更改: database 現(xiàn)在可以是一個(gè) path-like object 對(duì)象了，不僅僅是字符串。

在 3.10 版更改: 增加了 sqlite3.connect/handle 審計(jì)事件。

sqlite3.register_converter(typename, callable)?

注冊(cè)一個(gè)回調(diào)對(duì)象 callable, 用來(lái)轉(zhuǎn)換數(shù)據(jù)庫(kù)中的字節(jié)串為自定的 Python 類型。所有類型為 typename 的數(shù)據(jù)庫(kù)的值在轉(zhuǎn)換時(shí)，都會(huì)調(diào)用這個(gè)回調(diào)對(duì)象。通過(guò)指定 connect() 函數(shù)的 detect-types 參數(shù)來(lái)設(shè)置類型檢測(cè)的方式。注意，typename 與查詢語(yǔ)句中的類型名進(jìn)行匹配時(shí)不區(qū)分大小寫。

sqlite3.register_adapter(type, callable)?

注冊(cè)一個(gè)回調(diào)對(duì)象 callable，用來(lái)轉(zhuǎn)換自定義Python類型為一個(gè) SQLite 支持的類型。 這個(gè)回調(diào)對(duì)象 callable 僅接受一個(gè) Python 值作為參數(shù)，而且必須返回以下某個(gè)類型的值：int，float，str 或 bytes。

sqlite3.complete_statement(sql)?

如果字符串 sql 包含一個(gè)或多個(gè)完整的 SQL 語(yǔ)句（以分號(hào)結(jié)束）則返回 True。它不會(huì)驗(yàn)證 SQL 語(yǔ)法是否正確，僅會(huì)驗(yàn)證字符串字面上是否完整，以及是否以分號(hào)結(jié)束。

它可以用來(lái)構(gòu)建一個(gè) SQLite shell，下面是一個(gè)例子：

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")

while True:
    line = input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print(cur.fetchall())
        except sqlite3.Error as e:
            err_msg = str(e)
            err_code = e.sqlite_errorcode
            err_name = e.sqlite_errorname
            print(f"{err_name} ({err_code}): {err_msg}")
        buffer = ""

con.close()

sqlite3.enable_callback_tracebacks(flag)?

By default you will not get any tracebacks in user-defined functions, aggregates, converters, authorizer callbacks etc. If you want to debug them, you can call this function with flag set to True. Afterwards, you will get tracebacks from callbacks on sys.stderr. Use False to disable the feature again.

Register an unraisable hook handler for an improved debug experience:

>>>

>>> import sqlite3
>>> sqlite3.enable_callback_tracebacks(True)
>>> cx = sqlite3.connect(":memory:")
>>> cx.set_trace_callback(lambda stmt: 5/0)
>>> cx.execute("select 1")
Exception ignored in: <function <lambda> at 0x10b4e3ee0>
Traceback (most recent call last):
  File "<stdin>", line 1, in <lambda>
ZeroDivisionError: division by zero
>>> import sys
>>> sys.unraisablehook = lambda unraisable: print(unraisable)
>>> cx.execute("select 1")
UnraisableHookArgs(exc_type=<class 'ZeroDivisionError'>, exc_value=ZeroDivisionError('division by zero'), exc_traceback=<traceback object at 0x10b559900>, err_msg=None, object=<function <lambda> at 0x10b4e3ee0>)
<sqlite3.Cursor object at 0x10b1fe840>

連接對(duì)象（Connection）?

class sqlite3.Connection?

An SQLite database connection has the following attributes and methods:

isolation_level?

獲取或設(shè)置當(dāng)前默認(rèn)的隔離級(jí)別。 表示自動(dòng)提交模式的 None 以及 "DEFERRED", "IMMEDIATE" 或 "EXCLUSIVE" 其中之一。 詳細(xì)描述請(qǐng)參閱 控制事務(wù)。

in_transaction?

如果是在活動(dòng)事務(wù)中（還沒有提交改變），返回 True，否則，返回 False。它是一個(gè)只讀屬性。

3.2 新版功能.

cursor(factory=Cursor)?

這個(gè)方法接受一個(gè)可選參數(shù) factory，如果要指定這個(gè)參數(shù)，它必須是一個(gè)可調(diào)用對(duì)象，而且必須返回 Cursor 類的一個(gè)實(shí)例或者子類。

blobopen(table, column, row, /, *, readonly=False, name='main')?

Open a Blob handle to the BLOB located in table name table, column name column, and row index row of database name. When readonly is True the blob is opened without write permissions. Trying to open a blob in a WITHOUT ROWID table will raise OperationalError.

備注

The blob size cannot be changed using the Blob class. Use the SQL function zeroblob to create a blob with a fixed size.

3.11 新版功能.

commit()?

這個(gè)方法提交當(dāng)前事務(wù)。如果沒有調(diào)用這個(gè)方法，那么從上一次提交 commit() 以來(lái)所有的變化在其他數(shù)據(jù)庫(kù)連接上都是不可見的。如果你往數(shù)據(jù)庫(kù)里寫了數(shù)據(jù)，但是又查詢不到，請(qǐng)檢查是否忘記了調(diào)用這個(gè)方法。

rollback()?

這個(gè)方法回滾從上一次調(diào)用 commit() 以來(lái)所有數(shù)據(jù)庫(kù)的改變。

close()?

關(guān)閉數(shù)據(jù)庫(kù)連接。注意，它不會(huì)自動(dòng)調(diào)用 commit() 方法。如果在關(guān)閉數(shù)據(jù)庫(kù)連接之前沒有調(diào)用 commit()，那么你的修改將會(huì)丟失！

execute(sql[, parameters])?

Create a new Cursor object and call execute() on it with the given sql and parameters. Return the new cursor object.

executemany(sql[, parameters])?

Create a new Cursor object and call executemany() on it with the given sql and parameters. Return the new cursor object.

executescript(sql_script)?

Create a new Cursor object and call executescript() on it with the given sql_script. Return the new cursor object.

create_function(name, num_params, func, *, deterministic=False)?

創(chuàng)建一個(gè)可以在 SQL 語(yǔ)句中使用的用戶自定義函數(shù)，函數(shù)名為 name。 num_params 為該函數(shù)所接受的形參個(gè)數(shù)（如果 num_params 為 -1，則該函數(shù)可接受任意數(shù)量的參數(shù)）， func 是一個(gè) Python 可調(diào)用對(duì)象，它將作為 SQL 函數(shù)被調(diào)用。 如果 deterministic 為真值，則所創(chuàng)建的函數(shù)將被標(biāo)記為 deterministic，這允許 SQLite 執(zhí)行額外的優(yōu)化。 此旗標(biāo)在 SQLite 3.8.3 或更高版本中受到支持，如果在舊版本中使用將引發(fā) NotSupportedError。

此函數(shù)可返回任何 SQLite 所支持的類型: bytes, str, int, float 和 None。

在 3.8 版更改: 增加了 deterministic 形參。

示例:

import sqlite3
import hashlib

def md5sum(t):
    return hashlib.md5(t).hexdigest()

con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", (b"foo",))
print(cur.fetchone()[0])

con.close()

create_aggregate(name, num_params, aggregate_class)?

創(chuàng)建一個(gè)自定義的聚合函數(shù)。

參數(shù)中 aggregate_class 類必須實(shí)現(xiàn)兩個(gè)方法：step 和 finalize。step 方法接受 num_params 個(gè)參數(shù)（如果 num_params 為 -1，那么這個(gè)函數(shù)可以接受任意數(shù)量的參數(shù)）；finalize 方法返回最終的聚合結(jié)果。

finalize 方法可以返回任何 SQLite 支持的類型：bytes，str，int，float 和 None。

示例:

import sqlite3

class MySum:
    def __init__(self):
        self.count = 0

    def step(self, value):
        self.count += value

    def finalize(self):
        return self.count

con = sqlite3.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.cursor()
cur.execute("create table test(i)")
cur.execute("insert into test(i) values (1)")
cur.execute("insert into test(i) values (2)")
cur.execute("select mysum(i) from test")
print(cur.fetchone()[0])

con.close()

create_window_function(name, num_params, aggregate_class, /)?

Creates user-defined aggregate window function name.

aggregate_class must implement the following methods:

• step: adds a row to the current window

• value: returns the current value of the aggregate

• inverse: removes a row from the current window

• finalize: returns the final value of the aggregate

step and value accept num_params number of parameters, unless num_params is -1, in which case they may take any number of arguments. finalize and value can return any of the types supported by SQLite: bytes, str, int, float, and None. Call create_window_function() with aggregate_class set to None to clear window function name.

Aggregate window functions are supported by SQLite 3.25.0 and higher. NotSupportedError will be raised if used with older versions.

3.11 新版功能.

示例:

# Example taken from https://www.sqlite.org/windowfunctions.html#udfwinfunc
import sqlite3


class WindowSumInt:
    def __init__(self):
        self.count = 0

    def step(self, value):
        """Adds a row to the current window."""
        self.count += value

    def value(self):
        """Returns the current value of the aggregate."""
        return self.count

    def inverse(self, value):
        """Removes a row from the current window."""
        self.count -= value

    def finalize(self):
        """Returns the final value of the aggregate.

        Any clean-up actions should be placed here.
        """
        return self.count


con = sqlite3.connect(":memory:")
cur = con.execute("create table test(x, y)")
values = [
    ("a", 4),
    ("b", 5),
    ("c", 3),
    ("d", 8),
    ("e", 1),
]
cur.executemany("insert into test values(?, ?)", values)
con.create_window_function("sumint", 1, WindowSumInt)
cur.execute("""
    select x, sumint(y) over (
        order by x rows between 1 preceding and 1 following
    ) as sum_y
    from test order by x
""")
print(cur.fetchall())

create_collation(name, callable)?

Create a collation named name using the collating function callable. callable is passed two string arguments, and it should return an integer:

• 1 if the first is ordered higher than the second

• -1 if the first is ordered lower than the second

• 0 if they are ordered equal

The following example shows a reverse sorting collation:

import sqlite3

def collate_reverse(string1, string2):
    if string1 == string2:
        return 0
    elif string1 < string2:
        return 1
    else:
        return -1

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    print(row)
con.close()

Remove a collation function by setting callable to None.

在 3.11 版更改: The collation name can contain any Unicode character. Earlier, only ASCII characters were allowed.

interrupt()?

可以從不同的線程調(diào)用這個(gè)方法來(lái)終止所有查詢操作，這些查詢操作可能正在連接上執(zhí)行。此方法調(diào)用之后， 查詢將會(huì)終止，而且查詢的調(diào)用者會(huì)獲得一個(gè)異常。

set_authorizer(authorizer_callback)?

此方法注冊(cè)一個(gè)授權(quán)回調(diào)對(duì)象。每次在訪問(wèn)數(shù)據(jù)庫(kù)中某個(gè)表的某一列的時(shí)候，這個(gè)回調(diào)對(duì)象將會(huì)被調(diào)用。如果要允許訪問(wèn)，則返回 SQLITE_OK，如果要終止整個(gè) SQL 語(yǔ)句，則返回 SQLITE_DENY，如果這一列需要當(dāng)做 NULL 值處理，則返回 SQLITE_IGNORE。這些常量可以在 sqlite3 模塊中找到。

回調(diào)的第一個(gè)參數(shù)表示要授權(quán)的操作類型。 第二個(gè)和第三個(gè)參數(shù)將是參數(shù)或 None，具體取決于第一個(gè)參數(shù)的值。 第 4 個(gè)參數(shù)是數(shù)據(jù)庫(kù)的名稱（“main”，“temp”等），如果需要的話。 第 5 個(gè)參數(shù)是負(fù)責(zé)訪問(wèn)嘗試的最內(nèi)層觸發(fā)器或視圖的名稱，或者如果此訪問(wèn)嘗試直接來(lái)自輸入 SQL 代碼，則為 None。

請(qǐng)參閱 SQLite 文檔，了解第一個(gè)參數(shù)的可能值以及第二個(gè)和第三個(gè)參數(shù)的含義，具體取決于第一個(gè)參數(shù)。 所有必需的常量都可以在 sqlite3 模塊中找到。

Passing None as authorizer_callback will disable the authorizer.

在 3.11 版更改: Added support for disabling the authorizer using None.

set_progress_handler(handler, n)?

此例程注冊(cè)回調(diào)。 對(duì)SQLite虛擬機(jī)的每個(gè)多指令調(diào)用回調(diào)。 如果要在長(zhǎng)時(shí)間運(yùn)行的操作期間從SQLite調(diào)用（例如更新用戶界面），這非常有用。

如果要清除以前安裝的任何進(jìn)度處理程序，調(diào)用該方法時(shí)請(qǐng)將 handler 參數(shù)設(shè)置為 None。

從處理函數(shù)返回非零值將終止當(dāng)前正在執(zhí)行的查詢并導(dǎo)致它引發(fā) OperationalError 異常。

set_trace_callback(trace_callback)?

為每個(gè) SQLite 后端實(shí)際執(zhí)行的 SQL 語(yǔ)句注冊(cè)要調(diào)用的 trace_callback。

The only argument passed to the callback is the statement (as str) that is being executed. The return value of the callback is ignored. Note that the backend does not only run statements passed to the Cursor.execute() methods. Other sources include the transaction management of the sqlite3 module and the execution of triggers defined in the current database.

將傳入的 trace_callback 設(shè)為 None 將禁用跟蹤回調(diào)。

備注

Exceptions raised in the trace callback are not propagated. As a development and debugging aid, use enable_callback_tracebacks() to enable printing tracebacks from exceptions raised in the trace callback.

3.3 新版功能.

enable_load_extension(enabled)?

此例程允許/禁止SQLite引擎從共享庫(kù)加載SQLite擴(kuò)展。 SQLite擴(kuò)展可以定義新功能，聚合或全新的虛擬表實(shí)現(xiàn)。 一個(gè)眾所周知的擴(kuò)展是與SQLite一起分發(fā)的全文搜索擴(kuò)展。

默認(rèn)情況下禁用可加載擴(kuò)展。 見 1.

引發(fā)一個(gè) 審計(jì)事件 sqlite3.enable_load_extension，附帶參數(shù) connection, enabled。

3.2 新版功能.

在 3.10 版更改: 增加了 sqlite3.enable_load_extension 審計(jì)事件。

import sqlite3

con = sqlite3.connect(":memory:")

# enable extension loading
con.enable_load_extension(True)

# Load the fulltext search extension
con.execute("select load_extension('./fts3.so')")

# alternatively you can load the extension using an API call:
# con.load_extension("./fts3.so")

# disable extension loading again
con.enable_load_extension(False)

# example from SQLite wiki
con.execute("create virtual table recipe using fts3(name, ingredients)")
con.executescript("""
    insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');
    insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');
    insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');
    insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');
    """)
for row in con.execute("select rowid, name, ingredients from recipe where name match 'pie'"):
    print(row)

con.close()

load_extension(path)?

This routine loads an SQLite extension from a shared library. You have to enable extension loading with enable_load_extension() before you can use this routine.

默認(rèn)情況下禁用可加載擴(kuò)展。 見 1.

引發(fā)一個(gè) 審計(jì)事件 sqlite3.load_extension，附帶參數(shù) connection, path。

3.2 新版功能.

在 3.10 版更改: 增加了 sqlite3.load_extension 審計(jì)事件。

row_factory?

您可以將此屬性更改為可接受游標(biāo)和原始行作為元組的可調(diào)用對(duì)象，并將返回實(shí)際結(jié)果行。 這樣，您可以實(shí)現(xiàn)更高級(jí)的返回結(jié)果的方法，例如返回一個(gè)可以按名稱訪問(wèn)列的對(duì)象。

示例:

import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])

con.close()

如果返回一個(gè)元組是不夠的，并且你想要對(duì)列進(jìn)行基于名稱的訪問(wèn)，你應(yīng)該考慮將 row_factory 設(shè)置為高度優(yōu)化的 sqlite3.Row 類型。 Row 提供基于索引和不區(qū)分大小寫的基于名稱的訪問(wèn)，幾乎沒有內(nèi)存開銷。 它可能比您自己的基于字典的自定義方法甚至基于 db_row 的解決方案更好。

text_factory?

Using this attribute you can control what objects are returned for the TEXT data type. By default, this attribute is set to str and the sqlite3 module will return str objects for TEXT. If you want to return bytes instead, you can set it to bytes.

您還可以將其設(shè)置為接受單個(gè) bytestring 參數(shù)的任何其他可調(diào)用對(duì)象，并返回結(jié)果對(duì)象。

請(qǐng)參閱以下示例代碼以進(jìn)行說(shuō)明：

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

AUSTRIA = "?sterreich"

# by default, rows are returned as str
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert row[0] == AUSTRIA

# but we can make sqlite3 always return bytestrings ...
con.text_factory = bytes
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is bytes
# the bytestrings will be encoded in UTF-8, unless you stored garbage in the
# database ...
assert row[0] == AUSTRIA.encode("utf-8")

# we can also implement a custom text_factory ...
# here we implement one that appends "foo" to all strings
con.text_factory = lambda x: x.decode("utf-8") + "foo"
cur.execute("select ?", ("bar",))
row = cur.fetchone()
assert row[0] == "barfoo"

con.close()

total_changes?

返回自打開數(shù)據(jù)庫(kù)連接以來(lái)已修改，插入或刪除的數(shù)據(jù)庫(kù)行的總數(shù)。

iterdump()?

返回以SQL文本格式轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)的迭代器。 保存內(nèi)存數(shù)據(jù)庫(kù)以便以后恢復(fù)時(shí)很有用。 此函數(shù)提供與 sqlite3 shell 中的 .dump 命令相同的功能。

示例:

# Convert file existing_db.db to SQL dump file dump.sql
import sqlite3

con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)
con.close()

backup(target, *, pages=- 1, progress=None, name='main', sleep=0.250)?

This method makes a backup of an SQLite database even while it's being accessed by other clients, or concurrently by the same connection. The copy will be written into the mandatory argument target, that must be another Connection instance.

默認(rèn)情況下，或者當(dāng) pages 為 0 或負(fù)整數(shù)時(shí)，整個(gè)數(shù)據(jù)庫(kù)將在一個(gè)步驟中復(fù)制；否則該方法一次循環(huán)復(fù)制 pages 規(guī)定數(shù)量的頁(yè)面。

如果指定了 progress，則它必須為 None 或一個(gè)將在每次迭代時(shí)附帶三個(gè)整數(shù)參數(shù)執(zhí)行的可調(diào)用對(duì)象，這三個(gè)參數(shù)分別是前一次迭代的狀態(tài) status，將要拷貝的剩余頁(yè)數(shù) remaining 以及總頁(yè)數(shù) total。

name 參數(shù)指定將被拷貝的數(shù)據(jù)庫(kù)名稱：它必須是一個(gè)字符串，其內(nèi)容為表示主數(shù)據(jù)庫(kù)的默認(rèn)值 "main"，表示臨時(shí)數(shù)據(jù)庫(kù)的 "temp" 或是在 ATTACH DATABASE 語(yǔ)句的 AS 關(guān)鍵字之后指定表示附加數(shù)據(jù)庫(kù)的名稱。

sleep 參數(shù)指定在備份剩余頁(yè)的連續(xù)嘗試之間要休眠的秒數(shù)，可以指定為一個(gè)整數(shù)或一個(gè)浮點(diǎn)數(shù)值。

示例一，將現(xiàn)有數(shù)據(jù)庫(kù)復(fù)制到另一個(gè)數(shù)據(jù)庫(kù)中：

import sqlite3

def progress(status, remaining, total):
    print(f'Copied {total-remaining} of {total} pages...')

con = sqlite3.connect('existing_db.db')
bck = sqlite3.connect('backup.db')
with bck:
    con.backup(bck, pages=1, progress=progress)
bck.close()
con.close()

示例二，將現(xiàn)有數(shù)據(jù)庫(kù)復(fù)制到臨時(shí)副本中：

import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)

3.7 新版功能.

getlimit(category, /)?

Get a connection run-time limit. category is the limit category to be queried.

Example, query the maximum length of an SQL statement:

import sqlite3
con = sqlite3.connect(":memory:")
lim = con.getlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH)
print(f"SQLITE_LIMIT_SQL_LENGTH={lim}")

3.11 新版功能.

setlimit(category, limit, /)?

Set a connection run-time limit. category is the limit category to be set. limit is the new limit. If the new limit is a negative number, the limit is unchanged.

Attempts to increase a limit above its hard upper bound are silently truncated to the hard upper bound. Regardless of whether or not the limit was changed, the prior value of the limit is returned.

Example, limit the number of attached databases to 1:

import sqlite3
con = sqlite3.connect(":memory:")
con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED, 1)

3.11 新版功能.

serialize(*, name='main')?

This method serializes a database into a bytes object. For an ordinary on-disk database file, the serialization is just a copy of the disk file. For an in-memory database or a "temp" database, the serialization is the same sequence of bytes which would be written to disk if that database were backed up to disk.

name is the database to be serialized, and defaults to the main database.

備注

This method is only available if the underlying SQLite library has the serialize API.

3.11 新版功能.

deserialize(data, /, *, name='main')?

This method causes the database connection to disconnect from database name, and reopen name as an in-memory database based on the serialization contained in data. Deserialization will raise OperationalError if the database connection is currently involved in a read transaction or a backup operation. DataError will be raised if len(data) is larger than 2**63 - 1, and DatabaseError will be raised if data does not contain a valid SQLite database.

備注

This method is only available if the underlying SQLite library has the deserialize API.

3.11 新版功能.

Cursor 對(duì)象?

class sqlite3.Cursor?

Cursor 游標(biāo)實(shí)例具有以下屬性和方法。

execute(sql[, parameters])?

執(zhí)行一條 SQL 語(yǔ)句。 可以使用 占位符 將值綁定到語(yǔ)句中。

execute() 將只執(zhí)行一條單獨(dú)的 SQL 語(yǔ)句。 如果你嘗試用它執(zhí)行超過(guò)一條語(yǔ)句，將會(huì)引發(fā) Warning。 如果你想要用一次調(diào)用執(zhí)行多條 SQL 語(yǔ)句請(qǐng)使用 executescript()。

executemany(sql, seq_of_parameters)?

執(zhí)行一條 帶形參的 SQL 命令，使用所有形參序列或在序列 seq_of_parameters 中找到的映射。 sqlite3 模塊還允許使用 iterator 代替序列來(lái)產(chǎn)生形參。

import sqlite3

class IterChars:
    def __init__(self):
        self.count = ord('a')

    def __iter__(self):
        return self

    def __next__(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count += 1
        return (chr(self.count - 1),) # this is a 1-tuple

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)

cur.execute("select c from characters")
print(cur.fetchall())

con.close()

這是一個(gè)使用生成器 generator 的簡(jiǎn)短示例：

import sqlite3
import string

def char_generator():
    for c in string.ascii_lowercase:
        yield (c,)

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)", char_generator())

cur.execute("select c from characters")
print(cur.fetchall())

con.close()

executescript(sql_script)?

這是用于一次性執(zhí)行多條 SQL 語(yǔ)句的非標(biāo)準(zhǔn)便捷方法。 它會(huì)首先發(fā)出一條 COMMIT 語(yǔ)句，然后執(zhí)行通過(guò)參數(shù)獲得的 SQL 腳本。 此方法會(huì)忽略 isolation_level；任何事件控制都必須被添加到 sql_script。

sql_script 可以是一個(gè) str 類的實(shí)例。

示例:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,
        lastname,
        age
    );

    create table book(
        title,
        author,
        published
    );

    insert into book(title, author, published)
    values (
        'Dirk Gently''s Holistic Detective Agency',
        'Douglas Adams',
        1987
    );
    """)
con.close()

fetchone()?

獲取一個(gè)查詢結(jié)果集的下一行，返回一個(gè)單獨(dú)序列，或是在沒有更多可用數(shù)據(jù)時(shí)返回 None。

fetchmany(size=cursor.arraysize)?

獲取下一個(gè)多行查詢結(jié)果集，返回一個(gè)列表。 當(dāng)沒有更多可用行時(shí)將返回一個(gè)空列表。

每次調(diào)用獲取的行數(shù)由 size 形參指定。 如果沒有給出該形參，則由 cursor 的 arraysize 決定要獲取的行數(shù)。 此方法將基于 size 形參值嘗試獲取指定數(shù)量的行。 如果獲取不到指定的行數(shù)，則可能返回較少的行。

請(qǐng)注意 size 形參會(huì)涉及到性能方面的考慮。為了獲得優(yōu)化的性能，通常最好是使用 arraysize 屬性。 如果使用 size 形參，則最好在從一個(gè) fetchmany() 調(diào)用到下一個(gè)調(diào)用之間保持相同的值。

fetchall()?

獲取一個(gè)查詢結(jié)果的所有（剩余）行，返回一個(gè)列表。 請(qǐng)注意 cursor 的 arraysize 屬性會(huì)影響此操作的執(zhí)行效率。 當(dāng)沒有可用行時(shí)將返回一個(gè)空列表。

close()?

立即關(guān)閉 cursor（而不是在當(dāng) __del__ 被調(diào)用的時(shí)候）。

從這一時(shí)刻起該 cursor 將不再可用，如果再嘗試用該 cursor 執(zhí)行任何操作將引發(fā) ProgrammingError 異常。

setinputsizes(sizes)?

Required by the DB-API. Does nothing in sqlite3.

setoutputsize(size[, column])?

Required by the DB-API. Does nothing in sqlite3.

rowcount?

雖然 sqlite3 模塊的 Cursor 類實(shí)現(xiàn)了此屬性，但數(shù)據(jù)庫(kù)引擎本身對(duì)于確定 "受影響行"/"已選擇行" 的支持并不完善。

對(duì)于 executemany() 語(yǔ)句，修改行數(shù)會(huì)被匯總至 rowcount。

根據(jù) Python DB API 規(guī)格描述的要求，rowcount 屬性 "當(dāng)未在 cursor 上執(zhí)行 executeXX() 或者上一次操作的 rowcount 不是由接口確定時(shí)為 -1"。 這包括 SELECT 語(yǔ)句，因?yàn)槲覀儫o(wú)法確定一次查詢將產(chǎn)生的行計(jì)數(shù)，而要等獲取了所有行時(shí)才會(huì)知道。

lastrowid?

This read-only attribute provides the row id of the last inserted row. It is only updated after successful INSERT or REPLACE statements using the execute() method. For other statements, after executemany() or executescript(), or if the insertion failed, the value of lastrowid is left unchanged. The initial value of lastrowid is None.

備注

Inserts into WITHOUT ROWID tables are not recorded.

在 3.6 版更改: 增加了 REPLACE 語(yǔ)句的支持。

arraysize?

用于控制 fetchmany() 返回行數(shù)的可讀取/寫入屬性。 該屬性的默認(rèn)值為 1，表示每次調(diào)用將獲取單獨(dú)一行。

description?

這個(gè)只讀屬性將提供上一次查詢的列名稱。 為了與 Python DB API 保持兼容，它會(huì)為每個(gè)列返回一個(gè) 7 元組，每個(gè)元組的最后六個(gè)條目均為 None。

對(duì)于沒有任何匹配行的 SELECT 語(yǔ)句同樣會(huì)設(shè)置該屬性。

connection?

這個(gè)只讀屬性將提供 Cursor 對(duì)象所使用的 SQLite 數(shù)據(jù)庫(kù) Connection。 通過(guò)調(diào)用 con.cursor() 創(chuàng)建的 Cursor 對(duì)象所包含的 connection 屬性將指向 con:

>>>

>>> con = sqlite3.connect(":memory:")
>>> cur = con.cursor()
>>> cur.connection == con
True

行對(duì)象?

class sqlite3.Row?

一個(gè) Row 實(shí)例，該實(shí)例將作為用于 Connection 對(duì)象的高度優(yōu)化的 row_factory。 它的大部分行為都會(huì)模仿元組的特性。

它支持使用列名稱的映射訪問(wèn)以及索引、迭代、文本表示、相等檢測(cè)和 len() 等操作。

如果兩個(gè) Row 對(duì)象具有完全相同的列并且其成員均相等，則它們的比較結(jié)果為相等。

keys()?

此方法會(huì)在一次查詢之后立即返回一個(gè)列名稱的列表，它是 Cursor.description 中每個(gè)元組的第一個(gè)成員。

在 3.5 版更改: 添加了對(duì)切片操作的支持。

讓我們假設(shè)我們?nèi)缟厦娴睦铀境跏蓟粋€(gè)表:

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
cur.execute("""insert into stocks
            values ('2006-01-05','BUY','RHAT',100,35.14)""")
con.commit()
cur.close()

現(xiàn)在我們將 Row 插入:

>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = cur.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14

Blob Objects?

class sqlite3.Blob?

A Blob instance is a file-like object that can read and write data in an SQLite BLOB. Call len(blob) to get the size (number of bytes) of the blob. Use indices and slices for direct access to the blob data.

Use the Blob as a context manager to ensure that the blob handle is closed after use.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table test(blob_col blob)")
con.execute("insert into test(blob_col) values (zeroblob(13))")

# Write to our blob, using two write operations:
with con.blobopen("test", "blob_col", 1) as blob:
    blob.write(b"hello, ")
    blob.write(b"world.")
    # Modify the first and last bytes of our blob
    blob[0] = ord("H")
    blob[-1] = ord("!")

# Read the contents of our blob
with con.blobopen("test", "blob_col", 1) as blob:
    greeting = blob.read()

print(greeting)  # outputs "b'Hello, world!'"

close()?

Close the blob.

The blob will be unusable from this point onward. An Error (or subclass) exception will be raised if any further operation is attempted with the blob.

read(length=- 1, /)?

Read length bytes of data from the blob at the current offset position. If the end of the blob is reached, the data up to EOF will be returned. When length is not specified, or is negative, read() will read until the end of the blob.

write(data, /)?

Write data to the blob at the current offset. This function cannot change the blob length. Writing beyond the end of the blob will raise ValueError.

tell()?

Return the current access position of the blob.

seek(offset, origin=os.SEEK_SET, /)?

Set the current access position of the blob to offset. The origin argument defaults to os.SEEK_SET (absolute blob positioning). Other values for origin are os.SEEK_CUR (seek relative to the current position) and os.SEEK_END (seek relative to the blob’s end).

異常?

exception sqlite3.Warning?

Exception 的一個(gè)子類。

exception sqlite3.Error?

此模塊中其他異常的基類。 它是 Exception 的一個(gè)子類。

sqlite_errorcode?

The numeric error code from the SQLite API

3.11 新版功能.

sqlite_errorname?

The symbolic name of the numeric error code from the SQLite API

3.11 新版功能.

exception sqlite3.DatabaseError?

針對(duì)數(shù)據(jù)庫(kù)相關(guān)錯(cuò)誤引發(fā)的異常。

exception sqlite3.IntegrityError?

當(dāng)數(shù)據(jù)庫(kù)的關(guān)系一致性受到影響時(shí)引發(fā)的異常。 例如外鍵檢查失敗等。 它是 DatabaseError 的子類。

exception sqlite3.ProgrammingError?

編程錯(cuò)誤引發(fā)的異常，例如表未找到或已存在，SQL 語(yǔ)句存在語(yǔ)法錯(cuò)誤，指定的形參數(shù)量錯(cuò)誤等。 它是 DatabaseError 的子類。

exception sqlite3.OperationalError?

與數(shù)據(jù)庫(kù)操作相關(guān)而不一定能受程序員掌控的錯(cuò)誤引發(fā)的異常，例如發(fā)生非預(yù)期的連接中斷，數(shù)據(jù)源名稱未找到，事務(wù)無(wú)法被執(zhí)行等。 它是 DatabaseError 的子類。

exception sqlite3.NotSupportedError?

在使用了某個(gè)數(shù)據(jù)庫(kù)不支持的方法或數(shù)據(jù)庫(kù) API 時(shí)引發(fā)的異常，例如在一個(gè)不支持事務(wù)或禁用了事務(wù)的連接上調(diào)用 rollback() 方法等。 它是 DatabaseError 的子類。

SQLite 與 Python 類型?

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

con.close()

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

sqlite3.register_adapter(Point, adapt_point)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

con.close()

import sqlite3
import datetime
import time

def adapt_datetime(ts):
    return time.mktime(ts.timetuple())

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

con = sqlite3.connect(":memory:")
cur = con.cursor()

now = datetime.datetime.now()
cur.execute("select ?", (now,))
print(cur.fetchone()[0])

con.close()

def convert_point(s):
    x, y = map(float, s.split(b";"))
    return Point(x, y)

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return ("%f;%f" % (point.x, point.y)).encode('ascii')

def convert_point(s):
    x, y = list(map(float, s.split(b";")))
    return Point(x, y)

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print("with declared types:", cur.fetchone()[0])
cur.close()
con.close()

#######################
# 1) Using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print("with column names:", cur.fetchone()[0])
cur.close()
con.close()

import sqlite3
import datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()
now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print(today, "=>", row[0], type(row[0]))
print(now, "=>", row[1], type(row[1]))

cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print("current_date", row[0], type(row[0]))
print("current_timestamp", row[1], type(row[1]))

con.close()

控制事務(wù)?

底層的 sqlite3 庫(kù)默認(rèn)會(huì)以 autocommit 模式運(yùn)行，但 Python 的 sqlite3 模塊默認(rèn)則不使用此模式。

autocommit 模式意味著修改數(shù)據(jù)庫(kù)的操作會(huì)立即生效。 BEGIN 或 SAVEPOINT 語(yǔ)句會(huì)禁用 autocommit 模式，而用于結(jié)束外層事務(wù)的 COMMIT, ROLLBACK 或 RELEASE 則會(huì)恢復(fù) autocommit 模式。

Python 的 sqlite3 模塊默認(rèn)會(huì)在數(shù)據(jù)修改語(yǔ)言 (DML) 類語(yǔ)句 (即 INSERT/UPDATE/DELETE/REPLACE) 之前隱式地執(zhí)行一條 BEGIN 語(yǔ)句。

你可以控制 sqlite3 隱式執(zhí)行的 BEGIN 語(yǔ)句的種類，具體做法是通過(guò)將 isolation_level 形參傳給 connect() 調(diào)用，或者通過(guò)指定連接的 isolation_level 屬性。 如果你沒有指定 isolation_level，將使用基本的 BEGIN，它等價(jià)于指定 DEFERRED。 其他可能的值為 IMMEDIATE 和 EXCLUSIVE。

你可以禁用 sqlite3 模塊的隱式事務(wù)管理，具體做法是將 isolation_level 設(shè)為 None。 這將使得下層的 sqlite3 庫(kù)采用 autocommit 模式。 隨后你可以通過(guò)在代碼中顯式地使用 BEGIN, ROLLBACK, SAVEPOINT 和 RELEASE 語(yǔ)句來(lái)完全控制事務(wù)狀態(tài)。

請(qǐng)注意 executescript() 會(huì)忽略 isolation_level；任何事務(wù)控制必要要顯式地添加。

有效使用 sqlite3?

import sqlite3

langs = [
    ("C++", 1985),
    ("Objective-C", 1984),
]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table lang(name, first_appeared)")

# Fill the table
con.executemany("insert into lang(name, first_appeared) values (?, ?)", langs)

# Print the table contents
for row in con.execute("select name, first_appeared from lang"):
    print(row)

print("I just deleted", con.execute("delete from lang").rowcount, "rows")

# close is not a shortcut method and it's not called automatically,
# so the connection object should be closed manually
con.close()

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute("select 'John' as name, 42 as age")
for row in cur:
    assert row[0] == row["name"]
    assert row["name"] == row["nAmE"]
    assert row[1] == row["age"]
    assert row[1] == row["AgE"]

con.close()

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table lang (id integer primary key, name varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into lang(name) values (?)", ("Python",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into lang(name) values (?)", ("Python",))
except sqlite3.IntegrityError:
    print("couldn't add Python twice")

# Connection object used as context manager only commits or rollbacks transactions,
# so the connection object should be closed manually
con.close()