Problem in reading a "db" object using file uploader

Hi,

I am having a problem trying to upload the database object using file_uploader and read the tables from the database. I am using SQLite dB for the same.
Below is the sample code:

import sqlite3
file = st.file_uploader("Upload file ", type = “db”, encoding = ‘auto’)
conn = sqlite3.connect(file)

I am getting below error after running the above code:
expected str, bytes or os.PathLike object, not _io.BytesIO

Please help me on how to read the table in a db using the above method.

Hello @anshul.

st.file_uploader does not really upload a file, but loads its content in memory, whereas sqlite3.connect() expects a file path.

A solution is to create a temporary file and give its path to sqlite3.connect().

import streamlit as st
import tempfile
import sqlite3


conn = None
db = st.file_uploader("Upload a SQLite database file.", type="db")

if db:
    with tempfile.NamedTemporaryFile() as fp:
        fp.write(db.getvalue())
        conn = sqlite3.connect(fp.name)

if conn:
    # You can use your connection object here...
2 Likes

Thanks @Synode.

However, I am getting “unable to open database file” error while executing
“conn = sqlite3.connect(fp.name)”.

Can you please help me with this.

I didn’t have any issue with this database from sqlitetutorial.net : https://cdn.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip

Maybe your database file is invalid? Or the script failed to create a temporary file, but I need more info to solve this.

Hi @Synode,

I used the same code shared by you in a separate python file. I am getting the same error. Can it be because I dont have admin rights on my laptop ?

Is there any other way we can change the location of the temporary file ?

Indeed, it is probably a permission issue then.

Here is another version, it creates a temporary file with a random uuid name in the directory from which your have started streamlit. When the connection object is created, it removes the temporary file.

import streamlit as st
import sqlite3
import pathlib
import uuid


conn = None
db = st.file_uploader("Upload a SQLite database file.", type="db")

if db:
    fp = pathlib.Path(str(uuid.uuid4()))
    # fp = pathlib.Path("/path/to/your/tmpfile")
    try:
        fp.write_bytes(db.getvalue())
        conn = sqlite3.connect(str(fp))
    finally:
        if fp.is_file():
            fp.unlink()

if conn:
    st.write("Connection object:", conn)

@Synode… Thanks a lot… Sorry for troubling you so much. I am getting another error now while running the code.

"unlink() got an unexpected keyword argument ‘missing_ok’

Thanks in advance.

Oops, I forgot that missing_ok was a python 3.8 parameter. I’ve updated my last reply.
Replace fp.unlink(missing_ok=True) with:

if fp.is_file():
    fp.unlink()

@Synode

I am still facing an error here at line fp.unlik().

“[WinError 32] The process cannot access the file because it is being used by another process: ‘d7e0d930-3ff7-4929-bf3d-b0c3fc17b04b’”

There is no other program which is using the file. :frowning:

Try that, it’s a little bit cleaner and it should work in your case.

import sqlite3
import streamlit as st
from contextlib import contextmanager
from pathlib import Path
from uuid import uuid4


@contextmanager
def sqlite_connect(db_bytes):
    fp = Path(str(uuid4()))
    fp.write_bytes(db_bytes.getvalue())
    conn = sqlite3.connect(str(fp))

    try:
        yield conn
    finally:
        conn.close()
        fp.unlink()


db = st.file_uploader("Upload a SQLite database file.", type="db")

if db:
    with sqlite_connect(db) as conn:
        st.write("Connection object:", conn)
        # ... your code ...

1 Like

@Synode… Thanks a lot for your patience and bearing with me.

Your solution worked like a charm… :slight_smile:

1 Like

@Synode is :fire: :fire: :fire:!

3 Likes