pythonでSQLite3を使う手順のメモ

pythonの標準ライブラリであるsqlite3を使ってSQLite3のDBを使う手順のメモです
https://docs.python.org/ja/3/library/sqlite3.html

1. install

標準ライブラリなのでインストールしなくても使えます

2. DBをつくる

sqlite3.connect()すると指定したPATHにあるSQLite3 DBファイルに接続します。
SQLite3 DBファイルがない場合は自動で作ってくれます。

import sqlite3
conn = sqlite3.connect('./TEST.db')
conn.close()

PATHを':memory:'にするとメモリ上にDBを作ってclose()した時点で消えるように出来ます。操作方法の確認をやりたいときに便利です。

conn = sqlite3.connect(':memory:')
conn.close()

3. table

3-1. tableを追加する

カーソルを作ってexecute()内にSQL文を渡すことで実行内容を指示できます。一通り指示してからconnectをcommit()すると変更がSQLite3 DBファイルに適用されます。下の場合、整数型のid列と文字列型のname列をもつpersonsという名前のtableを作成します。

import sqlite3
conn = sqlite3.connect('./TEST.db')

cur = conn.cursor()  # カーソルを作成
cur.execute('CREATE TABLE persons(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)')  # tableを作成する指示

conn.commit()  # commit()した時点でDBファイルが更新されます
conn.close()

tableが既にあるとエラーになりますが、IF NOT EXISTSを付けておくと存在しない場合に限り追加するようになります

cur.execute('CREATE TABLE IF NOT EXISTS persons(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)')

3-2. table一覧を取得する

table一覧を取得するにはsqlite_masterを読み込みます。読み込みはexecute()でSELECTした後にfetchall()することで実行され、listで受け取ることが出来ます。

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")
cur.execute("CREATE TABLE fuga (fuga1, fuga2, fuga3)")

cur.execute("SELECT name from sqlite_master where type='table';")
print('table一覧: ', cur.fetchall())

con.close()

3-3. tableをrenameする

ALTER TABLEで既存のtableについて変更出来ますので、table名を変更したい場合は以下のように書けます。

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")
cur.execute("CREATE TABLE fuga (fuga1, fuga2, fuga3)")

cur.execute("ALTER TABLE hoge rename to hogehoge")
cur.execute("ALTER TABLE fuga rename to fugafuga")

cur.execute("SELECT name from sqlite_master where type='table';")
print('table一覧: ', cur.fetchall())

con.close()

3-4. tableを削除する

tableの削除はDROP TABLEです

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")
cur.execute("CREATE TABLE fuga (fuga1, fuga2, fuga3)")

cur.execute('DROP TABLE hoge')

cur.execute("SELECT name from sqlite_master where type='table';")
print('table一覧: ', cur.fetchall())

con.close()

4. column

4-1. column一覧を取得する

PRAGMA TABLE_INFO(table名)でcolumn情報をlistで受け取れます

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1 STRING, hoge2 STRING)")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()

cid, name, type, notnull, dflt_valueの順で出力されます

出力

[(0, 'hoge1', 'STRING', 0, None, 0), (1, 'hoge2', 'STRING', 0, None, 0)]

4-2. columnを追加する

tableのrenameで使ったALTER TABLEコマンドでcolumnを追加することが出来ます

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")

cur.execute("ALTER TABLE hoge ADD COLUMN hoge_added")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()

4-3. columnをrenameする

SQLite3ではcolumnのrenameが出来ないので、新しくtableを作って移し替えることになります。基本的にcolumn名の変更はやらないという思想だと思います。

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")

cur.execute("ALTER TABLE hoge RENAME TO hoge_tmp")
cur.execute("CREATE TABLE hoge (hogehoge1, hogehoge2)")
cur.execute("INSERT INTO hoge (hogehoge1, hogehoge2) SELECT hoge1, hoge2 FROM hoge_tmp")
cur.execute("DROP TABLE hoge_tmp")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()

4-4. columnを削除する

削除も出来ませんので新しくtableを作って移し替えることになります

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")

cur.execute("ALTER TABLE hoge RENAME TO hoge_tmp")
cur.execute("CREATE TABLE hoge (hogehoge2)")
cur.execute("INSERT INTO hoge (hogehoge2) SELECT hoge2 FROM hoge_tmp")
cur.execute("DROP TABLE hoge_tmp")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()

5. recordを書き込む

INSERTコマンドで書き込むことが出来ます。columnが存在しても書き込まれなかった場合はdflt_valueで指定された値が入ります。また、AUTOINCREMENTなcolumnを省略して書きこむと、自動的に+1した値を入れてくれます(指定することも出来ます)。

conn = sqlite3.connect(dbname)
conn = sqlite3.connect(":memory:")

cur = conn.cursor()

cur.execute('CREATE TABLE hoge(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING, age INTEGER)')
cur.execute('INSERT INTO hoge(name, age) values("Taro", 12)')
cur.execute('INSERT INTO hoge(name) values("Hanako")')
cur.execute('INSERT INTO hoge(id, name, age) values(20, "Akiko", 2)')
cur.execute('INSERT INTO hoge(name, age) values("Jiro", 10)')

conn.commit()

cur.execute('SELECT * FROM hoge')  # hogeという名前のtableからすべて読んでくる
print(cur.fetchall())  # 上記で指定した範囲のrecordを取得する

cur.close()
conn.close()

出力

[(1, 'Taro', 12), (2, 'Hanako', None), (20, 'Akiko', 2), (21, 'Jiro', 10)]

placeholderを使う

ユーザーから受け取った値など外部から来た値を登録する場合などは、そのままINSERTコマンドに渡してしまうとSQLインジェクション攻撃される可能性が出てきます。

SQLインジェクション攻撃に無防備な書き方

cur.execute('INSERT INTO hoge(name, age) values({}, {})'.format(name_input, age_input))

placeholderにする事でこれを回避することが出来ます。placeholderとして受け取った値にコマンドが書いてあっても文字列として扱ってくれますのでSQLインジェクションが働かなくなります。以下のように挿入したい場所に?を書いておいて、execute()の第2引数にtupleでplaceholderの値を渡します。

推奨される書き方

cur.execute('INSERT INTO hoge(name, age) values(?, ?)', (name_input, age_input))

コマンド、table名、column名はplaceholderに出来ませんがこれはコマンドやtableを外部からの指示で実行するような書き方自体が非推奨という事だと思います。

6. 読み込む

SELECTコマンドで選択してfetchall()すると読んできてくれます。以下ではhogeという名前のtableの内容すべてを読んでくるように指定しています。

cur.execute('SELECT * FROM hoge')
print(cur.fetchall())

fetchall()でなくfetchone()すればrecordを1つだけ、fetchmany()すれば指定した数のrecordを読んでくれます。

1つだけ読む

print(cur.fetchall())

5つ読む

print(cur.fetchmany(5))

指定のcolumnだけ読み込む

SELECT * ではすべてのcolumnを読み込みますが、読み込むcolumnを指定することも出来ます。以下ではid列とname列のみ読み込みます。

cur.execute('SELECT id, name FROM hoge')

出力

[(1, 'Taro'), (2, 'Hanako'), (20, 'Akiko'), (21, 'Jiro')]

条件に一致したものだけ読み込む

SELECTコマンドのオプションにWHEREをつけると指定した条件に一致したものだけ読み込むように出来ます

conn = sqlite3.connect(dbname)
conn = sqlite3.connect(":memory:")

cur = conn.cursor()

cur.execute('CREATE TABLE hoge(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING, age INTEGER)')
cur.execute('INSERT INTO hoge(name, age) values("Taro", 12)')
cur.execute('INSERT INTO hoge(name) values("Hanako")')
cur.execute('INSERT INTO hoge(id, name, age) values(20, "Akiko", 2)')
cur.execute('INSERT INTO hoge(name, age) values("Jiro", 10)')

conn.commit()

cur.execute('SELECT * FROM hoge WHERE age <= 12')
print(cur.fetchall())

cur.execute('SELECT * FROM hoge WHERE name = "Hanako"')
print(cur.fetchall())

cur.execute('SELECT * FROM hoge WHERE name <> "Jiro"')
print(cur.fetchall())

cur.close()
conn.close()

出力

[(1, 'Taro', 12), (20, 'Akiko', 2), (21, 'Jiro', 10)]
[(2, 'Hanako', None)]
[(1, 'Taro', 12), (2, 'Hanako', None), (20, 'Akiko', 2)]

7. pandas.DataFrameで読み書き

pandas.DataFrameから読み書きすることも出来ます

import sqlite3
import numpy as np
import pandas as pd

df = pd.DataFrame({'a': np.arange(5),
                   'b': np.arange(5) + 1,
                   'c': np.arange(5) + 2})
display(df)

conn = sqlite3.connect(':memory:')
cur = conn.cursor()

df.to_sql('sample', conn, if_exists='replace')

cur.execute('SELECT * FROM sample')
print(cur.fetchall())

df2 = pd.read_sql('SELECT * FROM sample', conn).set_index('index')
display(df2)

cur.close()
conn.close()

まとめ

pandas.DataFrameで読み書き出来る機能が感動的に使いやすいです
レッツトライ