【プログラミング】SQLite3：LIKE句によるパターンマッチング

2023年3月17日2023年5月2日

URLをコピーしました！

SQLite3

前回、フォーマット文字列（f文字列）で{}（波括弧・中括弧）を使う方法とエスケープの方法を解説しました。

あわせて読みたい

【Python基礎】フォーマット文字列（f文字列）で{}（波括弧・中括弧）を使う方法とエスケープの方法（ク… 【f文字列】前回、concurrent.futuresでの返り値を取得する方法を解説しました。今回はフォーマット文字列（f文字列）で「{}（波括弧・中括弧）」を使う方法を解説し…

今回はSQLite3のLIKE句というものを使って、パターンマッチングをしてみます。

パターンマッチングとは、ある文字列に対して特徴的な文字列（パターン）を比較し、抽出する（マッチング）させることを言います。

例えば①プログラミング、②プログラムという二つの文字列があったとします。

「プロ」というパターンの文字列を抽出する場合、①、②の両方が抽出されます。

「グラム」というパターンの文字列を抽出する場合、②のみが抽出されます。

このようにある文字列に対して特定のパターンで抽出できるかどうかを判定するのがパターンマッチングです。

ということでまずはSQLite3のデータベースを作成します。

import sqlite3

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    cur.execute('CREATE TABLE test(id INTEGER PRIMARY KEY, fruits TEXT)')
    conn.commit()

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    cur.execute('INSERT INTO test(id, fruits) values(0, "Apple")')
    cur.execute('INSERT INTO test(id, fruits) values(1, "Banana")')
    cur.execute('INSERT INTO test(id, fruits) values(2, "Pineapple")')
    cur.execute('INSERT INTO test(id, fruits) values(3, "Peach")')
    cur.execute('INSERT INTO test(id, fruits) values(4, "Orange")')
    conn.commit()

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test').fetchall()
    print(data)

実行結果
[(0, 'Apple'), (1, 'Banana'), (2, 'Pineapple'), (3, 'Peach'), (4, 'Orange')]

最初の2つのプログラムでデータベースの作成とデータの入力を行なっています。

これらは何度も行うとエラーとなるので、それぞれ１回ずつのみ実行します。

それでは始めていきましょう。

メタ文字

先ほどの①プログラミング、②プログラムという二つの文字列の例において、「プロ」とか「グラム」のような特定の文字以外に、色々なパターンに当てはまる文字が存在します。

それをメタ文字と言います。

例えば、こちらの例をそれぞれ別に抽出する必要があるかどうか、そのパターンを決める必要があります。

「プロ」が先頭に存在する
「プロ」が最後に存在する
「プロ」が文字列中のどこかに存在する

１．「プロ」が先頭に存在する場合であれば、「プロ」の前には文字が存在せず、後ろに文字が存在するという指定をします。

２．「プロ」が最後に存在する場合、１とは逆で「プロ」の前には文字が存在し、後ろには存在しないという指定をします。

３．「プロ」が文字列中のどこかに存在する場合、「プロ」の前後に文字が存在してもいいし、しなくてもいいという指定をします。

これらの指定のために「文字列が存在しますよ」とか「文字列が存在してもしなくてもいいですよ」という指定をする文字がメタ文字です。

SQLite3では以下の２つのメタ文字が指定できるようです。

メタ文字	意味
_	任意（なんでもいい）の文字が１個
%	任意（なんでもいい）の文字が０個以上

LIKE句

SQLite3でパターンマッチングをする場合、LIKE句というものを使い、パターンを指定します。

LIKE句の書き方としては「SELECT 取得する列 FROM テーブル WHERE マッチングさせる列 LIKE マッチングのパターン」です。

最初に「P」で始まるデータを抽出する場合は「p%」というパターンを使うため、このようになります。

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test WHERE fruits LIKE "p%"').fetchall()
    print(data)

実行結果
[(2, 'Pineapple'), (3, 'Peach')]

「le」で終わるデータを抽出する場合は「%le」というパターンを使います。

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test WHERE fruits LIKE "%le"').fetchall()
    print(data)

実行結果
[(0, 'Apple'), (2, 'Pineapple')]

「an」が文字列中のどこかに存在するものを抽出する場合は「%an%」というパターンを使います。

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test WHERE fruits LIKE "%an%"').fetchall()
    print(data)

実行結果
[(1, 'Banana'), (4, 'Orange')]

ではここでメタ文字を使わず、「an」というパターンを指定するとどうなるか見てみましょう。

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test WHERE fruits LIKE "an"').fetchall()
    print(data)

実行結果
[]

何も抽出されませんでした。

理由としては「an」というパターンの場合、前にも後ろにも文字がない「an」という文字列にしかマッチングせず、今回のデータの中にはそのようなデータがないためです。

試しに「Apple」というパターンでマッチングさせてみましょう。

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test WHERE fruits LIKE "Apple"').fetchall()
    print(data)

実行結果
[(0, 'Apple')]

IDが0の「Apple」だけは完全に一致するため抽出されました。

それでは最初の「A」を小文字「a」にして、「apple」でパターンマッチングさせてみましょう。

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test WHERE fruits LIKE "apple"').fetchall()
    print(data)

実行結果
[(0, 'Apple')]

この場合もIDが0の「Apple」だけが抽出されました。

つまりSQLite3では大文字小文字は区別していないということなので注意が必要です。

最後に「_（任意の文字が1個）」を使ってみます。

with sqlite3.connect('test1.db') as conn:
    cur = conn.cursor()
    data = cur.execute('SELECT * FROM test WHERE fruits LIKE "_ea%"').fetchall()
    print(data)

実行結果
[(3, 'Peach')]

「ea」のパターンをもつものは「Pineapple」もあるのですが、今回のパターンの指定が「任意の文字1個＋ea＋任意の文字0個以上」なので「Pineapple」はマッチングしなかったというわけです。

次回はSQLite3で文字列を置換するREPLACEを見てみましょう。