SQLite中如何用变量指定表名及插入值？Python新手求助

Hey there! I see where you went wrong with your INSERT statement—let's get this sorted out step by step.

First, let's break down the issue: SQLite's parameterized queries (using ? placeholders) work great for values like your order and theme parameters, but table names can't be parameterized with ?. That means you do need to use .format() to insert the table name, but you shouldn't mix your value parameters into that .format() call.

Here's the corrected version of your add function:

def add(order, theme):
    table = input("With what table do you want to work with? ")
    # Build the INSERT query with dynamic table name (only replace the table here)
    insert_query = 'INSERT INTO {} VALUES(NULL, ?, ?)'.format(table)
    parameters = [order, theme]
    # Pass the query and parameters separately to execute()
    c.execute(insert_query, parameters)

Why your original code didn't work #

In your initial attempt, you tried to pass parameters into .format()—this converts the list to a string (like [1, "test"]) and shoves it directly into the SQL query, which causes invalid SQL syntax. The correct approach is to keep the ? placeholders for your values, then pass the parameters as the second argument to c.execute()—this lets SQLite handle safely inserting the values without syntax errors or injection risks (for the values, at least).

Important Security Note #

Since we're using .format() for the table name, never trust unvalidated user input here. If a malicious user enters a sneaky string as the table name, they could perform a SQL injection attack. To fix this, always validate the table name against a list of allowed tables:

def add(order, theme):
    allowed_tables = ['my_table', 'another_table', 'valid_table']
    table = input("With what table do you want to work with? ")
    
    # Validate the table name first
    if table not in allowed_tables:
        print("Error: Invalid table name!")
        return
    
    insert_query = 'INSERT INTO {} VALUES(NULL, ?, ?)'.format(table)
    parameters = [order, theme]
    c.execute(insert_query, parameters)

This way, you ensure only pre-approved tables are used, keeping your database safe.

内容的提问来源于stack exchange，提问作者Jose Martinez Sobrado