SQLite3完整性错误排查：为何会出现该错误？

阿华AIGC实验室

2026-5-26

Troubleshooting SQLite3 Integrity Error in Your Recipe Insert Function

Hey there! Let's figure out why that SQLite3 Integrity Error is popping up. Since you've already ruled out data type issues, let's walk through the other most common triggers for this error:

1. Duplicate Primary Key Value

If recipe_id is set as the primary key for your recipes table (which makes sense for unique recipe identifiers), inserting a value that already exists in the table will throw this error immediately.

Fix Options:

Check for existing IDs first before inserting:

def insert_recipe(recipe_id, recipe_name, img_link, recipe_link):
    with sql.connect('app.db') as connection:
        cursor = connection.cursor()
        # Verify if the ID already exists
        cursor.execute("SELECT 1 FROM recipes WHERE recipe_id = ?", (recipe_id,))
        if not cursor.fetchone():
            cursor.execute("INSERT INTO recipes (recipe_id, recipe_name, img_link, recipe_link) VALUES (?,?,?,?)", 
                          (recipe_id, recipe_name, img_link, recipe_link))
            connection.commit()
        else:
            print(f"Oops! Recipe ID {recipe_id} already exists in the database.")

Use INSERT OR IGNORE to skip duplicates silently (if that's acceptable for your use case):

cursor.execute("INSERT OR IGNORE INTO recipes (recipe_id, recipe_name, img_link, recipe_link) VALUES (?,?,?,?)", 
              (recipe_id, recipe_name, img_link, recipe_link))

Use INSERT OR REPLACE to overwrite existing entries with the same ID:

cursor.execute("INSERT OR REPLACE INTO recipes (recipe_id, recipe_name, img_link, recipe_link) VALUES (?,?,?,?)", 
              (recipe_id, recipe_name, img_link, recipe_link))

2. Violation of NOT NULL Constraints

Take a look at your recipes table schema—if any of the fields (like recipe_name, img_link, or recipe_link) are defined with a NOT NULL constraint, passing a None value or an empty string (depending on how SQLite interprets it) will trigger the error.

Quick Check:

Add a print statement to verify the values you're passing are valid:

def insert_recipe(recipe_id, recipe_name, img_link, recipe_link):
    print(f"Inserting values: ID={recipe_id}, Name={recipe_name}, Img={img_link}, Link={recipe_link}")
    with sql.connect('app.db') as connection:
        # Rest of your code...

3. Violation of UNIQUE Constraints

Even if a field isn't the primary key, it might have a UNIQUE constraint (for example, if you don't want duplicate recipe_link entries). Inserting a duplicate value into such a field will also cause an Integrity Error.

Verify Table Constraints:

Run this query to check the full schema of your recipes table:

def insert_recipe(recipe_id, recipe_name, img_link, recipe_link):
    with sql.connect('app.db') as connection:
        cursor = connection.cursor()
        cursor.execute("PRAGMA table_info(recipes)")
        print("Table schema details:")
        for row in cursor.fetchall():
            print(row)  # Will show constraints like PRIMARY KEY, NOT NULL, UNIQUE
        # Rest of your code...

Critical Step: Capture the Exact Error Message

Right now, you're only seeing a generic error page. Modify your function to catch the exception and print the full error details—this will tell you exactly which constraint is being violated:

import sqlite3  # Make sure you have this imported

def insert_recipe(recipe_id, recipe_name, img_link, recipe_link):
    try:
        with sql.connect('app.db') as connection:
            cursor = connection.cursor()
            cursor.execute("INSERT INTO recipes (recipe_id, recipe_name, img_link, recipe_link) VALUES (?,?,?,?)", 
                          (recipe_id, recipe_name, img_link, recipe_link))
            connection.commit()
    except sqlite3.IntegrityError as e:
        print(f"Detailed Integrity Error: {e}")

For example, you might see messages like: