SQLite データベース活用③ DB操作のあれこれ
ここ2カ月ぐらい書いてきたSQLiteについて（取り敢えずの）最終回です。話の流れ的に入れられなかった（忘れていたのもありますが…）ものを覚え書き程度に書いていきます。
DB内に存在するテーブルを確認
昔作ったdbが何者か分からなくなる時があります（笑）そんな時、どんなテーブルが含まれているか確認するためのコードです。
サンプルのdbとして、これまでの投稿で使用してきた'side_effects.db'を使用しています。
import sqlite3
db = sqlite3.connect('side_effects.db')
cur = db.cursor()cur.execute("""
SELECT name FROM sqlite_master WHERE type='table'
""")
cur.fetchall()＞ [('demo',), ('drug',), ('hist',), ('reac',), ('join_table',)]
'side_effects.db'は以下の投稿で作成しましたが、その時作成したテーブル名が取得できています。
テーブル内に存在する列情報を確認
テーブル内にどんなデータが入っているのか確認するために、列名（Accessではフィールド名と表現されます）を取得できると便利ですよね。
例えば、'side_effects.db'の中の'join_table'にどのような列が含まれているか確認するには、以下のようなコマンドを使用します。
cur.execute("""
PRAGMA table_info('join_table')
""")
cur.fetchall()
PRAGMA table_info('テーブル名')を実行すると、以下の情報をもつタプルが返されます。
cid: columnのID（整数型） ※0から始まります
name: 列名（文字列型）
type: データ型（文字列型）
notnull: NULLを許可するかどうか（整数型, 0: 許可, 1: 不許可）
dflt_value: デフォルト値（NULLまたは文字列型）
pk: プライマリキーかどうか（整数型, 0: プライマリキーではない, 1: プライマリキー）
列名だけではなく、各列がどのような設定となっているかも確認できます。
DBをExcelデータに変換
DBから抽出したデータをExcelで扱いたいときもあります。そんな時はpandasのread_sql_query()を使用すると便利です。
おさらいですが、dbからの抽出を行うときはexecute()の引数にSQL文（query）を書いて実行していました。
drug = input()
query = f"""
SELECT * FROM join_table
WHERE `医薬品（一般名）` LIKE '%{drug}%'
AND 医薬品の関与 = '被疑薬'
"""
cur.execute(query)
cur.fetchall()このSQL文をそのままread_sql_query()の引数に使い、ダイレクトにDataFrameを作成する事ができます。
df = pd.read_sql_query(SQL文, db)
import pandas as pd
import sqlite3
db = sqlite3.connect('side_effects.db')
cur = db.cursor()
drug = input()
query = f"""
SELECT * FROM join_table
WHERE `医薬品（一般名）` LIKE '%{drug}%'
AND 医薬品の関与 = '被疑薬'
"""
df = pd.read_sql_query(query, db)
df.to_excel(f'{drug}_抽出結果.xlsx', index=False)
cur.close()
db.close()試しに「リツキシマブ」と入力すると、「リツキシマブ_抽出結果.xlsx」が作成され抽出結果をExcelデータとして保存することができました。
今回はqueryとして医薬品名（あいまい検索）、'被疑薬'という抽出条件を付けましたが、SQL文のWHERE句以下を削除すればテーブル全体をExcelデータとして保存できます。
データベースは奥深いですね。勉強すれば仕事に直結してスキルアップできそうです。今後は不定期になりますが、SQLiteについて勉強したことをアップしていきたいと思います。