Pythonを使ってSQLiteデータベースを操作
Pythonを使ってSQLiteデータベースへの接続方法と各種SQLコマンドの実行方法を確認します。
本文書での動作確認はすべてmacOS上で行っているためすぐにSQLiteデータベースを使うことができるためPythonを使ってちょっとしたデータベース処理を行い時に活用することができます。
Pythonに限らずSQLiteはさまざまな言語で利用できるデータベースで、MySQLやPostgreSQLと同じリレーショナルデータベースです。
目次
SQLiteデータベースの作成
SQLiteデータベースはファイルベースのデータベースなのでファイルにデータを保存します。利用するためにはデータベースファイルを作成する必要があるため、touchコマンドでSQLiteデータベース用のファイルを作成します。ここではsqlite.dbとしていますが、任意の名前をつけてください。touchコマンドで作成しているのでファイルの中身は空です。
$ touch sqlite.db
データベース接続とテーブル作成
データベースへの接続
pythonでは標準ライブラリにSQLiteデータベースに接続するためのsqlite3モジュールが入っているので、モジュールをimportしてconnectメソッドを利用することでSQLiteデータベースに接続することができます。connectメソッドの引数にはSQLiteデータベースのファイルパスを設定します。
sqlite.dbを作成したディレクトリと同じディレクトリにmain.pyファイルを作成します。
import sqlite3
conn = sqlite3.connect('sqlite.db')
データベースへの接続ができたら、cursorメソッドでcursorインスタンスを作成します。
c = conn.cursor()
テーブルの作成
cursorインスタンスが持つexecuteメソッドを使ってデータベースにテーブルを作成します。データベースの操作を行うSQL文はexecuteメソッドの中に記述します。
c.execute("create table users (id integer PRIMARY KEY, name text NOT NULL)")
テーブルを作成するためのSQL文は以下の形をしています。
create table テーブル名 (列名、列名、….)
今回作成したテーブルの名前はusersでidとnameという2つの列を持っています。
usersテーブルのid列はプライマリキー制約でデータタイプIntegerを指定しています。プライマリキー制約を設定しているためid列に同じ値を入れることはできません。
name列にはデータタイプtextを指定しています。NOT NULL制約でNULL値を入れれないようにしています。名前に値がないとデータの挿入はできません。
closeメソッドを利用することで接続しているデータベースとの接続を切ることができます。接続して処理が完了した後は切断処理を忘れずに行いましょう。
conn.close()
一連のコードを一つにまとめると以下のようになります。実行するとusersテーブルが作成されます。
import sqlite3
conn = sqlite3.connect('sqlite.db')
c = conn.cursor()
c.execute("create table users (id integer PRIMARY KEY, name text NOT NULL)")
conn.close()
作成したPythonファイルの実行はpythonコマンドで実行してください。
% python3 main.py
テーブルの作成確認
実際にSQLiteデータベースにusersテーブルが作成できているか確認してみましょう。pythonではなくsqlite3コマンドを利用して確認してみます。
sqlite3コマンドでsqlite.dbに接続後.tableコマンドを実行し、作成されたusersテーブルが表示されればpythonからのテーブル作成は正常に完了しています。
$ sqlite3 sqlite.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .table
users
データの挿入
データの挿入はinsert文を使って行います。
c.execute("insert into users(id,name) values(1,'John Smith')");
データを挿入するSQLは以下の形をしています。
insert into テーブル名(列名、列名、…) values(値、値、…)
insert文を実行した後は、commitメソッドを実行する必要があります。テーブルへの挿入がcommitによりテーブルに反映されます。commitメソッドを実行しないとテーブルへの挿入は行われません。
conn.commit()
テーブルの列の数と挿入する値の数が一致する場合は、下記のようにテーブルの列名を省略することができます。
c.execute("insert into users values(2,'John Doe')");
またプレースホルダーを使うと下記のように記述することができます。1つ目の?が3、2つ目の?がSteve Arnottに対応します。通常はこのプレースホルダーを利用してinsert文を実行します。値はタプルを使って渡します。
c.execute("insert into users values(?,?)",(3,'Steve Arnott'));
実際に動作確認を行った一連のコードは下記の通りです。
import sqlite3
conn = sqlite3.connect('sqlite.db')
c = conn.cursor()
c.execute("insert into users values(?,?)",(3,'Steve Arnott'));
conn.commit()
conn.close()
制約エラー
テーブル作成時にid列にPRIMARY KEY制約、name列にNOT NULL制約を設定しました。
実際に同じidのデータを挿入するとどうなるか確認しましょう。
insertを実行すると下記のユニーク制約エラーが発生します。
sqlite3.IntegrityError: UNIQUE constraint failed: users.id
PythonではNULL値はNoneなので、nameにNoneを入れて挿入すると以下のNOT NULL制約のエラーが発生します。
sqlite3.IntegrityError: NOT NULL constraint failed: users.name
複数のデータ挿入
これまではinsertで1行ずつデータを挿入していましたが、プレースホルダとタプルのリストを利用すると複数のデータを1つのメソッドで一度に挿入することができます。
data = [
(4,'Kate Spade'),
(5,'Harry Bosch'),
(6,'Scott Walker '),
]
c.executemany("insert into users values(?,?)",data);
データの取得(一括)
insertを利用して挿入したデータをselect文を使って取得します。
c.execute("select * from users")
result = c.fetchall()
print(result)
select * from usersのアスタリスクはすべての列の情報を意味します。今回はusersテーブルに2列しかないため、下記のように記述してもそれほど大変ではありません。
select id, name from users
しかし、何十列もあるテーブルからデータを取得する際にすべての列を記述するのは効率的ではありません。そのような時に特に*(アスタリスク）は非常に役に立ちます。
fetchallメソッドを使うとselect * from usersで取得できたデータを一括で取り出すことができます。
[(1, 'John Smith'), (2, 'John Doe'), (3, 'Steve Arnott'), (4, 'Kate Spade'), (5, 'Harry Bosch'), (6, 'Scott Walker ')]
データ取得(個別)
fetchallではすべてのデータを取り出すことができましたが、1件ずつ取り出す場合はfor文を使って行うことができます。
c.execute("select * from users")
for row in c:
print('ID:' + str(row[0]))
print('NAME:' + row[1])
ID:1
NAME:John Smith
ID:2
NAME:John Doe
ID:3
NAME:Steve Arnott
ID:4
NAME:Kate Spade
ID:5
NAME:Harry Bosch
ID:6
NAME:Scott Walker
また個別にデータを取得できるfetchoneメソッドもあります。fetchoneの場合は1件毎に取り出すためwhile文を利用します。値がなくなるとfetchoneの結果はNULL値のNoneになることを利用しています。
c.execute("select * from users")
while True:
result = c.fetchone()
if result is None :
break
print(result))
結果はタプルで取得できます。
(1, 'John Smith')
(2, 'John Doe')
(3, 'Steve Arnott')
(4, 'Kate Spade')
(5, 'Harry Bosch')
(6, 'Scott Walker ')
データの更新
データの更新はupdate文を使って行います。更新する値と検索に使う値はプレースホルダーを利用することができます。
update文を実施した後は、commitメソッドでデータベースへの反映が行われます。
c.execute("update users set name = ? where id = ?",('Tommy Hilfiger',5))
conn.commit()
データの削除
データの削除はdelete文を使って行います。
delete文を実施した後は、commitメソッドでデータベースへの反映を行います。
c.execute("delete from users where id = ?",(5,))
conn.commit()
(5,)のコンマを入れずに実行するとValueError: parameters are of unsupported typeのエラーが発生して実行できません。(5)だと数値とみなされるためです。(5,)だとタプルになるため実行することができます。
テーブルの削除
テーブルの削除はdrop table文を使って行います。drop tableの場合はcommitメソッドは必要ありません。
c.execute("drop table users")
