Untitled

import sqlite3


def create_tables():
    conn = sqlite3.connect('practice6.db')
    cursor = conn.cursor()

    _SQL_1 = """CREATE TABLE IF NOT EXISTS teacher(
                    teacher_id INTEGER PRIMARY KEY AUTOINCREMENT, 
                    name VARCHAR(255), 
                    surname VARCHAR(255),
                    sex VARCHAR(255),
                    subject VARCHAR(255)
    )"""

    _SQL_2 = """CREATE TABLE IF NOT EXISTS pupil(
                    pupil_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(255),
                    surname VARCHAR(255),
                    sex VARCHAR(255),
                    class INTEGER
    )"""

    _SQL_3 = """CREATE TABLE IF NOT EXISTS teacher_pupil(
                    teacher_id INTEGER,
                    pupil_id INTEGER,
                    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
                    FOREIGN KEY (pupil_id) REFERENCES pupil(pupil_id)
    )"""

    for i in [_SQL_1, _SQL_2, _SQL_3]:
        cursor.execute(i)
    conn.commit()

    cursor.close()
    conn.close()


def get_giorgi_teachers():
    conn = sqlite3.connect('practice6.db')
    cursor = conn.cursor()

    _SQL = """
            SELECT t.name
            FROM pupil p 
                JOIN teacher_pupil tp ON (p.pupil_id = tp.pupil_id) 
                JOIN teacher t ON (tp.teacher_id = t.teacher_id)
            WHERE p.name = 'giorgi';
    """

    result = cursor.execute(_SQL)
    data = result.fetchall()

    cursor.close()
    conn.close()

    return data


# create_tables()
print(get_giorgi_teachers())