Pythonを使ってsqliteデータベースへの接続方法と各種SQLコマンドの実行方法を確認します。

下記の処理はすべてMAC OS上で行っているためすぐにsqliteデータベースを使うことができるためPythonを使ってちょっとしたデータベース処理を行い時に活用することができます。

sqliteデータベースの作成

touchコマンドでsqliteデータベースを作成します。ここではsqlite.dbとしていますが、任意の名前をつけてください。touchコマンドで作成しているのでファイルの中身は空です。


$ touch sqlite.db

データベース接続とテーブル作成

データベースへの接続

pythonでは標準ライブラリにsqliteデータベースに接続するためのsqlite3モジュールが入っているので、モジュールをimportしてconnectメソッドでsqliteデータベースに接続することができます。connectメソッドにはsqliteデータベースのファイルパスを設定します。


import sqlite3

conn = sqlite3.connect('sqlite.db')
connectで指定したファイルが存在しない場合は、新たに指定した名前のファイルが作成されエラーになりません。

データベースへの接続ができたら、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を指定しています。プライマリキー制約なので同じ値を入れることはできません。

name列にはデータタイプtextを指定しています。NOT NULL制約でNULL値を入れれないようにしています。名前に値がないとデータの挿入はできません。

プログラミングに慣れている人であればデータタイプ等の用語が出ても難しくはないかと思います。しかし入門書にとっては少しハードルが高いかもしれません。もしわからなければデータタイプを設定しなくてもsqliteのテーブルは作成できます。データを挿入した時にsqliteがデータタイプを判別して挿入してくれます。

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()

テーブルの作成確認

実際に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")