12.6. sqlite3
--- SQLite データベースに対する DB-API 2.0 インタフェース¶
ソースコード: Lib/sqlite3/
SQLite は、軽量なディスク上のデータベースを提供する C ライブラリです。別のサーバプロセスを用意する必要なく、 SQL クエリー言語の非標準的な一種を使用してデータベースにアクセスできます。一部のアプリケーションは内部データ保存に SQLite を使えます。また、SQLite を使ってアプリケーションのプロトタイプを作り、その後そのコードを PostgreSQL や Oracle のような大規模データベースに移植するということも可能です。
sqlite3 モジュールの著者は Gerhard Häring です。 PEP 249 で記述されている DB-API 2.0 に準拠した SQL インターフェイスを提供します。
このモジュールを使うには、最初にデータベースを表す Connection
オブジェクトを作ります。ここではデータはファイル example.db
に格納されているものとします:
import sqlite3
conn = sqlite3.connect('example.db')
特別な名前である :memory:
を使うと RAM 上にデータベースを作ることもできます。
Connection
があれば、 Cursor
オブジェクトを作りその 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 文を実行した後データを取得する方法は3つありどれを使っても構いません。一つはカーソルをイテレータ (iterator) として扱う、一つはカーソルの fetchone()
メソッドを呼んで一致した内の一行を取得する、もう一つは fetchall()
メソッドを呼んで一致した全ての行のリストとして受け取る、という3つです。
以下の例ではイテレータの形を使います:
>>> 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 のウェブページ -- sqlite3 は「pysqlite」という名の下、外部で開発されています。
- https://www.sqlite.org
- SQLite のウェブページ。ここの文書ではサポートされる SQL 方言の文法と使えるデータ型を説明しています。
- http://www.w3schools.com/sql/
- SQL 学習に効くチュートリアル、リファレンス、実例集。
- PEP 249 - Database API Specification 2.0
- Marc-Andre 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
モジュールは戻り値のカラムの宣言された型を読み取るようになります。意味を持つのは宣言の最初の単語です。すなわち、"integer primary key" においては "integer" が読み取られます。また、 "number(10)" では、 "number" が読み取られます。そして、そのカラムに対して、変換関数の辞書を探してその型に対して登録された関数を使うようにします。
-
sqlite3.
PARSE_COLNAMES
¶ この定数は
connect()
関数の detect_types パラメータとして使われます。この定数を設定すると SQLite のインタフェースは戻り値のそれぞれのカラムの名前を読み取るようになります。文字列の中の [mytype] といった形の部分を探し、'mytype' がそのカラムの名前であると判断します。そして 'mytype' のエントリを変換関数辞書の中から見つけ、見つかった変換関数を値を返す際に用います。
Cursor.description
で見つかるカラム名はその最初の単語だけです。すなわち、もし'as "x [datetime]"'
のようなものを SQL の中で使っていたとすると、読み取るのはカラム名の中の最初の空白までの全てですので、カラム名として使われるのは単純に "x" ということになります。
-
sqlite3.
connect
(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])¶ ファイル database の SQLite データベースへの接続を開きます。
":memory:"
という名前を使うことでディスクの代わりに RAM 上のデータベースへの接続を開くこともできます。データベースが複数の接続からアクセスされている状況で、その内の一つがデータベースに変更を加えたとき、SQLite データベースはそのトランザクションがコミットされるまでロックされます。timeout パラメータで、例外を送出するまで接続がロックが解除されるのをどれだけ待つかを決めます。デフォルトは 5.0 (5秒) です。
isolation_level パラメータについては、
Connection
オブジェクトの、isolation_level
プロパティを参照してください。SQLite はネイティブで TEXT、INTEGER、REAL、BLOB および NULL のみをサポートしています。その他のタイプを使用したい場合はあなた自身で追加しなければなりません。detect_types パラメータおよび、
register_converter()
関数でモジュールレベルで登録できるカスタム 変換関数 を使用することで簡単に追加できます。パラメータ detect_types のデフォルトは 0 (つまりオフ、型検知無し)です。型検知を有効にするためには、
PARSE_DECLTYPES
とPARSE_COLNAMES
の適当な組み合わせをこのパラメータにセットします。By default, check_same_thread is
True
and only the creating thread may use the connection. If setFalse
, the returned connection may be shared across multiple threads. When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.デフォルトでは、
sqlite3
モジュールは connect の呼び出しの際にモジュールのConnection
クラスを使います。しかし、Connection
クラスを継承したクラスを factory パラメータに渡してconnect()
にそのクラスを使わせることもできます。詳しくはこのマニュアルの SQLite と Python の型 節を参考にしてください。
sqlite3
モジュールは SQL 解析のオーバーヘッドを避けるために内部で文キャッシュを使っています。接続に対してキャッシュされる文の数を自分で指定したいならば、 cached_statements パラメータに設定してください。現在の実装ではデフォルトでキャッシュされる SQL 文の数を 100 にしています。uri が真の場合、 database は URI として解釈されます。これにより、オプションを指定することができます。例えば、データベースを読み出し専用モードで使用できるように開くには、次のようにします:
db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)
More information about this feature, including a list of recognized options, can be found in the SQLite URI documentation.
バージョン 3.4 で変更:
uri
パラメータが追加されました。
-
sqlite3.
register_converter
(typename, callable)¶ Registers a callable to convert a bytestring from the database into a custom Python type. The callable will be invoked for all database values that are of the type typename. Confer the parameter detect_types of the
connect()
function for how the type detection works. Note that the case of typename and the name of the type in your query must match!
-
sqlite3.
register_adapter
(type, callable)¶ 自分が使いたい Python の型 type を SQLite がサポートしている型に変換する呼び出し可能オブジェクト (callable) を登録します。その呼び出し可能オブジェクト callable はただ一つの引数に Python の値を受け取り、int, float, str または bytes のいずれかの型の値を返さなければなりません。
-
sqlite3.
complete_statement
(sql)¶ 文字列 sql がセミコロンで終端された一つ以上の完全な 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()
12.6.2. Connection オブジェクト¶
-
class
sqlite3.
Connection
¶ SQLite データベースコネクション。以下の属性やメソッドを持ちます:
-
isolation_level
¶ Get or set the current isolation level.
None
for autocommit mode or one of "DEFERRED", "IMMEDIATE" or "EXCLUSIVE". See section トランザクション制御 for a more detailed explanation.
-
cursor
(factory=Cursor)¶ cursor メソッドはオション引数 factory を 1 つだけ受け付けます。 渡された場合は、
Cursor
またはそのサブクラスのインスタンスを返す呼び出し可能オブジェクトでなければなりません。
-
commit
()¶ このメソッドは現在のトランザクションをコミットします。このメソッドを呼ばないと、前回
commit()
を呼び出してから行ったすべての変更は、他のデータベースコネクションから見ることができません。もし、データベースに書き込んだはずのデータが見えなくて悩んでいる場合は、このメソッドの呼び出しを忘れていないかチェックしてください。
-
close
()¶ このメソッドはデータベースコネクションを閉じます。このメソッドが自動的に
commit()
を呼び出さないことに注意してください。commit()
をせずにコネクションを閉じると、変更が消えてしまいます!
-
execute
(sql[, parameters])¶ This is a nonstandard shortcut that creates a cursor object by calling the
cursor()
method, calls the cursor'sexecute()
method with the parameters given, and returns the cursor.
-
executemany
(sql[, parameters])¶ This is a nonstandard shortcut that creates a cursor object by calling the
cursor()
method, calls the cursor'sexecutemany()
method with the parameters given, and returns the cursor.
-
executescript
(sql_script)¶ This is a nonstandard shortcut that creates a cursor object by calling the
cursor()
method, calls the cursor'sexecutescript()
method with the given sql_script, and returns the cursor.
-
create_function
(name, num_params, func)¶ Creates a user-defined function that you can later use from within SQL statements under the function name name. num_params is the number of parameters the function accepts (if num_params is -1, the function may take any number of arguments), and func is a Python callable that is called as the SQL function.
The function can return any of the types supported by SQLite: bytes, str, int, float and
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)¶ ユーザ定義の集計関数を作成します。
The aggregate class must implement a
step
method, which accepts the number of parameters num_params (if num_params is -1, the function may take any number of arguments), and afinalize
method which will return the final result of the aggregate.The
finalize
method can return any of the types supported by SQLite: bytes, str, int, float andNone
.例:
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 で指定される照合順序を作成します。呼び出し可能オブジェクトには二つの文字列が渡されます。一つめのものが二つめのものより低く順序付けられるならば -1 を返し、等しければ 0 を返し、一つめのものが二つめのものより高く順序付けられるならば 1 を返すようにしなければなりません。この関数はソート(SQL での ORDER BY)をコントロールするもので、比較を行なうことは他の SQL 操作には影響を与えないことに注意しましょう。
また、呼び出し可能オブジェクトに渡される引数は Python のバイト文字列として渡されますが、それは通常 UTF-8 で符号化されたものになります。
以下の例は「間違った方法で」ソートする自作の照合順序です:
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()
照合順序を取り除くには callable に
None
を指定してcreate_collation
を呼び出します:con.create_collation("reverse", None)
-
interrupt
()¶ このメソッドを別スレッドから呼び出して接続上で現在実行中であろうクエリを中断させられます。クエリが中断されると呼び出し元は例外を受け取ります。
このルーチンはコールバックを登録します。コールバックはデータベースのテーブルのカラムにアクセスしようとするたびに呼び出されます。コールバックはアクセスが許可されるならば
SQLITE_OK
を、SQL 文全体がエラーとともに中断されるべきならばSQLITE_DENY
を、カラムが NULL 値として扱われるべきならSQLITE_IGNORE
を返さなければなりません。これらの定数はsqlite3
モジュールに用意されています。コールバックの第一引数はどの種類の操作が許可されるかを決めます。第二第三引数には第一引数に依存して本当に使われる引数か
None
かが渡されます。第四引数はもし適用されるならばデータベースの名前("main", "temp", etc.)です。第五引数はアクセスを試みる要因となった最も内側のトリガまたはビューの名前、またはアクセスの試みが入力された SQL コードに直接起因するものならばNone
です。第一引数に与えることができる値や、その第一引数によって決まる第二第三引数の意味については、SQLite の文書を参考にしてください。必要な定数は全て
sqlite3
モジュールに用意されています。
-
set_progress_handler
(handler, n)¶ このメソッドはコールバックを登録します。コールバックは SQLite 仮想マシン上の n 個の命令を実行するごとに呼び出されます。これは、GUI 更新などのために、長時間かかる処理中に SQLite からの呼び出しが欲しい場合に便利です。
以前登録した progress handler をクリアしたい場合は、このメソッドを、 handler 引数に
None
を渡して呼び出してください。
-
set_trace_callback
(trace_callback)¶ 各 SQL 文が SQLite バックエンドによって実際に実行されるたびに呼び出される trace_callback を登録します。
コールバックに渡される唯一の引数は、実行されている SQL 文 (の文字列)です。コールバックの戻り値は無視されます。バックエンドは
Cursor.execute()
メソッドに渡された SQL 文だけを実行するわけではないことに注意してください。他のソースには、 Python モジュールのトランザクション管理や、現在のデータベースに定義されたトリガーの実行が含まれます。trace_callback として
None
を渡すと、トレースコールバックを無効にできます。バージョン 3.3 で追加.
-
enable_load_extension
(enabled)¶ このメソッドは SQLite エンジンが共有ライブラリから SQLite 拡張を読み込むのを許可したり、禁止したりします。SQLite 拡張は新しい関数や集計関数や仮想テーブルの実装を定義できます。1つの有名な拡張は SQLite によって頒布されている全テキスト検索拡張です。
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 laoding 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()
で拡張の読み込みを許可しておかなくてはなりません。SQLite 拡張はデフォルトで無効にされています。[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 の行に基づいた解法よりも優れているかもしれません。
-
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 testフォーマットでダンプするためのイテレータを返します。 メモリ内のデータベースの内容を、後で復元するために保存する場合に便利です。この関数には、 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 文はパラメータ化できます(すなわち SQL リテラルの代わりの場所確保文字 (placeholder) を入れておけます)。
sqlite3
モジュールは2種類の場所確保記法をサポートします。一つは疑問符(qmark スタイル)、もう一つは名前(named スタイル)です。両方のスタイルの例です:
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()
will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise aWarning
. Useexecutescript()
if you want to execute multiple SQL statements with one call.
-
executemany
(sql, seq_of_parameters)¶ Executes an SQL command against all parameter sequences or mappings found in the sequence seq_of_parameters. The
sqlite3
module also allows using an iterator yielding parameters instead of a sequence.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())
もう少し短いジェネレータ (generator) を使った例です:
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 can be an instance of
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 ); """)
-
fetchmany
(size=cursor.arraysize)¶ クエリ結果から次の幾つかの row をフェッチして、リストを返します。これ以上データがない場合は空のリストを返します。
一回の呼び出しで返される row の数は、size 引数で指定できます。この引数が与えられない場合、cursor の arraysize 属性が利用されます。このメソッドは可能な限り指定された size の数の row を fetch しようとするべきです。もし、指定された数の row が利用可能でない場合、それより少ない数の row が返されます。
size 引数とパフォーマンスの関係についての注意です。パフォーマンスを最適化するためには、大抵、 arraysize 属性を利用するのがベストです。 size 引数を利用したのであれば、次の
fetchmany()
の呼び出しでも同じ数を利用するのがベストです。
-
fetchall
()¶ 全ての(残りの)クエリ結果の row をフェッチして、リストを返します。cursor の arraysize 属性がこの操作のパフォーマンスに影響することに気をつけてください。これ以上の row がない場合は、空のリストが返されます。
-
close
()¶ Close the cursor now (rather than whenever
__del__
is called).The cursor will be unusable from this point forward; a
ProgrammingError
exception will be raised if any operation is attempted with the cursor.
-
rowcount
¶ 一応
sqlite3
モジュールのCursor
クラスはこの属性を実装していますが、データベースエンジン自身の「影響を受けた行」/「選択された行」の決定方法は少し風変わりです。executemany()
では、変更数がrowcount
に合計されます。Python DB API 仕様で要求されるように、
rowcount
属性は「カーソルに対してexecuteXX()
が行なわれていないか、最後の操作の rowcount がインターフェースによって決定できなかった場合は -1 」です。これにはSELECT
文も含まれます。すべての列を取得するまでクエリによって生じた列の数を決定できないからです。SQLite のバージョン 3.6.5 以前は、条件なしで
DELETE FROM table
を実行するとrowcount
が 0 にセットされます。
-
lastrowid
¶ This read-only attribute provides the rowid of the last modified row. It is only set if you issued an
INSERT
statement using theexecute()
method. For operations other thanINSERT
or whenexecutemany()
is called,lastrowid
is set toNone
.
-
arraysize
¶ Read/write attribute that controls the number of rows returned by
fetchmany()
. The default value is 1 which means a single row would be fetched per call.
-
description
¶ この読み出し専用の属性は、最後のクエリの結果のカラム名を提供します。 Python DB API との互換性を維持するために、各カラムに対して 7つのタプルを返しますが、タプルの後ろ6つの要素は全て
None
です。この属性は
SELECT
文にマッチする row が1つもなかった場合でもセットされます。
-
connection
¶ この読み出し専用の属性は、
Cursor
オブジェクトが使用する SQLite データベースのConnection
を提供します。con.cursor()
を呼び出すことにより作成されるCursor
オブジェクトは、 con を参照するconnection
属性を持ちます:>>> con = sqlite3.connect(":memory:") >>> cur = con.cursor() >>> cur.connection == con True
-
12.6.4. Row オブジェクト¶
-
class
sqlite3.
Row
¶ Row
インスタンスは、Connection
オブジェクトのrow_factory
として高度に最適化されています。タプルによく似た機能を持つ row を作成します。カラム名とインデックスによる要素へのアクセス, イテレーション, repr(), 同値テスト,
len()
をサポートしています。もし、2つの
Row
オブジェクトが完全に同じカラムと値を持っていた場合、それらは同値になります。-
keys
()¶ このメソッドはカラム名のリストを返します。クエリ直後から、これは
Cursor.description
の各タプルの最初のメンバになります。
バージョン 3.5 で変更: スライスがサポートされました。
-
Rowの例のために、まずサンプルのテーブルを初期化します:
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()
そして、 Row
を使ってみます:
>>> 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.
DatabaseError
¶ Exception raised for errors that are related to the database.
-
exception
sqlite3.
IntegrityError
¶ Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It is a subclass of
DatabaseError
.
-
exception
sqlite3.
ProgrammingError
¶ Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. It is a subclass of
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
モジュールの型システムは二つの方法で拡張できます。一つはオブジェクト適合(adaptation)を通じて追加された Python の型を SQLite に格納することです。もう一つは変換関数(converter)を通じて sqlite3
モジュールに SQLite の型を違った Python の型に変換させることです。
12.6.6.2. 追加された Python の型を SQLite データベースに格納するために適合関数を使う¶
既に述べたように、SQLite が最初からサポートする型は限られたものだけです。それ以外の Python の型を SQLite で使うには、その型を sqlite3
モジュールがサポートしている型の一つに 適合 させなくてはなりません。サポートしている型というのは、NoneType, int, float, str, bytes です。
sqlite3
モジュールで望みの Python の型をサポートされている型の一つに適合させる方法は二つあります。
12.6.6.2.1. オブジェクト自身で適合するようにする¶
自分でクラスを書いているならばこの方法が良いでしょう。次のようなクラスがあるとします:
class Point:
def __init__(self, x, y):
self.x, self.y = x, y
さてこの点を SQLite の一つのカラムに収めたいと考えたとしましょう。最初にしなければならないのはサポートされている型の中から点を表現するのに使えるものを選ぶことです。ここでは単純に文字列を使うことにして、座標を区切るのにはセミコロンを使いましょう。次に必要なのはクラスに変換された値を返す __conform__(self, protocol)
メソッドを追加することです。引数 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. 適合関数を登録する¶
もう一つの可能性は型を文字列表現に変換する関数を作り 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
に対するデフォルト適合関数があります。いま 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 へという往還(roundtrip)の変換ができる必要があります。
そこで変換関数(converter)です。
Point
クラスの例に戻りましょう。x, y 座標をセミコロンで区切った文字列として SQLite に格納したのでした。
まず、文字列を引数として取り Point
オブジェクトをそれから構築する変換関数を定義します。
注釈
変換関数は SQLite に送り込んだデータ型に関係なく 常に bytes
オブジェクトを渡されます。
def convert_point(s):
x, y = map(float, s.split(b";"))
return Point(x, y)
次に sqlite3
モジュールにデータベースから取得したものが本当に点であることを教えなければなりません。二つの方法があります:
- 宣言された型を通じて暗黙的に
- カラム名を通じて明示的に
どちらの方法も モジュールの関数と定数 節の中で説明されています。それぞれ 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 の date/time 関数とも互換性があります。
以下の例でこのことを確かめます。
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. トランザクション制御¶
By default, the sqlite3
module opens transactions implicitly before a
Data Modification Language (DML) statement (i.e.
INSERT
/UPDATE
/DELETE
/REPLACE
), and commits transactions
implicitly before a non-DML, non-query statement (i. e.
anything other than SELECT
or the aforementioned).
So if you are within a transaction and issue a command like CREATE TABLE
...
, VACUUM
, PRAGMA
, the sqlite3
module will commit implicitly
before executing that command. There are two reasons for doing that. The first
is that some of these commands don't work within transactions. The other reason
is that sqlite3 needs to keep track of the transaction state (if a transaction
is active or not). The current transaction state is exposed through the
Connection.in_transaction
attribute of the connection object.
You can control which kind of BEGIN
statements sqlite3 implicitly executes
(or none at all) via the isolation_level parameter to the connect()
call, or via the isolation_level
property of connections.
If you want autocommit mode, then set isolation_level
to None
.
Otherwise leave it at its default, which will result in a plain "BEGIN" statement, or set it to one of SQLite's supported isolation levels: "DEFERRED", "IMMEDIATE" or "EXCLUSIVE".
12.6.8. sqlite3
の効率的な使い方¶
12.6.8.1. ショートカットメソッドを使う¶
Connection
オブジェクトの非標準的なメソッド execute()
, executemany()
, executescript()
を使うことで、 (しばしば余計な) Cursor
オブジェクトをわざわざ作り出さずに済むので、コードをより簡潔に書くことができます。 Cursor
オブジェクトは暗黙裡に生成されショートカットメソッドの戻り値として受け取ることができます。この方法を使えば、 SELECT
文を実行してその結果について反復することが、 Connection
オブジェクトに対する呼び出し一つで行なえます。
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
モジュールの有用な機能の一つに、行生成関数として使われるための 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. コネクションをコンテキストマネージャーとして利用する¶
Connection オブジェクトはコンテキストマネージャーとして利用して、トランザクションを自動的にコミットしたりロールバックすることができます。例外が発生したときにトランザクションはロールバックされ、それ以外の場合、トランザクションはコミットされます:
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) いくつかのプラットフォームでこの機能なしでコンパイルされる SQLite ライブラリがあるので (特にMac OS X)、sqlite3 モジュールはデフォルトで SQLite 拡張サポートなしで構築されます。SQLite 拡張サポートを有効にするには、configure に --enable-loadable-sqlite-extensions を渡す必要があります。 |