Adding Images To MySQL

[Gabriel Genellina]

En Mon, 07 Apr 2008 15:17:26 -0300, Victor Subervi  
<victorsubervi at gmail.com> escribió:

>>     sql = "insert into PRODUCTS (PRODID, NAME, DESCRIPTION) " \
>>           "values (%s,%s,%s);"
>>     cursor.execute(sql, (prodid, prodname, description))
>>
>> and it works fine.
>>
>> Note that execute has two arguments: first, a string with the sql
>> statement text; second, a tuple containing the values. The %s in the sql
>> text are placeholders, they're replaced with the corresponding value  
>> from
>> the second argument. And there is no ' anywhere.
>
> Well, what I did, that now works, was essentially what you suggested, and
> simply assigned null values to variables that accept null values in the
> database. That works. What I was trying to do was build up just those
> variables that were to be updated or entered, and supply only those
> arguments. At this point it is rather academic, but can that be done?

Suppose you have the list of non-null column names in a variable  
`col_names` and their values in another list `col_values`

column_part = ','.join(col_names) # all,column,names,with,commas
pct_part = ','.join('%s' for _ in col_names) # as many "%s" as columns
sql = "insert into PRODUCTS (%s) values (%s);" % (column_part, pct_part)
cursor.execute(sql, col_values)

Perhaps you find more convenient to build a dictionary with column name,  
value: ok, then col_names above would be the keys() from the dictionary,  
and you can figure out how to get col_values.

> Amazingly (because I thought I had tested this) both the escape string  
> and
> the db Binary work now.

Glad to see that.

> I said it did not work in the other page, but now it does. Anyway, what  
> gets
> uploaded is a binary string. How can I convert that into a pretty  
> picture?

By serving it as the appropiate MIME type, image/jpeg by example. You  
don't have to "convert" anything, just return the same thing previously  
stored in the database. Again, test locally first: see if you can store a  
picture in the database and then retrieve it again without problems  
(locally, not thru the web).
Next step would be to use the web server but no database access, just  
reading a picture from a file:

content = open('image.jpg', 'rb').read()
print "Content-Type: image/jpeg\nContent-Length: %d\n" % len(content)
print content

The last step would be to replace the first line with a database query.

-- 
Gabriel Genellina