What datatype should I use for image in my flask sqlite database?

Question

So I have this db.model in my sqlite database in Flask. It looks like this:

class Drink(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(64), index = True)
    kind = db.Column(db.String(64), index = True)
    image = db.Column(db.LargeBinary)

    def __init__(self, name, kind, image):
        self.name = name
        self.kind = kind
        self.image = image

    def __repr__(self):
        return '<Drink %r>' % self.name

So, this issue is that I have this column, image, which will be an actual picture, but I don't know what datatype to use in the flask code.

Here is the flask code: Flask

class DrinkAPI(Resource):
    def __init__(self):
        self.reqparse = reqparse.RequestParser()
        self.reqparse.add_argument('name', type = str, required = True, help = 'No name title provided', location = 'json')
        self.reqparse.add_argument('type', type = str, required = True, help='No type provided', location = 'json')
        self.reqparse.add_argument('image', type = blob, required = True, help='No image provided', location = 'json')
        super(DrinkAPI, self).__init__()

    def get(self, id):
        if checkDrink(id):
            info = getDrinkInfo(id)
            return {'id': id, 'name': info[0], 'type':info[1], 'image': info[2]}
        abort(404)

    def put(self, id):
        if checkDrink(id):
            args = self.reqparse.parse_args()
            deleteDrink(id)
            drink = Drink(args['name'], args['type'], args['image'])
            addDrink(drink)
            return {'drink' : marshal(drink, drink_fields)}, 201 
        abort(404)

    def delete(self, id):
        deleteDrink(id)
        return { 'result': True}

See where I set the type of my reqparse of image to blob? That's not even an actual datatype, but I don't know what to put there. Do I need to subclass the fields.Raw? Any ideas? Thanks

NEW APPROACH TO QUESTION

Based on some comments, it seems like I should be storing the image in my static folder. I can do that. But then how do I reference it with my database? Is it a string that corresponds to the .jpg file?

It's usually a better idea to store images / large binary data in the filesystem. Not sure about your specific SQLite implementation, but most of these frameworks have easy ways to upload/serve images to and from the filesystem, storing a key or path in the DB. If you're set on using the database, maybe try: http://stackoverflow.com/questions/11017466/flask-return-image-created-from-database But beware this will be much less efficient than using the filesystem and serving static files. — Jordan P, Dec 05 '13 at 04:22
I guess it would be fine to just have images in a static folder. So, then I would just have a `String` in my database that is the path to the picture itself? How does that work? How does the database know that's it's a path and not just a weird string? — , Dec 05 '13 at 15:46

score 4 · Accepted Answer · answered Feb 03 '14 at 05:33

4

parser.add_argument(’picture’, type=werkzeug.datastructures.FileStorage, location=’files’)

answered Feb 03 '14 at 05:33

Mojiiz

7,898
6
26
25

+1 and accepted answer because it doesn't require the use of an html page and upload button, just the API request – Feb 10 '14 at 00:31

David K. Hess · Answer 2 · 2013-12-07T21:24:28.900

3

Normally images like this also need to be uploaded from a browser in addition to being served back out.

For that reason I use Flask-Uploads which handles all of the storage and naming issues for you when uploaded and when served.

Here's an example from the docs of how to use it:

photos = UploadSet('photos', IMAGES)

@app.route('/upload', methods=['GET', 'POST'])
def upload():
    if request.method == 'POST' and 'photo' in request.files:
        filename = photos.save(request.files['photo'])
        rec = Photo(filename=filename, user=g.user.id)
        rec.store()
        flash("Photo saved.")
        return redirect(url_for('show', id=rec.id))
    return render_template('upload.html')

@app.route('/photo/<id>')
def show(id):
    photo = Photo.load(id)
    if photo is None:
        abort(404)
    url = photos.url(photo.filename)
    return render_template('show.html', url=url, photo=photo)

You use the .save() method to store the incoming file in a directory. Flask-Uploads returns to you the filename it was saved with. Later, you use .url() or .path() to get a reference to the saved file.

edited Dec 07 '13 at 21:24

answered Dec 05 '13 at 19:42

David K. Hess

16,632
2
49
73

Doesn't this not fix the issue of how to represent it in the DB? – Dec 05 '13 at 20:45
Flask-Uploads has a .save() method which will take the upload, store it in a directory and return to you a filename that you can then store in the database. Later, when you want to retrieve the file, you use a .path() method which you pass the saved filename to and it will return to you a complete file path. – David K. Hess Dec 07 '13 at 21:21
2

Yes, this answer does not actually tell you how to store our images in the DB. The point is that you do *not* want to do that anyway. You should serve image files, and in fact all static content, directly from the file system and preferably via your "real" web server (apache/nginx/whatever) instead of through Flask. – Matthias Urlichs Jan 28 '14 at 10:40
@AlexChumbley, he modified his question at the bottom without changing the title of the question. – David K. Hess Jan 28 '14 at 20:00
Out of spec but, Please don't trust the user given file names and always hash those file names before storing them... A good read: http://flask.pocoo.org/docs/0.12/patterns/fileuploads/ – Leustad Jan 24 '17 at 18:50

What datatype should I use for image in my flask sqlite database?

2 Answers2