如何用Python的SQLite3实现登录功能？代码报错求解决及替代库推荐

Hey there! Let's work through your issues one by one—first fixing that error, then talking about database options, and adding a few quick security tips.

1. Fixing the ProgrammingError in Your Code #

That error pops up because your read() function has two critical issues:

• You’re passing a second SQL statement to c.execute() instead of parameter values (the second argument should be a tuple of values to bind to ? placeholders in your query).
• Your SQL queries don’t reference user input correctly, plus you forgot parentheses on conn.commit() (without (), the function never runs!).

Let’s rewrite your code to fix these problems and make the login logic work properly:

import sqlite3

# Connect to the database (creates the file if it doesn't exist)
conn = sqlite3.connect("pooptest123.db")
c = conn.cursor()

def create_table():
    # Use IF NOT EXISTS to avoid errors if the table already exists
    c.execute("CREATE TABLE IF NOT EXISTS login(Username VARCHAR, Passwords VARCHAR)")
    conn.commit()  # Don't forget the parentheses here!

def create_account():
    username = input("Create username: ")
    password = input("Create password: ")
    # Safely insert data with parameter binding (prevents SQL injection)
    c.execute("INSERT INTO login (Username, Passwords) VALUES (?, ?)", (username, password))
    conn.commit()
    print("Account created successfully!")

# Create the table (if needed) and make a new account
create_table()
create_account()

def login():
    sure = input("Login?(Y OR N): ")
    if sure.lower() == "y":
        input_username = input("Username: ")
        input_password = input("Password: ")
        
        # Query the database for a matching username + password
        c.execute("SELECT * FROM login WHERE Username = ? AND Passwords = ?", 
                  (input_username, input_password))
        # Fetch the first matching result (if any)
        matching_account = c.fetchone()
        
        if matching_account:
            print("Username and password correct!")
            print("Logging in...")
        else:
            print("Incorrect username or password!")

# Run the login prompt
login()

# Always close the connection when done
conn.close()

Key Fixes Explained: #

• Removed the broken read() function: We moved the database query directly into the login logic where it’s needed.
• Parameter binding: Using ? placeholders ensures your code is safe from SQL injection and fixes the binding error.
• Proper result checking: Instead of comparing input to SQL strings (which doesn’t make sense), we fetch the actual database record and check if it exists.
• IF NOT EXISTS in table creation: Prevents errors if you run the script multiple times.

2. Database Alternatives for Small-Scale Data #

You mentioned you don’t need to store massive amounts of data—SQLite is actually perfect for your use case. It’s a lightweight, file-based database that doesn’t require a separate server, making it ideal for small personal projects or apps with low user counts.

If you want to explore other simple options:

• DuckDB: A file-based analytical database, great if you ever need to run more complex queries later, but overkill for basic login functionality.
• JSON/CSV files: For tiny datasets (like <100 users), you could use a JSON file to store accounts, but SQLite is more robust for querying and data integrity.

MySQL is definitely unnecessary here—it requires server setup, configuration, and maintenance, which is way more than you need for a small login system.

3. Quick Security Tip (Important!) #

Right now, you’re storing passwords in plain text, which is a big security risk if your database ever gets exposed. You should always hash passwords before storing them.

For example, using the bcrypt library (install it with pip install bcrypt):

import bcrypt

# When creating an account:
password = input("Create password: ").encode('utf-8')
hashed_password = bcrypt.hashpw(password, bcrypt.gensalt())
c.execute("INSERT INTO login (Username, Passwords) VALUES (?, ?)", (username, hashed_password))

# When logging in:
input_password = input("Password: ").encode('utf-8')
c.execute("SELECT Passwords FROM login WHERE Username = ?", (input_username,))
stored_hash = c.fetchone()
if stored_hash and bcrypt.checkpw(input_password, stored_hash[0]):
    print("Login successful!")

This way, even if someone accesses your database, they won’t be able to read the actual passwords.

内容的提问来源于stack exchange，提问作者Not Good Enough