Insert an image in postgresql database

Question

I would like to know How can I insert an image "bytea" into a table of my postgreSql database? I've been searching forums for hours and have seen the same question posted dozens of times, but yet to find a single answer. All I see is how to insert .jpeg's into an old column which isn't what I need.

Here's the database table:

create table category  (
"id_category" SERIAL,
"category_name" TEXT,
"category_image" bytea,
constraint id_cat_pkey primary key ("id_category"))without oids;

and when I add a new line, it doesn't work :

insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));

Answer 1

insert into category(category_name,category_image) values('tablette', bytea('D:\image.jpg'));

The above solution works if column type is bytea

insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));

The above solution works if column type is oid i.e., Blob

insert into category(category_name,category_image) values('tablette',decode('HexStringOfImage',hex));

The above decode function take two parameters. First parameter is HexString of Image.The second parameter is hex by default.Decode function coverts the hexString to bytes and store in bytea datatype column in postgres.

Answer 2

None of the above example worked well for me and on top of that I needed to add many images at once.

Full working example (python 3) with explanations:

With get_binary_array we get the value of the image (or file) as a binary array, using its path and file name as parameter (ex: '/home/Pictures/blue.png').

With send_files_to_postgresql we send all the images at once.

I previously created the database with one sequential 'id' that will automatically be incremented (but you can use your own homemade id) and one bytea 'image' field

import psycopg2

def get_binary_array(path):
    with open(path, "rb") as image:
        f = image.read()
        b = bytes(f).hex()
        return b

def send_files_to_postgresql(connection, cursor, file_names):
    query = "INSERT INTO table(image) VALUES (decode(%s, 'hex'))"
    mylist = []
    for file_name in file_names:
        mylist.append(get_binary_array(file_name))

    try:
        cursor.executemany(query, mylist)
       
        connection.commit()  # commit the changes to the database is advised for big files, see documentation
        count = cursor.rowcount # check that the images were all successfully added
        print (count, "Records inserted successfully into table")
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

def get_connection_cursor_tuple():
    connection = None
    try:
        params = config()
        print('Connecting to the PostgreSQL database...')
        connection = psycopg2.connect(**params)
        cursor = connection.cursor()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

    return connection, cursor

connection, cursor = connect_db.get_connection_cursor_tuple()
img_names = ['./blue.png', './landscape.jpg']
send_files_to_postgresql(connection, cursor, img_names)

Answer 3

If the column type is bytea then you can simply use the 'pg_read_binary_file'.

Example: pg_read_binary_file('/path-to-image/')

check postgresql documentation of pg_read_binary_file