ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 14408 and this is thread id 2776

Please am creating a Signup database for my streamlit project. I keep getting this error below anytime i create an account.:

ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 14408 and this is thread id 2776.

Traceback:

File "c:\users\elijah nkuah\.conda\envs\elijah\lib\site-packages\streamlit\script_runner.py", line 354, in _run_script
    exec(code, module.__dict__)File "C:\Users\Elijah Nkuah\Documents\python\datascience\financial-inclusion-in-africa\diabetes.py", line 89, in <module>
    main()File "C:\Users\Elijah Nkuah\Documents\python\datascience\financial-inclusion-in-africa\diabetes.py", line 82, in main
    create_usertable()File "C:\Users\Elijah Nkuah\Documents\python\datascience\financial-inclusion-in-africa\manage_db_1.py", line 12, in create_usertable
    c.execute("CREATE TABLE IF NOT EXISTS userstable(username TEXT, password TEXT)")
import streamlit as st

# EDA PKG
import numpy as np
import pandas as pd

# UTILS
import os
import joblib
import hashlib # you can also use passlib and bcrypt

# VISUALISATION PKG
import matplotlib.pyplot as plt
import matplotlib
import seaborn as sns
matplotlib.use('Agg')

# DATABASE
from manage_db_1 import *

# Password
def generate_hashes(password):
    return hashlib.sha256(str.encode(password)).hexdigest()

# Verify password
def verify_hashes(password, hashed_text):
    if generate_hashes(password) == hashed_text:
        return hashed_text
    return False

# Interface

def main():
    """Mortality Prediction App"""
    st.title("Disease Mortality Prediction App")
    
    menu = ['Home', 'Login', 'Signup']
    submenu = ['Plot','Prediction', 'Metrics']
    
    choice = st.sidebar.selectbox("Menu", menu)
    if choice == "Home":
        st.subheader("Home")
        st.write("What is Hepatitis?")
    elif choice == "Login":
        username = st.text_input("Username")
        password = st.text_input("Password", type="password")
        if st.sidebar.checkbox("Login"):
            create_usertable(username1,password1)
            hashed_pswd = generate_hashes(password)
            result = login_user(username, verify_hashes(password, hashed_pswd))
            #if password =="12345":
            if result:
                st.success("Welcome {}".format(username))
                
                activity = st.selectbox("Activity", submenu)
                if activity == "Plot":
                    st.subheader("Data Visualisation Plot")
                    
                elif activity == "Prediction":
                    st.subheader("Predictive Analytics")               
                
            else:
                st.warning("Incorrect Username/Password")
            
    elif choice == "Signup":
        new_username = st.text_input("User Name")
        new_password = st.text_input("Password", type='password')
        confirmed_password = st.text_input("Confirm Password", type='password')
        if new_password == confirmed_password:
            st.success("Password Confirmed")
        else:
            st.warning("Passwords not the same")
        if st.button("Submit"):
            create_usertable()
            hashed_new_password = generate_hashes(new_password)
            add_userdata(new_username, hashed_new_password)
            st.success("You have successfully created a new account")
            st.info("Login To Get Started")             
    
if __name__ == '__main__':
    main()

Code for Database is

import sqlite3
conn = sqlite3.connect("usersdata.db")
c = conn.cursor()


# FXN
def create_usertable():
    #c.execute('CREATE TABLE IF NOT EXISTS userstable(username, password)')
    c.execute('CREATE TABLE IF NOT EXISTS userstable(username TEXT, password TEXT)')
    
def add_userdata(username, password):
    c.execute('INSERT INTO userstable(username,password) VALUES (?,?)', (username,password))
    conn.commit()


def login_user(username, password):
    #c.execute('SELECT.*.FROM.userstable.WHERE.username.=?.AND.password.=?',(username,password))
    #data.=c.fetchall()
    #return.data
    c.execute('SELECT * FROM userstable WHERE username =? AND password =?',(username,password))
    data = c.fetchall()
    return data
    
    
def view_all_users():
    c.execute('SELECT * FROM userstable')
    data = c.fetchall()
    return data

Hi @Elijah_Nkuah - A few issues I think:

  1. You’re reusing the cursor across separate database calls.
  2. I’d run execute() on conn, not c. conn.execute() returns a c to fetch results.
  3. Always commit after a logically complete set of update operations to make them atomic.
  4. Create the conn object as a singleton so it survives Streamlit reruns (can use session state or wrap it in st.cache with a custom hash function for sqlite3.Connection).

As a quick fix try:

conn = sqlite3.connect("usersdata.db", check_same_thread=False)

(Warning, your user signup implementation may trip up if two users try to create the same login credentials simultaneously, and your db scheme allows duplicate users. Hashing the password isn’t secure. It needs to be encrypted with a secret key.)

You could use my simple auth solution instead of dealing with all these issues yourself. :slight_smile:

HTH,
Arvindra

1 Like

Thank you very much, i tried using the @st.cache but the same error appeared. Secondary i tried conn = sqlite3.connect("usersdata.db", check_same_thread=False) but it wasn’t good because username cannot be unique as stated.

Anyone to help, please?