【SQLite】10分で分かる　SQLiteとは？　

その他
2020.08.12

概要

  • SQLite(エスキューライト)とは、軽量コンパクトなリレーショナルデータベースシステムです。
  • 「小型」「高速」「自己完結型」「高信頼性」「高機能」のSQLデータベースエンジンを実装するC言語ライブラリ。
  • 主に「組み込み用途」や「小規模システムのデータストア」として利用されています。
  • 複数のユーザーの参照が可能。更新系は、基本的に1ユーザーしか更新できない。
SQLite Home Page
www.sqlite.org
SQLite: Documentation
www.sqlite.org

「SQLite」の使いところ

SQLiteは、さまざまなIoTデバイスのデータベースエンジンとして使用されています。

・スマートフォン(携帯電話)
・テレビ
・ゲーム機
・カメラ
・スマートウォッチ
・サーモスタット
・医療機器
・ドローン
・自動車
・飛行機 etc…

SQLiteは、IoTデバイスだけでなくWebサービスを構築するも、使用されています。Webサービスではデータの参照系で、MySQLなどと比較して、軽く・大規模データが扱える・データ管理が楽などの理由で選択する人が多くみられます。一般にレンタルサーバではDBのサイズが1GByteまたは2GByteまでしか扱えず(レンタルサーバの制限)、それを超えるようなデータを扱う際は、SQLiteが使用されます。

ファイルとしてのSQLite

単一ファイル

SQLiteは、単なる単一のファイルですので、USBで持ち運んだり、メールに添付したりと言ったことが可能です。データベースを手軽にコピーできる利便性があります。

例えば、「MySQL」や「PostgreSQL」は、クライアントサーバ構成であり、*.sqlファイルを受け取り、更にインポートしなければなりません。マシンにべったり張り付いたファイル構成となります。

クロスプラットフォーム

SQLiteのデータベースファイルはクロスプラットフォームで利用可能で、「ビッグエンディアンと リトルエンディアン」など気にせず、利用できます。

ダウンロード

以下からダウンロードします。

SQLite Home Page
www.sqlite.org

“Download”を選択すると、各種実行ファイルがダウンロードできます。ここでは、Windows10の64bitをダウンロードします。

ダウンロードして解凍すると、以下のように2ファイルの構成になります。

sqlite3.dllがライブラリになります。sqlite3.defは、dllからどの関数がエクスポートされるかが書かれたファイルです。必ずしも必要ではありません。

DBを作ろう – １　python編

入力データ

駅の乗降者数が入ったデータです。こちらからダウンロードできます。

国土数値情報 | 駅別乗降客数データ
nlftp.mlit.go.jp
S12-18_NumberOfPassengersダウンロード

ファイルをダウンロード後、拡張子をtxtからjsonに変更してください。

DB作成と参照

先ほどのインプットデータを、pythonを使用してsqliteのDBに変換します。

#!/usr/bin/python3.8

import sys
import sqlite3
import json

#############################################################################################
inpt_file_name               = r"..\1.data\S12-18_GML\S12-18_NumberOfPassengers.geojson"
#############################################################################################


with open(inpt_file_name, 'r',encoding="utf-8") as f:
    jData = json.load(f)
   
#########################################
#  Write DB
#########################################

dbname = 'MyInfo.db'

#Sqliteに接続
conn = sqlite3.connect(dbname)
cur = conn.cursor()

#sql文を実行
result = cur.execute('select count(*) from sqlite_master where type=\'table\' and name=\'stationPassengers\'')

cur.execute('DROP TABLE stationPassengers')
cur.execute('create table stationPassengers(stationName text, administrationCompany text, routeName text, lineType integer,\
            railroadCompanyClassificaion integer,duplicate  integer,  dataExistence integer ,passengers integer,latitude float,longitude float)')

for station  in jData["features"]:
    stationName                  = station["properties"]["S12_001"]
    administrationCompany        = station["properties"]["S12_002"]
    routeName                    = station["properties"]["S12_003"]
    lineType                     = station["properties"]["S12_004"]
    railroadCompanyClassificaion = station["properties"]["S12_005"]
    duplicate                    = station["properties"]["S12_030"]
    dataExistence                = station["properties"]["S12_031"]
    passenger                    = station["properties"]["S12_033"]
    latitude                     = ( station["geometry"]["coordinates"][0][0] + station["geometry"]["coordinates"][1][0] ) * 0.5
    longitude                    = ( station["geometry"]["coordinates"][0][1] + station["geometry"]["coordinates"][1][1] ) * 0.5

    sql = "insert into stationPassengers values(" + "\"" +  stationName + "\"" + "," \
        +  "\"" + administrationCompany + "\""  + "," \
        +  "\"" + routeName + "\""  + "," \
        +   str(lineType) + "," \
        +   str(railroadCompanyClassificaion ) + "," \
        +   str(duplicate) + "," \
        +   str(dataExistence) + "," \
        +   str(passenger ) + "," \
        +   str(latitude ) + "," \
        +   str(longitude ) + ")"
 
    cur.execute(sql)

conn.commit()

conn.close()
convertPassengersAtStationダウンロード

pythonを実行するとMyInfo.dbが作成されます。

早速、DBのビューワで中身を参照してみます。

DB Browser for SQLite
sqlitebrowser.org

テーブル構造が見れます。

データもキチンと入っています。

DBを作ろう – 2　php編

入力データ

DB作成と参照

早速DBを作成します。その前にPHP特有にPDOについて説明します。

PDOは、PHP Data Objectsの略です。SQLite、MySQL、PostgreSQLなどをほぼ同じコードで使えるようにしてくれるものになります。クラス形式で作られた形式なのでPDOクラスと呼ばれます。

phpLiteAdmin

phpLiteAdmin は、PHP で書かれた SQLite 管理ツールです。Mysqlでお馴染みのphpMyAdminと同じように使用できます。
データベースやテーブルの作成を行ったり、各種 SQL 文の発行、データのインポートやエクスポート
データの編集を SQL 文を作成することなくブラウザから行うことができます。

以下のサイトからダウンロードして、構築できます。

phpLiteAdmin
www.phpliteadmin.org

日本語ですぐに使ってみたい方は、ブラウザを使用して以下から使用できます。

https://phpliteadmin.primers.jp/data/phpliteadmin.php
phpliteadmin.primers.jp

以下のような画面で、DBを作成したりクエリできたりします。

sqlファイルのインポート

テキストのSQLデータ作成

  • テキストのSQLデータを作成し、 sqlite3コマンドでDBを作成します。
  • 例えば、テキストのSQLデータを作成するpythonコードは、以下の通りです。
import csv


fout =   open('C:\\Users\\testout.sql', "w",encoding='utf-8')


fout.write("DROP TABLE IF EXISTS convenList;" + "\n");

fout.write("CREATE TABLE convenList ("+ "\n");

fout.write(" name TEXT NOT NULL, "+ "\n");
fout.write(" id INTEGER PRIMARY KEY,"+ "\n");
fout.write(" address TEXT NOT NULL,"+ "\n");
fout.write(" open_date TEXT NOT NULL,"+ "\n");
fout.write(" latitude REAL NOT NULL,"+ "\n");
fout.write(" longitude REAL NOT NULL"+ "\n");
fout.write(");"+ "\n");


#insert into book values(1, 100, "ABC", '大窪',"abc");


with open('C:\\Users\\test.csv', mode='r',encoding="utf_8") as f:

    reader = csv.reader(f)
    for row in reader:
       name = row[0]
       id =  row[1]
       address =  row[2]
       open_date =  row[3]
       latitude =  row[4]
       longitude = row[5]

       if(latitude != "AddressNotFound"):
            buff = 'insert into convenList values(' + '"' + name + '"' + ',' +  id + ',' + '"' + address + '"' + ',' +  '"' + open_date + '"' +',' +  latitude + ',' + longitude + ');' + "\n"
            fout.write(buff);
            print(row)

fout.close()

インポート

% sqlite3 test.db < select.sql
スポンサーリンク