Блог

Не выводит необходимые элементы. Также как мне добавлять и удалять?

 import sqlite3
from sqlite3 import Error


def create_connection(path):
    connection = None
    try:
        connection = sqlite3.connect(path)
        print("Connection to SQLite DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

connection = create_connection("pets.sqlite")

def execute_query(conn, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

def execute_read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")

create_pets_table = """
CREATE TABLE IF NOT EXISTS pets (
    id INTEGER,
    name TEXT,
    age INTEGER,
    gender TEXT,
    id_eat NOT NULL INTEGER PRIMARY KEY AUTOINCREMENT,
    Type_of_animal TEXT
);
"""

execute_query(connection, create_pets_table)


create_corm_table = """
CREATE TABLE IF NOT EXISTS corm (
    type_eat TEXT,
    company TEXT,
    price INTEGER,
    name TEXT,
    eatid INTEGER,
    FOREIGN KEY (eatid) REFERENCES pets (id_eat)
);
"""

execute_query(connection, create_corm_table)

create_petss = """
INSERT INTO
  pets (id, name, age, gender, Type_of_animal)
VALUES
  (1, 'Боб', 3,'М', 'Кот'),
  (2, 'Полли', 4, 'Ж', 'Собака'),
  (3, 'Бакс', 8, 'М', 'Собака'),
  (4, 'Спайк', 10, 'М', 'Кот'),
  (5, 'Марс', 2, 'М', 'Крыса');
"""

execute_query(connection, create_petss)

create_corms = """
INSERT INTO
  corm (type_eat, company, price, name)
VALUES
    ('Сухой корм', 'Wiskas', 120,'Сухой корм Wiskas'),
    ('Сухой корм', 'Pedigri', 300, 'Сухой корм Pedigri'),
    ('Желе', 'Wiskas', 50, 'Желе Wiskas'),
    ('Набор овощей', 'LovePets', 30, 'Овощи для крыс');
"""

execute_query(connection, create_corms)

sql = """
    SELECT 
     pets.name,
     pets.gender,
     pets.age,
     corm.name,
     corm.price 
    FROM
     corm
     INNER JOIN pets ON pets.id_eat = corm.eatid
"""
sql_select = execute_read_query(connection, sql)

for sql_select in sql_select:
    print(sql_select)
 

Вывод:

 Connection to SQLite DB successful
The error 'near "INTEGER": syntax error' occurred
Query executed successfully
Query executed successfully
The error 'NOT NULL constraint failed: corm.eatid' occurred

Process finished with exit code 0
 

Я не могу решить проблему

При определении столбца таблицы тип данных должен быть первым. Итак

 id_eat NOT NULL INTEGER PRIMARY KEY AUTOINCREMENT,
 

должно быть

 id_eat INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,