What is the correct way to Insert an image into Postgres with python?

Question

I attempted to adapt the examples here and 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()

Results is:

TypeError: not all arguments converted during string formatting

Which makes sense to me as the image is not a String; however, I don't know how to insert properly. How should the insertion be performed?

You have two SQL statements in your query. I would split them in two. The insert statement should work fine with ```psycopg2.Binary``` — nathancahill, Jan 26 '14 at 17:44
@nathancahill breaking it out into two queries doesn't solve the type error on the image insert — SMTF, Jan 26 '14 at 18:23
You're doing an incorrect upsert there - it won't work in the face of concurrency. It's not immediately clear to me why Psycopg2 doesn't like this query; `psycopg2.Binary(image_data)` is indeed the correct approach for dealing with bytea data. Your query is also nonsense; the `INSERT` takes `(media_id, data)` but you've only supplied an argument for `media_id` in the `SELECT`-list. — Craig Ringer, Jan 27 '14 at 02:21
Please show what happens when you just try to do a simple insert (since this is about binary handling); keep the upsert mess for later. Regarding upserts, read http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql — Craig Ringer, Jan 27 '14 at 02:22

Clodoaldo Neto · Accepted Answer · 2014-01-28T13:13:38.747

Your heavily redacted code has many problems, some already pointed in the comments. I hope this example is reasonably clear

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)

What is the correct way to Insert an image into Postgres with python?

1 Answers1