How to display a BLOB image stored in my SQLite database?

Question

I have a CRUD form with entries and 4 button for deleting, updating, creating, getting values from my database, I want to implement another button to open an imagen which is binded to my id entry also able to works with my deleting, updating, creating, getting buttons, I've been trying to use BLOB and I'm able to save an image in my database as BLOB. Actually I understand that I need to create textvariables for my entries like 'idvar = StringVar() , namevar = Stringvar()..., etc', So I'm not sure how to do it for an image label in order to work with my CRUD buttons deleting, updating, creating, getting

This is my code I got so far and it's working well saving images into my photos columns:

from tkinter import *
import sqlite3

top = Tk()
top.configure(width='444', heigh='400')

conn = sqlite3.connect('test.db')
c = conn.cursor()

def enterdata():
    id = 'hello'
    photo = convert_pic()
    c.execute('INSERT INTO test (id, photo) VALUES (?, ?)', (id, photo)) #Here my id has integer value and my photo has BLOB value in my database 
    conn.commit()

def convert_pic():
    filename = 'images/image6.jpg'
    with open(filename, 'rb') as file:
        photo = file.read()
    return photo


btn = Button(top, text='save photo', command=enterdata)
btn.place(x='100', y='111')


mainloop()

@CoolCloud Yes, the image 'images/image6.jpg' is saved correctly in my database when I push the button btn = Button(top, text='save photo', command=enterdata — Paul
– Paul, Commented Mar 7, 2021 at 14:50

Delrius Euphoria · Accepted Answer · 2021-03-07 16:15:00Z

2

Now that you have the BLOB you can use io.BytesIO. I will create an example to demonstrate, like:

from PIL import Image, ImageTk
from io import BytesIO

def show(data):
    img_byte = BytesIO(data)
    img = ImageTk.PhotoImage(Image.open(img_byte))
    Label(root,image=img).pack()
    root.image = img # Keep a reference

So now you can query the database and fetch the value:

def fetch():
    c = con.cursor()
    id = 1 # Any id
    c.execute('SELECT photo FROM test where id=?',(id,))
    data = c.fetchall()[0][0] # Get the blob data
    show(data) # Call the function with the passes data

This will show the image in a label in the root window for the entered id.

edited Mar 7, 2021 at 16:15

answered Mar 7, 2021 at 15:06

Delrius Euphoria

15.1k3 gold badges20 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Paul Over a year ago

@CooCloud it shows this error IndexError: tuple index out of range , I took [0][1] because my photo is in the second column

Delrius Euphoria Over a year ago

@Paul Did you try [0][0], because we are just selecting one column.

Paul Over a year ago

I tried [0][0] but once push button designed to call that function it seems the the photo charged and it expands the window but I can't see the photo itself

Delrius Euphoria Over a year ago

@Paul Sorry, my bad, add root.image = img to the end of show().

Paul Over a year ago

Awesome, it's working fine, though I'm little confused about my CRUD form, could you give me some advices how to implement it with photos?, should I post another question or just write it here directly?

|

Collectives™ on Stack Overflow

How to display a BLOB image stored in my SQLite database?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related