How to save a image file on a Postgres database?

Question

For learning purposes, I'm creating a site using Python+Flask. I want to recover an image from database and show it on screen. But one step at a time.

I have no idea how to save an image in my database in the first place. My searches only revealed that I have to use a bytea type in my database. Then I get my image and somehow (??) convert it to an array of bytes (bytea == array of bites?) and somehow (??) use this array in a insert command.

I was able to discover (maybe) how to do it in Java (here) and C# (here), but I would really like to use Python, at least for now.

Can someone help me?

There are tons of questions of this kind in this site. But most (easily over 85%) of them are replied as "You shouldn't save images in your database, they belong in fs" and fail to answer the question. The rest don't quite solve my problem. So please don't mark this as duplicate if the duplicate has this kind of answer.

Solution 1

I don't normally write complete example programs for people, but you didn't demand it and it's a pretty simple one, so here you go:

#!/usr/bin/env python3

import os
import sys
import psycopg2
import argparse

db_conn_str = "dbname=regress user=craig"

create_table_stm = """
CREATE TABLE files (
    id serial primary key,
    orig_filename text not null,
    file_data bytea not null
)
"""

def main(argv):
    parser = argparse.ArgumentParser()
    parser_action = parser.add_mutually_exclusive_group(required=True)
    parser_action.add_argument("--store", action='store_const', const=True, help="Load an image from the named file and save it in the DB")
    parser_action.add_argument("--fetch", type=int, help="Fetch an image from the DB and store it in the named file, overwriting it if it exists. Takes the database file identifier as an argument.", metavar='42')
    parser.add_argument("filename", help="Name of file to write to / fetch from")

    args = parser.parse_args(argv[1:])

    conn = psycopg2.connect(db_conn_str)
    curs = conn.cursor()

    # Ensure DB structure is present
    curs.execute("SELECT 1 FROM information_schema.tables WHERE table_schema = %s AND table_name = %s", ('public','files'))
    result = curs.fetchall()
    if len(result) == 0:
        curs.execute(create_table_stm)

    # and run the command
    if args.store:
        # Reads the whole file into memory. If you want to avoid that,
        # use large object storage instead of bytea; see the psycopg2
        # and postgresql documentation.
        f = open(args.filename,'rb')

        # The following code works as-is in Python 3.
        #
        # In Python 2, you can't just pass a 'str' directly, as psycopg2
        # will think it's an encoded text string, not raw bytes. You must
        # either use psycopg2.Binary to wrap it, or load the data into a
        # "bytearray" object.
        #
        # so either:
        #
        #   filedata = psycopg2.Binary( f.read() )
        #
        # or
        #
        #   filedata = buffer( f.read() )
        #
        filedata = f.read()
        curs.execute("INSERT INTO files(id, orig_filename, file_data) VALUES (DEFAULT,%s,%s) RETURNING id", (args.filename, filedata))
        returned_id = curs.fetchone()[0]
        f.close()
        conn.commit()
        print("Stored {0} into DB record {1}".format(args.filename, returned_id))

    elif args.fetch is not None:
        # Fetches the file from the DB into memory then writes it out.
        # Same as for store, to avoid that use a large object.
        f = open(args.filename,'wb')
        curs.execute("SELECT file_data, orig_filename FROM files WHERE id = %s", (int(args.fetch),))
        (file_data, orig_filename) = curs.fetchone()

            # In Python 3 this code works as-is.
            # In Python 2, you must get the str from the returned buffer object.
        f.write(file_data)
        f.close()
        print("Fetched {0} into file {1}; original filename was {2}".format(args.fetch, args.filename, orig_filename))

    conn.close()

if __name__ == '__main__':
    main(sys.argv)

Written with Python 3.3. Using Python 2.7 requires that you read the file and convert to a buffer object or use the large object functions. Converting to Python 2.6 and older requires installation of argparse, probably other changes.

You'll want to change the database connection string to something suitable for your system if you're going to test-run it.

If you're working with big images consider using psycopg2's large object support instead of bytea - in particular, lo_import for store, lo_export for writing directly to a file, and the large object read functions for reading small chunks of the image at a time.

Solution 2

I hope this will work for you.

import Image
import StringIO
im = Image.open("file_name.jpg") # Getting the Image
fp = StringIO.StringIO()
im.save(fp,"JPEG")
output = fp.getvalue() # The output is 8-bit String.

StringIO Image

Solution 3

import psycopg2
import sys

def readImage():
    try:
        fin = open("woman.jpg", "rb")
        img = fin.read()
        return img

    except IOError, e:
        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)

    finally:
        if fin:
            fin.close()
try:
    con = psycopg2.connect(database="testdb", user="abc")
    cur = con.cursor()
    data = readImage()
    binary = psycopg2.Binary(data)
    cur.execute("INSERT INTO images(id, data) VALUES (1, %s)", (binary,) )
    con.commit()
except psycopg2.DatabaseError, e:
    if con:
        con.rollback()
    print 'Error %s' % e    
    sys.exit(1)
finally: 
    if con:
        con.close()

Solution 4

that's my solution, it can work in my website:

@main.route('/upload', methods=['GET', 'POST'])
def upload_avatar():
    if request.method == 'POST':
        file = request.files['file']
        if file and allowed_file(file.filename):
            current_user.avatar_local = file.read()
            db.session.add(current_user)
            db.session.commit()
            return redirect(url_for('main.user_page', username=current_user.username))
    return render_template('upload_avatar.html', user=current_user)

using Flask,Flask-Alchemy to handle database.

{% block edit_avatar  %}
    <form action="" method=post enctype=multipart/form-data>
      <p><input type=file name=file>
         <input type=submit value=Upload>
    </form>
{% endblock %}

that's html file.you can embed it in you html.

View more solutions

Comments

[Craig Ringer]

You can, but that's really not the right answer. The database efficiently stores binary data in bytea fields, so base64 encoding it is totally unnecessary.

[Felipe Matos]

Ok, I tried this and almost worked. For future reference, I first installed Python Image Library from here. I was able to run a query with your code (which is a great signal), but my database is UFT-8, so I found problems with encoding. After a bit o research directed to encoding, I discovered that (surprise!) psycopg supports this kind of operation, right here. I manage to insert the entry following those steps, now I have to find out how to recover it.

[Felipe Matos]

Great! That's exactly what I was looking for! I had already manage to insert a file in the database following a previous answer, but I was still lost when trying to recover it. As I'm using Python 2.7, I'll have to use a buffer object, like you said, but they look so complex to use! I'll do some research on it. Thanks, this was really helpful!

[Craig Ringer]

There's no need to actually load the image in PIL, you only need to read the file and store it in the DB. Nonetheless, +1 for a useful example.

[Craig Ringer]

@FelipeMatos Also, keep in mind that while the example above saves the image loaded from the database to a file, you can just as easily load it from the buffer into a PIL image for display, send it to a http client, etc. You very rarely need to write it to disk - and if you do, you'd generally use tempfile.TemporaryFile.

[iraycd]

@CraigRinger You are right. But if the image is modified and stored as a thumbnail. I guess this will be useful. :)

[reetesh11]

When I am trying this, I am getting *** TypeError: can't escape instance to binary

[Matt]

casting the data to psycopg2.Binary was the key for me, thanks!