Store and read a file in a sqlite3 database with SQLAlchemy in Python3

Question

I want to store and read a file (e.g. myfile.dat) from my hard drive into a sqlite3 database accessing over SQLAlchemy with Python3.

I want to store one picture for each row of Person-table. I just want to show that picture in a GUI when the data of that Person are shown.

Can you eloborate why? Usually files are stored separately, as SQLite is not optimized for this purpose. — Mikko Ohtamaa
– Mikko Ohtamaa, Commented Oct 4, 2015 at 20:21
@MikkoOhtamaa Please see the updated question-text. I don't know which optimizations there should be. A files is data. A database is to store data. I see no problem. — buhtz
– buhtz, Commented Oct 7, 2015 at 1:23
Most Python frameworks (Django) do this so that they they store the file on the the disk. Each file gots random id. In the database they only store a reference to this file: random file id and possibly the original filename. — Mikko Ohtamaa
– Mikko Ohtamaa, Commented Oct 7, 2015 at 21:26
If database grows size when you toss in files there normal database operations, not touching the file data, may suffer too. — Mikko Ohtamaa
– Mikko Ohtamaa, Commented Oct 7, 2015 at 21:27
That's why usually people want to keep files out of databases. — Mikko Ohtamaa
– Mikko Ohtamaa, Commented Oct 7, 2015 at 21:27

buhtz · Accepted Answer · 2015-10-07 01:43:53Z

3

Simply read the file in binary-mode from hard drive and store it as a BLOB to the database.

 with open('image.png', 'rb') as f:
     fcontent = f.read()

Get the BLOB (as image) from database and feed it to a Python-byte stream. Then you can use it e.g. as a Stream-Object in wxPython.

 # read the BLOB as 'image' from database
 # and use it
 stream = io.BytesIO(image)
 image = wx.Image(stream)
 bitmap = wx.Bitmap(image)

answered Oct 7, 2015 at 1:43

buhtz

12.5k22 gold badges95 silver badges196 bronze badges

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

1 Answer 1