使用sqlite3创建PRIMARY KEY时遭遇OperationalError错误求助

问题描述 #

尝试用sqlite3创建带PRIMARY KEY的表，执行插入操作时触发错误，移除主键定义后代码可正常运行。

原代码 #

import sqlite3


class DataBaseManager:
    
    
    def __init__(self, database):
        
        self.database = database
        self.tablevideos = "videos"
        self.cols = (
                "idkey INTEGER NOT NULL PRIMARY KEY, "
                "name INTEGER NOT NULL, "
                "uploaded INTEGER DEFAULT NULL"
            )
        self.defaults = {
                "uploaded": None
            }
        self.insertcols = "?,?"
        self._create_database()
   
   
    def _create_database(self):
        """
        Creates the database if it does not exist
        """
        
        connection = sqlite3.connect(self.database)
        cursor = connection.cursor()
        query = (
            "CREATE TABLE IF NOT EXISTS "
            "{}({})".format(self.tablevideos, self.cols)
            )
        cursor.execute(query)
        connection.commit()
        cursor.close()
        connection.close()
    
    
    def insert(self):
        
        connection = sqlite3.connect(self.database)
        cursor = connection.cursor()
        query = (
            "INSERT INTO {} "
            "VALUES ({})".format(self.tablevideos, self.insertcols)
            )
        for i in [1,2,3]:
            data = [
                i,
                *self.defaults.values()
                ]
            cursor.execute(query, data)
        connection.commit()
        cursor.close()
        connection.close()

databasemanager = DataBaseManager("data.db")
databasemanager.insert()

错误信息 #

Traceback (most recent call last):
  File "D:\Sync1\Code\Python3\FileTagerPy\example.py", line 59, in <module>
    databasemanager.insert()
  File "D:\Sync1\Code\Python3\FileTagerPy\example.py", line 53, in insert
    cursor.execute(query, data)
sqlite3.OperationalError: table videos has 3 columns but 2 values were supplied

问题原因 #

表定义了3列（idkey、name、uploaded），但插入语句未指定目标列名，SQLite会默认按表的列顺序匹配所有列，而你只提供了2个值，导致列数不匹配。

修复方案 #

提供两种修复方式，可根据需求选择：

方式一：手动指定主键值，匹配列数 #

修改插入占位符数量，并在数据中包含主键值：

def __init__(self, database):
    # 其他代码不变
    self.insertcols = "?,?,?"  # 改为3个占位符，对应3列
    self._create_database()

def insert(self):
    connection = sqlite3.connect(self.database)
    cursor = connection.cursor()
    query = (
        "INSERT INTO {} "
        "VALUES ({})".format(self.tablevideos, self.insertcols)
        )
    for i in [1,2,3]:
        data = [
            i,  # idkey的值
            i,  # name的值，可根据实际需求修改
            *self.defaults.values()
            ]
        cursor.execute(query, data)
    connection.commit()
    cursor.close()
    connection.close()

方式二：主键自动递增，指定插入列（推荐） #

让主键由数据库自动生成，插入时仅提供非主键列的值，并明确指定目标列：

class DataBaseManager:
    def __init__(self, database):
        self.database = database
        self.tablevideos = "videos"
        # 主键添加AUTOINCREMENT，明确自动递增规则
        self.cols = (
                "idkey INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
                "name INTEGER NOT NULL, "
                "uploaded INTEGER DEFAULT NULL"
            )
        self.defaults = {
                "uploaded": None
            }
        # 定义插入列和对应占位符
        self.insertcols = "(name, uploaded) VALUES (?,?)"
        self._create_database()
   
    # _create_database方法保持不变
    
    def insert(self):
        connection = sqlite3.connect(self.database)
        cursor = connection.cursor()
        query = (
            "INSERT INTO {} "
            "{}".format(self.tablevideos, self.insertcols)
            )
        for i in [1,2,3]:
            data = [
                i,  # name的值
                *self.defaults.values()
                ]
            cursor.execute(query, data)
        connection.commit()
        cursor.close()
        connection.close()

补充说明 #

• 方式二更符合主键的常规使用逻辑，避免手动指定主键可能引发的重复值问题。
• SQLite中，INTEGER PRIMARY KEY类型列默认会自动递增，AUTOINCREMENT关键字会进一步确保主键值严格递增，不会复用已删除的ID。

内容的提问来源于stack exchange，提问作者Abel Gutiérrez