今回は データベースのSQLiteに触れてみます。

初めてのデータベースです。
だいたいどんなものなのかざっと触れた後に、データの入れ方と引き出し方の基本的なことをやってみようと思います。

使用環境

Windows10
Anaconda Python3系
Jupyter Notebook

データベースはデータの基地という意味で、米軍によって第二次大戦後に開発されました。
開発の目的は、点在化するデータを整理し共有し検索し加工しやすく一元管理する為。
1970年代にIBMが世界初のRDBMS（リレーショナルデータベース管理システム）の「System R」を開発。
データベースの操作に使う「SEQUEL」（Structured English Query Language）が現在のデータベース言語SQLの土台となった。
データベースを使えば、電話帳や経理ソフト、ツイッターのようなシステムでデータの管理ができる。なるほど。
無料で使えるものから有償のものまで様々。

< データベースのメリット >

データの一元管理
様々なテータを関連付けして格納
データの重複がないように設定できる
データの一貫性、整合性を保てる
データの共有ができる
データへの同時アクセスが処理できる
データの並び替えや加工ができる

< データベースの種類 >
以下のようなものがあります。

MySQL
MarinaDB
PostgreSQL
MongoDB
NoSQL
TinyDB
Microsoft SQL Server
Oracle Database
SQLite

< データベースの操作と構造 >
データベースを操作するには SQLという言語を使います。
Pythonでデータベースを使う場合はPython スクリプトの中にSQL文を書いてデータを検索したり追加したりを行う。

データベースの構造は、その中に複数のテーブルと呼ばれるものを持ちます。Excelシートのように行と列の2次元データです。
行をレコードもしくはローといい、列をカラムまたはフィールドといいます。
テーブル同士を連結・連動できることと、Excelで扱えないような大量のデータ処理も容易にできる。

SQLiteのメリット/デメリット

SQLiteは上記で挙げたデータベースの中でも特に手軽に使え、Webブラウザや様々なアプリ、自動車やスマートフォンの中でも使われているとのこと。

メリットの主なものは、一つのファイルとして扱えるので保存や復元が簡単、軽量かつ高性能。オープンソースで無料。
Pythonでは標準ライブラリに入っているのでインストールする必要がなく、sqlite3をインポートするだけですぐに使えます。

デメリットの主なものは、パスワードでアクセス管理したり出来ないようです。個人情報など重要なデータは極力避けた方がよさげ？
MySQLなど本格的にサーバーに設置して大規模処理するデータベースとは異なる目的で作られたそうですので、デメリットではありませんが用途を考慮する必要がある。

SQL文の説明

現在多くのデータベースはリレーショナル型を採用しており、SQL文で書けばほとんどのDBMSを操作できます。
SQLは大まかに3種類で構成され、データベースを制御したり操作したりする際に使い分けます。
SQLは命令文をデータベースに送ることで対話をやりとりし、動詞と目的語を組み合わせたシンプルな構文になっている。
SQLはデータベースを操作するだけの言語なので、アプリケーションを作るにはその他のプログラミング言語と組み合わせて使います。
ターミナルからSQLのコマンド入力でデータベースを直接操作することも可能。
SQLはSQLiteで使用する場合は大文字でも小文字でもOK。
解説書やネット記事は大文字小文字が色々混ざっておりますが、伝統的かつ分かり易く大文字を使うという方が多いような印象ですので、そうします。

SQLを3つに分類すると

1 データ定義言語
Data Definition Language
例）

- CREATE データベースやテーブルのオブジェクトを新規に定義する  
- JOIN テーブル同士の結合  
- DROP 定義したオブジェクトの削除  
- ALTER 定義したオブジェクトの内容変更  
- TRUNCATE データの全削除

2 データ操作言語
Data Manipulation Language
例)

- SELECT データの検索  
- INSERT データを挿入  
- DELETE データの削除  
- UPDATE データの更新

3 データ制御言語
Data Control Language
例）

- GRANT ユーザー権限を付与
- REVOKE ユーザー権限の削除
- BEGIN トランザクションの開始
- COMMIT トランザクションを確定（保存
- ROLLBACK トランザクションの取り消し

SQLiteを使ってみる

実際に、PythonとSQLでSQLiteを動かしてみます。

SQLiteはPythonの標準モジュールなので、sqlite3をインポートすれば、いきなり使えます。
SQLiteのデータベースはテキストファイルなどと同様に一つのファイルに保存されます。
ファイル名の拡張子は「.db」でも「.sqlite」でも何でもOKです。

下記のコードについて

test.dbというSQLiteのデータベースファイルを作成 ※2
お決まりの手続きに従いデータベース接続とカーソルの定義 ※3,4
もしitemsというテーブルが既にあったら一旦削除 ※5
テーブルitemsを新規作成し、カラム名（項目名）id,name,priceと、それらの型を設定 ※6
3行のレコードを一つずつ挿入 ※7
コミット（保存） ※8
データベース登録したデータを全て見る ※9、10
接続を閉じて終了

PythonとSQLでテーブル作成、レコード挿入、テーブル全表示

# ※1 説明は下にあります。
import sqlite3

# ※2 
dbname = "test.db"

# ※3 
conn = sqlite3.connect(dbname)

# ※4 
cur = conn.cursor()

# ※5 
cur.execute("DROP TABLE IF EXISTS items")

# ※6 
cur.execute("""
    CREATE TABLE items(
        id INTEGER PRIMARY KEY,
        name TEXT UNIQUE,
        price INTEGER
    )
""")

# ※7 
cur.execute("INSERT INTO items (name,price) VALUES ('卵',160)")
cur.execute("INSERT INTO items (name,price) VALUES ('牛肉',780)")
cur.execute("INSERT INTO items (name,price) VALUES ('サラダ油',230)")


# ※8 
conn.commit()

# ※9 
cur = conn.cursor()
cur.execute("SELECT id,name,price FROM items")
item_list = cur.fetchall()

# ※10 
for i in item_list:
    print(i)

# ※11 
conn.close()

(1, '卵', 160)
(2, '牛肉', 780)
(3, 'サラダ油', 230)

↑ はJupyter Notebookでの実行結果。

上記コードの解説

※1 モジュールのインポート

※2 sqliteのデータベース定義。ファイル名を test.db とする。拡張子は何でもOK。

※3 接続オブジェクトを定義。connはコネクションの略（ファイルが存在しない場合はファイルが新規作成される）

※4 カーソルのオブジェクト定義。curはカーソルの略。

※5 既にテーブル名 items が存在すれば一旦削除する。既にテーブルが存在する状態で次にCREATE TABLEをするとエラーが出る為に削除した。

※6 テーブルの作成と定義

テーブル名：items
カラム名：id、型：INTEGER（整数型）PRIMARY KEY、プライマリーキーと書くとidが自動で割り振られる
カラム名：name、型：TEXT（テキスト型）
カラム名：price、型：INTEGER（整数型）

※7 データを挿入。1行ずつ3つのレコードを入れてみる。データはnameとpriceだけ入れる。idは自動で記入される。

※8 データベースにコミットする。これを書かないと保存されない。

※9 挿入したデータを抽出する。 1. カーソル定義
1. itemsテーブルからid,name,priceのカラムを選択。1つだけ書けばその列だけが取り出される。
1. fetchall()で全行を変数item_listに格納

※10 1行ずつ表示

※11 接続を閉じる

上記のデータベースに新しいデータを追記し、一覧を表示

import sqlite3

dbname = "test.db"
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# 新規データを挿入
cur.execute("INSERT INTO items (name,price) VALUES ('コショウ',120)")
conn.commit()

# データベース一覧を表示
cur = conn.cursor()
cur.execute("SELECT id,name,price FROM items")
item_list = cur.fetchall()
for i in item_list:
    print(i)

# 閉じる
conn.close()

(1, '卵', 160)
(2, '牛肉', 780)
(3, 'サラダ油', 230)
(4, 'コショウ', 120)

今度は複数の新規データを一度に挿入

import sqlite3

dbname = "test.db"
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# 複数の新規データを挿入
data = [("米",1980),("砂糖",250),("みかん",350),("白菜",150),("ねぎ",120),("豆腐",60),("醤油",210)]
cur.executemany("INSERT INTO items (name,price) VALUES (?,?)",data)
conn.commit()

# データベース一覧を表示
cur = conn.cursor()
cur.execute("SELECT id,name,price FROM items")
item_list = cur.fetchall()
for i in item_list:
    print(i)

# 閉じる
conn.close()

(1, '卵', 160)
(2, '牛肉', 780)
(3, 'サラダ油', 230)
(4, 'コショウ', 120)
(5, '米', 1980)
(6, '砂糖', 250)
(7, 'みかん', 350)
(8, '白菜', 150)
(9, 'ねぎ', 120)
(10, '豆腐', 60)
(11, '醤油', 210)

上記のデータベースから500円以上のものだけを抽出してみる。

import sqlite3

dbname = "test.db"
conn = sqlite3.connect(dbname)
cur = conn.cursor()


# 500円以上のデータを表示 (SELECTの後ろの3つのカラム名を「*」で代替した。カラムをすべて選択)
cur.execute("SELECT * FROM items WHERE price>=500")
item_list = cur.fetchall()
for i in item_list:
    print(i)

# 閉じる
conn.close()

(2, '牛肉', 780)
(5, '米', 1980)

目次に戻る

Pandasを使って複数のCSVファイルを一挙にデータベース登録

ここからは上記とは別のプログラムを作ります。
今から作成するPython スクリプトと同じディレクトリの中に複数のファイルが保存してあるとします。
それらの中のCSVファイルだけを抜き出し、Pandasで一気にSQLiteに書き込んでいきます。
Pandasを使うので、事前にインストールが必要です。＞ pip install pandas

注）CSVファイルはカラム数や項目など全てそろえて同じ形式で書き込まれていなければ挿入の際にエラーが出ます。

複数のCSVファイルは在庫管理表のようなものが記入されており、それをデータベースに移し替える作業を想定する。各ファイルには商品番号,商品名,製造メーカー,卸元,単価,在庫数量,倉庫番号の7つの項目で、
データが複数行入っているとします。
さらに、先頭行に項目名は入っていないとします。

pandasでCSVを連続書き込み

import glob
import csv
import sqlite3
import pandas as pd

# ※1
lst = glob.glob("./*.csv")
# ※2
print(lst)

# ※3
dbname = "zaiko.sqlite"
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# ※4
cur.execute("CREATE TABLE IF NOT EXISTS zaiko_ichiran")

# ※5
for i in lst:
    
    # ※6
    df = pd.read_csv(i,header=None)
    
    # ※7
    df.columns = ["商品番号","商品名","製造メーカー","卸元","単価","在庫数量","倉庫番号"]

    # ※8
    df.to_sql('zaiko_ichiran' , conn , if exists = "append",index = False)
    
    # ※9
    print(i,"Done")
    conn.commit()
    
conn.close()

上記コードの説明

※1 作業ディレクトリ内のCSVファイルをすべて検索してリストにする。

※2 リストの一覧表示。

※3 ファイル名が zaiko.sqlite のSQLiteに接続。

※4 テーブル名 zaiko_ichiran（在庫一覧）が存在しなければ新規作成。

※5 CSVファイルを一つずつ取り出す。

※6 ヘッダーのない（先頭行に項目名＝カラム名の入っていない）CSVファイルをで読み込む。

※7 読んだCSVデータにカラム名を指定する。

※8 読み込んだCSVデータをsqlデータベースに追記で書き込む。インデックスはこの場合は無しに。

※9 書き終えた事を表示する。

上記で書き込んだデータベースを読む

import sqlite3
import pandas as pd

dbname = "zaiko.sqlite"
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# ※1
df = pd.read_sql('SELECT *FROM zaiko_ichiran',conn)
print(df)

# ※2
for row in cur.execute('SELECT * FROM zaiko_ichiran WHERE "在庫数量" <=5'):
    print(row)
    print("=============")
    
# ※3
for row in cur.execute('SELECT "倉庫番号",count(*) FROM zaiko_ichiran WHERE "在庫数量" >= 200 GROUP BY "倉庫番号"')：
    print(row)
    print("=============")
    
    
conn.close()

上記解説

※1 pandasのpd.read_sqlを使ってデータベースのテーブル名 zaiko_ichira を読込。書き出し。

※2 テーブル zaiko_ichira からすべてのカラムを選択し、在庫数量が5個以下のレコードを1行ずつ表示。

※3 テーブル zaiko_ichira のうち在庫数量が200以上のレコードを倉庫毎にカウント。
倉庫1には5品、倉庫2は10品、倉庫3は6品なら（1,5)(2,10)(3,6)が縦に表示される。

おまけ。pandasでcsvを書き込む。

pandasのDataFrameを使って新規作成 01.csv にデータを書き込む場合は以下のように。
Excelを使って入力してCSVファイルに変換した方が早そうです。

import pandas as pd

df = pd.DataFrame([["001","ささパンダ","株式会社竹富","いかした商事","250","50","1"],
                 ["002","しーしーオマル","(有)白鳥","(有)白鳥","8500","8","3"],
                 ["003","スースー冷却枕","北枕社","仏霊園","980","45","3"]])

df.to_csv("01.csv")  # windowsのexcel等で開く場合は df.to_csv("01.csv",encoding="shft_jis")

テーブルの取得

追記。テーブル一覧の取得方法。
リンクで済ませるテスト。
Python - sqlite3でテーブル一覧取得 - sushi.py

おわりに

ネット検索した感じ、PythonとSQLiteの組み合わせはそれほど多く記事が出ていないようです。
SQLの構文解説か、SQLiteをコマンドラインで動かす方法はそこそこある。
pandasを使ってとなると更に少ない。

DBOnline様をいろいろ参考にさせて頂きました。ありがとうございました。

追記 Android スマホのQpythonでsqliteの動作確認しました。
Python スクリプトをscripts3のフォルダに入れて保存したんですが、データベースのファイルはqpythonフォルダに自動的に保存されました。
(カレントディレクトリがデフォルトでqpythonになっているからだと思いますが。)

それからtermux上のPythonでも難なく動きます。

よちよちpython

独習 python/Qpython/Pydroid3/termux/Linux

データベース PythonでSQLiteをつかってみる