### ナビゲーション

- 索引
- モジュール |
- 次へ |
- 前へ |
- Python 2.7ja1 documentation »
- Python 標準ライブラリ »
- 11. データの永続化 »

# 11.13. `sqlite3` — SQLite データベースに対する DB-API 2.0 インタフェース

バージョン 2.5 で追加.

SQLite は、別にサーバプロセスは必要とせずデータベースのアクセスに SQL 問い合わせ言語の非標準的な一種を使える軽量なディスク上のデータベースを提供する C ライブラリです。ある種のアプリケーションは内部データ保存に SQLite を使えます。また、SQLite を使ってアプリケーションのプロトタイプを作りその後そのコードを PostgreSQL や Oracle のような大規模データベースに移植するということも可能です。

sqlite3 は Gerhard Häring によって書かれ、 PEP 249 に記述された DB-API 2.0 仕様に準拠した SQL インタフェースを提供するものです。

このモジュールを使うには、最初にデータベースを表す `Connection` オブジェクトを作ります。ここではデータはファイル `/tmp/example` に格納されているものとします。

    conn = sqlite3.connect('/tmp/example')

特別な名前である `: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 cursor if we are done with it
    c.close()

たいてい、SQL 操作は Python 変数の値を使う必要があります。この時、クエリーを Python の文字列操作を使って構築することは、安全とは言えないので、すべきではありません。そのようなことをするとプログラムが SQL インジェクション攻撃に対し脆弱になりかねません。

代わりに、DB-API のパラメータ割り当てを使います。 `?` を変数の値を使いたいところに埋めておきます。その上で、値のタプルをカーソルの `execute()` メソッドの第2引数として引き渡します。(他のデータベースモジュールでは変数の場所を示すのに `%s` や `:1` などの異なった表記を用いることがあります。) 例を示します。

    # Never do this -- insecure!
    symbol = 'IBM'
    c.execute("... where symbol = '%s'" % symbol)

    # Do this instead
    t = (symbol,)
    c.execute('select * from stocks where symbol=?', t)

    # Larger example
    for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
              ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
              ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
             ]:
        c.execute('insert into stocks values (?,?,?,?,?)', t)

SELECT 文を実行した後データを取得する方法は3つありどれを使っても構いません。一つはカーソルをイテレータ(iterator)として扱う、一つはカーソルの `fetchone()` メソッドを呼んで一致した内の一行を取得する、もう一つは `fetchall()` メソッドを呼んで一致した全ての行のリストとして受け取る、という3つです。

以下の例ではイテレータの形を使います。

    >>> c = conn.cursor()
    >>> c.execute('select * from stocks order by price')
    >>> for row in c:
    ...    print row
    ...
    (u'2006-01-05', u'BUY', u'RHAT', 100, 35.14)
    (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
    (u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
    (u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
    >>>

参考

http://code.google.com/p/pysqlite/
  pysqlite のウェブページ – sqlite3 は「pysqlite」という名の下、外部で開発されています。
http://www.sqlite.org
  SQLite のウェブページ。ここの文書ではサポートされる SQL 方言の文法と使えるデータ型を説明しています
PEP 249 - Database API Specification 2.0
  Marc-Andre Lemburg により書かれた PEP
http://www.python.jp/doc/contrib/peps/pep-0249.txt
  訳注: PEP 249 の日本語訳があります

## 11.13.1. モジュールの関数と定数

`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])

  ファイル database の SQLite データベースへの接続を開きます。 `":memory:"` という名前を使うことでディスクの代わりに RAM 上のデータベースへの接続を開くこともできます。

  データベースが複数の接続からアクセスされている状況で、その内の一つがデータベースに変更を加えたとき、SQLite データベースはそのトランザクションがコミットされるまでロックされます。 timeout パラメータで、例外を送出するまで接続がロックが解除されるのをどれだけ待つかを決めます。デフォルトは 5.0 (5秒) です。

  isolation_level パラメータについては、 `Connection` オブジェクトの、 `Connection.isolation_level` 属性を参照してください。

  SQLite がネイティブにサポートするのは TEXT, INTEGER, FLOAT, BLOB および NULL 型だけです。もし他の型を使いたければ、その型のためのサポートを自分で追加しなければなりません。 detect_types パラメータを、モジュールレベルの `register_converter()` 関数で登録した自作の 変換関数 と一緒に使えば、簡単にできます。

  パラメータ detect_types のデフォルトは 0 (つまりオフ、型検知無し)です。型検知を有効にするためには、 `PARSE_DECLTYPES` と `PARSE_COLNAMES` の適当な組み合わせをこのパラメータにセットします。

  デフォルトでは、 `sqlite3` モジュールは connect の呼び出しの際にモジュールの `Connection` クラスを使います。しかし、 `Connection` クラスを継承したクラスを factory パラメータに渡して `connect()` にそのクラスを使わせることもできます。詳しくはこのマニュアルの SQLite と Python の型 節を参考にしてください。

  `sqlite3` モジュールは SQL 解析のオーバーヘッドを避けるために内部で文キャッシュを使っています。接続に対してキャッシュされる文の数を自分で指定したいならば、 cached_statements パラメータに設定してください。現在の実装ではデフォルトでキャッシュされる SQL 文の数を 100 にしています。

`sqlite3.``register_converter`(typename, callable)

  データベースから得られるバイト列を希望する Python の型に変換する呼び出し可能オブジェクト (callable) を登録します。その呼び出し可能オブジェクトは型が typename である全てのデータベース上の値に対して呼び出されます。型検知がどのように働くかについては `connect()` 関数の detect_types パラメータの説明も参照してください。注意が必要なのは typename はクエリの中の型名と大文字小文字も一致しなければならないということです。

`sqlite3.``register_adapter`(type, callable)

  自分が使いたい Python の型 type を SQLite がサポートしている型に変換する呼び出し可能オブジェクト (callable) を登録します。その呼び出し可能オブジェクト callable はただ一つの引数に Python の値を受け取り、int, long, float, (UTF-8 でエンコードされた) str, unicode または buffer のいずれかの型の値を返さなければなりません

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

      con.close()

`sqlite3.``enable_callback_tracebacks`(flag)

  デフォルトでは、ユーザ定義の関数、集計関数、変換関数、認可コールバックなどはトレースバックを出力しません。デバッグの際にはこの関数を flag に `True` を指定して呼び出します。そうした後は先に述べたような関数のトレースバックが `sys.stderr` に出力されます。元に戻すには `False` を使います。

## 11.13.2. Connection オブジェクト

class `sqlite3.``Connection`

  SQLite データベースコネクション。以下の属性やメソッドを持ちます。

`Connection.``isolation_level`

  現在の分離レベルを取得または設定します。 `None` で自動コミットモードまたは “DEFERRED”, “IMMEDIATE”, “EXLUSIVE” のどれかです。より詳しい説明は トランザクション制御 節を参照してください。

`Connection.``cursor`([cursorClass])

  cursor メソッドはオプション引数 cursorClass を受け付けます。これを指定するならば、指定されたクラスは `sqlite3.Cursor` を継承したカーソルクラスでなければなりません。

`Connection.``commit`()

  このメソッドは現在のトランザクションをコミットします。このメソッドを呼ばないと、前回 `commit()` を呼び出してから行ったすべての変更は、他のデータベースコネクションから見ることができません。もし、データベースに書き込んだはずのデータが見えなくて悩んでいる場合は、このメソッドの呼び出しを忘れていないかチェックしてください。

`Connection.``rollback`()

  このメソッドは最後に行った `commit()` 後の全ての変更をロールバックします。

`Connection.``close`()

  このメソッドはデータベースコネクションを閉じます。このメソッドが自動的に `commit()` を呼び出さないことに注意してください。 `commit()` をせずにコネクションを閉じると、変更が消えてしまいます。

`Connection.``execute`(sql[, parameters])

  このメソッドは非標準のショートカットで、cursor メソッドを呼び出して中間的なカーソルオブジェクトを作り、そのカーソルの `execute` メソッドを与えられたパラメータと共に呼び出します。

`Connection.``executemany`(sql[, parameters])

  このメソッドは非標準のショートカットで、cursor メソッドを呼び出して中間的なカーソルオブジェクトを作り、そのカーソルの `executemany` メソッドを与えられたパラメータと共に呼び出します。

`Connection.``executescript`(sql_script)

  このメソッドは非標準のショートカットで、cursor メソッドを呼び出して中間的なカーソルオブジェクトを作り、そのカーソルの `executescript` メソッドを与えられたパラメータと共に呼び出します。

`Connection.``create_function`(name, num_params, func)

  後から SQL 文中で name という名前の関数として使えるユーザ定義関数を作成します。 num_params は関数が受け付ける引数の数、 func は SQL 関数として使われる Python の呼び出し可能オブジェクトです。

  関数は SQLite でサポートされている任意の型を返すことができます。具体的には unicode, str, int, long, float, buffer および None です。

  例:

      import sqlite3
      import md5

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

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

`Connection.``create_aggregate`(name, num_params, aggregate_class)

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

  集計クラスにはパラメータ num_params で指定される個数の引数を取る `step` メソッドおよび最終的な集計結果を返す `finalize` メソッドを実装しなければなりません。

  `finalize` メソッドは SQLite でサポートされている任意の型を返すことができます。具体的には unicode, str, int, long, float, buffer および 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]

`Connection.``create_collation`(name, callable)

  name と callable で指定される照合順序を作成します。呼び出し可能オブジェクトには二つの文字列が渡されます。一つめのものが二つめのものより低く順序付けられるならば -1 を返し、等しければ 0 を返し、一つめのものが二つめのものより高く順序付けられるならば 1 を返すようにしなければなりません。この関数はソート(SQL での ORDER BY)をコントロールするもので、比較を行なうことは他の SQL 操作には影響を与えないことに注意しましょう。

  また、呼び出し可能オブジェクトに渡される引数は Python のバイト文字列として渡されますが、それは通常 UTF-8 で符号化されたものになります。

  以下の例は「間違った方法で」ソートする自作の照合順序です:

      import sqlite3

      def collate_reverse(string1, string2):
          return -cmp(string1, string2)

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

  照合順序を取り除くには `create_collation` を callable として None を渡して呼び出します:

      con.create_collation("reverse", None)

`Connection.``interrupt`()

  このメソッドを別スレッドから呼び出して接続上で現在実行中であろうクエリを中断させられます。クエリが中断されると呼び出し元は例外を受け取ります。

`Connection.``set_authorizer`(authorizer_callback)

  このルーチンはコールバックを登録します。コールバックはデータベースのテーブルのカラムにアクセスしようとするたびに呼び出されます。コールバックはアクセスが許可されるならば `SQLITE_OK` を、SQL 文全体がエラーとともに中断されるべきならば `SQLITE_DENY` を、カラムが NULL 値として扱われるべきなら `SQLITE_IGNORE` を返さなければなりません。これらの定数は `sqlite3` モジュールに用意されています。

  コールバックの第一引数はどの種類の操作が許可されるかを決めます。第二第三引数には第一引数に依存して本当に使われる引数か `None` かが渡されます。第四引数はもし適用されるならばデータベースの名前(“main”, “temp”, etc.)です。第五引数はアクセスを試みる要因となった最も内側のトリガまたはビューの名前、またはアクセスの試みが入力された SQL コードに直接起因するものならば `None` です。

  第一引数に与えることができる値や、その第一引数によって決まる第二第三引数の意味については、SQLite の文書を参考にしてください。必要な定数は全て `sqlite3` モジュールに用意されています。

`Connection.``set_progress_handler`(handler, n)

  バージョン 2.6 で追加.

  このメソッドはコールバックを登録します。コールバックは SQLite 仮想マシン上の n 個の命令を実行するごとに呼び出されます。これは、GUI 更新などのために、長時間かかる処理中に SQLite からの呼び出しが欲しい場合に便利です。

  以前登録した progress handler をクリアしたい場合は、このメソッドを、 handler 引数に `None` を渡して呼び出してください。

`Connection.``enable_load_extension`(enabled)

  バージョン 2.7 で追加.

  このメソッドは SQLite エンジンが共有ライブラリから SQLite 拡張を読み込むのを許可したり、禁止したりします。 SQLite 拡張は新しい関数や集計関数や仮想テーブルの実装を定義できます。 1つの有名な拡張は SQLite によって頒布されている全テキスト検索拡張です。

      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

`Connection.``load_extension`(path)

  バージョン 2.7 で追加.

  このメソッドは共有ライブラリから SQLite 拡張を読み込みます。このメソッドを使う前に `enable_load_extension()` で拡張の読み込みを許可しておかなくてはなりません。

`Connection.``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"]

  タプルを返すのでは物足りず、名前に基づいたカラムへのアクセスが行ないたい場合は、高度に最適化された `sqlite3.Row` 型を `row_factory` にセットすることを考えてはいかがでしょうか。 `Row` クラスでは添字でも大文字小文字を無視した名前でもカラムにアクセスでき、しかもほとんどメモリーを浪費しません。おそらく、辞書を使うような独自実装のアプローチよりも、もしかすると db の行に基づいた解法よりも良いものかもしれません。

`Connection.``text_factory`

  この属性を使って `TEXT` データ型をどのオブジェクトで返すかを制御できます。デフォルトではこの属性は `unicode` に設定されており、 `sqlite3` モジュールは `TEXT` を Unicode オブジェクトで返します。もしバイト列で返したいならば、 `str` に設定してください。

  効率の問題を考えて、非ASCIIデータに限って Unicode オブジェクトを返し、その他の場合にはバイト列を返す方法もあります。これを有効にしたければ、この属性を `sqlite3.OptimizedUnicode` に設定してください。

  バイト列を受け取って望みの型のオブジェクトを返すような呼び出し可能オブジェクトを何でも設定して構いません。

  以下の説明用のコード例を参照してください:

      import sqlite3

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

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

      AUSTRIA = u"\xd6sterreich"

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

      # but we can make pysqlite always return bytestrings ...
      con.text_factory = str
      cur.execute("select ?", (AUSTRIA,))
      row = cur.fetchone()
      assert type(row[0]) == str
      # 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 will ignore Unicode characters that cannot be
      # decoded from UTF-8
      con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
      cur.execute("select ?", ("this is latin1 and would normally create errors" + u"\xe4\xf6\xfc".encode("latin1"),))
      row = cur.fetchone()
      assert type(row[0]) == unicode

      # pysqlite offers a builtin optimized text_factory that will return bytestring
      # objects, if the data is in ASCII only, and otherwise return unicode objects
      con.text_factory = sqlite3.OptimizedUnicode
      cur.execute("select ?", (AUSTRIA,))
      row = cur.fetchone()
      assert type(row[0]) == unicode

      cur.execute("select ?", ("Germany",))
      row = cur.fetchone()
      assert type(row[0]) == str

`Connection.``total_changes`

  データベース接続が開始されて以来の行の変更・挿入・削除がなされた行の総数を返します。

`Connection.``iterdump`

  データベースをSQL testフォーマットでダンプするためのイテレータを返します。 in-memory データベースの内容を、後でリストアするための保存する場合に便利です。この関数は sqlite3 シェルの中の `.dump` コマンドと同じ機能を持っています。

  バージョン 2.6 で追加.

  例:

      # existing_db.db ファイルを dump.sql ファイルにダンプする
      import sqlite3, os

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

## 11.13.3. カーソルオブジェクト

class `sqlite3.``Cursor`

  `Cursor` インスタンスは以下の属性やメソッドを持ちます。

`Cursor.``execute`(sql[, parameters])

  SQL 文を実行します。SQL 文はパラメータ化できます(すなわち SQL リテラルの代わりの場所確保文字 (placeholder) を入れておけます)。 `sqlite3` モジュールは2種類の場所確保記法をサポートします。一つは疑問符(qmark スタイル)、もう一つは名前(named スタイル)です。

  まず最初の例は qmark スタイルのパラメータを使った書き方を示します:

      import sqlite3

      con = sqlite3.connect("mydb")

      cur = con.cursor()

      who = "Yeltsin"
      age = 72

      cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))
      print cur.fetchone()

  次の例は named スタイルの使い方です:

      import sqlite3

      con = sqlite3.connect("mydb")

      cur = con.cursor()

      who = "Yeltsin"
      age = 72

      cur.execute("select name_last, age from people where name_last=:who and age=:age",
          {"who": who, "age": age})
      print cur.fetchone()

  `execute()` は一つの SQL 文しか実行しません。二つ以上の文を実行しようとすると、Warning を発生させます。複数の SQL 文を一つの呼び出しで実行したい場合は `executescript()` を使ってください。

`Cursor.``executemany`(sql, seq_of_parameters)

  SQL 文 sql を seq_of_parameters シーケンス(またはマッピング)に含まれる全てのパラメータに対して実行します。 `sqlite3` モジュールでは、シーケンスの代わりにパラメータの組を作り出すイテレータ使うことが許されています。

      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

      def char_generator():
          import string
          for c in string.letters[:26]:
              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()

`Cursor.``executescript`(sql_script)

  これは非標準の便宜メソッドで、一度に複数の SQL 文を実行することができます。メソッドは最初に `COMMIT` 文を発行し、次いで引数として渡された SQLスクリプトを実行します。

  sql_script はバイト文字列または Unicode 文字列です。

  例:

      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
          );
          """)

`Cursor.``fetchone`()

  クエリ結果から次の row をフェッチして、1つのシーケンスを返します。これ以上データがない場合は `None` を返します。

`Cursor.``fetchmany`([size=cursor.arraysize])

  クエリ結果から次の幾つかの row をフェッチして、リストを返します。これ以上データがない場合は空のリストを返します。

  一回の呼び出しで返される row の数は、 size 引数で指定できます。この引数が与えられない場合、 cursor の arraysize 属性が利用されます。このメソッドは可能な限り指定された size の数の row を fetch しようとするべきです。もし、指定された数の row が利用可能でない場合、それより少ない数の row が返されます。

  size 引数とパフォーマンスの関係についての注意です。パフォーマンスを最適化するためには、大抵、 arraysize 属性を利用するのがベストです。 size 引数を利用したのであれば、次の `fetchmany()` の呼び出しでも同じ数を利用するのがベストです。

`Cursor.``fetchall`()

  全ての(残りの)クエリ結果の row をフェッチして、リストを返します。 cursor の arraysize 属性がこの操作のパフォーマンスに影響することに気をつけてください。これ以上の row がない場合は、空のリストが返されます。

`Cursor.``rowcount`

  一応 `sqlite3` モジュールの `Cursor` クラスはこの属性を実装していますが、データベースエンジン自身の「影響を受けた行」/「選択された行」の決定方法は少し風変わりです。

  `DELETE` 文では、条件を付けずに `DELETE FROM table` とすると SQLite は `rowcount` を 0 と報告します。

  `executemany()` では、変更数が `rowcount` に合計されます。

  Python DB API 仕様で求められているように、 `rowcount` 属性は「現在のカーソルがまだ `executeXXX()` を実行していない場合や、データベースインタフェースから最後に行った操作の結果行数を決定できない場合には、この属性は -1 となります。」

  `SELECT` 文でも、全ての行を取得し終えるまで全部で何行になったか決められないので `rowcount` はいつでも -1 です。

`Cursor.``lastrowid`

  この読み込み専用の属性は、最後に変更した row の rowid を提供します。この属性は、 `execute()` メソッドを利用して `INSERT` 文を実行したときのみ設定されます。 `INSERT` 以外の操作や、 `executemany()` メソッドを利用した場合は、 `lastrowid` は `None` に設定されます。

`Cursor.``description`

  この読み込み専用の属性は、最後のクエリの結果のカラム名を提供します。 Python DB API との互換性を維持するために、各カラムに対して 7つのタプルを返しますが、タプルの後ろ6つの要素は全て `None` です。

  この属性は `SELECT` 文にマッチする row が1つもなかった場合でもセットされます。

## 11.13.4. Row オブジェクト

class `sqlite3.``Row`

  `Row` インスタンスは、 `Connection` オブジェクトの `row_factory` として高度に最適化されています。タプルによく似た機能を持つ row を作成します。

  カラム名とインデックスによる要素へのアクセス, イテレーション, repr(), 同値テスト, `len()` をサポートしています。

  もし、2つの `Row` オブジェクトが完全に同じカラムと値を持っていた場合、それらは同値になります。

  バージョン 2.6 で変更: .. Added iteration and equality (hashability).

  イテレーションと同値性、ハッシュ可能

  `keys`()

    このメソッドはカラム名のタプルを返します。クエリ直後から、これは `Cursor.description` の各タプルの最初のメンバになります。

    バージョン 2.6 で追加.

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)
    <type 'sqlite3.Row'>
    >>> r
    (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
    >>> len(r)
    5
    >>> r[2]
    u'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

## 11.13.5. SQLite と Python の型

### 11.13.5.1. 入門編

SQLite が最初からサポートしているのは次の型です。

> `NULL`, `INTEGER`, `REAL`, `TEXT`, `BLOB`

したがって、次の Python の型は問題なく SQLite に送り込めます:

Python の型 | SQLite の型
--- | ---
`None` | `NULL`
`int` | `INTEGER`
`long` | `INTEGER`
`float` | `REAL`
`str` (UTF8-encoded) | `TEXT`
`unicode` | `TEXT`
`buffer` | `BLOB`

SQLite の型から Python の型へのデフォルトでの変換は以下の通りです:

SQLite の型 | Python の型
--- | ---
`NULL` | `None`
`INTEGER` | `int` または `long`, (サイズによる)
`REAL` | `float`
`TEXT` | `text_factory` に依存する。デフォルトでは `unicode`
`BLOB` | `buffer`

`sqlite3` モジュールの型システムは二つの方法で拡張できます。一つはオブジェクト適合(adaptation)を通じて追加された Python の型を SQLite に格納することです。もう一つは変換関数(converter)を通じて `sqlite3` モジュールに SQLite の型を違った Python の型に変換させることです。

### 11.13.5.2. 追加された Python の型を SQLite データベースに格納するために適合関数を使う

既に述べたように、SQLite が最初からサポートする型は限られたものだけです。それ以外の Python の型を SQLite で使うには、その型を `sqlite3` モジュールがサポートしている型の一つに 適合 させなくてはなりません。サポートしている型というのは、NoneType, int, long, float, str, unicode, buffer です。

`sqlite3` モジュールは PEP 246 に述べられているような Python オブジェクト適合を用います。使われるプロトコルは `PrepareProtocol` です。

`sqlite3` モジュールで望みの Python の型をサポートされている型の一つに適合させる方法は二つあります。

#### 11.13.5.2.1. オブジェクト自身で適合するようにする

自分でクラスを書いているならばこの方法が良いでしょう。次のようなクラスがあるとします:

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

さてこの点を SQLite の一つのカラムに収めたいと考えたとしましょう。最初にしなければならないのはサポートされている型の中から点を表現するのに使えるものを選ぶことです。ここでは単純に文字列を使うことにして、座標を区切るのにはセミコロンを使いましょう。次に必要なのはクラスに変換された値を返す `__conform__(self, protocol)` メソッドを追加することです。引数 protocol は `PrepareProtocol` になります。

    import sqlite3

    class Point(object):
        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]

#### 11.13.5.2.2. 適合関数を登録する

もう一つの可能性は型を文字列表現に変換する関数を作り `register_adapter()` でその関数を登録することです。

ノート

適合させる型/クラスは新スタイルクラス(new-style class)でなければなりません。すなわち、 `object` を基底クラスの一つとしていなければなりません。

    import sqlite3

    class Point(object):
        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, 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]

### 11.13.5.3. SQLite の値を好きな Python 型に変換する

適合関数を書くことで好きな Python 型を SQLite に送り込めるようになりました。しかし、本当に使い物になるようにするには Python から SQLite さらに Python へという往還(roundtrip)の変換ができる必要があります。

そこで変換関数(converter)です。

`Point` クラスの例に戻りましょう。x, y 座標をセミコロンで区切った文字列として SQLite に格納したのでした。

まず、文字列を引数として取り `Point` オブジェクトをそれから構築する変換関数を定義します。

ノート

変換関数は SQLite に送り込んだデータ型に関係なく 常に 文字列を渡されます。

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

次に `sqlite3` モジュールにデータベースから取得したものが本当に点であることを教えなければなりません。二つの方法があります:

- 宣言された型を通じて暗黙的に
- カラム名を通じて明示的に

どちらの方法も モジュールの関数と定数 節の中で説明されています。それぞれ `PARSE_DECLTYPES` 定数と `PARSE_COLNAMES` 定数の項目です。

以下の例で両方のアプローチを紹介します。

    import sqlite3

    class Point(object):
        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)

    def convert_point(s):
        x, y = map(float, s.split(";"))
        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()

### 11.13.5.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])

## 11.13.6. トランザクション制御

デフォルトでは、 `sqlite3` モジュールはデータ変更言語(DML)文(すなわち `INSERT`/`UPDATE`/`DELETE`/`REPLACE`)の前に暗黙のうちにトランザクションを開始し、非DML、非クエリ文(すなわち `SELECT` や上記のいずれでもないもの。)の前にトランザクションをコミットします。

ですから、もしトランザクション中に `CREATE TABLE ...`, `VACUUM`, `PRAGMA` といったコマンドを発行すると、 `sqlite3` モジュールはそのコマンドの実行前に暗黙のうちにコミットします。このようにする理由は二つあります。第一にこうしたコマンドのうちの幾つかはトランザクション中ではうまく動きません。第二に sqlite3 はトランザクションの状態(トランザクションが掛かっているかどうか) を追跡する必要があるからです。

sqlite3 が暗黙のうちに実行する `BEGIN` 文の種類(またはそういうものを使わないこと)を `connect()` 呼び出しの isolation_level パラメータを通じて、または接続の `isolation_level` プロパティを通じて、制御することができます。

もし 自動コミットモード が使いたければ、 `isolation_level` は None にしてください。

そうでなければデフォルトのまま `BEGIN` 文を使い続けるか、 SQLite がサポートする分離レベル “DEFERRED”, “IMMEDIATE” または “EXCLUSIVE” を設定してください。

## 11.13.7. `sqlite3` の効率的な使い方

### 11.13.7.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

    # Using a dummy WHERE clause to not let SQLite take the shortcut table deletes.
    print "I just deleted", con.execute("delete from person where 1=1").rowcount, "rows"

### 11.13.7.2. 位置ではなく名前でカラムにアクセスする

`sqlite3` モジュールの有用な機能の一つに、行生成関数として使われるための `sqlite3.Row` クラスがあります。

このクラスでラップされた行は、位置インデクス(タプルのような)でも大文字小文字を区別しない名前でもアクセスできます:

    import sqlite3

    con = sqlite3.connect("mydb")
    con.row_factory = sqlite3.Row

    cur = con.cursor()
    cur.execute("select name_last, age from people")
    for row in cur:
        assert row[0] == row["name_last"]
        assert row["name_last"] == row["nAmE_lAsT"]
        assert row[1] == row["age"]
        assert row[1] == row["AgE"]

### 11.13.7.3. コネクションをコンテキストマネージャーとして利用する

バージョン 2.6 で追加.

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 catched
    try:
        with con:
            con.execute("insert into person(firstname) values (?)", ("Joe",))
    except sqlite3.IntegrityError:
        print "couldn't add Joe twice"

## 11.13.8. 既知の問題

### 11.13.8.1. マルチスレッド

古いバージョンの SQLite はスレッド間でのコネクションの共有に問題がありました。その理由は、Python のモジュールではスレッド間のコネクションとカーソルの共有ができないためです。依然としてそのようなことをしようとすると、実行時に例外を受け取るでしょう。

唯一の例外は `interrupt()` メソッドで、これだけが異なるスレッドから呼び出せます。

### ナビゲーション

- 索引
- モジュール |
- 次へ |
- 前へ |
- Python 2.7ja1 documentation »
- Python 標準ライブラリ »
- 11. データの永続化 »