Flask API开发中sqlite3.OperationalError语法错误的解决方法求助

Hey there, let's get that login API back on track! The error you're hitting is a common pitfall when working across different database systems—here's a breakdown of what's wrong and how to fix it:

The Root Cause #

SQLite uses question marks (?) as parameter placeholders in queries, but your code is using %s—that's the syntax for MySQL and some other database systems. SQLite doesn't recognize %s as valid syntax, which is why you're seeing the near "%": syntax error.

Step 1: Fix the Query Syntax #

First, replace the %s in your cur.execute() call with SQLite's standard ? placeholders:

cur.execute('SELECT * FROM users WHERE email = ? AND password = ?', (username, password))

Step 2: Fix Hidden Session & Data Access Bugs #

You’ve got two more potential issues waiting to break your code:

1. Flask sessions need a secret key: Flask encrypts session data, so you have to set a SECRET_KEY to make this work. Add this line right after initializing your app:

   app.secret_key = 'your_secure_random_key_here' # Use a long, random string in production
   

2. fetchone() returns tuples, not dictionaries: By default, SQLite cursor results are tuples, so account['id'] will throw a TypeError. Fix this either by:
   • Accessing values via index (e.g., account[0] for the id column, if that's the first column in your users table)
   • Or enabling dictionary-style row access by setting the connection's row factory:

     con = sqlite3.connect("users.db")
     con.row_factory = sqlite3.Row # Now rows act like dictionaries
     cur = con.cursor()
     

Step 3: Properly Manage Database Connections #

To avoid resource leaks, use a with statement to automatically close your database connection when you're done with it:

with sqlite3.connect("users.db") as con:
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    cur.execute('SELECT * FROM users WHERE email = ? AND password = ?',(username,password))
    account = cur.fetchone()
    # Rest of your login logic here

Full Corrected Code #

Here’s the updated version of your code with all fixes applied:

from flask import Flask, render_template, request, session, url_for
import sqlite3

app = Flask(__name__)
app.secret_key = 'your_secure_random_key_here' # Required for session functionality

@app.route("/", methods=['GET', 'POST'])
def login():
    msg = ''
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form:
        username = request.form['username']
        password = request.form['password']
        # Auto-manage connection with `with` statement
        with sqlite3.connect("users.db") as con:
            con.row_factory = sqlite3.Row # Enable dictionary-style row access
            cur = con.cursor()
            # Use SQLite's ? placeholders instead of %s
            cur.execute('SELECT * FROM users WHERE email = ? AND password = ?',(username,password))
            account = cur.fetchone()
            if account:
                session['logged'] = True
                session['id'] = account['id']
                session['username'] = account['username']
                return "Login Successful"
            else:
                return "Incorrect username/Password!"
    return render_template("login.html", msg=msg)

if __name__ == '__main__':
    app.debug = True
    app.run()

Quick Security Note #

Storing plain-text passwords in your database is a major security risk! For production, use a library like bcrypt or passlib to hash passwords before storing them, and verify the hash instead of comparing plain text.

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