Flask中SQLite的schema.sql自增字段语法错误排查求助

Let's break down what's going wrong here and fix it step by step:

The Root Cause #

Your error message points to the comma near autoincrement, but the actual issue is how you're defining the text field. In SQLite, single quotes (' ') are used for string values, not identifiers like column names. Since text is a reserved keyword in SQL, wrapping it in single quotes confuses the parser—this is what's causing the misleading error about the comma.

Corrected Schema SQL #

Here's the fixed version of your schema:

DROP TABLE IF EXISTS entries;
CREATE TABLE entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    "text" TEXT NOT NULL
);

Alternatively, you can use backticks (compatible with SQLite and other databases like MySQL):

DROP TABLE IF EXISTS entries;
CREATE TABLE entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    `text` TEXT NOT NULL
);

Bonus: Simplify the Auto-Increment ID #

A quick tip about SQLite's behavior: If you don't need strictly sequential IDs (i.e., you don't mind reusing IDs from deleted rows), you can omit AUTOINCREMENT entirely. SQLite automatically generates unique, incrementing IDs for columns defined as INTEGER PRIMARY KEY:

id INTEGER PRIMARY KEY,

This is more efficient than using AUTOINCREMENT, which adds extra overhead to ensure IDs are never reused.

How to Troubleshoot SQLite Syntax Issues in the Future #

• Test SQL directly in SQLite: Run your schema in the SQLite command-line tool first (open it with sqlite3 in your terminal). It will give you more precise error locations than Flask's wrapper.
• Watch for reserved keywords: Always wrap SQL keywords used as identifiers (like text, user, date) in double quotes or backticks.
• Stick to SQLite's rules: SQLite has specific syntax quirks (like AUTOINCREMENT only working with INTEGER PRIMARY KEY) that differ slightly from other SQL databases.

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