psycopg2で画像データを読み書きする

psycopg2で画像データを読み書きする方法です。

実際のところ

画像データの格納にはbytea型を使います。
PostgreSQLのbytea型はmemorybuffer型を返すので、ちゃんとbytes関数なりで整形しないといけません。

import psycopg2

DSN  = "dbname=postgres user=shuzo_kino"
SQL3 = "INSERT INTO image(byte_data)\
        VALUES (%(imagedata)s);"
SQL4 = "SELECT byte_data FROM image \
        WHERE id = (%(id)s) LIMIT 1;"

def pushImage(query, filename):
  fp        = open(filename, "rb").read()
  imgdata   = psycopg2.Binary(fp)
  postquery = {'imagedata': imgdata}
  curs.execute(query, postquery)

def saveImage(query, filename, id):
  vals = {'id': id}
  curs.execute(query, vals)
  fp = curs.fetchone()[0]
  with open(filename,'wb') as file:
    file.write(bytes(fp))

with psycopg2.connect(DSN) as conn:
  with conn.cursor() as curs:
    distImg = "/Users/shuzo_kino/Desktop/sample.png"
    saveImage(SQL4, distImg, 16)

    sourceImg = "/Users/shuzo_kino/Mexico.Tacos.01.jpg"
    pushImage(SQL3, sourceImg)

参考もと

Basic module usage — Psycopg 2.6.2 documentation
python - How to save a image file on a Postgres database? - Stack Overflow
buffer - What exactly is the point of memoryview in Python - Stack Overflow