【Python】SQLiteで構文に変数を用いたい

Question

前提・実現したいこと

Pythonのsqlite3を用いてデータベースの勉強をしています。
データベースに値を挿入する際、変数を用いたいのですが、下記のようなエラーが出てうまくいきません。

お詳しい方、解決法を教えてください！

発生している問題・エラーメッセージ

sqlite3.Error occurred: no such column: tex

該当のソースコード

import sqlite3 as sq
db_path = r"C:\~~~\sample_data.db"
# データベース接続
conn = sq.connect(db_path)
# カーソル生成
cursor = conn.cursor()

tex = "aaa"

try:
 # CREATE
 cursor.execute("DROP TABLE IF EXISTS sample")
 cursor.execute(
  "CREATE TABLE IF NOT EXISTS sample (id INTEGER PRIMARY KEY, name TEXT)")
 # INSERT
 cursor.execute("INSERT INTO sample VALUES (1, tex)")
 conn.commit()
 conn.close()
except sq.Error as e:
 print('sqlite3.Error occurred:', e.args[0])

試したこと

try文中のtexを 'aaa' に変えると、通常通り動作しました。

補足情報（FW/ツールのバージョンなど）

Windows10
sqlite 3.32.3

Answer 1

文字列操作として、変数を使って文字を構築する方法は押さえておくべきですが、

データベースのクエリ構築で変数を直接扱うのはお勧めしません。
プレースホルダを使いましょう。

sqlite3 ライブラリ ドキュメント より

たいてい、SQL 操作では Python 変数の値を使う必要があります。この時、クエリーを Python の文字列操作を使って構築することは安全とは言えないので、すべきではありません。そのようなことをするとプログラムが SQL インジェクション攻撃に対し脆弱になります

解決方法

代わりに、DB-API のパラメータ割り当てを使います。 ? を変数の値を使いたいところに埋めておきます。その上で、値のタプルをカーソルの execute() メソッドの第2引数として引き渡します。

cursor.execute("INSERT INTO sample VALUES (NULL, ?)", (txt,))

Comments

[netz-eng]

ご回答ありがとうございます。
該当部分をご提示いただいた文に書き換えたところ、無事データベースに文字列が格納されました！

一人では解決できなかったと思うので、本当に助かりました。ありがとうございます。

Answer 2

この場合、SQLだからということではなく、クエリは単なる文字列なので、文字列の中を変数に置き換える方法を使えばOKです。

いくつか方法はありますが、python3.6以降であれば、以下のようにできます。
以前であれば、format関数を使いましょう。

 cursor.execute(f"INSERT INTO sample VALUES (1, {tex})")

Comments

[netz-eng]

ご回答ありがとうございます。

tex に "aaa" を格納したうえで教えていただいたコードに書き換えてみたところ、
```Python
sqlite3.Error occurred: no such column: aaa
```
と、エラー文の内容が、texから変数の中身に変わりました。

[YakumoSaki]

aaaがシングルクォートで囲まれてないからですね
` cursor.execute(f"INSERT INTO sample VALUES (1, '{tex}')")` のようにシングルクォートを足してください。

[netz-eng]

シングルクォーテーションで囲ったところ、
　sqlite3.Error occurred: UNIQUE constraint failed: TK.id
というエラーに変わりました。
データベースの1列目にidを指定しているために起きたエラー、ということはなんとなくわかるのですが、、、

行き違いでBAを選んでしまったあとで申し訳ないのですが、後学のため教えていただけると嬉しいです。

[YakumoSaki]

UNIQUE constraint failed はテーブル上のキーが重複しているというエラーです。SQL自体は実行できています。
create table時に id INTEGER PRIMARY KEY と指定しているので、idが重複したデータを入れることはできません。

VALUES (1, '{tex}') の 1 を他の数字に変えれば成功するはずです。

[netz-eng]

なるほど！　理解できました。
質問文に載せていない前の部分で id=1 でデータを挿入していました。

お騒がせいたしました（汗）
ご丁寧にありがとうございました。

Answer 3

https://note.nkmk.me/python-f-strings/

上記を参考に、formatメソッドや、f文字列をご利用ください。

Comments

[netz-eng]

ご回答ありがとうございます。
今回はteamiklさんに教えていただいた方法で解決しましたが、今後のため載せていただいた参考URLについてまた学ばせていただこうと思います。