Processing MS Access .mdb Files

☁️ Community Cloud
1

Greetings and salutations!

Very new to python in general and web-apps in particular. I made a nifty script that decomposes an MS Access database into separate CSV files for later processing.

def connect_odbc(db_filepath):
    """Connect to database located at input db_filepath"""

    import pyodbc

    # The DRIVER curly brackets are doubled up to escape themselves
    # The DBQ curly brackets are NOT doubled up because they are placeholders
    con_string = (
        "DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};" "DBQ={};"
    ).format(db_filepath)

    cnxn = pyodbc.connect(con_string)
    crsr = cnxn.cursor()

    return crsr


# GET FILENAME INPUT
db_filepath = st.file_uploader("Select TestStand .mdb file to process", type=["mdb"])

# BUTTON to GET db_filepath
if st.button("connect db"):

    # Connect to DB
    st.text("TestStand database file path is {}.".format(db_filepath))
    crsr = connect_odbc(db_filepath)

However, it only works when running locally. When I try to run this code in Streamlit (so I can share with other clients and colleagues), the pyodbc fails to connect::

TestStand database file path is UploadedFile(id=1, name=‘TestStand Results 002.mdb’, type=‘application/msaccess’, size=14327808).

Error: (‘HY024’, “[HY024] [Microsoft][ODBC Microsoft Access Driver] ‘(unknown)’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. (-1023) (SQLDriverConnect); [HY024] [Microsoft][ODBC Microsoft Access Driver] ‘(unknown)’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. (-1023)”)

How do I get the ‘uploaded’ path correctly parsed into the pyodbc connector??

Thank you for your advice and insights!

May peace be with you,
Ash

2

Hey @Ash010110,

Thanks for sharing this question.

The st.file_uploader widget doesn’t actually create a file when a file is uploaded; instead, the file is temporarily stored in memory (the return value is a BytesIO object) – as a result, you wouldn’t be able to retrieve a path to the file.

If you need to pass the file itself to another function, you’ll need to add code to store the file somewhere (not in memory), and then you can use the path to that stored file (here’s a thread where someone shared how they used st.file_uploader and then stored the uploaded file in AWS S3, as an example).

Since the file is presumably saved on your local machine, you’re able to run the app locally and use the path to the local file.

Some threads that might be helpful:

3

Hello @Caroline ,

Thank you so much for the response!

So for my use-case, I want to use the python script in the Streamlit server to act on an MS Access database that is on the local machine.

Does this mean I don’t need to upload at all? I can just prompt the user of the Web App to provide the filepath to their local .mdb file they wish to parse, and the Streamlit app can “operate” on that file?

May peace be with you,
Ash

4

Hi @Ash010110,

If the user of the app needs to provide the app with a file stored on their local machine, you should still have them upload the file using st.file_uploader, but it sounds like you’re going to need to store that file somewhere (rather than just working with the in-memory file-like object). For example, you can store the file in AWS S3 and then pass the path to that file to the pyodbc connector.

Does this mean I don’t need to upload at all? I can just prompt the user of the Web App to provide the filepath to their local .mdb file they wish to parse, and the Streamlit app can “operate” on that file?`

You would only be able to do this ^ if the users are running your app locally on their computers (i.e. it’s not deployed – since a deployed app won’t have access to their local machine).

5

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