今日はアプリケーションで使っていたデータベースについて補足説明します。

私としたことが、本編ではデータベースの呼び出しとカーソル操作だけ記載していて、作成方法の説明をすっぽかしていました(笑)

さて、使うのはPythonのsqlite3という軽量なデータベースですが、サーバー接続なしで手軽に使えるため、一般的なデータベースというよりは、ライブラリやJsonに近い立ち位置だと勝手に思っています。

ただ、SQL構文を使ってデータの操作を行う点はその他のデータベースと同じですので、順を追って確認していきましょう。

Import pandas as pd
Import sqlite3

#登録するデータの用意
obs_stations = pd.read_excel("obs_stations.xlsx",skiprows=1,header=0)
data=obs_stations.loc[:,['地点コード', '地点', '府県番号']]
tuple_ =  [tuple(x) for x in data.values]


#データベース
conn = sqlite3.connect("obs_stations.sqlite") 

#カーソルオブジェクト
cur = conn.cursor()

cur.execute("""DROP TABLE IF EXISTS location""")
cur.execute("""CREATE TABLE location
            (station_id INTEGER NOT NULL,
            station_name VARCHAR(48) NOT NULL,
            fuken_id INTEGER)
            """)

cur = conn.cursor()

 # Insert実行
cur.executemany(
    "INSERT INTO location (station_id, station_name, fuken_id) VALUES (?,?,?)", tuple_)

# 変更
conn.commit()
# 保存
conn.close()

まず、登録するデータの型は事前にタプルへ変更しておく必要があります。

次に、新しいデータベース「obs_stations.sqlite」を作成し、 sqlite3 が動作できるようにします。

conn = sqlite3.connect("obs_stations.sqlite") 

変数connに返されるのはディスク上のデータベースへの接続なので、SQL文の実行ができるようカーソルを用意します。

cur = conn.cursor()

ここまでが下準備で、次は”””CREATE TABLE [TABLE NAME]  (列名  データ型  制約)”””でデータベースのテーブルを作成します。

データ型以降はオプションなので、あっても無くてもOKです。ただし、データの汚染やバグを防ぐことができるメリットもあります。

例えば「station_id INTEGER NOT NULL」は、【station_id】が整数型で、【NOT NULL】の制約によってNULL 値が列に入力されないようにします。こうすることでデータベースの更新を行う際、常に何らかの整数値が割り当てられます。

※必須ではありませんが、新規テーブル作成の際、既に同じ名前のテーブルが存在しているのか存在しないのか、分からない場合に対処するため、”DROP TABLE IF EXISTS [TABLE NAME]”というSQL文を仕込んでいます。

cur.execute("""DROP TABLE IF EXISTS location""")
cur.execute("""CREATE TABLE location
            (station_id INTEGER NOT NULL,
            station_name VARCHAR(48) NOT NULL,
            fuken_id INTEGER)
            """)

SQL文についてよくよく調べていると、文自体はMySQLと同じみたいですね。

気をつける点として、Python上ではSQL文を”””ダブルクォーテーション3つで囲んでいますが、どうやらこれは改行を有効にできる構文ルールのようです。使い慣れていないと不思議な書き方に思いますが、1行に収まる場合は通常のテキストのように、ダブルクォーテーション1つで構文実行が可能でした。

cur = conn.cursor()

 # Insert実行
cur.executemany(
    "INSERT INTO location (station_id, station_name, fuken_id) VALUES (?,?,?)", tuple_)

テーブル作成が終わったら、一度カーソルを戻してからそれぞれの列にタプル形式で値の代入を行います。後は変更と保存をしてできあがりです。

一度保存をすれば使い回しができるのでとても便利ですよ～。

では今日はここまで！