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
– nathancahill, Commented Jan 26, 2014 at 17:44
@nathancahill breaking it out into two queries doesn't solve the type error on the image insert — SMTF
– SMTF, Commented Jan 26, 2014 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
– Craig Ringer, Commented Jan 27, 2014 at 2: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 stackoverflow.com/questions/17267417/… — Craig Ringer
– Craig Ringer, Commented Jan 27, 2014 at 2:22

Clodoaldo Neto · Accepted Answer · 2014-01-28 13:13:38Z

7

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)

edited Jan 28, 2014 at 13:13

answered Jan 27, 2014 at 9:02

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rgaponov Over a year ago

does we have select here as psycopg2.Binary ?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related