How to save image file in Postgres database?

Question

For training purposes, I create a site using Python + Flask. I want to restore an image from a database and show it on the screen. But one step at a time.

I do not know how to save the image in my database in the first place. My searches showed that I should use the 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 the insert command.

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

Can someone help me?

There are many questions of this kind on this site. But most (easily more than 85%) of them answer as "You do not have to save images in your database, they belong to fs" and cannot answer the question. The rest do not quite solve my problem. Therefore, please do not mark this as a duplicate if the duplicate has such an answer.

Answer 1

I usually don’t write complete examples of programs for people, but you didn’t require it, and it’s pretty simple, so here you are:

 #!/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 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 by Python 3.3. Using Python 2.7 requires you to read the file and convert it to a buffer object or use the large functions of the object. Converting to Python 2.6 and later requires installing argparse, possibly other changes.

You want to change the database connection string to something suitable for your system if you are going to test it.

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

Answer 2

I hope this works 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

Answer 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() 

Answer 4

You can use Python base64 to encode and decode arbitrary binary strings to text strings.

Answer 5

what is my solution, it can work on 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 process the 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 html file. You can paste it in html.