SQLの基礎はなんとなく習得できたので、Pythonの標準ライブラリ「sqlite3」を使ってみたいと思います。
使えるデータ型を確認し、準備の項目で必要なオブジェクトを作成します。
あとは簡単なデータベースの操作を行うつもりです。
このページは、基本的なSQLが書けることを前提としています。
参考にしたもの
SQL自体は2週間ぐらいかけてこちらの書籍で学習しました。
使用した環境
WSL2 Ubuntu - 20.04
Python 3.10.0
使えるデータ型
SQLIiteがサポートしているデータ型は次の5つです。
- INTEGER
- REAL
- TEXT
- BLOB
- NULL
これらの型はPythonで処理するときに、デフォルトで以下のように変換されます。
- INTEGER → int
- REAL → float
- TEXT → str
- BLOB → bytes
- NULL → None
準備
標準ライブラリなのでインストール不要で、インポートするだけで使えます。
import sqlite3
次にデータベースと接続するコネクションクラスのオブジェクトを作成します。
con = sqlite3.connect(データベースのパス)
変数名は自由にどうぞ。
パスのデータベースが存在しない場合、新しくファイルが作成されます。
パスに「:memory:」を使うと、メモリ上に一時的なデータベースを作ることも可能です。
con = sqlite3.connect(':memory:')
さらに、コネクションクラスからカーソルオブジェクトを作ります。
cur = con.cursor()
このカーソルを介して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文の実行には、カーソルオブジェクトのメソッドを使います。
メソッドは3つ用意されていて、
- 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」メソッドがあります。
自動コミットを有効にするには、コネクションオブジェクトの「isolation_level」を「None」に設定します。
>>> 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) ... ('吾輩は猫である', '夏目漱石') ('坊っちゃん', '夏目漱石') ('斜陽', '太宰治') ('人間失格', '太宰治')
メソッド
カーソルのメソッドは3つあります。
- fetchone――1行取り出し
- 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で読み込んだ後には4行のデータが入っていますが、メソッドを行うたびに、取り出し位置が変化しています。
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」では4行分を入れていましたが、ちゃんと追加されているか確認してみましょう。
>>> cur.execute("SELECT * FROM Books")
<sqlite3.Cursor object at 0x7f3ed74c48c0>
>>> cur.fetchall()
[('吾輩は猫である', '夏目漱石'), ('坊っちゃん', '夏目漱石'), ('斜陽', '太宰治'), ('人間失格', '太宰治'), ('羅生門', '芥川龍之介'), ('蜘蛛の糸', '芥川龍之介'), ('銀河鉄道の夜', '宮沢賢治'), ('注文の多い料理店', '宮沢賢治')]
問題ないですね。
「プレースホルダーなんて使わず、変数を使えばいいんじゃないの?」と思うかもしれません(私は思いました)が、それではSQLインジェクション攻撃に弱いようです。
たいてい、SQL 操作では Python 変数の値を使う必要があります。この時、クエリーを Python の文字列操作を使って構築することは安全とは言えないので、すべきではありません。そのようなことをするとプログラムが SQL インジェクション攻撃に対し脆弱になります
sqlite3 --- SQLite データベースに対する DB-API 2.0 インターフェース
文字列操作をしなければ安全なのかとも思ったのですが、調べてもそのことに言及がなかったので、まずプレースホルダーを使う方が無難でしょう。
プレースホルダーはexecuteメソッドでも使えます。
>>> 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>
3つのテーブルを新規に作成し、「testint」にはデータも追加しています。
テーブル一覧を確認しましょうか。
「sqlite_master」というテーブルを利用します。
sqlite_masterの列定義 |
---|
type TEXT |
name TEXT |
tbl_name TEXT |
rootpage INTEGER |
sql TEXT |
このなかの「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()
コネクションオブジェクトの属性
close() | コネクションオブジェクトを閉じる |
commit() | コミット |
rollback() | ロールバック |
in_transaction | 変更されたコミットがあるか |
isolation_level | 自動コミット設定 デフォルトでは無効。Noneで有効に |
カーソルオブジェクトの属性
execute() | SQL文の実行 |
executemany() | 複数のデータを処理 |
executescript() | 複数のSQL文の実行 |
fetchone() | 一行取り出し |
fetchmany() | 複数行取り出し |
fetchall() | すべての行取り出し |
SQLをある程度使えるなら、モジュールを扱うのもそんなに苦労しないのではないでしょうか。
データベースはいろいろなところで使われているので、Pythonと「sqlite3モジュール」を使いこなせると幅が広がりそうですね。