Pythonでpg8000を使用してPostgreSQLに接続&Lambdaで使う

概要

PythonでPostgreSQLで接続するにはpsycopg2がよく使われますが
AWSのLambdaにserverless frameworkを使用してデプロイして実行する際にpsycopg2でハマってしまったのでPure Pythonのpg8000を使用しました

インストール

pip install pg8000

接続

import pg8000

conn = pg8000.connect(
    user='<user>',
    host='<host>',
    database='<db>',
    password='<password>'
)

# こんな感じもok
conf = {
    user='<user>',
    host='<host>',
    database='<db>',
    password='<password>'
}
conn = pg8000.connect(**conf)

SELECT

とりあえずテーブルはこんな感じで

id	name	age	from	language
1	shimizu	23	nagano	python
2	kondo	22	kanagawa	java
3	saito	23	kanagawa	javascript

cur = conn.cursor()

sql = 'SELECT * FROM user'

cur.execute(sql)
results = cur.fetchall()

for row in results:
    print(row)
'''
[1, 'shimizu', 23, 'nagano', 'python']
[2, 'kondo', 22, 'kanagawa', 'java']
[3, 'saito', 23, 'kanagawa', 'javascript']
'''

cur.close()
conn.close()

クエリの実行結果はlistを格納したtupleとして渡されます

INSERT

cur = conn.cursor()

kojima = (4, 'kojima', 23, 'aichi', 'python')
users = (
    (5, 'kobayashi', 24, 'kanagawa', 'java'),
    (5, 'maeda', 24, 'tokyo', 'swift'),
)

sql = '''
INSERT INTO user(id, name, age, from, language)
    VALUES(%s, %s, %s, %s, %s)
'''

try:
    cur.execute(sql, kojima)
    cur.executemany(sql, users)
    conn.commit()
except Exception as e:
    print(e)

cur.close()
conn.close()

プレースホルダ(%s)を利用してtupleをINSERTしています
ちなみに以下のようにするとオートコミットになります

conn.autocommit = True

プレースホルダの種類

execute()する前に以下のように設定すると、プレースホルダのパラメータスタイルを変更できます。

pg8000.paramstyle = 'named'

format(デフォルト): %s

おそらく何も設定していない状態だと%sが適用されます
execute()の第2引数のtupleから順番に適用されます

qmark: ?

%sが?に変わっただけです

sql = '''
INSERT INTO user(id, name, age, from, language)
    VALUES(?, ?, ?, ?, ?)
'''

try:
    pg8000.paramstyle = 'qmark'
    cur.execute(sql, (4, 'kojima', 23, 'aichi', 'python'))
    conn.commit()
except Exception as e:
    print(e)

named: :hoge

phpのpdoを使っている人は馴染み深いかなと思います

execute()の第2引数としてdictを渡すことができます
:<dictのkey名>とdictのkeyが対応して、keyに紐づいたvalueが適応されます

sql = '''
INSERT INTO user(id, name, age, from, language)
    VALUES(:id, :name, :age, :from, :language)
'''

user = {
    'id': 4,
    'name': 'kojima',
    'age': 23,
    'from': 'aichi',
    'language': 'python'
}

try:
    pg8000.paramstyle = 'named'
    cur.execute(sql, user)
    conn.commit()
except Exception as e:
    print(e)

pyformat: %(hoge)s

:namedとそんなに変わらないです

sql = '''
INSERT INTO user(id, name, age, from, language)
    VALUES(%(id)s, %(name)s, %(age)s, %(from)s, %(language)s)
'''

user = {
    'id': 4,
    'name': 'kojima',
    'age': 23,
    'from': 'aichi',
    'language': 'python'
}

try:
    pg8000.paramstyle = 'pyformat'
    cur.execute(sql, user)
    conn.commit()
except Exception as e:
    print(e)

その他

paramstyleは上記で挙げた以外にもあります 参考(github)
もちろんexecute()だけでなくexecutemany()にも対応しています

UPSERT

今回記事を書いたきっかけとしてUPSERTをしたかったのでUPSERTも載せときます

sql = '''
INSERT INTO user(id, name, age, from, language)
    VALUES(:id, :name, :age, :from, :language)
    ON CONFLICT ON CONSTRAINT user_pkey 
    DO UPDATE SET
        id=:id, name=:name, age=:age, from=:from, language=:language
'''

serverless frameworkについて

今回はパッケージ管理はpipenvを使用しています

pipenv install pg8000

pipenvで作成したPipfileを一緒にデプロイするツールとしてserverless-python-requirementsを使用しています

最後に

今回はpg8000を使用しましたが、serverless-python-requirementsのdockerizePip: non-linuxを使用すればAWSのLambda用にDockerイメージからビルドしてくれるようなので、psycopg2使用できるかもしれません