【Sqlite3】PythonでSqlite3を操作する

Pythonで気軽に導入できるデータベースとして、Sqlite3があったのでこちらを使用してみようと思い、その備忘録を記載します。

今回は基本的な構文のみ使用しているため、カラムの制約や複雑なクエリなどは紹介していません。ただし、SQLの一連の流れとして

• データベースと接続
• テーブルの作成
• テーブルの名前を変更
• テーブルのカラムを追加
• テーブルにレコードを追加
• テーブルのレコードの値を変更
• テーブルのレコードを削除
• テーブルのカラムを削除
• テーブルの削除

を最終的なまとめとして紹介します。

本記事はPythonのSqlite3チュートリアルとSqliteホームページを参考にしています。

1. Databaseの作成

PythonでSqlite3を使用するにあたり、sqlite3モジュールを使用します。ただし、Pythonの標準モジュールのため特にインストールの必要はありません。

データベースと接続するには、sqlite3#connectメソッドを使用します。このとき、引数でデータベース*.dbを指定しますが、データベースが存在しない場合は暗黙的に作成されます。

データベース接続

import sqlite3

con = sqlite3.connect("Examples.db")

さて、データベースに対して何か処理を実行するためにはデータベースカーソルを使用する必要があります。そのため、以下のようにカーソルオブジェクトを生成します。

cur = con.cursor()

今回の記事では次のメソッドを用いて、SQLを実行します：

• Cursor#execute()
• Cursor#executemany()

それぞれ1回、複数回クエリを実行することができます。

また、本記事ではあまり着目していませんが、レコードの登録といったテーブルへの変更が行われた際にその変更を確定させるconnect#commit()メソッド：

# INSERT文実行
con.commit()

やデータベースの接続に対して、データベース接続を終了するconnect#close()メソッド：

con = connect("MyDatabase.db")
# Any Process
con.close()

なども紹介すべき点です。（記事のコード内には記載していませんが...）

2. テーブルの作成

テーブルを作成するSQLは次の通り：

CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"

今回作成するカラムのデータタイプとPythonのデータタイプの対応関係は次のテーブルの通り：

Python	Sqlite
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

さて、テーブルがデータベース内で作成されたことをsqlite_master内にテーブル名exampleが格納されたことから確認します：

テーブルの確認

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
SELECT_NAME_FROM_MASTER = "SELECT name FROM sqlite_master;"

# クエリ実行
cur.execute(CREATE_TABLE)
res = cur.execute(SELECT_NAME_FROM_MASTER)
print(res.fetchone())

ここで、クエリの実行結果を取得する際に

• Cursor#fetchone()
• Cursor#fetchAll()

を使用します。それぞれ１つ、複数の結果を取得します。また、しれっとIF NOT EXISTSを含めているので、テーブルexampleがすでにデータベースに存在する場合は、テーブルが作成されません。なお戻り値はタプル（タプルのリスト）であることに注意してください。

上記クエリを実行すると、ターミナル上に

('example',)

と出力されます。このことからデータベースにテーブルexampleが作成されたことが確認できました。なお、テーブルが存在しない場合はNoneが返されます。

3. テーブルにレコードを追加

テーブルexampleにレコードを追加するクエリは次の通り：

INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"

テーブルexampleのカラム数は5つなので、値は5つ格納できるように定義しています。ここで、?はパラメータのバインドのためプレースホルダーです。こちらはSQLインジェクションの対策のために推奨されている方法です。SQLインジェクションについては詳しく説明しませんが、例えば次のようなコードに注意が必要です：

SQLインジェクションの例

symbol = input()
sql = f"SELECT * FROM stocks WHERE symbol = '{symbol}'"
print(sql)
cur.execute(sql)

このコードでsymbolに

' OR TRUE; --

と入力すると、実行されるSELECT文は

SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'

となります。WHERE句は常にTRUEとなるのでテーブルのレコードが取得できてしまいます。このようなSELECT文がパスワードのような誰でも取得されてはいけない情報について使用されていたらまずいですよね。ざっくりこんな感じの問題です。

さて、プレースホルダー?の話に戻ると、パラメータは

cur.execute('SQL includes placeholder', 'Parameter: tuple')

のように第2引数に与えます。このとき、パラメータがタプルであることに注意してください。（リストでもOK）

以上のことから、テーブルにレコードを追加するコードは次のようになります：

テーブルにレコード追加

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"
SELECT_TYPE_OF_COLUMNS = "SELECT typeof(t1), typeof(t2),typeof(t3),typeof(t4),typeof(t5) FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'), # str
    (500.0, 500.0, 500.0, 500.0, 500.0), # float
    (500, 500, 500, 500, 500), # int
    (b'0500', b'0500', b'0500', b'0500', b'0500'), # bytes
    (None, None, None, None, None), # None
]

# クエリ実行
cur.execute(CREATE_TABLE)
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
res_type = cur.execute(SELECT_TYPE_OF_COLUMNS)
for type in res_type:
    print(type)
res_value = cur.execute(SELECT_VALUE_OF_COLUMNS)
for value in res_value:
    print(value)

このコードを実行した結果はターミナルに以下のように表示されます。

SELECT_TYPE_OF_COLUMNS

('text', 'integer', 'integer', 'real', 'text')
('text', 'integer', 'integer', 'real', 'real')
('text', 'integer', 'integer', 'real', 'integer')
('blob', 'blob', 'blob', 'blob', 'blob')
('null', 'null', 'null', 'null', 'null')

SELECT_VALUE_OF_COLUMNS

('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)

ここでもう一度、データタイプ対応表です。

Python	Sqlite
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

テーブルexampleのデータ型は

(t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB)

だったことを思い出しますと、データタイプはデータに応じてその型を変えています。わかりやすいのが、BytesデータとNoneデータですね。それぞれBLOBとNULLとなっていることがわかります。

このようにSqliteではデータ型がざっくりとしているわけですが、これはSqliteの柔軟な型付け(flexible typing)機能で説明があるので参照してみてください。

以上までにレコードの追加ができました。

4. テーブルのレコードの値を変更

テーブルexampleのレコードの値を変更するためのクエリは次の通り：

UPDATE_RECORDS_BY_COLUMN_VALUE = "UPDATE example SET t1 = ? WHERE t2 = ?;"

上記のクエリはテーブルのカラムt2の値によってt1の値を変更します。例えば、現在のレコードには500という値が含まれていますのでt2=500のときに、t1='Changed'と変更することができます。この例をコードに直すと、次の通り：

レコードの値を更新

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
UPDATE_RECORDS_BY_COLUMN_VALUE = "UPDATE example SET t1 = ? WHERE t2 = ?;"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# クエリ実行
cur.execute(UPDATE_RECORDS_BY_COLUMN_VALUE, ("Changed", 500, ))
res_value = cur.execute(SELECT_VALUE_OF_COLUMNS)
for value in res_value:
    print(value)

上記コードを実行するとターミナルには以下のように表示されます：

SELECT_VALUE_OF_COLUMNS

('Changed', 500, 500, 500.0, '500.0')
('Changed', 500, 500, 500.0, 500.0)
('Changed', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)

t2の値が500となっているレコードが3つあるので、対応するレコードのt1の値が'Changed'という値に変更されていることがわかります。

5. テーブルのレコードを削除

そろそろテーブルexampleの値が変更されている、またはINSERT文を何度も使用しているとレコード数が増えすぎてしまっている状態かと思います。
なので、レコードを消去していきます。レコードをすべて削除するクエリは次の通り：

DELETE_ALL_RECORDS = "DELETE FROM example;"

上記クエリを実行するコードが

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"
DELETE_ALL_RECORDS = "DELETE FROM example;"

# クエリ実行
cur.execute(DELETE_ALL_RECORDS)
res_value = cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall()

if not res_value:
    print("Records Deleted")
else:
    print("Records Exist")

となります。正常にレコードが削除された場合res_valueは空のリストとなるはずですので、ターミナルは次のように表示されているはずです：

リストが空かどうか

Records Deleted

次に、全レコードの削除ではなく、あるカラムの値によって削除するクエリを試します：

DELETE_RECORD_BY_COLUMN_VALUE = "DELETE FROM example WHERE t3 = ?;"

上記のクエリはt3の値とパラメータが一致しているレコードを削除します。今回はt3の値としてb'0500'とした場合で考えます。コードは次の通り：

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
DELETE_RECORD_BY_COLUMN_VALUE = "DELETE FROM example WHERE t3 = ?;"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'), # str
    (500.0, 500.0, 500.0, 500.0, 500.0), # float
    (500, 500, 500, 500, 500), # int
    (b'0500', b'0500', b'0500', b'0500', b'0500'), # bytes
    (None,None,None,None,None), # None
]

# クエリ実行
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
cur.execute(DELETE_RECORD_BY_COLUMN_VALUE, (b'0500', ))

res_value = cur.execute(SELECT_VALUE_OF_COLUMNS)
for value in res_value:
    print(value)

上記コードを実行すると、4つ目のレコードが削除されて、

SELECT_VALUE_OF_COLUMNS

('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(None, None, None, None, None)

となります。

6. テーブルの名前を変更

今度はテーブルの名前を変更してみます。テーブル名の変更を行うクエリは次の通り：

ALTER_TABLE_RENAME = "ALTER TABLE example RENAME TO renamed;"

上記クエリではテーブルexampleの名前を変更します。クエリを試すコードは次の通り：

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
ALTER_TABLE_RENAME = "ALTER TABLE example RENAME TO renamed;"
SELECT_MASTER_TABLE = "SELECT name FROM sqlite_master;"
SELECT_MASTER_TABLE_EXIST = "SELECT name FROM sqlite_master WHERE TYPE = 'table' AND name = ?;"

# クエリ実行
res = cur.execute(SELECT_MASTER_TABLE_EXIST, ("renamed", )).fetchone()
if res is None:
    cur.execute(ALTER_TABLE_RENAME)

cur.execute(CREATE_TABLE)
for table in cur.execute(SELECT_MASTER_TABLE).fetchall():
    print(table)

ここで、ALTER TABLEの構文の中にIF NOT EXISTSにあたる構文がなかったので、こちらの記事を参考にしてIF文で代用しました。
上記コードを実行すると、テーブルexampleをテーブルrenamedに変更した後に、exampleを新たに生成しているので、ターミナルは次のように表示されているはずです：

('renamed',)
('example',)

7. テーブルにカラムを追加

現在のテーブルexampleは5カラムが定義されています：

(t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB)

このテーブルに新たに

t6 TEXT

を追加していきます。そのクエリが次の通り：

ALTER_TABLE_ADD_COLUMN = "ALTER TABLE example ADD COLUMN t6 TEXT;"

こちらを使用したコードは次の通り：

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
ALTER_TABLE_ADD_COLUMN = "ALTER TABLE example ADD COLUMN t6 TEXT;"
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
INSERT_RECORDS_ADD = "INSERT INTO example VALUES (?, ?, ?, ?, ?, ?);"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0),
    (500, 500, 500, 500, 500),
    (b'0500', b'0500', b'0500', b'0500', b'0500'),
    (None,None,None,None,None),
]

INSERT_DATAS_ADD = [
    ('500.0', '500.0', '500.0', '500.0', '500.0', '1000.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0, 1000.0),
    (None,None,None,None,None,None),
]

# クエリ実行
cur.execute(CREATE_TABLE)
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
for before in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("Before: ")
    print(before)

cur.execute(ALTER_TABLE_ADD_COLUMN)
cur.executemany(INSERT_RECORDS_ADD, INSERT_DATAS_ADD)
for after in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("After: ")
    print(after)

上記コードを実行するとターミナルは次のようになるはずです：

Before: 
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)
After:
('500.0', 500, 500, 500.0, '500.0', None)
('500.0', 500, 500, 500.0, 500.0, None)
('500', 500, 500, 500.0, 500, None)
(b'0500', b'0500', b'0500', b'0500', b'0500', None)
(None, None, None, None, None, None)
('500.0', 500, 500, 500.0, '500.0', '1000.0')
('500.0', 500, 500, 500.0, 500.0, '1000.0')
(None, None, None, None, None, None)

カラムt6を追加前のカラム数が5つであるのに対して、追加後には6つのカラムが表示されていることがわかります。なお、カラム追加前に挿入したレコードの6カラム名はNoneとなっています。

8. テーブルのカラムを削除

カラムを削除するためには、次のクエリを使用します：

ALTER_TABLE_DROP_COLUMN = "ALTER TABLE example DROP COLUMN t6"

先ほどのコードの最後の追加して実行することを考えてみます。

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
ALTER_TABLE_ADD_COLUMN = "ALTER TABLE example ADD COLUMN t6 TEXT;"
ALTER_TABLE_DROP_COLUMN = "ALTER TABLE example DROP COLUMN t6"
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
INSERT_RECORDS_ADD = "INSERT INTO example VALUES (?, ?, ?, ?, ?, ?);"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0),
    (500, 500, 500, 500, 500),
    (b'0500', b'0500', b'0500', b'0500', b'0500'),
    (None,None,None,None,None),
]

INSERT_DATAS_ADD = [
    ('500.0', '500.0', '500.0', '500.0', '500.0', '1000.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0, 1000.0),
    (None,None,None,None,None,None),
]

# クエリ実行
cur.execute(CREATE_TABLE)
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
for before in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("Before: ")
    print(before)

cur.execute(ALTER_TABLE_ADD_COLUMN)
cur.executemany(INSERT_RECORDS_ADD, INSERT_DATAS_ADD)
for after in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("After: ")
    print(after)

print("Column6 Deleted: ")
cur.execute(ALTER_TABLE_DROP_COLUMN)
for i in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print(i)

こちらを実行するとターミナルは先ほどの結果の後に

Column6 Deleted:
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
(None, None, None, None, None)

が表示されているはずです。

9. テーブルの削除

最後に、テーブルを削除するクエリは次の通り：

DROP_TABLE = "DROP TABLE IF EXISTS example;"

テーブルexampleが存在したら削除するというクエリです。これを実行し、確認するためのコードは次の通り：

import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
DROP_TABLE = "DROP TABLE IF EXISTS example;"
SELECT_MASTER_TABLE = "SELECT name FROM sqlite_master;"

cur.execute(CREATE_TABLE)
res_before = cur.execute(SELECT_MASTER_TABLE).fetchone()
print(res_before)
cur.execute(DROP_TABLE)
res_after = cur.execute(SELECT_MASTER_TABLE).fetchone()
print(res_after)

上記コードを実行すると、ターミナルは次のようになるはずです：

('example',)
None

※テーブルrenamedを放置したままの場合は、res_beforeとres_afterの取得メソッドをfetchall()に変えて出力するとよいかと思います。（消し忘れていて初めはこちらを試しました。）

まとめ

今回の記事はSqlite3をPythonで実行するというものでした。内容は基本的な構文ばかりですが、少し長くなったので一律ですべてのクエリを実行するのは次回の記事に回します。
今後は今回省略したカラムの制約やSqlite3で使用可能な関数などをまとめてみたいと思います。