12.6. sqlite3 —SQLiteデータベース用のDB-API2.0インターフェース—Pythonドキュメント
12.6。 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
conn = sqlite3.connect('example.db')
特別な名前:memory:
を指定して、RAMにデータベースを作成することもできます。
接続を取得したら、カーソルオブジェクトを作成し、その execute()メソッドを呼び出してSQLコマンドを実行できます。
c = conn.cursor()
# Create table
c.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
# Save (commit) the changes
conn.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.
conn.close()
保存したデータは永続的であり、後続のセッションで利用できます。
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
通常、SQL操作ではPython変数の値を使用する必要があります。 安全ではないため、Pythonの文字列操作を使用してクエリをアセンブルしないでください。 これにより、プログラムがSQLインジェクション攻撃に対して脆弱になります(問題が発生する可能性のあるユーモラスな例については、 https://xkcd.com/327/を参照してください)。
代わりに、DB-APIのパラメーター置換を使用してください。 値を使用する場所に?
をプレースホルダーとして配置し、カーソルの execute()メソッドの2番目の引数として値のタプルを指定します。 (他のデータベースモジュールは、%s
や:1
などの異なるプレースホルダーを使用する場合があります。)例:
# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.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),
]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
SELECTステートメントの実行後にデータを取得するには、カーソルをイテレータとして扱うか、カーソルの fetchone()メソッドを呼び出して単一の一致する行を取得するか、を呼び出します。 fetchall()を使用して、一致する行のリストを取得します。
この例では、イテレータ形式を使用しています。
>>> for row in c.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://github.com/ghaering/pysqlite
- pysqlite Webページ– sqlite3は、「pysqlite」という名前で外部から開発されています。
- https://www.sqlite.org
- SQLiteWebページ。 ドキュメントには、サポートされているSQLダイアレクトの構文と使用可能なデータ型が記載されています。
- http://www.w3schools.com/sql/
- SQL構文を学習するためのチュートリアル、リファレンス、および例。
- PEP 249 -データベースAPI仕様2.0
- Marc-AndréLemburgによって書かれたPEP。
12.6.1。 モジュールの関数と定数
- 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 "x [datetime]"'
のようなものを使用する場合、列名の最初の空白まですべてを解析します。列名は単に「x」になります。
- sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])
SQLiteデータベースファイルデータベースへの接続を開きます。
":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 の任意の組み合わせに設定して、タイプ検出をオンにすることができます。
デフォルトでは、 check_same_thread は True であり、作成中のスレッドのみが接続を使用できます。 False に設定すると、返された接続が複数のスレッド間で共有される場合があります。 同じ接続で複数のスレッドを使用する場合は、データの破損を防ぐために、書き込み操作をユーザーがシリアル化する必要があります。
デフォルトでは、 sqlite3 モジュールは接続呼び出しに Connection クラスを使用します。 ただし、 factory パラメータにクラスを指定することで、 Connection クラスをサブクラス化し、 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 パラメーターを追加。
- 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 を使用して、機能を再度無効にします。
12.6.2。 接続オブジェクト
- class sqlite3.Connection
SQLiteデータベース接続には、次の属性とメソッドがあります。
- isolation_level
現在のデフォルトの分離レベルを取得または設定します。 なし自動コミットモード、または「DEFERRED」、「IMMEDIATE」、「EXCLUSIVE」のいずれか。 詳細な説明については、セクショントランザクションの制御を参照してください。
- 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)
関数名 name でSQLステートメント内から後で使用できるユーザー定義関数を作成します。 num_params は関数が受け入れるパラメーターの数であり( num_params が-1の場合、関数は任意の数の引数を取ることができます)、 func はPythonで呼び出し可能ですこれはSQL関数と呼ばれます。
この関数は、SQLiteでサポートされている任意の型(bytes、str、int、float、
None
)を返すことができます。例:
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])
- 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])
- 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番目の引数は、最初の引数に応じて引数またはなしになります。 4番目の引数は、該当する場合、データベースの名前( "main"、 "temp"など)です。 5番目の引数は、アクセス試行を担当する最も内側のトリガーまたはビューの名前です。このアクセス試行が入力SQLコードから直接行われる場合は、なしです。
最初の引数の可能な値と、最初の引数に応じた2番目と3番目の引数の意味については、SQLiteのドキュメントを参照してください。 必要なすべての定数は、 sqlite3 モジュールで利用できます。
- set_progress_handler(handler, n)
このルーチンはコールバックを登録します。 コールバックは、SQLite仮想マシンの n 命令ごとに呼び出されます。 これは、GUIの更新など、長時間実行される操作中にSQLiteから呼び出される場合に役立ちます。
以前にインストールされたプログレスハンドラーをクリアする場合は、 handler に対して None を指定してメソッドを呼び出します。
ハンドラー関数からゼロ以外の値を返すと、現在実行中のクエリが終了し、 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)
- 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"])
タプルを返すだけでは不十分で、列への名前ベースのアクセスが必要な場合は、 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"
- 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)
12.6.3。 カーソルオブジェクト
- 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())
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())
ジェネレーターを使用した短い例を次に示します。
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())
- 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 ); """)
- fetchone()
クエリ結果セットの次の行をフェッチして単一のシーケンスを返すか、使用可能なデータがなくなるとなしを返します。
- fetchmany(size=cursor.arraysize)
クエリ結果の次の行セットをフェッチして、リストを返します。 使用可能な行がなくなると、空のリストが返されます。
呼び出しごとにフェッチする行数は、 size パラメーターで指定されます。 指定しない場合、カーソルのarraysizeによってフェッチされる行数が決まります。 このメソッドは、sizeパラメーターで指定された数の行をフェッチしようとする必要があります。 指定された行数が使用できないためにこれが不可能な場合は、返される行が少なくなる可能性があります。
size パラメータに関連するパフォーマンスの考慮事項があることに注意してください。 最適なパフォーマンスを得るには、通常、arraysize属性を使用するのが最適です。 size パラメータを使用する場合は、 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つの項目がなしである各列に対して7タプルを返します。
一致する行がない
SELECT
ステートメントにも設定されます。
- connection
この読み取り専用属性は、カーソルオブジェクトによって使用されるSQLiteデータベース接続を提供します。 con.cursor()を呼び出して作成された Cursor オブジェクトには、 con を参照する connection 属性があります。
>>> con = sqlite3.connect(":memory:") >>> cur = con.cursor() >>> cur.connection == con True
12.6.4。 行オブジェクト
- class sqlite3.Row
Row インスタンスは、 Connection オブジェクト用に高度に最適化された row_factory として機能します。 ほとんどの機能でタプルを模倣しようとします。
列名とインデックス、反復、表現、同等性テスト、および len()によるマッピングアクセスをサポートします。
2つの Row オブジェクトの列がまったく同じで、それらのメンバーが等しい場合、それらは等しいと比較されます。
- keys()
このメソッドは、列名のリストを返します。 クエリの直後は、 Cursor.description の各タプルの最初のメンバーです。
バージョン3.5で変更:スライスのサポートが追加されました。
上記の例のようにテーブルを初期化するとします。
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.close()
次に、行を次のように接続します。
>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = c.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
12.6.5。 例外
- exception sqlite3.Warning
- 例外のサブクラス。
- exception sqlite3.Error
- このモジュールの他の例外の基本クラス。 これは例外のサブクラスです。
- exception sqlite3.DatabaseError
- データベースに関連するエラーに対して発生した例外。
- exception sqlite3.IntegrityError
- データベースのリレーショナル整合性が影響を受ける場合に発生する例外。 外部キーチェックは失敗します。 DatabaseError のサブクラスです。
- exception sqlite3.ProgrammingError
- プログラミングエラーのために発生した例外。 テーブルが見つからないか、すでに存在している、SQLステートメントの構文エラー、指定されたパラメーターの数が間違っているなど。 DatabaseError のサブクラスです。
- exception sqlite3.OperationalError
- データベースの操作に関連し、必ずしもプログラマーの制御下にあるとは限らないエラーに対して発生した例外。 予期しない切断が発生した、データソース名が見つからない、トランザクションを処理できなかったなど。 DatabaseError のサブクラスです。
- exception sqlite3.NotSupportedError
- データベースでサポートされていないメソッドまたはデータベースAPIが使用された場合に発生する例外。 トランザクションをサポートしていない接続、またはトランザクションがオフになっている接続で rollback()メソッドを呼び出す。 DatabaseError のサブクラスです。
12.6.6。 SQLiteとPythonのタイプ
12.6.6.1。 序章
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タイプ。
12.6.6.2。 アダプターを使用して追加のPythonタイプをSQLiteデータベースに保存する
前に説明したように、SQLiteは限られたタイプのセットのみをネイティブにサポートします。 SQLiteで他のPython型を使用するには、SQLiteでサポートされているsqlite3モジュールの型の1つ(NoneType、int、float、str、bytesのいずれか)にそれらを適応する必要があります。
sqlite3 モジュールを有効にして、カスタムPythonタイプをサポートされているタイプの1つに適合させるには2つの方法があります。
12.6.6.2.1。 オブジェクトを適応させる
クラスを自分で作成する場合、これは良いアプローチです。 次のようなクラスがあるとします。
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])
12.6.6.2.2。 呼び出し可能なアダプターの登録
もう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])
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])
12.6.6.3。 SQLite値をカスタムPythonタイプに変換する
アダプターを作成すると、カスタムPythonタイプをSQLiteに送信できます。 しかし、それを本当に便利にするには、PythonからSQLite、Pythonへのラウンドトリップを機能させる必要があります。
コンバーターを入力してください。
Point
クラスに戻りましょう。 セミコロンで区切られたx座標とy座標を文字列としてSQLiteに保存しました。
まず、文字列をパラメーターとして受け取り、そこからPoint
オブジェクトを作成するコンバーター関数を定義します。
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()
12.6.6.4。 デフォルトのアダプターとコンバーター
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]))
SQLiteに保存されているタイムスタンプの小数部分が6桁より長い場合、その値はタイムスタンプコンバーターによってマイクロ秒の精度に切り捨てられます。
12.6.7。 トランザクションの制御
基盤となる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ステートメントの前にオープントランザクションを暗黙的にコミットするために使用されていました。 これはもはや当てはまりません。
12.6.8。 使用する sqlite3 効率的
12.6.8.1。 ショートカットメソッドの使用
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")
12.6.8.2。 インデックスではなく名前で列にアクセスする
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"]
12.6.8.3。 接続をコンテキストマネージャーとして使用する
接続オブジェクトは、トランザクションを自動的にコミットまたはロールバックするコンテキストマネージャーとして使用できます。 例外が発生した場合、トランザクションはロールバックされます。 それ以外の場合、トランザクションはコミットされます。
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")
12.6.9。 一般的な問題
12.6.9.1。 マルチスレッド
古いバージョンのSQLiteには、スレッド間の接続の共有に問題がありました。 そのため、Pythonモジュールではスレッド間での接続とカーソルの共有が許可されていません。 それでもそうしようとすると、実行時に例外が発生します。
唯一の例外は interrupt()メソッドの呼び出しです。これは、別のスレッドから呼び出す場合にのみ意味があります。
脚注
- 1( 1 、 2 )
- 一部のプラットフォーム(特にMac OS X)にはこの機能なしでコンパイルされたSQLiteライブラリがあるため、sqlite3モジュールはデフォルトでロード可能な拡張機能をサポートして構築されていません。 ロード可能な拡張機能のサポートを取得するには、構成する–enable-loadable-sqlite-extensionsを渡す必要があります。