SQLite: unable to use PRIMARY KEY (sqlite3.OperationalError)

Question

I'm trying to create a PRIMARY KEY with sqlite3 but I get an error.

Code:

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()

The error is:

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

If I remove "idkey INTEGER NOT NULL PRIMARY KEY, " it works.

Thanks!

Answer 1

You have to list the names of the columns name and uploaded that will receive the values of the inserted row.

In __init__ add one more line:

self.insertcols = "?,?"
self.insertcolnames = "name,uploaded"
self._create_database()

and in insert:

query = "INSERT INTO {} ({}) VALUES ({})".format(self.tablevideos, self.insertcolnames, self.insertcols)

This will return the statement:

INSERT INTO videos (name,uploaded) VALUES (?,?)