【Python】psycopg2 パラメータ指定方法メモ

スポンサードサーチ

接続

The psycopg2 module contentを参照

conn = psycopg2.connect(
        dbname=xxxxxx.
        user=xxxxxx,
        password=xxxxxx,
        host=xxxxxx,
        port=xxxxxx
    )
cur = conn.cursor()
cur.execute("SELECT * FROM users")
cur.close()
conn.close()

パラメータ指定

通常

Passing parameters to SQL queriesを参照

良い例:

id = 1
name = '太郎'
cur.execute("SELECT * FROM users WHERE id = %s", (id,))
cur.execute("SELECT * FROM users WHERE id = %s", [id,])
# []の場合は,がなくても良い
cur.execute("SELECT * FROM users WHERE id = %s", [id])

cur.execute("SELECT * FROM users WHERE id = %s AND name = %s", (id, name))
cur.execute("SELECT * FROM users WHERE id = %s AND name = %s", [id, name])

cur.execute("SELECT * FROM users WHERE id = %(id)s", {'id': id})
cur.execute("SELECT * FROM users WHERE id = %(id)s AND name = %(name)s", {'id': id, 'name': name})

悪い例:

id = 1
# ()か[]で囲むのが必要
cur.execute("SELECT * FROM users WHERE id = %s", id)
# パラメータが1つでも(id,)のように,が必要
cur.execute("SELECT * FROM users WHERE id = %s", (id))
# %(id)sのようにsが必要
cur.execute("SELECT * FROM users WHERE id = %(id)", {'id': id})

LIKE句

namd = '太郎'
# name LIKE '太郎%'
cur.execute("SELECT * FROM users WHERE name LIKE '太郎%%'")
cur.execute("SELECT * FROM users WHERE name LIKE %s", (name+'%',)
cur.execute("SELECT * FROM users WHERE name LIKE %(name)s", ('name': name+'%'})

# name LIKE '%太郎'
cur.execute("SELECT * FROM users WHERE name LIKE '%%太郎'")
cur.execute("SELECT * FROM users WHERE name LIKE %s", ('%'+name,)
cur.execute("SELECT * FROM users WHERE name LIKE %(name)s", ('name': '%'+name})

# name LIKE '%太郎%'
cur.execute("SELECT * FROM users WHERE name LIKE '%%太郎%%'")
cur.execute("SELECT * FROM users WHERE name LIKE %s", ('%'+name+'%',)
cur.execute("SELECT * FROM users WHERE name LIKE %(name)s", ('name': '%'+name+'%'})

IN句

ids = [1, 2, 3]
# id = ANY(ARRAY[1,2,3])のようなクエリを生成する場合
cur.execute("SELECT * FROM users WHERE id = ANY(%s)", (ids,))
cur.execute("SELECT * FROM users WHERE id = ANY(%(ids)s)", {'ids': ids})

# id IN (1,2,3)のようなクエリを生成する場合
cur.execute("SELECT * FROM users WHERE id IN %s", (tuple(ids),))
cur.execute("SELECT * FROM users WHERE id IN %(ids)s", {'ids': tuple(ids)})

コメントを残す

メールアドレスが公開されることはありません。