スポンサードサーチ
接続
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)})