how to store image in postgres using Flask model

Question

I am trying to store an image using flask model. I don't know how to store the image in postgres, so I have encoded the image to base64 and I am trying to store that resulting text in postgres. It is working but is there any recommended way to store that encoded text or the image in postgres using flask model

class User_tbl(db.Model):
  id = db.Column(db.Integer,primary_key=True)
  mobile=db.Column(db.String(13),unique=True)
  country=db.Column(db.String(30))
  image=db.Column(db.String(256))

def __init__(self,mobile,country,image):
    self.mobile=mobile
    self.country=country
    self.image = image

Answer 1

I know that maybe it's too late to answer this question, but in this days I was trying to solve something similar and none of the solutions proposed seem to shed light on the main problem. Of course any best practice rests on your needs. In general terms however, you will find that embed a file in the database is not a good practice. Well, it depends. Reading the "Storing Binary files in the Database" produced by postgresql wiki, I discovered that there are some circumtances in which this practice is instead higly recommended, for instance when the files must be ACID. In those cases, at least in Postgres, bytea datatype is to be preferred over text or BLOB binary, sometimes at the cost of some higher memory requirements for the server.

In this case: 1) you don't need special sqlalchemy dialects. LargeBinary datatype will suffice, since it will be translated as a "large and/or unlengthed binary type for the target platform". 2) You don't need any encode/decode functions in PostgreSQL, of course in this specific case. 3) As I told before, it is not always a good strategy to save the files into the filesystem. In any case do not use text data type with base64 encoding. Your data will inflated more or less of the 33%, thus resulting in a huge storage impact, whereas bytea has not the same drawback

Thus, I propose these changes to your model:

class User_tbl(db.Model):
  id = db.Column(db.Integer,primary_key=True)
  mobile=db.Column(db.String(13),unique=True)
  country=db.Column(db.String(30))
  image=db.Column(db.LargeBinary)

Then you can save files into Postgres simply by passing your FileStorage parameter as a binary:

image = request.files['fileimg'].read()

Answer 2

It would be far easier to avoid all of this encoding and decoding and simply save it as a binary blob. In which case, use a sqlalchemy.dialects.postgresql.BYTEA column.

Answer 3

I know of the encode and decode functions in PostgreSQL for dealing with base64 data, see:

https://www.postgresql.org/docs/current/static/functions-string.html

(encode/decode)

Thanks,

Answer 4

The recomended way to store an image in postgres via flask is to store the image in your static folder(where you store Javascript & CSS files) and serve it via a web server i.e. nginx. It will be able to do it more efficiently than flask.You should only store the path to your image on postgres and then store the actual image on the File system.