SQLite データベース作成②

前回はデータベースにテーブルを作成し、1つずつデータを入力する方法を紹介しました。今回はより実践的にExcelデータをデータベースに書き込む方法をご紹介します。

データの準備

データベース

前回の投稿で使用したデータベース（drugs.db）にExcel上にあるデータを書き込みます。

import sqlite3
db = sqlite3.connect("drugs.db")

drugs.dbが存在していれば、以下の操作でdrugs.dbにデータが書き込まれます。もし存在していなければ、新規で「drugs.db」が作成され、データが書き込まれることになります。

サンプルデータ

Excelデータを書き込むと言っても、Excelデータをpandasを用いてDataframeに変えてから書き込みます。今回はpd.DataFrame()を用いてDataframeを作成する所から始めます。

import pandas as pd
df = pd.DataFrame(
    [['ビクトーザ皮下注', 'ノボ ノルディスク', 2], 
    ['オゼンピック皮下注', 'ノボ ノルディスク', 2], 
    ['リベルサス錠', 'ノボ ノルディスク', 1], 
    ['バイエッタ皮下注', 'アストラゼネカ', 2], 
    ['リキスミア皮下注', 'サノフィ', 2], 
    ['トルリシティ皮下注', 'イーライリリー', 2], 
    ['マンジャロ皮下注', 'イーライリリー', 2]], 
    columns=['name', 'maker', 'form']
)

もし、適当なExcelデータがあれば、そちらを使って頂いても構いません。

df = pd.read_excel('ファイルパス', sheet_name='シート名')

Dataframeをデータベースへ書き込む

書き込み方は簡単です。

df.to_sql('GLP1', db, if_exists='replace')

dfに対してto_sql()を使用するだけで、dfをdbに'GLP1'というテーブルとして書き込むことができます。

DetaFrame.to_sql(テーブル名, データベース名, if_exists='replace')

第一引数にテーブル名を指定しますが、データベース内に既に同名のテーブルが存在するとエラーが出ます。

引数にif_exists='replace'を追加することで、もし同名のテーブルがあったとしても置換（上書き）されます。

テーブルの中身を確認　SELECT

drugs.dbには前回作成した"Aug2024"と先ほど作成した"GLP1"という２つのテーブルが存在します。中身を確認するには、まずSELECT文を使用しデータを抽出します。

SELECT カラム名1, カラム名2, … FROM テーブル名

前回の投稿でデータベース作成やレコード挿入を行ったときは、カーソルオブジェクトを作成し、cur.execute()の引数にCREATE文やINSERT文を入れてSQLクエリを実行しました。今回も同様にexecute()の引数にSELECT文を入れることで実行できます。
カラム名のところは「*」とすると全てのカラムを取得できるので便利です。

# カーソルオブジェクトを作成
cur = db.cursor()
# SQLクエリ実行
cur.execute('SELECT * FROM GLP1')

fetchall()メソッドで、クエリ結果として得られた全ての行をリスト形式で取得することができます。ちなみに、fetchone()を用いると最初の1行だけが取得できます。
結果をresultsという変数に格納し、for文を使用して1行ずつ表示してます。

results = cur.fetchall()
for result in results:
    print(result)

データベース内のテーブルから抽出された情報"results"はデータフレームと同様に二次元配列なので、results[行][列]のように指定すれば要素を取得できます。例えば、results[2][1]（←0からカウントするので、3行2列目の意味）は"リベルサス錠"が返ってきます。

データの抽出　WHERE

先ほどのSELECT文では抽出条件を設定しなかったので、全てのレコードが抽出されました。抽出条件を設定するには、SELECT文にWHERE句を追加します。

SELECT * FROM テーブル名 WHERE 抽出条件

例えば、”GLP1”テーブルの中でイーライリリー製品を抽出したいときは、

cur.execute('SELECT * FROM GLP1 WHERE maker =="イーライリリー"')
results = cur.fetchall()
for result in results:
    print(result)

というように条件式「WHERE maker =="イーライリリー"」を付加します。
データ型が数字であれば、不等号も使用できます。

cur.execute('SELECT * FROM Aug2024 WHERE form>=2')
results2 = cur.fetchall()
for result2 in results2:
    print(result2)

剤型が2以上のもの（1:飲み薬 以外のもの）が抽出できました。

データのソート　ORDER BY

データを並べ替えることも出来ます。ORDER BYを使用し、ASC（昇順）またはDESC（降順）を指定します。何も指定しなければASCでソートされます。

SELECT * FROM テーブル名 ORDER BY カラム名1 ASC/DESC

cur.execute('SELECT * FROM GLP1 ORDER BY maker ASC')
results = cur.fetchall()
for result in results:
    print(result)

条件を「,」区切りで複数指定することも可能です。

cur.execute('SELECT * FROM GLP1 ORDER BY maker ASC, name DESC')
results = cur.fetchall()
for result in results:
    print(result)

サンプルデータだと分かりにくいですが、メーカー名を昇順でソートし、同じメーカー名の中で医薬品名は降順で並んでいます。

cur.close()
db.close()

---

データベースの操作は、４つの基本操作Create（作成）、Read（読み取り）、Update（更新）、Delete（削除）からなります。頭文字をとってCRUDと表現されたりします。前回から今回にかけてCとRの基本をご紹介しました。次回は、UとDについてのお話です。