参考

参考1:SQLite Home Page - SQLite公式
参考2:SQLite入門 - 入門サイト
参考3:sqlite3 — SQLite データベースに対する DB-API 2.0 インタフェース - Python公式
参考4:Pythonでsqlite - Qiita - とっかかりとして
参考5:python + sqlite3でリストを簡単に扱う - Qiita - リストを使いたい
参考6:How can I get dict from sqlite query? - Stack Overflow - 行データを辞書で欲しい

はじめに

このブログは自作のスクリプトで運用していて、これまで記事のデータベースとしてcsvを使っていたのをSQLiteに変更したのでその作業記録としてメモ。

とにかくやってみる

どういうcsvデータかというと

タイトル,ディレクトリ,日付,カテゴリ1,カテゴリ2,...

というのが並んだもので、記事ごとにカテゴリの数が不定なのでカテゴリはリストにしたいが、SQLiteの要素としてリストは入れられないというのが一つの問題でした。

そこで参考5を見つけたので以下のようなものを書いた:

import sqlite3

CREATE_TABLE = '''
create table if not exists blogdata (
title text unique,
dir text unique,
date text,
categories CATEGORYLIST
);
'''

#sqliteとpythonでリストをやりとりできるようにする
CATEGORYLIST = list
sqlite3.register_adapter(CATEGORYLIST, lambda l: ';'.join(l))
sqlite3.register_converter("CATEGORYLIST", lambda s: s.decode().split(';'))

#参考6よりデータを辞書で受けとるために定義
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description): 
        d[col[0]] = row[idx]
    return d

#db接続
conn = sqlite3.connect("database.sqlite3",
                       detect_types = sqlite3.PARSE_DECLTYPES) # リスト変換のため必須
#conn.row_factory = sqlite3.Row # イテレータで欲しいときはこれ
conn.row_factory = dict_factory # 辞書で欲しい

#カーソルを得る
cur = conn.cursor()

#SQL実行
cur.execute(CREATE_TABLE)

BLOG_DATA = []
with open('database.csv', 'r') as f:
    buf = f.read().splitlines()
    for line in buf[::-1]:
        l = line.split(',')
        BLOG_DATA.append((l[0],l[1],l[2],l[3:]))

#多くのデータに対してSQLを一挙に実行、カテゴリのリストはうまいこと処理してくれる
cur.executemany('INSERT INTO blogdata VALUES(?,?,?,?);', BLOG_DATA)

#コミットしてdbを閉じる
conn.commit()
cur.close()
conn.close()

データベースにはid integer primary keyのような項目(カラム)をつけることが多いようですが必須なのでしょうか。

解説

上の例で使い方はだいたい分かると思いますが、

  1. データベースに接続してカーソルを得る
  2. カーソルを通してSQL実行、execute, executemany
  3. 実行結果はfetchone,fetchallで得る。イテレータとしても使える。
  4. コミットして接続を閉じる(コミットしないと結果がDBファイルに反映されない)

という流れです。上のスクリプトでは3.のところはやっていないですが、例えば

cur.execute('SELECT * FROM blogdata')
data = cur.fetchall()

とすれば全データがdataにリストとして入る。fetchoneなら一つずつ取り出せる。ちなみに上では行データにdict_factoryを設定しているのでデータは辞書形式で返ってくる。だからdata[0]['title']のようにアクセスできる。

イテレータとして使うというのは

for row in cur.execute('SELECT * FROM blogdata'):
    print(row)

ということ。

注意として、参考3によるとSQL文を実行するときに(以下のcはカーソル)

c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

のようにSQL文をPythonの文字列として構築してはいけないと注意されています。これは安全ではないようで、かならず?とタプルを使うべき、つまり

c.execute("SELECT * FROM stocks WHERE symbol = ?", (symbol,))

のようにしなければいけないとのことです。参考:SQLインジェクション - Wikipedia

まとめ

PythonからSQLiteに接続してデータを検索して結果を得るというのは簡単にできることがわかりました。個人でローカルで使うにはセキュリティのことなど考えなくても良さそうですが、念の為に少し触れておきました。