Python 3の標準ライブラリであるsqlite3を使って、SQLite と呼ばれるデータベースを触ってみるメモです。
基本
テーブルを作成
以下のコードでは、都道府県のデータを格納するprefecturesという名前のテーブルを定義します。このテーブルは、name(都道府県名), capital(都道府県庁所在地), population(人口), area(面積)という4つのカラムを持ちます。
import sqlite3 # データベースの保存先 database_store_path = './example.db' # 最初にデータベースを表す Connection オブジェクトを作る conn = sqlite3.connect(database_store_path) # カーソルオブジェクトをつくる c = conn.cursor() # executeによりSQLコマンドを実行 # ここでは、'prefectures'という名前のtableを生成するSQLコマンドを実行 # ここでtext, integer, realというのは「カラム型」を意味する c.execute('''CREATE TABLE prefectures (name text, capital text, population integer, area real)''')
データを挿入
次に、prefecturesテーブルにいくつかの都道府県のデータを挿入していきます。以下では、c.execute()を使ってデータを1個ずつ挿入する方法と、c.executemany()を使ってデータ列を一度に挿入する方法を示しています。
人口、面積のデータは英語版Wikipediaを参照しました。
# executeによりSQLコマンドを実行 # ここでは、'stocks'という名前のtableに、1個分のデータを挿入するSQLコマンドを実行 c.execute("INSERT INTO prefectures VALUES ('Kanagawa','Yokohama',9058094,2415.83)") c.execute("INSERT INTO prefectures VALUES ('Tokyo','Tokyo',13929280,2194.07)") # '?'というプレースホルダーを使って1個分のデータを挿入することもできる c.execute("INSERT INTO prefectures VALUES (?,?,?,?)", ('Chiba', 'Chiba', 6278060, 5157.61)) # '?'というプレースホルダーを使って複数のデータを挿入することもできる prefecture_list = [('Tochigi', 'Utsunomiya', 1943886, 6408.09), ('Ibaraki', 'Mito', 2871199, 6097.19)] c.executemany("INSERT INTO prefectures VALUES (?,?,?,?)", prefecture_list)
後始末
以下のコードによりDBを更新して保存します。
# 変更を保存 (commit) conn.commit() # 後始末 conn.close()
データの読み込み
さきほどexample.dbに保存したテーブルに格納されたデータを読み込んでみます。データの読み込み方は、SQLite入門 によると以下の3種類あります。実際は1番目と2番目を使いそうな気がします。
# 一度保存したDBを開く conn = sqlite3.connect(database_store_path) c = conn.cursor() # 1. カーソルをイテレータ (iterator) として扱う c.execute('SELECT * FROM prefectures') for row in c: print(row) print() # 2. fetchallで結果リストを取得する c.execute('SELECT * FROM prefectures') for row in c.fetchall(): print(row) print() # 3. fetchoneで1件ずつ取得する c.execute('SELECT * FROM prefectures') print(c.fetchone()) # 1番目のレコード print(c.fetchone()) # 2番目のレコード print(c.fetchone()) # 3番目のレコード print(c.fetchone()) # 4番目のレコード print(c.fetchone()) # 5番目のレコード print(c.fetchone()) # 6番目のレコードは存在しないのでNoneが返る print() # 後始末 conn.close()
出力結果は以下のとおりです。
('Kanagawa', 'Yokohama', 9058094, 2415.83)
('Tokyo', 'Tokyo', 13929280, 2194.07)
('Chiba', 'Chiba', 6278060, 5157.61)
('Tochigi', 'Utsunomiya', 1943886, 6408.09)
('Ibaraki', 'Mito', 2871199, 6097.19)
('Kanagawa', 'Yokohama', 9058094, 2415.83)
('Tokyo', 'Tokyo', 13929280, 2194.07)
('Chiba', 'Chiba', 6278060, 5157.61)
('Tochigi', 'Utsunomiya', 1943886, 6408.09)
('Ibaraki', 'Mito', 2871199, 6097.19)
('Kanagawa', 'Yokohama', 9058094, 2415.83)
('Tokyo', 'Tokyo', 13929280, 2194.07)
('Chiba', 'Chiba', 6278060, 5157.61)
('Tochigi', 'Utsunomiya', 1943886, 6408.09)
('Ibaraki', 'Mito', 2871199, 6097.19)
None
応用
特定のカラムだけを取り出す
4つのカラムのうち、面積と人口のカラムのみを取り出すにはSELECTを以下のように使います。
# 一度保存したDBを開く conn = sqlite3.connect(database_store_path) c = conn.cursor() # 特定の列だけを取り出す c.execute('SELECT area,population FROM prefectures') for row in c: print(row) # 後始末 conn.close()
結果は以下です。
(2415.83, 9058094) (2194.07, 13929280) (5157.61, 6278060) (6408.09, 1943886) (6097.19, 2871199)
これ以降、DBを開いてカーソルを取得する部分と後始末の部分は省略します。変数cにはカーソルが入っていると思ってください。
特定の条件にあったデータだけを取り出す
人口が500万以上のデータだけを取り出すにはSELECTにWHEREで条件を加えます。
# 人口が500万以上の行だけを取り出す c.execute('SELECT * FROM prefectures WHERE population > 5000000') for row in c: print(row)
結果は以下です。
('Kanagawa', 'Yokohama', 9058094, 2415.83)
('Tokyo', 'Tokyo', 13929280, 2194.07)
('Chiba', 'Chiba', 6278060, 5157.61)
データを特定の条件で並び替える
例えば人口が多い順にデータを並び替えて取り出すにはSELECTにORDER BYで条件を加えます。最後のDESCというのが降順を意味していて、これをASCにするか省略するかすると昇順になります。
# 人口が多い順に取り出す c.execute('SELECT * FROM prefectures ORDER BY population DESC') for row in c: print(row)
結果は以下です。
('Tokyo', 'Tokyo', 13929280, 2194.07)
('Kanagawa', 'Yokohama', 9058094, 2415.83)
('Chiba', 'Chiba', 6278060, 5157.61)
('Ibaraki', 'Mito', 2871199, 6097.19)
('Tochigi', 'Utsunomiya', 1943886, 6408.09)
カラムを追加する
人口密度を表すカラムを追加してみます。SQL文でデータを追加・更新・削除する方法 (2/2)を参考にしました。
# 人口密度を表すカラムを追加 c.execute('ALTER TABLE prefectures ADD population_density float') # 人口密度を挿入 c.execute('UPDATE prefectures SET population_density=population / area') # 結果を表示 c.execute('SELECT * FROM prefectures') for row in c: print(row)
結果は以下です。
('Kanagawa', 'Yokohama', 9058094, 2415.83, 3749.474921662534)
('Tokyo', 'Tokyo', 13929280, 2194.07, 6348.603280661054)
('Chiba', 'Chiba', 6278060, 5157.61, 1217.242094691146)
('Tochigi', 'Utsunomiya', 1943886, 6408.09, 303.3487357387302)
('Ibaraki', 'Mito', 2871199, 6097.19, 470.9052858775928)
素性が不明なデータベースを探索
データベースファイルが与えられたときに中身を探索する方法について記します。ちなみに、db.pyでデータベース探索 にあるように、db.pyというツールを使えばもっとかんたんにできるようですが、ここでは練習としてsqlite3モジュールのみで探索してみます。
まずはデータベースに格納されているテーブル一覧を取得する方法です。sqlite_masterに格納されている情報を使います。
# テーブル一覧を取得 # c.f. https://www.kite.com/python/answers/how-to-list-tables-using-sqlite3-in-python c.execute("SELECT name FROM sqlite_master WHERE type='table'") print(c.fetchall())
結果は以下です。
[('prefectures',)]
これでprefecturesという名前のテーブルがあることがわかりました。
次に、prefecturesテーブルのカラム名を取得します。やり方が複数ありそうで自信がありませんが、以下のコードで取得できました。
# テーブルのカラム名を取得 # https://stackoverflow.com/questions/947215/how-to-get-a-list-of-column-names-on-sqlite3-database") c.execute("SELECT name FROM PRAGMA_TABLE_INFO('prefectures')") print(c.fetchall())
結果は以下です。
[('name',), ('capital',), ('population',), ('area',), ('population_density',)]
