Shunya Ueta

Pythonで変数を挿入してSQLクエリを柔軟に構築する

データ処理のタスクをこなしていると、Python で SQL に変数を挿入し柔軟に SQL クエリを構築したくなる。

例えば、

のような状況が考えられる。

変数を SQL に組み込んで実行したい際には、kayak/pypikaのような SQL builder もあるが、個人的には可読性が悪くなるし、SQL クエリの作成のためだけに余計なパッケージをいれたくない。

そのためパッケージを入れずに簡潔なクエリ構築方法をここでは紹介する。

編集履歴

1. 単なる文字列として SQL クエリを構築

f-string で文字列に変数を挿入して、SQL クエリを構築する

def get_guery(num: int, category: str):
	sql=f"SELECT field1, field2, field3, field4 FROM TABLE WHERE condition1={num} AND condition2={category}"
	return sql

欠点

2. 複数行文字列として SQL クエリを構築

def get_guery(num: int, category: str):
    sql = f"""
        SELECT field1, field2, field3, field4
        FROM table
        WHERE condition1={num}
        AND condition2={category}
    """
	return sql

1 番目と比較すると、複数行を扱えるstring literal-longstringを採用することで、SQL クエリが複数行になることでで見やすい (@cocu_tan さん、ご指摘ありがとうございます!)

欠点

3. SQL ファイルを文字列として読み込み、 .format()で変数を挿入する

SQL ファイルが ./sql/test.sql に設置されている状態とする。

SELECT
  field1,
  field2,
  field3,
  field4
FROM
  TABLE
WHERE
  condition1={num}
  AND condition2={category}

Python 側での実装例

import os


def get_guery(num: int, category: str, filename: str)->str:
    """
    SQLファイルに変数を挿入してクエリを作成する

    Args:
        num (int): 年齢
        category (str): 好きな果物
        filename (str): SQLファイルの名前

    Returns:
        str: SQLクエリ
    """
    with open(os.path.join("./sql", filename), "r") as f:
        return f.read().format(num=num, category=category)

print(get_guery(num=18, category="apple", filename="test.sql"))

実行結果

SELECT
  field1,
  field2,
  field3,
  field4
FROM
  TABLE
WHERE
  condition1=18
  AND condition2=apple

SQL ファイルの中に Python の .format() 記法を事前に埋め込むことが今回のコツですね。

余談

SQL ファイル内に

-- {}の中にPythonから変数が挿入される
SELECT
  field1,
  field2,
  field3,
  field4
FROM
  TABLE
WHERE
  condition1={num}
  AND condition2={category}

1 行目のようなコメントを入れてしまうと、SQL ファイルの中ではコメントとして評価される部分も Pythn の文字列内では.format()の挿入対象となる。その影響で.format()で挿入されるべき変数の数が 3 つと評価され、実際の引数は 2 つしか与えておらずエラーが発生する。 SQL ファイルのコメントであろうと、{}と記述しないように気をつけておきましょう。

追記

Twitter でみんなの管理方法をお聞きできたので、記しておく。 ありがたい。

@shuhei_fujiwara さん

BigQuery の話だけど僕は

単体で成立したい気持ち、たしかに分かる~

@satoshihirose さん

Airflow のみの話にはなってしまいますが、sql はファイルとして管理して基本 jinja template 対応のオペレーター使ってましたね https://qiita.com/munaita_/items/6bdcfb10f36c8c6b4753 > https://twitter.com/satoshihirose/status/1387776005943840772

Airflow のときに jinja 使えるんですね。今度から使います!

@reto_nayuta さん

私も(Airflow ではないですが)jinja 使うことが多いですね https://twitter.com/reto_nayuta/status/1387801584512364544

@SassaHero さん

3 を、from string import Template で変数置き換えを実現してます! https://twitter.com/SassaHero/status/1387878168481075200

from string import Template 知らなかった

Ref

---

関連しているかもしれない記事


📮 📧 🐏: 記事への感想のおたよりをおまちしてます。 お気軽にお送りください。 メールアドレス入力があればメールで返信させていただきます。 もちろんお返事を希望せずに単なる感想だけでも大歓迎です。

このサイトの更新情報をRSSで配信しています。 お好きなフィードリーダーで購読してみてください。

このウェブサイトの運営や著者の活動を支援していただける方を募集しています。 もしよろしければ、Buy Me a Coffee からサポート(投げ銭)していただけると、著者の活動のモチベーションに繋がります✨
Amazonでほしいものリストも公開しているので、こちらからもサポートしていただけると励みになります。

#python #sql