Streamlit上传stock.db出现FileUploaderEncodingWarning的解决方法

Let's break down what's causing this warning and how to fix it step by step.

What's the Warning About? #

FileUploaderEncodingWarning pops up because you're handling a binary file (SQLite .db files are binary, not plain text) with Streamlit's file_uploader, but the default behavior assumes text encoding. When you don't explicitly handle the binary data correctly, Streamlit throws this warning to alert you that you might be misprocessing the file.

Key Issues in Your Current Code #

1. You're creating a temporary file but not explicitly using binary write mode, which triggers the encoding warning.
2. Your main() function still tries to connect to a local ./stock.db instead of the uploaded database file.
3. Database helper functions are defined inside the if db: block, meaning they won't be accessible to main() if the file upload condition isn't met (or vice versa).
4. There's a typo (compamy_name instead of company_name) in your create_project SQL query that would cause a database error later.

Fixed Code Implementation #

import sqlite3
from sqlite3 import Error
import csv
import numpy as np
import tempfile
import streamlit as st
import traceback

# Move helper functions to global scope so they're accessible everywhere
def create_connection(db_file):
    """ create a database connection to the SQLite database specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)
    return conn

def create_project(conn, project):
    """ Create a new project into the projects table
    :param conn:
    :param project:
    :return: project id
    """
    sql = ''' INSERT INTO projects(company_name,begin_price,end_price)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, project)
    conn.commit()
    return cur.lastrowid

def create_task(conn, task):
    """ Create a new task
    :param conn:
    :param task:
    :return:
    """
    sql = ''' INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
              VALUES(?,?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()
    return cur.lastrowid

def main(conn):
    if not conn:
        st.error("No valid database connection found!")
        return
    
    try:
        curs = conn.cursor()
        curs.execute('''CREATE TABLE IF NOT EXISTS stock (
                            id INT PRIMARY KEY,
                            company_name VARCHAR(200),
                            now_price INT
                        )''')
        conn.commit()
        curs.close()
    except Exception as e:
        st.error(f"Error creating table: {traceback.format_exc()}")
        return
    
    # Note: If data.csv is a local file, ensure it's in the same directory as your app
    # Alternatively, add a file uploader for the CSV to make it more flexible
    try:
        with open('data.csv', encoding='utf-8-sig') as file:
            data = csv.DictReader(file)
            curs = conn.cursor()
            for row in data:
                st.write(f"Inserting row: {row}")
                ins = 'INSERT INTO stock (id, company_name, now_price) VALUES (?, ?, ?)'
                curs.execute(ins, (
                    row['id'],
                    row['company_name'],
                    int(row['now_price'])
                ))
            conn.commit()
            st.success("Data imported successfully!")
            curs.close()
    except FileNotFoundError:
        st.error("data.csv not found in the app directory!")
    except Exception as e:
        st.error(f"Error importing CSV: {traceback.format_exc()}")

if __name__ == '__main__':
    conn = None
    db = st.file_uploader("Upload your stock.db file", type="db")
    
    if db:
        # Use binary write mode to handle the SQLite binary file correctly
        with tempfile.NamedTemporaryFile(mode='wb', delete=False) as fp:
            fp.write(db.getvalue())
            conn = create_connection(fp.name)
            st.success("Database connected successfully!")
    
    # Pass the uploaded database connection to main
    main(conn)

Critical Changes Made #

1. Binary Mode for Temporary File: We opened the temp file with mode='wb' to explicitly handle the binary SQLite file, which eliminates the FileUploaderEncodingWarning.
2. Global Helper Functions: Moved create_connection, create_project, and create_task to the global scope so they're accessible to all parts of the code.
3. Use Uploaded Database: Modified main() to accept the connection from the uploaded file instead of hardcoding a local database path.
4. Better Error Handling: Added Streamlit-specific error messages (using st.error and st.success) to make debugging easier for end users.
5. Fixed Typo: Corrected compamy_name to company_name in the create_project SQL query to avoid future database errors.

Additional Tips #

• If data.csv isn't a fixed local file, consider adding a second file_uploader for the CSV to make your app more flexible.
• Always use conn.commit() after making database changes (you had this for insertions, but added it for table creation too).
• Avoid using tempfile.NamedTemporaryFile with delete=False in production unless you handle cleanup, but for development it's fine.

内容的提问来源于stack exchange，提问作者hojin