【sqlite3】PythonでDB操作：テーブルの参照とSQL条件文の作成方法

概要

sqlite3モジュールを使用して、DB参照をする方法についてまとめた。
SQLiteで作成したDBファイルを操作対象としている。

前提

DB環境構築～DBファイルの作成ができていること。

以下のUserテーブルに対して操作を行う。

CREATE TABLE User ( 
  id INTEGER PRIMARY KEY, 
  name TEXT, 
  kana TEXT, 
  memo TEXT 
);

テーブルのデータ

dbファイルのパス

sqlite3モジュールの使い方

sqlite3モジュールのインポート

sqlite3モジュールをインポートすればDBまわり（SQLite限定）の機能について使用可能。

import sqlite3

DBを操作する手順

以下の手順でDBを操作する。

接続

DBを操作するために、以下のようにSQLiteで作成されたDBファイルに接続する。

import sqlite3
import os

# DBファイルパス
sqlite_path = os.path.join("db", "test.db")

# sqliteファイルに接続
# connectionオブジェクト経由でDB操作
connection = sqlite3.connect(sqlite_path)
# カーソルを取得
cursor = connection.cursor()

尚、接続した後はカーソルオブジェクトを取得する。
このカーソルオブジェクトを使用してDB操作を行う。

カーソルとは、現在の入力位置を指し示す記号。
このカーソルの位置を一行進めたり、最終行まで進めたりして検索レコードを取得するというイメージ。

切断

DBと接続したら、最後に切断する処理を必ず記述すること。
慣習と思っていい。

# DBとの接続切断
connection.close()

検索機能（操作）

主な関数

基本的な使い方

import sqlite3
import os

# DB接続
sqlite_path = os.path.join("db", "test.db")
connection = sqlite3.connect(sqlite_path)
cursor = connection.cursor()

# SELECT
cursor.execute("SELECT * FROM User")
# 一行取得するにはcursor.fechone()
# タプルが返却される
res = cursor.fetchone() 
print(res)
# 全件取得するにはcursor.fetchall()
# タプルのリストが返却される
res = cursor.fetchall()
print(res)

# DBとの接続切断
connection.close()

# --------------------------------------
# 出力
# ※fetchone()の結果
#(1, '山田', 'ヤマダ', 'A5からいれるよ')
# ※fetchall()の結果
#[(2, '田中', 'タナカ', None), (3, '佐藤', 'サトウ', None), (4, '鈴木', 'スズキ', 'テスト'), (5, '木村', 'キムラ', 'メモ')]

cursor.execute()の中にテーブルを参照するためのSQL文を記述する。
最終的にデータを取得するのはカーソルメソッドになる。

尚、取得したデータの型はタプルになる。

様々な条件文の作成方法

WHERE

idが「1」のデータを取得。

import sqlite3
import os

# DB接続
sqlite_path = os.path.join("db", "test.db")
connection = sqlite3.connect(sqlite_path)
cursor = connection.cursor()

# SELECT
cursor.execute("SELECT * FROM User WHERE id = ?", (1,))
res = cursor.fetchone() 
print(res)

# DBとの接続切断
connection.close()

# --------------------------------------
# 出力
#(1, '山田', 'ヤマダ', 'A5からいれるよ')

SQLの文字列に「？」を記述することで、第二引数に「？」に設定する値を設定可能。
尚、第二引数はタプルになるため、検索条件が1件の場合は最後にカンマを忘れないこと。

CASE

memoがnullの場合、「メモが未登録」と設定して全件取得。

# CASE
cursor.execute("""
    SELECT 
        id, 
        name, 
        CASE 
            WHEN memo IS NULL THEN 'メモが未登録'
            ELSE memo
        END as memo
    FROM User
""")
res = cursor.fetchall() 
print(res)
#[(1, '山田', 'A5からいれるよ'), (2, '田中', 'メモが未登録'), (3, '佐藤', 'メモが未登録'), (4, '鈴木', 'テスト'), (5, '木村', 'メモ')]

ORDER BY

idの昇順に取得。

# ORDER BY　id昇順
cursor.execute("SELECT * FROM User ORDER BY id asc")
res = cursor.fetchall()
print(res)
#[(1, '山田', 'ヤマダ', 'A5からいれるよ'), (2, '田中', 'タナカ', None), (3, '佐藤', 'サトウ', None), (4, '鈴木', 'スズキ', 'テスト'), (5, '木村', 'キムラ', 'メモ')]

尚、「asc」は省略可。

# ORDER BY  id降順
cursor.execute("SELECT * FROM User ORDER BY id desc")
res = cursor.fetchall()
print(res)
#[(5, '木村', 'キムラ', 'メモ'), (4, '鈴木', 'スズキ', 'テスト'), (3, '佐藤', 'サトウ', None), (2, '田中', 'タナカ', None), (1, '山田', 'ヤマダ', 'A5からいれるよ')]

上記はidの降順に取得。
尚、降順にしたい場合は「desc」必須。

BETWEEN

idが1～3のデータを取得。

# BETWEEN
cursor.execute("SELECT * FROM User WHERE id BETWEEN ? AND ?", (1, 3))
res = cursor.fetchall()
print(res)
#[(1, '山田', 'ヤマダ', 'A5からいれるよ'), (2, '田中', 'タナカ', None), (3, '佐藤', 'サトウ', None)]

NOT

nameが「山田」以外のデータを取得。

# NOT
cursor.execute("SELECT * FROM User WHERE NOT name = ?", ("山田",))
res = cursor.fetchall()
print(res)
#[(2, '田中', 'タナカ', None), (3, '佐藤', 'サトウ', None), (4, '鈴木', 'スズキ', 'テスト'), (5, '木村', 'キムラ', 'メモ')]

IN

nameが「山田」「田中」のデータを取得。

# LIKE
cursor.execute("SELECT * FROM User WHERE name LIKE ?", ("%田%",))
res = cursor.fetchall()
print(res)
#[(1, '山田', 'ヤマダ', 'A5からいれるよ'), (2, '田中', 'タナカ', None)]

LIKE

nameに「田」があるデータを取得。

# LIKE
cursor.execute("SELECT * FROM User WHERE name LIKE ?", ("%田%",))
res = cursor.fetchall()
print(res)
#[(1, '山田', 'ヤマダ', 'A5からいれるよ'), (2, '田中', 'タナカ', None)]

IS NULL

memoがnullのデータを取得。

# IS NULL
cursor.execute("SELECT * FROM User WHERE memo is NULL")
res = cursor.fetchall()
print(res)
#[(2, '田中', 'タナカ', None), (3, '佐藤', 'サトウ', None)]