Displaying an SqlLite Image

Using Streamlit
1

I’ve searched the forum on this and didn’t find a direct answer to this.

I’m adding an option to my receipt manager to be able to take a picture of a receipt as opposed to typing it all in. At this point I’m not adding any AI, just a simple photo to keep a record. Maybe AI later…

So I have a BLOB field in my database table for receipts named receipt_image. I created a page to take a picture of the image and store it in the record. That is all working fine (verified by using the SQLite Browser to confirm the image is viewable in the BLOB field). Now I have added a link to the dataframe that lists the receipts to open a new page that will display the image. The page is as follows:

import database

import streamlit as st

conn = database.ReceiptsDatabase()

conn.database_connect()

# Get the image id

image_id = st.query_params.get(“image_id”, None)

# Get the image passed, if any

image_stream = conn.get_receipt_image(image_id)

with st.form(“View image”):

st.title(“View image”)

if image_stream:

st.image(image_stream)

submit = st.form_submit_button(“Go back”, on_click=st.query_params.clear)

And here’s the function for get_receipt_image:

def get_receipt_image(self, record_id):

    image_stream = None

conn = self.database_connect()

sql_string = “”"select receipt_image from receipts

                    where id = :id"""

results = conn.query(sql_string,

params={

“id”:record_id

                            },

ttl=0)

if len(results):

for row in results:

image_bytes = bytes(row[0], “utf-8”)

image_stream = BytesIO(image_bytes)

return image_stream

But when this runs I’m getting the following error:

PIL.UnidentifiedImageError: cannot identify image file <_io.BytesIO object at 0x7fceaed309f0>

File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/runtime/scriptrunner/exec_code.py”, line 129, in exec_func_with_error_handling
result = func()
^^^^^^
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/runtime/scriptrunner/script_runner.py”, line 671, in code_to_exec
exec(code, module.dict) # noqa: S102
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/jonas/Projects/ReceiptManager/main.py”, line 58, in
pg.run()
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/navigation/page.py”, line 310, in run
exec(code, module.dict) # noqa: S102
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/jonas/Projects/ReceiptManager/pages/viewreceiptimage.py”, line 15, in
st.image(image_stream)
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/runtime/metrics_util.py”, line 531, in wrapped_func
result = non_optional_func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/elements/image.py”, line 206, in image
marshall_images(
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/elements/lib/image_utils.py”, line 445, in marshall_images
proto_img.url = image_to_url(
^^^^^^^^^^^^^
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/elements/lib/image_utils.py”, line 336, in image_to_url
image_format = _validate_image_format_string(image_data, output_format)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/streamlit/elements/lib/image_utils.py”, line 111, in _validate_image_format_string
pil_image = Image.open(io.BytesIO(image_data))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/jonas/Projects/ReceiptManager/.venv/lib/python3.12/site-packages/PIL/Image.py”, line 3560, in open
raise UnidentifiedImageError(msg)

I’m not sure what to do here. Am I supposed to save the image in a file as a specific type so PIL can recgonize?

2

Figure it out. My code for retrieving the image content from the database was incorrect. Here’s how it should have been done:

    def get_receipt_image(self, record_id):
        image_stream = None
        conn = self.database_connect()
        sql_string = """select receipt_image from receipts
                        where id = :id"""
        results = conn.query(sql_string,
                                params={
                                    "id":record_id
                                },
                                ttl=0)
        
        if results.shape[0] > 0:
            image_stream = results.loc[0]['receipt_image']
        return image_stream

And I figured it out when I finally but the code in debug just to check the results of the get_receipt_image() call, it was returning ‘r’.

I also decided to not convert the image to bytes in the database handling file but rather on the Streamlit page itself.

3

Ah — the issue here isn’t with Streamlit or PIL per se, it’s with how you were converting the image from the database.

In your original code you did:

image_bytes = bytes(row[0], "utf-8")
image_stream = BytesIO(image_bytes)

The problem is that image data is already binary, and calling bytes(..., "utf-8") corrupts it. That’s why PIL can’t recognize it. Images stored as BLOBs in SQLite or other databases should be used as-is, without trying to encode/decode as UTF‑8.

Your corrected version does exactly that:

if results.shape[0] > 0:
    image_stream = results.loc[0]['receipt_image']

Then, on the Streamlit page, you can wrap it in a BytesIO if necessary:

from io import BytesIO
st.image(BytesIO(image_stream))

Key takeaways:

  1. Don’t encode BLOBs as UTF-8 — they are already bytes.
  2. You can convert them to BytesIO on the Streamlit side for st.image().
  3. PIL will now recognize the image because it gets the correct binary data.

With this, you don’t need to save the image as a separate file — Streamlit can display it directly from the BytesIO object.
Hope it helps

1 Like
4

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.