Python SQLite使用变量值作为表名出现sqlite3.OperationalError的解决方法

Let's break down why you're hitting this error and how to fix it properly.

The Problem #

Your current code tries to create a table named 1241448952 (a numeric string), but SQLite throws a syntax error because SQL identifiers like table names can't start with a number unless they're wrapped in quotes. Additionally, your insert_db method has a second issue: directly formatting the name value into the SQL string will cause syntax errors for string values and exposes you to SQL injection risks.

Why This Happens #

SQLite's parser interprets unquoted numeric strings as literal numbers, not table names. So when you run CREATE TABLE 1241448952(name), it reads 1241448952 as a number and gets confused by the parentheses that follow.

The Fix #

You need to:

1. Wrap the numeric table name in double quotes (SQLite's standard for quoted identifiers) when creating the table and referencing it in queries.
2. Use parameterized queries for inserting data instead of string formatting to avoid syntax errors and SQL injection.
3. Fix the connection closing issue (your original code closes the connection after the first insert, making the class unusable for subsequent operations).

Modified Code #

import sqlite3

class IntDB:
    def __init__(self, _id: str):
        self.conn = sqlite3.connect("int_db.db", isolation_level=None)
        self.cur = self.conn.cursor()
        # Wrap the table name in double quotes to handle numeric identifiers
        sql_cmd = '''CREATE TABLE "{}"(name TEXT)'''.format(_id)
        try:
            self.cur.execute(sql_cmd)
        except sqlite3.OperationalError as e:
            # Handle case where table already exists (common edge case)
            if "already exists" not in str(e):
                raise e

    def insert_db(self, _id: str, name: str):
        # Use parameterized query with ? placeholder for safe value insertion
        # Wrap table name in double quotes here too
        sql_cmd = '''INSERT INTO "{}" VALUES (?)'''.format(_id)
        self.cur.execute(sql_cmd, (name,))
        # Since isolation_level is None, auto-commit is enabled — no need for manual commit

    def close(self):
        # Add explicit method to control connection shutdown
        self.conn.close()

# Usage example
if __name__ == "__main__":
    db = IntDB("1241448952")
    db.insert_db("1241448952", "John Doe")
    db.close()

Key Improvements #

• Quoted Table Name: Wrapping _id in "{}" tells SQLite to treat it as a table name, not a numeric literal.
• Parameterized Queries: Using ? as a placeholder for name ensures string values are properly escaped, preventing syntax errors and SQL injection.
• Error Handling: Added a try-except block to gracefully handle the case where the table already exists.
• Explicit Connection Closing: Moved conn.close() to a dedicated method so you can control when to shut down the connection, instead of breaking the class after the first insert.

内容的提问来源于stack exchange，提问作者심현규