pysqlite - how to save images

Question

I need to save an image file into sqlite database in python. I could not find a solution. How can I do it?

Thanks in advance.

Gemstone

Answer 1

write - cursor.execute('insert into File (id, name, bin) values (?,?,?)', (id, name, sqlite3.Binary(file.read())))

read - file = cursor.execute('select bin from File where id=?', (id,)).fetchone()

if you need to return bin data in web app - return cStringIO.StringIO(file['bin'])

Unamuno

Answer 2

Do you have to store the image in the database? I would write the image to the filesystem and store its path in the DB. (You may not be able to do this, depending on your particular case.)

If you absolutely must, look here.

Soapsuds

not the post itself but the second comment solved my problem. Thanks. – Gemstone

Answer 3

I am not sure if pysqlite is the same as sqlite3, which is currently default in the standard python library. But if you use sqlite3 you can store the image in a buffer object and store that in a blob field in sqlite. Be aware of the following though:

• storing images in a database is frowned upon by some, storing files and path in the database is the other possibility.
• make sure you return the proper mime type

Publius

Answer 4

It's never a good idea to record raw types in databases. Couldn't you just save the file on the filesystem, and record the path to it in database?

Catabolite

I need to save it into the database. It will be a small image. – Gemstone