Python repeat statement + DB related questions sqlite3.IntegrityError: NOT NULL constrain failed: T2022418.name

import sqlite3
import datetime

DT_Now = datetime.datetime.now()

Today = "T{}{}{}".format(DT_Now.year, DT_Now.month, DT_Now.day)

information = sqlite3.connect("info.db")

curs = information.cursor()

def CreateTable():
    curs.execute("CREATE TABLE IF NOT EXISTS \'{}\'(number INTEGER NOT NULL, name TEXT NOT 
    NULL, howMuch INTEGER DEFAULT 0)".format(Today)) 


def Setting():
    for i in range(1, 23):
        curs.execute("INSERT INTO \'{}\' (number) VALUES ('{}')".format(Today, i))
    list_students = ['TTT', 'SSS', 'RRR', 'QQQ', 'PPP', 'OOO', 'NNN', 'MMM', 'LLL', 'KKK', 'JJJ', 'III', 'HHH', 'GGG', 'FFF', 'EEE', 'DDD', 'CCC', 'BBB', 'AAA', 'ABD', 'ABC']
    for i in range(len(list_students)):
        curs.execute("INSERT INTO \'{}\' (name) VALUES ('{}')".format(Today, list_students[i]))

CreateTable()

Setting()

The same error comes out when I try to remove the NOT NULL of the SQL syntax. sql DB started from number 1, so I turned the repeat door from number 1, but is there a problem??

Error: File "/Users/"User name"/Desktop/"Work folder name"/DataBase.py", line 13, in Setting
    curs.execute("INSERT INTO \'{}\' (number) VALUES ('{}')".format(Today, i))
sqlite3.IntegrityError: NOT NULL constraint failed: T2022418.name

sqlite3 python

2022-09-20 10:49

1 Answers

First of all, the cause of the problem is that the query itself is invalid. In short, you are running the following query.

CREATE TABLE IF NOT EXISTS 20220420 (
    number INTEGER NOT NULL,
    name TEXT NOT NULL,
    howMuch INTEGER DEFAULT 0
);
INSERT INTO 20220420 (number) VALUES (1);
INSERT INTO 20220420 (number) VALUES (2);
/* Intermediate omitted */
INSERT INTO 20220420 (number) VALUES (23);
INSERT INTO 20220420 (name) VALUES ('TTT');
INSERT INTO 20220420 (name) VALUES ('SSS');
/* Intermediate omitted */
INSERT INTO 20220420 (name) VALUES ('ABC');

When executed, this query only succeeds in the first create table, and all others fail.

Why is that? Let's compare it to the following.

CREATE TABLE IF NOT EXISTS 20220420 (
    number INTEGER NOT NULL,
    name TEXT NOT NULL,
    howMuch INTEGER DEFAULT 0
);
INSERT INTO 20220420 (number, name) VALUES (1, 'TTT');
INSERT INTO 20220420 (number, name) VALUES (2, 'SSS');
/* Intermediate omitted */
INSERT INTO 20220420 (number, name) VALUES (23, 'ABC');

The key point is that in INSERT, you have to give all the columns you have NOT NULL at once. You can't just give the number and then the name. SQL is not Excel.

Maybe you wanted to do something like this.

def Setting():
    list_students = ['TTT', 'SSS', 'RRR', 'QQQ', 'PPP', 'OOO', 'NNN', 'MMM', 'LLL', 'KKK', 'JJJ', 'III', 'HHH', 'GGG', 'FFF', 'EEE', 'DDD', 'CCC', 'BBB', 'AAA', 'ABD', 'ABC']
    for idx, student_name in enumerate(list_students):
        curs.execute("INSERT INTO \'{}\' (number, name) VALUES ('{}', '{}')".format(Today, idx, student_name))

And if you knew the existence of enumerate(), you would know that you don't need range(1, 23).

PS. In fact, you don't even need enumerate(). If there was INCREMENT PRIMARY KEY on the table. And it's a really bad idea to make a table name like "Today's Date." You can do it if you want, but the moment you learn JOIN later, you'll want to resent yourself.

CREATE TABLE IF NOT EXISTS students (
    Number INTEGER PRIMARY KEY AUTOINCREMENT, -- Let him do it on his own.
    name TEXT NOT NULL,
    howMuch INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Let him handle today's date.
);

-- The next query now works neatly, and then goes, "Huh? There's nothing wrong with finding out things like, "When did you enter this student?"
INSERT INTO STUDENTS (name, how Much) VALUES ("Mansur", 999999999);

2022-09-20 10:49

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656