[Python]データベースの操作[sqlite3]

SQLの基礎はなんとなく習得できたので、Pythonの標準ライブラリ「sqlite3」を使ってみたいと思います。

使えるデータ型を確認し、準備の項目で必要なオブジェクトを作成します。
あとは簡単なデータベースの操作を行うつもりです。

このページは、基本的なSQLが書けることを前提としています。

参考にしたもの

• sqlite3[docs.python.org]
• SQLite

SQL自体は２週間ぐらいかけてこちらの書籍で学習しました。

　Androidアプリを作成しました。
　感情用のメモ帳です。

MoodNote - Apps on Google Play

Notes for your emotions. Record and organize your feelings.

play.google.com

使えるデータ型

SQLIiteがサポートしているデータ型は次の５つです。

• INTEGER
• REAL
• TEXT
• BLOB
• NULL

これらの型はPythonで処理するときに、デフォルトで以下のように変換されます。

• INTEGER → int
• REAL → float
• TEXT → str
• BLOB → bytes
• NULL → None

準備

標準ライブラリなのでインストール不要で、インポートするだけで使えます。

次にデータベースと接続するコネクションクラスのオブジェクトを作成します。

変数名は自由にどうぞ。

パスのデータベースが存在しない場合、新しくファイルが作成されます。

パスに「:memory:」を使うと、メモリ上に一時的なデータベースを作ることも可能です。

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

さらに、コネクションクラスからカーソルオブジェクトを作ります。

このカーソルを介してSQLを実行します。

では、ここまでを実際にやってみましょう。

Python 3.10.0 (default, Oct 12 2021, 16:02:08) [GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> con = sqlite3.connect('test.db')
>>> cur = con.cursor()
>>> type(con)
<class 'sqlite3.Connection'>
>>> type(cur)
<class 'sqlite3.Cursor'>

なにかしらの処理をし、使い終わったら最後にコネクションオブジェクトを閉じます。

closeメソッドです。

>>> con.close()

SQLの実行[execute]

SQL文の実行には、カーソルオブジェクトのメソッドを使います。

メソッドは３つ用意されていて、

• execute
• executemany
• executescript

機能は上から順に、SQL文をひとつだけ実行する、

渡されたリストやタプルなどを使って、SQL文を実行する、

複数のSQL文を実行する、

となっています。

この項目では「execute」を使って、テーブルの作成、データの追加、データの取り出しをやっていきます。

テーブル作成

「準備」の項でカーソルオブジェクトを作ったので、これを使います。

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

まずは空のデータベースにテーブルを作成してみましょう。

executeメソッドの引数にSQL文を渡します。

>>> cur.execute("CREATE TABLE Books (title TEXT, author TEXT)")
<sqlite3.Cursor object at 0x7f2d28a4f6c0>

これで「Books」というテーブルが作成されました。

データの追加

テーブルに「INSERT」で行を追加します。

>>> cur.execute("INSERT INTO Books VALUES ('吾輩は猫である', '夏目漱石')")
<sqlite3.Cursor object at 0x7f3ed72cb6c0>

これでデータが追加されました。

ですが、Pythonの「sqlite3」モジュールでは、デフォルトで自動コミットが無効になっているため、このまま終了したり、コネクションオブジェクトを閉じると、次回に呼び出しても何のデータも保存されていません。

コネクションのメソッド「commit」で変更を確定する必要があります。

カーソルではなく、コネクションオブジェクトの方です。

>>> con.commit()
>>> con.in_transaction
False

これでデータの保存が確定されました。
「in_transaction」は、コミットされていない変更があるかの確認です。

ちなみにロールバックするには、文字通り「rollback」メソッドがあります。

>>> con.isolation_level = None

データの取り出し

わかりやすいようにいくつか行を追加し、SQLの「SELECT」で情報を引き出します。

>>> cur.execute("INSERT INTO Books VALUES ('坊っちゃん', '夏目漱石')")
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> cur.execute("INSERT INTO Books VALUES ('斜陽', '太宰治')")
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> cur.execute("INSERT INTO Books VALUES ('人間失格', '太宰治')")
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> con.commit()
>>> cur.execute("SELECT * FROM Books")
<sqlite3.Cursor object at 0x7f3ed74c48c0>

カーソルに引き出したあと、さらにこのカーソルをfor文にかけるか、メソッドで取り出します。

for文

>>> for row in cur:
...     print(row)
... 
('吾輩は猫である', '夏目漱石')
('坊っちゃん', '夏目漱石')
('斜陽', '太宰治')
('人間失格', '太宰治')

メソッド

カーソルのメソッドは３つあります。

• fetchone――１行取り出し
• fetchmany――引数に数値を渡し、その数値ぶん行を取り出す
• fetchall――すべての行を取り出し

>>> cur.execute("SELECT * FROM Books")
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> cur.fetchone()
('吾輩は猫である', '夏目漱石')
>>> cur.fetchone()
('坊っちゃん', '夏目漱石')
>>> cur.fetchmany(2)
[('斜陽', '太宰治'), ('人間失格', '太宰治')]
>>> cur.execute("SELECT * FROM Books")
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> cur.fetchall()
[('吾輩は猫である', '夏目漱石'), ('坊っちゃん', '夏目漱石'), ('斜陽', '太宰治'), ('人間失格', '太宰治')]
>>> cur.fetchall()
[]

SELECTで読み込んだ後には４行のデータが入っていますが、メソッドを行うたびに、取り出し位置が変化しています。

fetchallですべて取り出した後、再び同じメソッドを実行しても、空のリストしか返ってきていません。

executemany

これまでは「execute」を使ってきましたが、「executemany」は主にINSERTやDELETE等で複数のデータを処理したいときに使います。

引数にはSQL文と、タプルやリストなどのシーケンスを渡します。

データがひとつずつだとしても、タプルやリストに収める必要があります。
※((a,), (b,))のように、カッコが二重になる。

>>> bunko = (('羅生門', '芥川龍之介'), ('蜘蛛の糸', '芥川龍之介'),
... ('銀河鉄道の夜', '宮沢賢治'), ('注文の多い料理店', '宮沢賢治'))
>>> cur.executemany("INSERT INTO Books VALUES (?, ?)", bunko)
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> con.commit()

特徴的なのは、プレースホルダーの「？」を使っているところです。

引数のタプルは展開され、その中身が？でマークしたところに当てはまります。
※プレースホルダーの数とタプルのなかのデータ数（上記の場合二つ）が一致している必要あり。

変数「bunko」では４行分を入れていましたが、ちゃんと追加されているか確認してみましょう。

>>> cur.execute("SELECT * FROM Books")
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> cur.fetchall()
[('吾輩は猫である', '夏目漱石'), ('坊っちゃん', '夏目漱石'), ('斜陽', '太宰治'), ('人間失格', '太宰治'), ('羅生門', '芥川龍之介'), ('蜘蛛の糸', '芥川龍之介'), ('銀河鉄道の夜', '宮沢賢治'), ('注文の多い料理店', '宮沢賢治')]

問題ないですね。

「プレースホルダーなんて使わず、変数を使えばいいんじゃないの？」と思うかもしれません（私は思いました）が、それではSQLインジェクション攻撃に弱いようです。

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

sqlite3 --- SQLite データベースに対する DB-API 2.0 インターフェース

文字列操作をしなければ安全なのかとも思ったのですが、調べてもそのことに言及がなかったので、まずプレースホルダーを使う方が無難でしょう。

>>> cur.execute("INSERT INTO Books VALUES (?, ?)", ('伊豆の踊り子', '川端康成'))

>>> cur.execute("INSERT INTO Books VALUES (:title, :name)", {'title': '雪国', 'name': '川端康成'})

executescript

このメソッドは複数のSQL文を実行します。

>>> cur.executescript('''
... CREATE TABLE testint (number INTEGER);
... INSERT INTO testint VALUES (1);
... INSERT INTO testint VALUES (2);
... INSERT INTO testint VALUES (3);
... 
... CREATE TABLE testfloat (float REAL);
... CREATE TABLE teststr (text TEXT);
... ''')
<sqlite3.Cursor object at 0x7fddd13076c0>

３つのテーブルを新規に作成し、「testint」にはデータも追加しています。

テーブル一覧を確認しましょうか。
「sqlite_master」というテーブルを利用します。

このなかの「tbl_name」で取得できます。

>>> cur.execute("SELECT tbl_name FROM sqlite_master")
<sqlite3.Cursor object at 0x7fddd1307ec0>
>>> for row in cur:
...     print(f'テーブル： {row[0]}')
... 
テーブル： Books
テーブル： testint
テーブル： testfloat
テーブル： teststr

executescriptで追加したテーブルが表示されています。

別の命令文を書いてみます。

>>> cur.executescript('''
... DELETE FROM testint WHERE number = 1;
... 
... INSERT INTO testfloat VALUES (1.11);
... INSERT INTO testfloat VALUES (2.22);
... 
... INSERT INTO teststr VALUES ('Sun');
... INSERT INTO teststr VALUES ('Mon');
... ''')
<sqlite3.Cursor object at 0x7fddd1307ec0>
>>> con.in_transaction
False

自動コミットを有効にしていませんが、すでにコミットされています。

このメソッドの仕様で「isolation_level」は無視されるようです。

まとめ

準備

import sqlite3
con = sqlite3.connect(データベースのパス)
cur = con.cursor()

コネクションオブジェクトの属性

カーソルオブジェクトの属性

SQLをある程度使えるなら、モジュールを扱うのもそんなに苦労しないのではないでしょうか。

データベースはいろいろなところで使われているので、Pythonと「sqlite3モジュール」を使いこなせると幅が広がりそうですね。