SQLiteを使ってローカルにDB, テーブルを作ってデータを出し入れする

こんにちは、イルカです。

webから収集してきた情報なんかをとりあえず手軽にローカルのDBにでも保存すっか…じゃあsqlite使おう、と思うことが多いのですが、
いつもやり方を忘れるので備忘録的にここに書いておきます。

テーブル定義

まずテーブル定義の仕様を決めます。
僕の好きなものを保存するテーブルにします。

column_name	data_type	index	memo
user_id	text	primary key	ユーザを識別するID文字列
name	text	primary key	食べ物などの名前文字列
extent	integer		どれくらい好きかの程度を表す整数値

SQLiteで使えるデータ型は以下を参照。
SQLiteのデータ型 | SQLite

DBとテーブル作成

DBとテーブルをpythonのsqlite3モジュールを使って作ってみます。
SQLiteのDBのファイルはpythonスクリプトを実行したディレクトリにできるようです。

create_db_and_table.py

#!/usr/bin/python
#-*- coding:utf-8 -*-


# python2.7標準で入ってるはず
import sqlite3

db_name = "iruca_sample.db"
table_name = "favorite"
sql = "CREATE TABLE %s(user_id text, name text, extent integer, PRIMARY KEY(user_id, name))" % ( table_name )

print sql

#ローカルのDBへの接続取得
connection = sqlite3.connect(db_name)

#カーソル取得
cursor = connection.cursor()

#SQL実行
cursor.execute( sql )

#コミット
connection.commit()

#接続を閉じて終了
connection.close()

実行してテーブルを作っておきましょう。

[root@hoge hatena]# python create_db_and_table.py
CREATE TABLE favorite(user_id text, name text, extent integer, PRIMARY KEY(user_id, name))

これでDBとテーブルができました。

データの挿入と確認

本当にテーブルができたのか、テーブルからデータを出し入れして確認してみましょう。

check_table.py

!/usr/bin/python
#-*- coding:utf-8 -*-


import sqlite3

db_name = "iruca_sample.db"
table_name = "favorite"
insert_sql = """
INSERT INTO %s
VALUES
("iruca21", "oyakodon", 100),
("iruca21", "kafun", -1000000),
("kujira16", "serori", -10)
""" % table_name

select_sql = """
SELECT user_id, name, extent FROM %s
WHERE user_id="iruca21"
""" % table_name

print insert_sql
print select_sql

#ローカルのDBへの接続取得
connection = sqlite3.connect(db_name)

#カーソル取得
cursor = connection.cursor()

#データ挿入SQL実行
cursor.execute( insert_sql )

#コミット
connection.commit()

#データ取得SQL実行
cursor.execute( select_sql )

#結果を表示
result = cursor.fetchall()
print "------"
for row in result:
        print row[0], row[1], row[2]

#接続を閉じて終了
connection.close()

では、上記のスクリプトを実行して確認しましょう。

[root@hoge hatena]# python check_table.py

INSERT INTO favorite
VALUES
("iruca21", "oyakodon", 100),
("iruca21", "kafun", -1000000),
("kujira16", "serori", -10)


SELECT user_id, name, extent FROM favorite
WHERE user_id="iruca21"

------
iruca21 kafun -1000000
iruca21 oyakodon 100

うん、満足満足。