python - 使用 python 将图像插入 Postgres 的正确方法是什么？

Question

我试图改编示例 here和 here

import psycopg2
#Given media_id and image_url and conn object
image_data = urllib2.urlopen(image_url).read()
sql =("INSERT INTO images (media_id, data) 
SELECT %s
WHERE 
NOT EXISTS (SELECT media_id FROM images WHERE media_is = CAST(%s as TEXT) ")
data_insert_image = (media_id, psycopg2.Binary(image_data))
cursor.execute(sql_insert_image, data_insert_image)
conn.commit()

结果是:

TypeError: not all arguments converted during string formatting

这对我来说很有意义，因为图像不是 String；但是，我不知道如何正确插入。应该如何进行插入？

Answer 1

您经过大量编辑的代码有很多问题，有些问题已经在评论中指出。我希望这个例子相当清楚

import psycopg2, urllib2

image_url = 'http://example.com/theimage.jpg'
image_data = urllib2.urlopen(image_url).read()
media_id = 3

# triple quotes allows better string formating
sql = """
    with s as (
        select media_id 
        from images 
        where media_id = %(media_id)s
    )
    insert into images (media_id, data)
    select %(media_id)s, %(data)s
    where not exists (select 1 from s)
    returning media_id
;"""

# a parameter dictionary is clearer than a tuple
data_insert_image = {
    'media_id': media_id,
    'data': psycopg2.Binary(image_data)
}

conn = psycopg2.connect("host=localhost4 port=5432 dbname=db user=u password=p")
cursor = conn.cursor()
cursor.execute(sql, data_insert_image)
# fetchone returns a single tuple or null
result = cursor.fetchone()
conn.commit()

if result is not None:
    print 'Media Id {0} was inserted'.format(result[0])
else:
    print 'Media Id {0} already exists'.format(media_id)