Adding Images to MySQL

Wed Apr 2 17:05:57 EDT 2008

En Wed, 02 Apr 2008 10:05:56 -0300, Victor Subervi  
<victorsubervi at gmail.com> escribió:

> I have tried the following code:
>
> #!/usr/local/bin/python
> import _mysql
> import MySQLdb
> host = 'mysqldb2.ehost-services.com'
> user = 'user'
> passwd = 'pass'
> db = 'bre'
> print 'Content-Type: image/jpeg\r\n'
> print '<html><body>\nHi!\n'
> db=MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
> c=db.cursor()
> imgfile=open("1.jpg",'rb')
> f = imgfile.read()
> sqlstr="insert into photo (id, img) values ('1', '" +
> _mysql.escape_string(imgfile.read()) +"');"
> c.execute(sqlstr)
> imgfile.close()
> c.close()
> print '\nBye!\n</body></html>'
>
> which prints Hi! but not Bye! and gives me an HTTP 200 error. I threw the
> line
> f = imgfile.read()
> in there just to make sure it is reading the imgfile. Also, I tested it  
> with
> all the import statements alone to make sure it was importing  
> everything. So
> the problem is the c.execute statement. Please advise.

(What a mess! I don't know where to begin...)

- You say Content-Type: image/jpeg but you emit HTML code. You're lucky if  
you see any text at all.
- HTTP 200 is not an error, it means the request was successful.
- See the site logs looking for sql errors.
- See the cgitb module http://docs.python.org/lib/module-cgitb.html
- Have you read what I wrote in the message you're replying to? Use bound  
parameters instead of building the SQL values yourself. I'm keeping it at  
the bottom of this message
- As a general advice, try to isolate the problems. Test the database  
stuff alone, in a local application. Test the cgi script alone, without  
database interaction. Test the database stuff in the web server (better if  
you have a shell account). Merge all and test again.
- Please don't top post; quote the relevant parts and write your comments  
below the text you're replying to.

> On Tue, Apr 1, 2008 at 1:37 PM, Gabriel Genellina  
> <gagsl-py2 at yahoo.com.ar> wrote:
>> En Tue, 01 Apr 2008 09:36:00 -0300, Victor Subervi
>> <victorsubervi at gmail.com> escribió:
>>
>> > Hi;
>> > I´m trying to figure out how to upload images into a MySQL database.
>> > (Yes,
>> > that is what I want to do.) I have a form that asks for data, like  
>> this:
>> > 1ra Foto Pequeña:
>> > <input type='file' name='pic1' />
>> > Then I send that form to a python script that processes like this:
>> > cursor.execute('insert into products (' + col_names + ') values (' +
>> > col_values + ');')
>> > where col_names is all the names of the columns and col_values,
>> > obviously,
>> > the values. Works fine for strings and digits. Not so well for files  
>> :)
>> > What
>> > do?
>>
>> Always use bound parameters - not only it's easier and less error prone,
>> it's safer too. How parameters are specified depends on the DBAPI module
>> you're using - read the module documentation. I think MySQLdb accept
>> dictionary-like marks:
>>
>> values = {'name': 'Red jar',
>>           'descr': 'A nice red jar',
>>           'pic': binary(picdata)
>>          }
>> cursor.execute('''insert into products (name,description,picture)
>>     values (%(name)s, %(descr)s, %(pic)s);''', values)
>>
>> See PEP249 http://www.python.org/dev/peps/pep-0249/ and the  
>> documentation
>> for your database module.

-- 
Gabriel Genellina