今回は、Pandasを使ってデータフレームの「あいまい検索」。

実行環境

Android スマホ
Termuxアプリ F-DROID版
Python 3.10.2
JupyterNotebook 6.4.7
外部ライブラリ
- Pandas 1.3.5
- ipython-sql

データを取得しSQLiteへ挿入

適当なテーブルを取得し、sqliteのデータベースファイルに挿入します。

テーブルの取得

SQL文の逆引き辞典のサイトをお借りします。

逆引きSQL構文集

こちらよりテーブルを取得します。

import pandas as pd

# url
URL = 'https://www.sql-reference.com/'

# テーブル取得
df = pd.read_html(URL)

ページには複数のテーブルがありますが、一番上のものだけを使います。使わせてもらいます。ありがとうございます。

# テーブルの1つ目を表示
df[0]

	項目	関連SQL
0	レコードを検索する	SELECT，FROM，WHERE，AND，OR
1	重複したレコードを省いて検索する	DISTINCT
2	NULL値を持つデータを検索する	IS NULL，IS NOT NULL
3	指定した範囲のレコードを検索する	BETWEEN
4	指定した複数の値に一つ以上一致するレコードを検索する	IN
5	ワイルドカードを使用してレコードを検索する	LIKE
6	検索結果の列名を別名で表示する	AS
7	レコードを並び替える	ORDER BY，ASC，DESC

「項目」「関連SQL」の列がある。インデックス列「id」を一番左の列につけたデータベースのテーブルを作りましょうかね。
作ったテーブルに、上で取得したDataFrameをインポートしようと思います。

SQL文でテーブル作成

まず、テーブル作成から。
JupyterNotebookで作業していますので、sqlのマジックコマンドを使えるようにします。データベースはsqliteを使います。

その前に、Jupyterで%sql、%%sqlを使えるようにするにはipython-sqlをインストールしておきます。無いとエラー。

#!pip install ipython-sql #出力省略

拡張の読み込みとsqliteデータベースファイル接続

%load_ext sql

%sql sqlite:///test_search.db

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

テーブル作成

%%sql
CREATE TABLE sample_tbl(
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    項目 TEXT,
    関連SQL TEXT);

   sqlite:///db_name
 * sqlite:///test_search.db
Done.





[]

データベースにDataFrameをto_sqlで追加

テーブルが作成されたようなので、上で取得したDataFrameを入れます。

import sqlite3

# データベースsqliteファイルに接続 
with sqlite3.connect("test_search.db") as conn:
    # DataFrameをテーブルに追加
    df0 = df[0]
    df0.to_sql(
        name="sample_tbl",
        con=conn, 
        if_exists='append',
        index=None)

エラーが出ませんでした。

DBテーブルを確認

追加されたかどうかテーブルを確認します。

%sql SELECT * FROM sqlite_master;

   sqlite:///db_name
 * sqlite:///test_search.db
Done.

type	name	tbl_name	rootpage	sql
table	sqlite_sequence	sqlite_sequence	3	CREATE TABLE sqlite_sequence(name,seq)
table	sample_tbl	sample_tbl	2	CREATE TABLE sample_tbl( id INTEGER PRIMARY KEY AUTOINCREMENT, 項目 TEXT, 関連SQL TEXT)

%%sql

SELECT * FROM sample_tbl;

   sqlite:///db_name
 * sqlite:///test_search.db
Done.

id	項目	関連SQL
1	レコードを検索する	SELECT，FROM，WHERE，AND，OR
2	重複したレコードを省いて検索する	DISTINCT
3	NULL値を持つデータを検索する	IS NULL，IS NOT NULL
4	指定した範囲のレコードを検索する	BETWEEN
5	指定した複数の値に一つ以上一致するレコードを検索する	IN
6	ワイルドカードを使用してレコードを検索する	LIKE
7	検索結果の列名を別名で表示する	AS
8	レコードを並び替える	ORDER BY，ASC，DESC

あいまい検索

やっと準備が整いました。

SQL文であいまい検索

SQL文で条件抽出、検索をするときは、

SELECT 表示カラム名 FROM テーブル名 WHERE 検索対象カラム名 LIKE 条件文

という形で入力します。

条件文は、「○○を含む」とすれば、あいまい検索ができる。条件を「'%○○%'」と書けば、対象カラムから○○を含むレコード(行)を抽出できます。やってみよう。

「ワイルドカード」を含む

%%sql

SELECT * FROM sample_tbl 
    WHERE 項目 
    LIKE '%ワイルドカード%' ;

   sqlite:///db_name
 * sqlite:///test_search.db
Done.

id	項目	関連SQL
6	ワイルドカードを使用してレコードを検索する	LIKE

「レコード」を含むレコードを検索

%%sql

SELECT * FROM sample_tbl 
    WHERE 項目 
    LIKE '%レコード%' ;

   sqlite:///db_name
 * sqlite:///test_search.db
Done.

id	項目	関連SQL
1	レコードを検索する	SELECT，FROM，WHERE，AND，OR
2	重複したレコードを省いて検索する	DISTINCT
4	指定した範囲のレコードを検索する	BETWEEN
5	指定した複数の値に一つ以上一致するレコードを検索する	IN
6	ワイルドカードを使用してレコードを検索する	LIKE
8	レコードを並び替える	ORDER BY，ASC，DESC

「範囲」「検索」で検索

%%sql

SELECT * FROM sample_tbl 
    WHERE 項目 
    LIKE '%範囲%検索%' ;

   sqlite:///db_name
 * sqlite:///test_search.db
Done.

id	項目	関連SQL
4	指定した範囲のレコードを検索する	BETWEEN

上の条件文の「範囲」「検索」を%範囲%検索%と書いていますが、順序を変えると、

%%sql

SELECT * FROM sample_tbl 
    WHERE 項目 
    LIKE '%検索%範囲%' ;

   sqlite:///db_name
 * sqlite:///test_search.db
Done.

id	項目	関連SQL

出てきませんでした！

OR(または)でつなぐ

OR(または)での検索はどちらか一方でも含むものを全部抽出するので大量に釣れる。

%%sql

SELECT * 
FROM sample_tbl 
WHERE 項目 
LIKE '%検索%' OR '%範囲%' ;

   sqlite:///db_name
 * sqlite:///test_search.db
Done.

id	項目	関連SQL
1	レコードを検索する	SELECT，FROM，WHERE，AND，OR
2	重複したレコードを省いて検索する	DISTINCT
3	NULL値を持つデータを検索する	IS NULL，IS NOT NULL
4	指定した範囲のレコードを検索する	BETWEEN
5	指定した複数の値に一つ以上一致するレコードを検索する	IN
6	ワイルドカードを使用してレコードを検索する	LIKE
7	検索結果の列名を別名で表示する	AS

順序を入れ換えると引っ掛からなくなる対策は、どうすりゃいいんじゃろか。放置して、次はPandasで同じことをしてみましょう。

Pandasであいまい検索

DataFrameから「○○を含む」条件抽出をする方法は幾つかある。

str.contains('○○')

df[df["検索対象カラム名"].str.contains("○○")

# 元のデータフレーム
df0

	項目	関連SQL
0	レコードを検索する	SELECT，FROM，WHERE，AND，OR
1	重複したレコードを省いて検索する	DISTINCT
2	NULL値を持つデータを検索する	IS NULL，IS NOT NULL
3	指定した範囲のレコードを検索する	BETWEEN
4	指定した複数の値に一つ以上一致するレコードを検索する	IN
5	ワイルドカードを使用してレコードを検索する	LIKE
6	検索結果の列名を別名で表示する	AS
7	レコードを並び替える	ORDER BY，ASC，DESC

「並び替え」をあいまい検索

df0[df0["項目"].str.contains("並び替え")]

	項目	関連SQL
7	レコードを並び替える	ORDER BY，ASC，DESC

str.match(正規表現)

df[df['検索対象カラム名'].str.match(正規表現)]

df0[df0["関連SQL"].str.match('.*IN.*')]

	項目	関連SQL
1	重複したレコードを省いて検索する	DISTINCT
4	指定した複数の値に一つ以上一致するレコードを検索する	IN

難しいw

pd.read_sql_query(SQL文)を使う

データベースに接続し、SQL文を書いたメソッドで実行する。

「表示」を含む検索

import sqlite3


with sqlite3.connect("test_search.db") as conn:
    df_qy = pd.read_sql_query('SELECT * FROM sample_tbl WHERE 項目 LIKE "%表示%"', con=conn)

# 表示
df_qy

	id	項目	関連SQL
0	7	検索結果の列名を別名で表示する	AS

おわりに

大したことしてないわりに長くなったのでこの辺りでやめます。
条件抽出や検索のテクニックは正規表現など使えたりしますし、たくさん場数踏んで体で覚えていくしかないですね。

以上です。

よちよちpython

独習 python/Qpython/Pydroid3/termux/Linux

【JupyterでSQLとPandas】あいまい検索の超基本