Pythonで始めるデータベース操作：SQLite3とBLOBデータの基本

Python
2023.12.05

PythonでSQLite3データベースを操作するには？

SQLite3はオープンソースのMySQLやPostgreSQL、商用データベースOracle Database、Microsoft SQL ServerなどのDBMS製品と異なり、サーバープロセスがなく製品のインストールが不要で1つのファイルでデータベースを管理するので非常に扱い易いDBMSとなっています。

本記事では、PythonでSQLite3に対してINSERT、UPDATE等を実行するexec関数とSELECTを実行するselect関数を作成して処理する方法を紹介します。

事前準備

サンプルコード実行用にtest.sqlite3データベースとcandy.pngのファイルを以下からダウンロードします。

testdbダウンロード


ダウンロード後は以下の図のようにCドライブにtestdbというフォルダを作成し、その中に上記２ファイルをコピーしてください。

exec関数とselect関数のサンプルコード

次に、exec関数とselect関数のサンプルコードを紹介します。
関数の下にはテスト用のコードも記載していますので参考にしてください。

import sqlite3

#sqlite3のデータベース
DB_PATH = "c:\\testdb\\test.sqlite3"

def exec(query, param = None):
    """
    SQLite3でクエリを実行する。

    Args:
        query (str): INSERT文等のクエリ文字列
        param (tuple): パラメータ 省略可

    """
    # データベースに接続
    conn = sqlite3.connect(DB_PATH)
    # カーソルを取得
    cur = conn.cursor()
    # SQL文を実行
    if param:
        cur.execute(query,param)
    else:
        cur.execute(query)
    # コミット
    conn.commit()
    # 接続を終了
    conn.close()
    
def select(query, param = None):
    """
    SQLite3でSELECT文を実行する。

    Args:
        query (str): SELECT文のクエリ文字列
        param (tuple): パラメータ 省略可

    Returns:
        tuple: 実行結果のタプル

    """
    # データベースに接続
    conn = sqlite3.connect(DB_PATH)
    # カーソルを取得
    cur = conn.cursor()
    # SELECT文を実行して結果を取得
    if param:
        cur.execute(query,param)
    else:
        cur.execute(query)
        
    rows = cur.fetchall()
    # 接続を終了
    conn.close()
    # 取得した結果を返す
    return rows


##############################################################
# 関数のテスト
if __name__ == "__main__":
    
    ###DROP TABLE 
    #strsql = "DROP TABLE 商品マスタ "
    #exec(strsql)

    ###CREATE TABLE 
    #strsql = "CREATE TABLE 商品マスタ ( "
    #strsql += "品番	TEXT,"
    #strsql += "品名	TEXT,"
    #strsql += "価格	INTEGER,"
    #strsql += "商品画像	BLOB,"
    #strsql += "PRIMARY KEY(品番)"
    #strsql += ")"
    #exec(strsql)

    ###DELETE
    strsql = "DELETE FROM 商品マスタ"
    exec(strsql)

    ###INSERT
    strsql = "INSERT INTO 商品マスタ(品番, 品名, 価格) VALUES('3301', 'チョコレート', 100)"
    exec(strsql)

    ###INSERT プリペアードステートメント
    strsql = "INSERT INTO 商品マスタ(品番, 品名, 価格) VALUES(?, ?, ?)"
    parameters = ("3302", "クッキー", 80)
    exec(strsql, parameters)

    ###INSERT BLOB
    file_path = "c:\\testdb\\candy.png"
    with open(file_path, 'rb') as file:
        binary_data = file.read()
    strsql = "INSERT INTO 商品マスタ(品番, 品名, 価格, 商品画像) VALUES(?, ?, ?, ?)"
    parameters = ("3303", "キャンディ", 70, binary_data)
    exec(strsql, parameters)



    ###SELECT 
    print("\nSELECT")
    strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = '3301'"
    ret = select(strsql)
    print(ret[0])

    ###SELECT プリペアードステートメント
    print("\nSELECT プリペアードステートメント")
    strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = ? or 価格 >= ?"
    parameters = ("3302", 100)
    ret = select(strsql, parameters)
    for i in range(len(ret)):   
        print(ret[i])

    ###SELECT BLOB
    print("\nSELECT BLOB")
    strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = ?"
    parameters = ("3303",) #パラメータが１つなのでタプルにする。
    ret = select(strsql, parameters)

    file_path = "c:\\testdb\\candy2.png"
    print(len(ret[0][3])) #ファイルサイズ
    with open(file_path, 'wb') as file:
        file.write(ret[0][3]) #バイナリファイルを書き込む

実行結果

SELECT
('3301', 'チョコレート', 100, None)

SELECT プリペアードステートメント
('3301', 'チョコレート', 100, None)
('3302', 'クッキー', 80, None)

SELECT BLOB
127579

コンソールには上記実行結果が出力されますが、BLOBにcandy.pngをINSERTした後にSELECTしてcandy2.pngとして保存しているためファイルが増えます。

解説

DBへの接続について

conn = sqlite3.connect(DB_PATH)

この処理を行うとDB_PATHに記載のデータベースを読み込みします、データベースファイルがない場合には自動的に作成されます。

###CREATE TABLE 
#strsql = "CREATE TABLE 商品マスタ ( "
#strsql += "品番	TEXT,"
#strsql += "品名	TEXT,"
#strsql += "価格	INTEGER,"
#strsql += "商品画像	BLOB,"
#strsql += "PRIMARY KEY(品番)"
#strsql += ")"
#exec(strsql)

この処理のコメントを外して、test.sqlite3のファイルを削除した後に実行することによりその確認が行えます。

プリペアードステートメント利用時の注意点について

###SELECT BLOB
print("\nSELECT BLOB")
strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = ?"
parameters = ("3303",) #パラメータが１つなのでタプルにする。
ret = select(strsql, parameters)

この処理ではparametersにタプルを渡していますが、パラメータが一つの場合には「,」を入れる必要があります。忘れがちですので記載するようにしましょう。

BLOBへの格納

###INSERT BLOB
file_path = "c:\\testdb\\candy.png"
with open(file_path, 'rb') as file:
    binary_data = file.read()
strsql = "INSERT INTO 商品マスタ(品番, 品名, 価格, 商品画像) VALUES(?, ?, ?, ?)"
parameters = ("3303", "キャンディ", 70, binary_data)
exec(strsql, parameters)

INSERT時にはbinary_dataをパラメータとして引数に渡す必要があります。

書き込みした結果はDB Browser for SQLiteを利用すると以下の図のように見えます。

BLOBから読み込み

###SELECT BLOB
print("\nSELECT BLOB")
strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = ?"
parameters = ("3303",) #パラメータが１つなのでタプルにする。
ret = select(strsql, parameters)

file_path = "c:\\testdb\\candy2.png"
print(len(ret[0][3])) #ファイルサイズ
with open(file_path, 'wb') as file:
    file.write(ret[0][3]) #バイナリファイルを書き込む

SELECTしてファイルに戻す時にはfile_pathを指定してバイナリファイルを書き込む必要があります。

複数アクセスについて

サーバープロセスのDBMSと異なり、複数アクセスの場合競合が起きやすいDBMSとなっていますので、単一アクセスで利用することをおすすめします。

最後に

以上がPythonでSQLite3データベースを操作する方法です。

SQLiteはPythonのライブラリ標準で組み込みされているsqlite3を利用することにより簡単に利用できます。

データを大量に扱う場合や恒久的にデータを保存する場合でも無償で利用できるのでそのようなシーンで検討してみてはいかがでしょうか。

コメント

タイトルとURLをコピーしました