Python – SQLite REGEXP SQL Query

測試環境為 CentOS 8 x86_64 (虛擬機)

關於 Python 存取 SQLite 資料庫請參考 – https://benjr.tw/104703

當透過 Python 使用到 SQL 指令中的 REGEXP (正規表示式比對字串),但卻發生以下的錯誤訊息,參考文章 – https://github.com/thomasnield/oreilly_intermediate_sql_for_data/issues/5 .

>>> datalist2=cur.execute("SELECT * FROM t1 WHERE data1 REGEXP '^[0-9,.]+$'")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such function: REGEXP

需使用以下的方式才能使用正確使用 REGEXP.

[root@localhost ~]# python3
Python 3.6.8 (default, Mar 25 2022, 11:15:52) 
[GCC 8.5.0 20210514 (Red Hat 8.5.0-10)] on linux
Type "help", "copyright", "credits" or "license" for more information.

匯入 sqlite3 與 re 套件.

>>> import sqlite3
>>> import re

建立名稱為 regexp function

>>> def regexp(expr, item):
>>>     reg = re.compile(expr)
>>>     return reg.search(item) is not None

這邊用到 re 套件的兩個函數 , 詳細請參考 – https://benjr.tw/105370

• reg.search(item)
  函數與參數格式為 re.search(pattern, string, flags) , re.search 會依據 指定 pattern 去搜尋整個 string 字串,並回傳匹配的字符(沒匹配到任何時則傳回 None), 但這邊我們只傳入 string , pattern 是透過 re.compile (下面說明) 來指定.
• re.compile(expr)
  函數與參數格式為 re.compile(pattern, flags) , 透過 re.compile 定義正則表達式,為 regular expression object 物件,可以重覆給 match , search , findall 函數使用.

透過 sqlite3.connect 連線到資料庫檔案,沒有該資料庫檔案時會自動建立在現行路徑.

>>> con = sqlite3.connect('mydata.db')

回傳 con 為 sqlite3.Connection 物件.

要使用自訂函數要靠 con 物件的 create_function method.

>>> con.create_function("REGEXP", 2, regexp)

create_function(name, no_of_params, func) 參數指定如下.

• name: 使用 SQL 語言時所要用的函數名稱.
• no_of_params: SQL 函數的傳入參數值個數.
• func: 自訂的函數名稱,這邊就是前面自訂的 regexp function.

回傳值可以為 bytes, str, int, float, and None any of the types supported by SQLite.

透過剛剛建立的 con 物件來建立 cursoe 物件.

cur = con.cursor()

之後就可以透過這個 sqlite3.Cursor 物件來執行 含有 REGEXP SQL Query 的語法.

建立測試用的資料表(使用 IF NOT EXISTS 時該資料表不存在時才會建立).

>>> cur.execute("CREATE TABLE IF NOT EXISTS t1 ( data1 CHAR(20))")
<sqlite3.Cursor object at 0x7fa791fdbce0>

使用 SQL 語法 INSERT INTO 一次插入多筆資料.

>>> cur.execute("INSERT INTO t1 VALUES ('123') ,('abc') ,('a23') ,('456') ,('P3') , ('0.98')")
<sqlite3.Cursor object at 0x7fa791fdbce0>

透過 SELECT 查看資料.

>>> datalist1=cur.execute("SELECT * FROM t1")
>>> list(datalist1)
[('123',), ('abc',), ('a23',), ('456',), ('P3',), ('098',)]

加上 REGEXP 的 SELECT ,其中的 REGEXP ‘^[0-9,.]+$’ 表示只抓包含數字 0-9 與 . (小數點) 的字串 .

>>> datalist2=cur.execute("SELECT * FROM t1 WHERE data1 REGEXP '^[0-9,.]+$'")
>>> list(datalist2)
[('123',), ('456',), ('0.98',)]

將資料儲存到資料庫檔案.

>>> con.commit()

關閉資料庫連線.

>>> con.close()