SQLite 簡易サーバー １

先の記事「データベースどうしよう？」で「今は使っていないRasPi 3B と USBメモリーで試してみます。」と書いた件、外出自粛の連休を使ってやってみました。

都合により、Raspberry Pi 3B+ とUSBメモリーでの実験です。

Phthon で先程動いたばかりで、簡単にINSERT, UPDATE, SELECT を試しただけですが、とりあえず期待した通りに動いています。どの程度のアクセス頻度まで使えるのかなど、追実験が必要だと思うので、タイトル末尾を１としました。

sqlite3 のインストールから始め、Webで検索して複数のサイトを参考に進めましたが、個々のサイト情報は省きます。
大変お世話になりました。ありがとうございます。

SQLite をローカルで使ってみる

本来の使い方です。SQL文を投げて使い方を勉強しました。

terminal で データベースとテーブルを作成して、INSERT, SELECT, UPDATE, DELETE を試し、DROP でテーブルを削除出来ることを知りました。

続いて、Python3 で試しますが、今のところ INSERT, SELECT, UPDATE が出来れば他は terminal や DB Browser for SQLite で十分と判断して作成に取り掛かりました。

MySQL を使ったことはあるのですがSQL文に馴染んでいないので、一歩づつ確かめながらの作業でした。

実験に使ったコード

実験は自宅のLANに接続した次の機材で行いました。
　簡易サーバー　Raspberry 3B+ と USBメモリー
　　　　　　　　ヴォリューム：’SQLite' 　フォーマット：exFat
　　　　　　　　DB名：T_sens.db　table名：temp
　　　　　　　　固定IP：192.168.11.202　port：50007
　クライアント　Raspberry Pi 4B

試してみる場合は続報「SQLite 簡易サーバー ２」もご覧ください。

簡易サーバー側のコード

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# socket サーバを作成

import socket
import sqlite3
dbname = '/media/pi/SQLite/DBs/T_sens.db'

# AF = IPv4 という意味
# TCP/IP の場合は、SOCK_STREAM を使う
with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
   # IPアドレスとポートを指定
s.bind(('192.168.11.202', 50007))
   # 1 接続
   s.listen(1)
   # connection するまで待つ
   while True:
       # 誰かがアクセスしてきたら、コネクションとアドレスを入れる
       sock, addr = s.accept()
       with sock:
           while True:
               # データを受け取る
               data = sock.recv(1024)
               if not data:
                   break
               d = data.decode('utf8')
               print(d)
               try:
                   conn = sqlite3.connect(dbname)
                   cur = conn.cursor()
                   
                   # 受信したSQL文をSQLiteに渡す
                   cur.execute(d)
                   
                   if d[:6] == 'SELECT' or d[:6] == 'select':
                       # レスポンスを取得するfetchall()
                       res = cur.fetchall()
                   else:
                       res = [(0,)]
                        
                   conn.commit()
                   cur.close()
                   conn.close()
               except:
                   res = [('Error',)]
                   
               # クライアントにデータを返す(b -> byte でないといけない)
               # list の要素がタプルなので list に変換
               if len(res) == 1:
                   out = list(res[0])
               else:
                   out = ['',] * len(res)
                   for i in range(len(res)):
                       out[i] = list(res[i])
               
               print(out)
               
               sock.sendall(bytes(str(out).encode('utf8')))

安全の為、INSERT, SELECT, UPDATE 以外は使えないように制限した方が良いかもしれませんね。

クライアント側のコード

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# クライアントを作成

import socket
addr = '192.168.11.202'
port = 50007

with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
   # サーバを指定
   s.connect((addr, port))
   # サーバにSQLコマンドを送る
   cmd = 'SELECT * FROM temp'
   #cmd = 'SELECT T0 FROM temp where time = "11:40:10"'
   #cmd = 'SELECT T0 FROM temp where time = "11:40:10"'
   #cmd = 'INSERT INTO temp(date,) values("200506",)'
   #cmd = 'INSERT INTO temp(date,time, T0, hum) values("200507", "10:15:56", 14.8, 42.9)'
   #cmd = 'UPDATE temp  SET T0 = 29.6 where time = "11:40:10"'
   
   cmd = cmd.encode('utf8')
   print(cmd)
   #s.sendall(bytes(cmd))
   s.sendall(cmd)
   # バッファサイズは1024。サーバからの文字列を受信
   data = s.recv(1024)
   #
   print(data.decode('utf8'))
   #print(data)

中間付近の cmd = の行のコメントをON/OFFしてSQL文を選択して試しました。ここにあるものは全て動作し、テーブルに反映されました。
byte変換、decode等、理解不足の面もあって、エラーが出るとコメントON/OFFして試している跡が残っています。

今後の予定

SQLite は複数同時書き込みに対応していないため、高頻度でアクセスすると何等か支障が出ると思います。（限界を確かめてそれなりに使う予定です。）
一方で、データベースファイル（複数のテーブルを含む）が１ファイルなのでバックアップやメンテが容易で、万一故障の際にも対応が簡単だと思います。負荷が大きいなら複数のRasPiに分散することも容易そうです。

MySQLのように万一の時にはシームレスに（自動で）サーバーが入れ替わるなんてことは到底無理ですが、小規模用途には「SQLite 簡易サーバー」で十分かも知れません。

引き続き検証を進めます。（個人用途で稼働させながらですが・・・）
続報があります。「SQLite 簡易サーバー ２」（5/10日追記）

今後とも宜しくお願い致します。