CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
SMTF
SMTF

Reputation: 705

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

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?

Upvotes: 4

Views: 4087

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

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)

Upvotes: 7

Related Questions