pythonでのsqlite3データベースの扱い

sqlite3は、DBごとに一つのファイルとして作成するため可搬性がよいが、それだけにシンプルなため、扱うGUIツールとしてはsqlitebrowserしかないようだ。

個々のパソコン等にsqlite3とsqlitebrowserをインストールすれば、sqlite3のファイルを扱える。

Wi-Fiプローブデータのうち、今のところ、2地点間流動（flow）データのみsqlite3に入れてある。

kofuのデータはすでに4GB程度に膨れ上がっているので、パソコン等にコピーして扱うのが不便である。そのため、サーバ上においてあるDBにpythonプログラムでアクセスするのが楽である。

以下ではその手始めのことを書く。

より実施的なクエリは

https://8tops.yamanashi.ac.jp/~toyoki/labTips/flow/kofu_sqlite.html

https://8tops.yamanashi.ac.jp/~toyoki/labTips/flow/kofu_travel_time_dist_sqlite.html

あたりを見てほしい。

バス運行解析ではもっと大規模なデータベース(MySQL)をあつかうことになる。MySQLはネットワーク越しにアクセスするのでセキュリティ管理が重要である。そのため、アクセスの仕方は、Google Driveの方に載せてある。

コマンドラインツールでsqlite3にアクセスする

どのデータベースにも対話的にDBにアクセスするCUI（コマンドラインツール）が存在する。

sqlite3ではそのままsqlite3というツール（それ自体がDBソフトの名前）がある。必要に応じて各自インストールして利用してみる。

サーバには、すでに入っている。（Raspberry Piにも基本ツールとして入っている。）

使い方はWeb上にたくさんある。

pythonでの基本的な扱い

  • DBにコネクトする
  • 一つ一つのコマンドを渡して結果を得るためのobject(CUIに対応)を生成する：cursor()
  • cursor.execute()にsql文を渡して結果をえる。
  • 結果（データ）の取り出し関数はfetchall()
    • ほかにもcursorにはメソッドがあるが、たぶん、以上で十分だろう。
In [1]:
import csv
import sqlite3
from datetime import datetime as dt
from datetime import timedelta
import pandas as pd

db_file = "/home/raspimngr/db/kofu_traveler.sqlite3" # ファイルの場所はそれぞれの環境に応じて変える
conn = sqlite3.connect(db_file) # DBへの接続
cur = conn.cursor() # コマンドライン的なオブジェクト

def query(sql_str):
    cur.execute(sql_str)
    return cur.fetchall()

sqlite3固有のコマンド

In [2]:
# テーブルの一覧をみる (どのようにしてcreateしたかも全部出力される)
query('select * from sqlite_master WHERE type="table"')
Out[2]:
[('table',
  'holidays',
  'holidays',
  2,
  'CREATE TABLE `holidays` (\n`yearday`TEXT NOT NULL\n)'),
 ('table',
  'flow',
  'flow',
  3,
  'CREATE TABLE `flow` (\n`yearday`TEXT NOT NULL,\n`hour`TEXT NOT NULL,\n`origin`TEXT NOT NULL,\n`destination`TEXT NOT NULL,\n`number`INTEGER NOT NULL,\n`glbit`INTEGER NOT NULL,\nconstraint flow1 unique (yearday, hour, origin, destination, glbit)\n)'),
 ('table',
  'flow_trunc10',
  'flow_trunc10',
  5,
  'CREATE TABLE `flow_trunc10` (\n`yearday`TEXT NOT NULL,\n`hour`TEXT NOT NULL,\n`origin`TEXT NOT NULL,\n`destination`TEXT NOT NULL,\n`number`INTEGER NOT NULL,\n`glbit`INTEGER NOT NULL,\nconstraint flow1 unique (yearday, hour, origin, destination, glbit)\n)'),
 ('table',
  'traveler',
  'traveler',
  7,
  'CREATE TABLE `traveler` (\n`addr`TEXT NOT NULL,\n`origin`TEXT NOT NULL,\n`destination`TEXT NOT NULL,\n`dDate`TEXT NOT NULL,\n`aDate`TEXT NOT NULL,\n`dTime`TEXT NOT NULL,\n`aTime`TEXT NOT NULL,\n`glBit`INTEGER NOT NULL,\nconstraint trip unique (addr, origin, destination, dDate, dTime)\n)'),
 ('table',
  'flow_all',
  'flow_all',
  9,
  'CREATE TABLE `flow_all` (\n`yearday`TEXT NOT NULL,\n`hour`TEXT NOT NULL,\n`origin`TEXT NOT NULL,\n`destination`TEXT NOT NULL,\n`number`INTEGER NOT NULL,\n`glbit`INTEGER NOT NULL,\nconstraint flow1 unique (yearday, hour, origin, destination, glbit)\n)'),
 ('table',
  'flow_all_trunc10',
  'flow_all_trunc10',
  11,
  'CREATE TABLE `flow_all_trunc10` (\n`yearday`TEXT NOT NULL,\n`hour`TEXT NOT NULL,\n`origin`TEXT NOT NULL,\n`destination`TEXT NOT NULL,\n`number`INTEGER NOT NULL,\n`glbit`INTEGER NOT NULL,\nconstraint flow1 unique (yearday, hour, origin, destination, glbit)\n)'),
 ('table',
  'traveler_all',
  'traveler_all',
  13,
  'CREATE TABLE `traveler_all` (\n`addr`TEXT NOT NULL,\n`origin`TEXT NOT NULL,\n`destination`TEXT NOT NULL,\n`dDate`TEXT NOT NULL,\n`aDate`TEXT NOT NULL,\n`dTime`TEXT NOT NULL,\n`aTime`TEXT NOT NULL,\n`glBit`INTEGER NOT NULL,\nconstraint trip unique (addr, origin, destination, dDate, dTime)\n)'),
 ('table',
  'sensor',
  'sensor',
  15,
  'CREATE TABLE `sensor` (\n`id`TEXT,\n`lat`REAL,\n`lon`REAL,\n`locationName`TEXT NOT NULL,\n`area`TEXT,\n`shortName`TEXT,\nPRIMARY KEY(id)\n)')]
In [3]:
# テーブルの最初の5行だけ表示 (内容の確認ができる)
query("select * from flow_all limit 5")
Out[3]:
[('2018-08-28', '10', '19', '10', 1, 0),
 ('2018-08-28', '10', '19', '19', 1, 0),
 ('2018-08-28', '10', '19', '3', 1, 0),
 ('2018-08-28', '11', '10', '10', 12, 0),
 ('2018-08-28', '11', '10', '17', 1, 0)]
In [4]:
# 列の名前を取得する  execute関数の返値は列名と値のセットなので列名のみは次のようにして出力
# 上で定義したquery関数はデータのみを返す

result = cur.execute("select * from flow_all limit 5")
# result.description # descriptionの構造を確認
list(map(lambda x: x[0], result.description))
Out[4]:
['yearday', 'hour', 'origin', 'destination', 'number', 'glbit']
In [ ]: