sqlite3 —SQLiteデータベース用のDB-API2.0インターフェース

ソースコード： ：source： `Lib / sqlite3 /`

SQLiteは、個別のサーバープロセスを必要とせず、SQLクエリ言語の非標準バリアントを使用してデータベースにアクセスできる軽量のディスクベースのデータベースを提供するCライブラリです。一部のアプリケーションは、内部データストレージにSQLiteを使用できます。 SQLiteを使用してアプリケーションのプロトタイプを作成し、そのコードをPostgreSQLやOracleなどのより大きなデータベースに移植することもできます。

sqlite3モジュールはGerhardHäringによって作成されました。 PEP 249 で説明されているDB-API2.0仕様に準拠したSQLインターフェイスを提供します。

モジュールを使用するには、最初にデータベースを表す Connection オブジェクトを作成する必要があります。ここで、データはexample.dbファイルに保存されます。

import sqlite3
con = sqlite3.connect('example.db')

特別な名前:memory:を指定して、RAMにデータベースを作成することもできます。

接続を取得したら、カーソルオブジェクトを作成し、その execute（）メソッドを呼び出してSQLコマンドを実行できます。

cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
con.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

保存したデータは永続的であり、後続のセッションで利用できます。

import sqlite3
con = sqlite3.connect('example.db')
cur = con.cursor()

通常、SQL操作ではPython変数の値を使用する必要があります。安全ではないため、Pythonの文字列操作を使用してクエリをアセンブルしないでください。プログラムがSQLインジェクション攻撃に対して脆弱になります（問題が発生する可能性のあるユーモラスな例については、 https://xkcd.com/327/を参照してください）。

代わりに、DB-APIのパラメーター置換を使用してください。値を使用する場所に?をプレースホルダーとして配置し、カーソルの execute（）メソッドの2番目の引数として値のタプルを指定します。（他のデータベースモジュールは、%sや:1などの異なるプレースホルダーを使用する場合があります。）例：

# Never do this -- insecure!
symbol = 'RHAT'
cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
cur.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(cur.fetchone())

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
cur.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

SELECTステートメントの実行後にデータを取得するには、カーソルを iterator として扱うか、カーソルの fetchone（）メソッドを呼び出して単一の一致する行を取得するか、を呼び出します。 fetchall（）を使用して、一致する行のリストを取得します。

この例では、イテレータ形式を使用しています。

>>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

も参照してください

https://www.sqlite.org: SQLiteWebページ。ドキュメントには、サポートされているSQLダイアレクトの構文と使用可能なデータ型が記載されています。
https://www.w3schools.com/sql/: SQL構文を学習するためのチュートリアル、リファレンス、および例。
PEP 249 -データベースAPI仕様2.0: Marc-AndréLemburgによって書かれたPEP。

モジュールの関数と定数

sqlite3.version: このモジュールのバージョン番号（文字列として）。これはSQLiteライブラリのバージョンではありません。

sqlite3.version_info: 整数のタプルとしての、このモジュールのバージョン番号。これはSQLiteライブラリのバージョンではありません。

sqlite3.sqlite_version: 文字列としてのランタイムSQLiteライブラリのバージョン番号。

sqlite3.sqlite_version_info: 整数のタプルとしてのランタイムSQLiteライブラリのバージョン番号。

sqlite3.PARSE_DECLTYPES

この定数は、 connect（）関数の detect_types パラメーターで使用することを目的としています。

これを設定すると、 sqlite3 モジュールは返される各列の宣言された型を解析します。宣言されたタイプの最初の単語を解析します。 e。「integerprimarykey」の場合は「integer」を解析し、「number（10）」の場合は「number」を解析します。次に、その列について、コンバーター辞書を調べ、そこでそのタイプに登録されているコンバーター関数を使用します。

sqlite3.PARSE_COLNAMES

この定数は、 connect（）関数の detect_types パラメーターで使用することを目的としています。

これを設定すると、SQLiteインターフェイスは返される各列の列名を解析します。そこで[mytype]で形成された文字列を探し、「mytype」が列のタイプであると判断します。コンバーターディクショナリで「mytype」のエントリを見つけようとし、そこで見つかったコンバーター関数を使用して値を返します。 Cursor.description にある列名には、タイプが含まれていません。 e。 SQLで'as "Expiration date [datetime]"'のようなものを使用する場合、列名の最初の'['まですべてを解析し、前のスペースを削除します。列名は単に「有効期限」になります。。

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

SQLiteデータベースファイルデータベースへの接続を開きます。カスタムファクトリが指定されていない限り、デフォルトでは Connection オブジェクトが返されます。

database は、開くデータベースファイルのパス名（絶対または現在の作業ディレクトリからの相対パス）を示すパスのようなオブジェクトです。 ":memory:"を使用して、ディスクではなくRAMにあるデータベースへのデータベース接続を開くことができます。

データベースが複数の接続によってアクセスされ、プロセスの1つがデータベースを変更すると、そのトランザクションがコミットされるまでSQLiteデータベースがロックされます。 timeout パラメーターは、例外が発生するまで接続がロックが解除されるまで待機する時間を指定します。タイムアウトパラメータのデフォルトは5.0（5秒）です。

isolation_level パラメーターについては、 Connection オブジェクトの isolation_level プロパティを参照してください。

SQLiteは、TEXT、INTEGER、REAL、BLOB、およびNULLタイプのみをネイティブにサポートします。他のタイプを使用する場合は、自分でそれらのサポートを追加する必要があります。 detect_types パラメーターと、モジュールレベルの register_converter（）関数に登録されているカスタムコンバーターを使用すると、これを簡単に行うことができます。

detect_types のデフォルトは0（i。 e。オフ、タイプ検出なし）、 PARSE_DECLTYPES と PARSE_COLNAMES の任意の組み合わせに設定して、タイプ検出をオンにすることができます。 SQLiteの動作により、 detect_types パラメーターが設定されている場合でも、生成されたフィールド（max(data)など）のタイプを検出できません。このような場合、返されるタイプは str です。

デフォルトでは、 check_same_thread は True であり、作成中のスレッドのみが接続を使用できます。 False を設定すると、返された接続が複数のスレッド間で共有される場合があります。同じ接続で複数のスレッドを使用する場合は、データの破損を防ぐために、書き込み操作をユーザーがシリアル化する必要があります。

デフォルトでは、 sqlite3 モジュールは接続呼び出しに Connection クラスを使用します。ただし、 Connection クラスをサブクラス化し、 factory パラメーターにクラスを指定することで、代わりに connect（）にクラスを使用させることができます。

詳細については、このマニュアルの SQLiteおよびPythonタイプのセクションを参照してください。

sqlite3 モジュールは、SQL解析のオーバーヘッドを回避するために、内部でステートメントキャッシュを使用します。接続用にキャッシュされるステートメントの数を明示的に設定する場合は、 cached_statements パラメーターを設定できます。現在実装されているデフォルトは、100個のステートメントをキャッシュすることです。

uri がtrueの場合、 database はURIとして解釈されます。これにより、オプションを指定できます。たとえば、データベースを読み取り専用モードで開くには、次を使用できます。

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

認識されているオプションのリストなど、この機能の詳細については、 SQLiteURIドキュメントを参照してください。

バージョン3.4で変更： uri パラメーターが追加されました。

バージョン3.7での変更： データベースは、文字列だけでなく、パスのようなオブジェクトにもできるようになりました。

sqlite3.register_converter(typename, callable): データベースからのバイト文字列をカスタムPython型に変換する呼び出し可能オブジェクトを登録します。呼び出し可能オブジェクトは、タイプ typename のすべてのデータベース値に対して呼び出されます。タイプ検出がどのように機能するかについては、 connect（）関数のパラメーター detect_types を付与してください。 typename とクエリ内のタイプの名前は、大文字と小文字を区別しない方法で一致することに注意してください。

sqlite3.register_adapter(type, callable): カスタムPythonタイプタイプをSQLiteでサポートされているタイプの1つに変換する呼び出し可能オブジェクトを登録します。呼び出し可能呼び出し可能は、Python値を単一のパラメーターとして受け入れ、int、float、str、またはbytesのタイプの値を返す必要があります。

sqlite3.complete_statement(sql)

文字列 sql にセミコロンで終了する1つ以上の完全なSQLステートメントが含まれている場合、 True を返します。 SQLが構文的に正しいことを検証するのではなく、閉じられていない文字列リテラルがなく、ステートメントがセミコロンで終了していることだけを検証します。

これは、次の例のように、SQLiteのシェルを構築するために使用できます。

# 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:
            print("An error occurred:", e.args[0])
        buffer = ""

con.close()

sqlite3.enable_callback_tracebacks(flag): デフォルトでは、ユーザー定義関数、集計、コンバーター、承認者のコールバックなどでトレースバックを取得することはありません。それらをデバッグしたい場合は、フラグをTrueに設定してこの関数を呼び出すことができます。その後、sys.stderrのコールバックからトレースバックを取得します。 False を使用して、機能を再度無効にします。

接続オブジェクト

class sqlite3.Connection

SQLiteデータベース接続には、次の属性とメソッドがあります。

isolation_level: 現在のデフォルトの分離レベルを取得または設定します。自動コミットモードの場合はなし、または「DEFERRED」、「IMMEDIATE」、「EXCLUSIVE」のいずれか。詳細な説明については、セクショントランザクションの制御を参照してください。

in_transaction: トランザクションがアクティブな場合（コミットされていない変更がある場合）は True 、それ以外の場合は False 。読み取り専用属性。

バージョン3.2の新機能。

cursor(factory=Cursor): カーソルメソッドは、単一のオプションパラメータ factory を受け入れます。指定する場合、これは Cursor またはそのサブクラスのインスタンスを返す呼び出し可能である必要があります。

commit(): このメソッドは、現在のトランザクションをコミットします。このメソッドを呼び出さない場合、commit()への最後の呼び出し以降に行ったことは、他のデータベース接続からは表示されません。データベースに書き込んだデータが表示されない理由がわからない場合は、このメソッドを呼び出すことを忘れていないことを確認してください。

rollback(): このメソッドは、 commit（）の最後の呼び出し以降のデータベースへの変更をロールバックします。

close(): これにより、データベース接続が閉じられます。これは commit（）を自動的に呼び出さないことに注意してください。最初に commit（）を呼び出さずにデータベース接続を閉じると、変更が失われます。

execute(sql[, parameters]): これは、 cursor（）メソッドを呼び出してカーソルオブジェクトを作成し、パラメータを指定してカーソルの execute（）メソッドを呼び出し、カーソル。

executemany(sql[, parameters]): これは、 cursor（）メソッドを呼び出してカーソルオブジェクトを作成し、パラメータを指定してカーソルの executemany（）メソッドを呼び出し、カーソル。

executescript(sql_script): これは、 cursor（）メソッドを呼び出してカーソルオブジェクトを作成し、指定された sql_script を使用してカーソルの executescript（）メソッドを呼び出し、カーソル。

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

関数名 name でSQLステートメント内から後で使用できるユーザー定義関数を作成します。 num_params は関数が受け入れるパラメーターの数であり（ num_params が-1の場合、関数は任意の数の引数を取ることができます）、 func はPythonで呼び出し可能ですこれはSQL関数と呼ばれます。 deterministic がtrueの場合、作成された関数は deterministic としてマークされ、SQLiteが追加の最適化を実行できるようにします。このフラグはSQLite3.8.3以降でサポートされています。古いバージョンで使用すると、 NotSupportedError が発生します。

この関数は、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)

ユーザー定義の集計関数を作成します。

集約クラスは、パラメーターの数 num_params を受け入れるstepメソッドを実装する必要があります（ num_params が-1の場合、関数は任意の数の引数を取ることができます）、 finalizeメソッドは、集計の最終結果を返します。

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_collation(name, callable)

指定された name および callable で照合を作成します。呼び出し可能オブジェクトには、2つの文字列引数が渡されます。最初の順序が2番目の順序よりも小さい場合は-1、同じ順序の場合は0、最初の順序が2番目の順序よりも大きい場合は1を返す必要があります。これはソート（SQLではORDER BY）を制御するため、比較が他のSQL操作に影響を与えないことに注意してください。

呼び出し可能オブジェクトは、通常UTF-8でエンコードされるPythonバイト文字列としてパラメーターを取得することに注意してください。

次の例は、「間違った方法」でソートするカスタム照合を示しています。

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()

照合を削除するには、Noneを呼び出し可能としてcreate_collationを呼び出します。

con.create_collation("reverse", None)

interrupt(): 別のスレッドからこのメソッドを呼び出して、接続で実行されている可能性のあるクエリを中止できます。その後、クエリは中止され、呼び出し元は例外を受け取ります。

set_authorizer(authorizer_callback)

このルーチンはコールバックを登録します。コールバックは、データベース内のテーブルの列にアクセスしようとするたびに呼び出されます。コールバックは、アクセスが許可されている場合はSQLITE_OKを返し、SQLステートメント全体をエラーで中止する必要がある場合はSQLITE_DENYを返し、列をNULL値として扱う必要がある場合はSQLITE_IGNOREを返します。これらの定数は、 sqlite3 モジュールで使用できます。

コールバックの最初の引数は、許可される操作の種類を示します。 2番目と3番目の引数は、最初の引数に応じて引数または None になります。 4番目の引数は、該当する場合、データベースの名前（ "main"、 "temp"など）です。 5番目の引数は、アクセス試行を担当する最も内側のトリガーまたはビューの名前です。このアクセス試行が入力SQLコードから直接行われる場合は、 None です。

最初の引数の可能な値と、最初の引数に応じた2番目と3番目の引数の意味については、SQLiteのドキュメントを参照してください。必要なすべての定数は、 sqlite3 モジュールで利用できます。

set_progress_handler(handler, n)

このルーチンはコールバックを登録します。コールバックは、SQLite仮想マシンの n 命令ごとに呼び出されます。これは、GUIの更新など、長時間実行される操作中にSQLiteから呼び出される場合に役立ちます。

以前にインストールされた進行状況ハンドラーをクリアする場合は、ハンドラーに対してなしを指定してメソッドを呼び出します。

ハンドラー関数からゼロ以外の値を返すと、現在実行中のクエリが終了し、 OperationalError 例外が発生します。

set_trace_callback(trace_callback)

SQLiteバックエンドによって実際に実行されるSQLステートメントごとに呼び出される trace_callback を登録します。

コールバックに渡される唯一の引数は、実行されているステートメント（文字列として）です。コールバックの戻り値は無視されます。バックエンドは、 Cursor.execute（）メソッドに渡されたステートメントを実行するだけではないことに注意してください。その他のソースには、Pythonモジュールのトランザクション管理や、現在のデータベースで定義されているトリガーの実行が含まれます。

None を trace_callback として渡すと、トレースコールバックが無効になります。

バージョン3.3の新機能。

enable_load_extension(enabled)

このルーチンは、SQLiteエンジンが共有ライブラリからSQLite拡張機能をロードすることを許可/禁止します。 SQLite拡張機能は、新しい関数、集計、またはまったく新しい仮想テーブルの実装を定義できます。よく知られている拡張機能の1つは、SQLiteで配布されている全文検索拡張機能です。

ロード可能な拡張機能はデフォルトで無効になっています。 1 を参照してください。

バージョン3.2の新機能。

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)

このルーチンは、共有ライブラリからSQLite拡張機能をロードします。このルーチンを使用する前に、 enable_load_extension（）を使用して拡張機能のロードを有効にする必要があります。

ロード可能な拡張機能はデフォルトで無効になっています。 1 を参照してください。

バージョン3.2の新機能。

row_factory

この属性を、カーソルと元の行をタプルとして受け入れ、実際の結果行を返す呼び出し可能オブジェクトに変更できます。このようにして、名前で列にアクセスすることもできるオブジェクトを返すなど、結果を返すより高度な方法を実装できます。

例：

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()

タプルを返すだけでは不十分で、列への名前ベースのアクセスが必要な場合は、 row_factory を高度に最適化された sqlite3.Row タイプに設定することを検討する必要があります。 Row は、メモリのオーバーヘッドをほとんど発生させずに、インデックスベースと大文字と小文字を区別しない名前ベースの列へのアクセスを提供します。おそらく、独自のカスタム辞書ベースのアプローチや、db_rowベースのソリューションよりも優れているでしょう。

text_factory

この属性を使用して、TEXTデータ型に対して返されるオブジェクトを制御できます。デフォルトでは、この属性は str に設定されており、 sqlite3 モジュールはTEXTのUnicodeオブジェクトを返します。代わりにバイト文字列を返したい場合は、 bytes に設定できます。

また、単一のバイト文字列パラメーターを受け入れ、結果のオブジェクトを返す他の呼び出し可能オブジェクトに設定することもできます。

説明については、次のサンプルコードを参照してください。

import sqlite3

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

AUSTRIA = "\xd6sterreich"

# by default, rows are returned as Unicode
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: データベース接続が開かれてから変更、挿入、または削除されたデータベース行の総数を返します。

iterdump()

データベースをSQLテキスト形式でダンプするイテレータを返します。後で復元するためにインメモリデータベースを保存するときに便利です。この関数は、 sqlite3 シェルの .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)

このメソッドは、SQLiteデータベースが他のクライアントからアクセスされている間、または同じ接続によって同時にアクセスされている場合でも、SQLiteデータベースのバックアップを作成します。コピーは必須の引数 target に書き込まれます。これは、別の Connection インスタンスである必要があります。

デフォルト、または pages が0または負の整数の場合、データベース全体が1つのステップでコピーされます。それ以外の場合、メソッドは一度に最大ページページをコピーするループを実行します。

progress を指定する場合は、Noneか、3つの整数引数を使用して各反復で実行される呼び出し可能オブジェクトである必要があります。それぞれ最後の反復の status です。、残りまだコピーされていないページ数、および合計ページ数。

name 引数は、コピーされるデータベース名を指定します。メインデータベースを示すデフォルトの"main"、メインデータベースを示す"temp"のいずれかを含む文字列である必要があります。一時データベース、または接続されたデータベースのATTACH DATABASEステートメントのASキーワードの後に指定された名前。

sleep 引数は、残りのページをバックアップする連続した試行の間にスリープする秒数を指定します。整数または浮動小数点値のいずれかとして指定できます。

例1、既存のデータベースを別のデータベースにコピーします。

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()

例2、既存のデータベースを一時コピーにコピーします。

import sqlite3

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

可用性：SQLite3.6.11以降

バージョン3.7の新機能。

カーソルオブジェクト

class sqlite3.Cursor

Cursor インスタンスには、次の属性とメソッドがあります。

execute(sql[, parameters])

SQLステートメントを実行します。 SQLステートメントはパラメーター化できます（i。 e。 SQLリテラルの代わりにプレースホルダー）。 sqlite3 モジュールは、疑問符（qmarkスタイル）と名前付きプレースホルダー（名前付きスタイル）の2種類のプレースホルダーをサポートします。

両方のスタイルの例を次に示します。

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print(cur.fetchone())

con.close()

execute（）は、単一のSQLステートメントのみを実行します。それを使用して複数のステートメントを実行しようとすると、警告が発生します。 1回の呼び出しで複数のSQLステートメントを実行する場合は、 executescript（）を使用します。

executemany(sql, seq_of_parameters)

シーケンス seq_of_parameters で見つかったすべてのパラメーターシーケンスまたはマッピングに対してSQLコマンドを実行します。 sqlite3 モジュールでは、シーケンスの代わりに iterator 生成パラメーターを使用することもできます。

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()

ジェネレーターを使用した短い例を次に示します。

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)

これは、複数のSQLステートメントを一度に実行するための非標準の便利な方法です。最初にCOMMITステートメントを発行し、次にパラメーターとして取得したSQLスクリプトを実行します。

sql_script は str のインスタンスにすることができます。

例：

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(): クエリ結果セットの次の行をフェッチして単一のシーケンスを返すか、使用可能なデータがなくなったときに None を返します。

fetchmany(size=cursor.arraysize)

クエリ結果の次の行セットをフェッチして、リストを返します。使用可能な行がなくなると、空のリストが返されます。

呼び出しごとにフェッチする行数は、 size パラメーターで指定されます。指定されていない場合、カーソルのarraysizeによって、フェッチされる行数が決まります。メソッドは、sizeパラメーターで示される数の行をフェッチしようとする必要があります。指定された行数が使用できないためにこれが不可能な場合は、返される行が少なくなる可能性があります。

size パラメーターに関連するパフォーマンスの考慮事項があることに注意してください。最適なパフォーマンスを得るには、通常、arraysize属性を使用するのが最適です。 size パラメーターを使用する場合は、1回の fetchmany（）呼び出しから次の呼び出しまで同じ値を保持するのが最適です。

fetchall(): クエリ結果のすべての（残りの）行をフェッチして、リストを返します。カーソルのarraysize属性は、この操作のパフォーマンスに影響を与える可能性があることに注意してください。使用可能な行がない場合は、空のリストが返されます。

close()

ここでカーソルを閉じます（__del__が呼び出されるたびにではなく）。

この時点からカーソルは使用できなくなります。カーソルで何らかの操作を行おうとすると、 ProgrammingError 例外が発生します。

rowcount

sqlite3 モジュールの Cursor クラスはこの属性を実装していますが、「影響を受ける行」/「選択された行」の決定に対するデータベースエンジン自体のサポートは風変わりです。

executemany（）ステートメントの場合、変更の数は rowcount に合計されます。

Python DB API仕様で要求されているように、 rowcount 属性は、カーソルに対してexecuteXX()が実行されていない場合、または最後の操作の行数がインターフェイスで判別できない場合、-1です。」。これにはSELECTステートメントが含まれます。これは、すべての行がフェッチされるまでクエリが生成した行数を特定できないためです。

3.6.5より前のSQLiteバージョンでは、条件なしでDELETE FROM tableを作成すると、 rowcount は0に設定されます。

lastrowid

この読み取り専用属性は、最後に変更された行のROWIDを提供します。 execute（）メソッドを使用してINSERTまたはREPLACEステートメントを発行した場合にのみ設定されます。 INSERTまたはREPLACE以外の操作の場合、または executemany（）が呼び出された場合、 lastrowid は None に設定されます。

INSERTまたはREPLACEステートメントが挿入に失敗した場合、前に成功したROWIDが返されます。

バージョン3.6で変更： REPLACEステートメントのサポートが追加されました。

arraysize: fetchmany（）によって返される行数を制御する読み取り/書き込み属性。デフォルト値は1です。これは、呼び出しごとに1つの行がフェッチされることを意味します。

description

この読み取り専用属性は、最後のクエリの列名を提供します。 Python DB APIとの互換性を維持するために、各タプルの最後の6つの項目が None である各列に対して7タプルを返します。

一致する行がないSELECTステートメントにも設定されます。

connection

この読み取り専用属性は、カーソルオブジェクトによって使用されるSQLiteデータベース接続を提供します。 con.cursor（）を呼び出して作成された Cursor オブジェクトには、 con を参照する connection 属性があります。

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

行オブジェクト

class sqlite3.Row

Row インスタンスは、 Connection オブジェクト用に高度に最適化された row_factory として機能します。ほとんどの機能でタプルを模倣しようとします。

列名とインデックス、反復、表現、同等性テスト、および len（）によるマッピングアクセスをサポートします。

2つの Row オブジェクトの列がまったく同じで、それらのメンバーが等しい場合、それらは等しいと比較されます。

keys(): このメソッドは、列名のリストを返します。クエリの直後は、 Cursor.description の各タプルの最初のメンバーです。

バージョン3.5で変更：スライスのサポートが追加されました。

上記の例のようにテーブルを初期化するとします。

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()

次に、 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

例外

exception sqlite3.Warning: 例外のサブクラス。

exception sqlite3.Error: このモジュールの他の例外の基本クラス。 Exception のサブクラスです。

exception sqlite3.DatabaseError: データベースに関連するエラーに対して発生した例外。

exception sqlite3.IntegrityError: データベースのリレーショナル整合性が影響を受ける場合に発生する例外。外部キーチェックは失敗します。 DatabaseError のサブクラスです。

exception sqlite3.ProgrammingError: プログラミングエラーのために発生した例外。テーブルが見つからないか、すでに存在している、SQLステートメントの構文エラー、指定されたパラメーターの数が間違っているなど。 DatabaseError のサブクラスです。

exception sqlite3.OperationalError: データベースの操作に関連し、必ずしもプログラマーの制御下にあるとは限らないエラーに対して発生した例外。予期しない切断が発生した、データソース名が見つからない、トランザクションを処理できなかったなど。 DatabaseError のサブクラスです。

exception sqlite3.NotSupportedError: データベースでサポートされていないメソッドまたはデータベースAPIが使用された場合に発生する例外。トランザクションをサポートしていない接続、またはトランザクションがオフになっている接続で rollback（）メソッドを呼び出す。 DatabaseError のサブクラスです。

SQLiteとPythonのタイプ

序章

SQLiteは、NULL、INTEGER、REAL、TEXT、BLOBのタイプをネイティブにサポートしています。

したがって、次のPythonタイプを問題なくSQLiteに送信できます。

Pythonタイプ	SQLiteタイプ
`None`	`NULL`
`int`	`INTEGER`
`float`	`REAL`
`str`	`TEXT`
`bytes`	`BLOB`

これは、SQLite型がデフォルトでPython型に変換される方法です。

SQLiteタイプ	Pythonタイプ
`NULL`	`None`
`INTEGER`	`int`
`REAL`	`float`
`TEXT`	デフォルトでは text_factory 、 str に依存します
`BLOB`	`bytes`

sqlite3 モジュールの型システムは、2つの方法で拡張できます。オブジェクトアダプテーションを介して追加のPython型をSQLiteデータベースに格納する方法と、 sqlite3 モジュールにSQLite型を変換させる方法です。コンバーターを介したさまざまなPythonタイプ。

アダプターを使用して追加のPythonタイプをSQLiteデータベースに保存する

前に説明したように、SQLiteは限られたタイプのセットのみをネイティブにサポートします。 SQLiteで他のPython型を使用するには、SQLiteでサポートされているsqlite3モジュールの型の1つ（NoneType、int、float、str、bytesのいずれか）に適応する必要があります。

sqlite3 モジュールを有効にして、カスタムPythonタイプをサポートされているタイプの1つに適合させるには2つの方法があります。

オブジェクトを適応させる

クラスを自分で作成する場合、これは良いアプローチです。次のようなクラスがあるとします。

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

ここで、ポイントを単一のSQLite列に格納します。まず、ポイントを表すために使用するサポートされているタイプの1つを最初に選択する必要があります。 strを使用し、セミコロンを使用して座標を区切ります。次に、変換された値を返す必要があるメソッド__conform__(self, protocol)をクラスに与える必要があります。パラメータプロトコルはPrepareProtocolになります。

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()

呼び出し可能なアダプターの登録

もう1つの可能性は、型を文字列表現に変換する関数を作成し、その関数を register_adapter（）に登録することです。

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()

sqlite3 モジュールには、Pythonの組み込み datetime.date および datetime.datetime タイプ用の2つのデフォルトアダプターがあります。ここで、 datetime.datetime オブジェクトをISO表現ではなく、Unixタイムスタンプとして保存するとします。

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()

SQLite値をカスタムPython型に変換する

アダプターを作成すると、カスタムPythonタイプをSQLiteに送信できます。しかし、それを本当に便利にするには、PythonからSQLite、Pythonへのラウンドトリップを機能させる必要があります。

コンバーターを入力してください。

Pointクラスに戻りましょう。セミコロンで区切られたx座標とy座標を文字列としてSQLiteに保存しました。

まず、文字列をパラメーターとして受け取り、そこからPointオブジェクトを作成するコンバーター関数を定義します。

ノート

コンバーター関数常には、SQLiteに値を送信したデータ型に関係なく、 bytes オブジェクトで呼び出されます。

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

次に、 sqlite3 モジュールに、データベースから選択したものが実際にポイントであることを認識させる必要があります。これを行うには2つの方法があります。

宣言された型を介して暗黙的に
列名を介して明示的に

どちらの方法も、定数 PARSE_DECLTYPES および PARSE_COLNAMES のエントリのセクションモジュール関数および定数で説明されています。

次の例は、両方のアプローチを示しています。

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()

デフォルトのアダプターとコンバーター

datetimeモジュールには、dateおよびdatetimeタイプ用のデフォルトのアダプターがあります。それらはISO日付/ ISOタイムスタンプとしてSQLiteに送信されます。

デフォルトのコンバーターは、 datetime.date の場合は「date」という名前で、 datetime.datetime の場合は「timestamp」という名前で登録されます。

このようにして、ほとんどの場合、追加の手間をかけずにPythonの日付/タイムスタンプを使用できます。アダプターの形式は、実験的なSQLiteの日付/時刻関数とも互換性があります。

次の例はこれを示しています。

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()

SQLiteに保存されているタイムスタンプの小数部分が6桁より長い場合、その値はタイムスタンプコンバーターによってマイクロ秒の精度に切り捨てられます。

トランザクションの制御

基盤となるsqlite3ライブラリはデフォルトでautocommitモードで動作しますが、Python sqlite3 モジュールはデフォルトでは動作しません。

autocommitモードは、データベースを変更するステートメントがすぐに有効になることを意味します。 BEGINまたはSAVEPOINTステートメントは、autocommitモードを無効にし、COMMIT、ROLLBACK、またはRELEASEは最も外側のトランザクションを終了し、autocommitモードをオンに戻します。

Python sqlite3 モジュールは、デフォルトで、データ操作言語（DML）ステートメントの前に暗黙的にBEGINステートメントを発行します（つまり、 INSERT / UPDATE / DELETE / REPLACE）。

BEGINステートメント sqlite3 の種類を isolation_level パラメーターから connect（）呼び出し、またはisolation_level接続のプロパティ。 isolation_level を指定しない場合、プレーンなBEGINが使用されます。これは、DEFERREDを指定するのと同じです。その他の可能な値は、IMMEDIATEおよびEXCLUSIVEです。

isolation_levelをNoneに設定すると、 sqlite3 モジュールの暗黙的なトランザクション管理を無効にできます。これにより、基盤となるsqlite3ライブラリがautocommitモードで動作したままになります。次に、コードでBEGIN、ROLLBACK、SAVEPOINT、およびRELEASEステートメントを明示的に発行することにより、トランザクション状態を完全に制御できます。

バージョン3.6で変更： sqlite3 は、DDLステートメントの前にオープントランザクションを暗黙的にコミットするために使用されていました。これはもはや当てはまりません。

sqlite3 を効率的に使用する

ショートカットメソッドの使用

Connection オブジェクトの非標準のexecute()、executemany()、およびexecutescript()メソッドを使用すると、作成する必要がないため、コードをより簡潔に記述できます。（多くの場合不要な） Cursor オブジェクトを明示的に。代わりに、 Cursor オブジェクトが暗黙的に作成され、これらのショートカットメソッドはカーソルオブジェクトを返します。このようにして、SELECTステートメントを実行し、 Connection オブジェクトに対する1回の呼び出しのみを使用して直接反復することができます。

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]

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

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print(row)

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

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

インデックスではなく名前で列にアクセスする

sqlite3 モジュールの便利な機能の1つは、行ファクトリとして使用するように設計された組み込みの sqlite3.Row クラスです。

このクラスでラップされた行には、インデックス（タプルなど）と大文字と小文字を区別しない名前の両方でアクセスできます。

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 person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# 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 person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

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

脚注

1（ 1 、 2 ）: 一部のプラットフォーム（特にMac OS X）にはこの機能なしでコンパイルされたSQLiteライブラリがあるため、sqlite3モジュールはデフォルトでロード可能な拡張機能をサポートして構築されていません。ロード可能な拡張機能のサポートを取得するには、--enable-loadable-sqlite-extensionsを渡して構成する必要があります。

sqlite3 —SQLiteデータベース用のDB-API2.0インターフェース—Pythonドキュメント

目次