Hey there, let's break down what's going wrong with your SQLite insert code and fix it step by step.

1. The Root Cause of the "no such column" Error #

When you use str.format() to build your INSERT query, you aren’t wrapping text values in quotes. So if you input a string like name, SQLite interprets it as a column name instead of a string value—that’s exactly why you’re seeing the "no such column: name" error. On top of that, this approach is a massive security risk (it leaves you open to SQL injection attacks), so we should never use string formatting to plug user input into SQL queries.

2. Other Hidden Issues in Your Code #

Let’s cover the other problems that are causing headaches:

• Parameter Order Mismatch: Your data_entry function expects arguments in the order (x,y,z,w) (mapping to unix, datestamp, keyword, value), but you’re calling it with data_entry(x,y,w,z)—swapping the keyword and value arguments. Also, your input types don’t match the table schema: you’re passing an int for keyword (which should be TEXT) and a string for value (which should be REAL).
• Early Connection/Cursor Closure: You’re closing the cursor and connection inside data_entry, which means you can’t run any other database operations later if you need to.
• No Input Validation: You’re converting inputs to int/str without checking if they match the table’s expected types, which can lead to more errors down the line.

3. Fixed & Improved Code #

Here’s the revised code that fixes all these issues:

import sqlite3

def create_table(conn):
    c = conn.cursor()
    # Explicitly structure table columns for clarity
    c.execute("""CREATE TABLE IF NOT EXISTS stuffPlot (
                unix REAL,
                datestamp TEXT,
                keyword TEXT,
                value REAL
                )""")
    c.close()

def data_entry(conn, unix, datestamp, keyword, value):
    c = conn.cursor()
    # Use parameterized queries with ? placeholders
    c.execute("INSERT INTO stuffPlot VALUES(?, ?, ?, ?)", (unix, datestamp, keyword, value))
    conn.commit()
    c.close()

if __name__ == "__main__":
    # Open connection once and reuse it for all operations
    conn = sqlite3.connect("tutorial.db")
    
    # Get and validate user inputs with basic error handling
    try:
        unix_val = float(input("Enter a number for unix timestamp (REAL): "))
        datestamp_val = str(input("Enter a datestamp string (TEXT): "))
        keyword_val = str(input("Enter a keyword string (TEXT): "))
        value_val = float(input("Enter a numeric value (REAL): "))
    except ValueError:
        print("Oops! Please enter the correct data type (numbers for unix/value, strings for the rest).")
        conn.close()
        exit()
    
    create_table(conn)
    data_entry(conn, unix_val, datestamp_val, keyword_val, value_val)
    
    # Close connection only when all tasks are done
    conn.close()
    print("Data inserted successfully! 🎉")

4. Key Fixes Explained #

Let’s walk through the most important changes:

• Parameterized Queries: Using ? placeholders lets SQLite handle all quoting and escaping automatically. This eliminates the "no such column" error completely and protects you from SQL injection.
• Correct Parameter Alignment: We’re now passing arguments in the exact order the table expects, and using descriptive variable names (like unix_val instead of x) to avoid confusion.
• Proper Connection Management: We open the database connection once, use it for both table creation and data insertion, and close it only when we’re finished with all database tasks.
• Basic Input Validation: The try/except block catches cases where users enter the wrong type (e.g., a string instead of a number) and gives a helpful error message.

5. A Critical Reminder #

Always use parameterized queries when inserting variable data into SQL statements. Building queries with string formatting isn’t just error-prone—it’s a serious security flaw that can let attackers manipulate or destroy your database.

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