Problem in reading a "db" object using file uploader

[anshul]

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.

[okld]

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...

[anshul]

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.

[okld]

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.

[anshul]

@okld

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)

Hi @okld,

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 ?

[okld]

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)

[anshul]

@okld

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:
        fp.unlink(missing_ok=True)

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.

[okld]

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

[anshul]

@okld

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.

[okld]

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 ...

[anshul]

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

Your solution worked like a charm…

[Charly_Wargnier]

@okld is !

[luisflarota]

Hi, Thanks for the solution given to synode. However, I am facing another issue.

Every time I read the db file it creates a file (same size as database, it is just a “file” type) and stores it in my folder.

Thanks in advance!

[okld]

Hello @luisflarota, welcome to the forum!

My solution does create temporary files like shown in your screenshot. It has the “File” type because I don’t save the database as a “.db” file.

However you should have only one of those present. When the script ends, it should delete the temporary database file.

Could you share your code so that I can reproduce the issue?

[luisflarota]

Thanks for answering synode. I think the solution was given from you to anshul. Sorry about that!

I understand what you say but every time I run my code (testing everything is correct) it creates files

This is the code I have for the backend:

class Basededatos:
    def __init__(self, data):
        self.connector = s3.connect(data)
    def cycle_time(self):
        query = pd.read_sql_query('SELECT.....', self.connector)
        return pd.DataFrame(query)
    def distancia_promedio(self):
        query = pd.read_sql_query('SELECT.....', self.connector)
        return pd.DataFrame(query)

This is the code I have for the frontend:

def main():
    st.set_option('deprecation.showfileUploaderEncoding', False)
    st.sidebar.write('Bring your database here:')
    data = st.sidebar.file_uploader('*Upload or drop the file, maxsize = 1GB:', type = 'odb')
        if data:
            datos = Loading_file(data)
            if st.sidebar.checkbox('Distancia promedio'):
                st.success('Loading Distancia promedio')
                st.dataframe(datos.dist_prom().style.format({'Distancia': '{:.2f}'}))
            if st.sidebar.checkbox('Tiempos de ciclo'):
                st.success('Loading tiempos de ciclo:')
                st.plotly_chart(datos.cicle_time())    
class Loading_file:
    def __init__(self,data):
        fp = pathlib.Path(str(uuid.uuid4()))
        fp.write_bytes(data.getvalue())
        self.data = back.Basededatos(str(fp))

Thanks again!

[okld]

The issue here is that nowhere in your code you tell to remove the database.
Your Loading_file class creates a file, but does not delete it in the end.

Here’s how you can fix it (I didn’t test the code though ):

def main():
    st.set_option('deprecation.showfileUploaderEncoding', False)
    st.sidebar.write('Bring your database here:')
    data = st.sidebar.file_uploader('*Upload or drop the file, maxsize = 1GB:', type = 'odb')
    if data:
        # This `with` statement will call Loading_file.__enter__()
        # When your code goes out of this `with` block, Loading_file.__exit__()
        # will be automatically called, even if an exception occurs.
        with Loading_file(data) as datos:
            if st.sidebar.checkbox('Distancia promedio'):
                st.success('Loading Distancia promedio')
                st.dataframe(datos.dist_prom().style.format({'Distancia': '{:.2f}'}))
            if st.sidebar.checkbox('Tiempos de ciclo'):
                st.success('Loading tiempos de ciclo:')
                st.plotly_chart(datos.cicle_time())    


class Loading_file:

    def __init__(self, data):
        self.bytes = data.getvalue()
        self.data = None
        self.fp = None
    
    def __enter__(self):
        """Called when entering a `with` block."""
        self.fp = pathlib.Path(str(uuid.uuid4()))
        self.fp.write_bytes(self.bytes)
        self.data = back.Basededatos(str(self.fp))
        # We return the current object
        return self  
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        """Run on `with` block exit."""
        # Here we just have to remove the temporary database file
        self.fp.unlink()

    # ... the rest of your methods ...

If you want to learn more about context managers (with statement), you can check out this link:

• 27. Context Managers — Python Tips 0.1 documentation

[luisflarota]

Thank @okld. You’re a genious + lifesaver!!!

[ravinpoudel]

Hi, I am trying to follow the similar/same approach to read in file and want to get file path. After having file path, I want to pass it to the Subprocess. I was able to see a randomfile name with content same as the input file being created in the directory, but still not getting the file path. Following is my code–basically modify the one above:

@contextmanager
def genome_connect(db_bytes):
    fp = Path(str(uuid4()))
    fp.write_bytes(db_bytes.getvalue())
    conn = str(fp)
    try:
        yield conn
    finally:
        conn.close()
        fp.unlink()


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

if genome:
    with genome_connect(genome) as conn:
        st.write("Connection object:", conn)

For subprocess:

args = ["guidemaker",
        "-i",conn,
        "-p",pam,
        "--guidelength",str(guidelength),
        "--strand",strand,
        "--lcp",str(lcp),
        "--dist",str(dist),
        "--outdir","out",
        "--log","logfile.txt",
        "--threads",str(threads)]


def run_command(args):
    """Run command, transfer stdout/stderr back into Streamlit and manage error"""
    #st.info(f"Running '{' '.join(args)}'")
    result = subprocess.run(args, capture_output=True, text=True)
    try:
        result.check_returncode()
        #st.info(result.stdout)
        st.text(result.stderr)
    except subprocess.CalledProcessError as e:
        st.error(result.stderr)
        raise e