homework (python_PY_SQL).py - master - VladimirMakarov88/Homework_SQL_Python

homework (python_PY_SQL).py
148 строк6 KB
VladimirMakarov88
update homework (python_PY_SQL).py
22 янв 2026, 19:30
22 янв 2026, 19:300d007e0
import psycopg2

def createdb(conn):
    with conn.cursor() as cur:
        cur.execute("""
                    CREATE TABLE IF NOT EXISTS clients (
                        client_id SERIAL PRIMARY KEY, 
                        first_name VARCHAR(255) NOT NULL,
                        last_name VARCHAR(255) NOT NULL,
                        email VARCHAR(255) UNIQUE NOT NULL)            
        """)
        
        cur.execute("""
                    CREATE TABLE IF NOT EXISTS client_phones (
                        phone_id SERIAL PRIMARY KEY,
                        client_id INTEGER NOT NULL REFERENCES clients(client_id) ON DELETE CASCADE,
                        phone_number VARCHAR(255) NOT NULL)
        """)
        conn.commit()

        
def add_client(conn, first_name, last_name, email, phones=None):
    with conn.cursor() as cur:
        cur.execute("""
                    INSERT INTO clients (first_name, last_name, email) values (%s, %s, %s)
                    RETURNING client_id
        """, (first_name, last_name, email))
        client_id = cur.fetchone()[0]
        if phones:
            for phone in phones:
                cur.execute("""
                    INSERT INTO client_phones (client_id, phone_number) values (%s, %s)
                """, (client_id, phone))
        conn.commit()
        return client_id
    
def add_phone(conn, client_id, phone):
    with conn.cursor() as cur:
        cur.execute("""
                    INSERT INTO client_phones (client_id, phone_number) values (%s, %s)
        """, (client_id, phone))
        conn.commit()

def change_client(conn, client_id, first_name=None, last_name=None, email=None, phones=None):
    with conn.cursor() as cur:
        update_client = []
        params = []
        if first_name is not None:
            update_client.append("first_name = %s")
            params.append(first_name)
        if last_name is not None:
            update_client.append("last_name = %s")
            params.append(last_name)
        if email is not None:
            update_client.append("email = %s")
            params.append(email)
        if update_client:
            new_info = ', '.join(update_client)
            query = f'UPDATE clients SET {new_info} WHERE client_id = %s'
            cur.execute(query, params + [client_id])
        if phones is not None:
            cur.execute("DELETE FROM client_phones WHERE client_id = %s", (client_id,))
        
        for phone in phones:
            if phone:
                cur.execute("""INSERT INTO client_phones (client_id, phone_number) values (%s, %s)
        """, (client_id, phone)) 
        conn.commit()

def delete_phone(conn, client_id, phone):
    with conn.cursor() as cur:
        cur.execute("""DELETE FROM client_phones WHERE client_id = %s AND phone_number = %s""", (client_id, phone))
        conn.commit()

def delete_client(conn, client_id):
    with conn.cursor() as cur:
        cur.execute("""DELETE FROM clients WHERE client_id = %s""", (client_id,))
        conn.commit()

def find_client(conn, first_name=None, last_name=None, email=None, phone=None):
    with conn.cursor() as cur:
        query = """SELECT DISTINCT c.client_id, c.first_name, c.last_name, c.email
        FROM clients c
        LEFT JOIN client_phones cp ON c.client_id = cp.client_id
        WHERE 1=1"""
        params = []

        if first_name:
            query += " AND c.first_name = %s"
            params.append(first_name)

        if last_name:
            query += " AND c.last_name = %s"
            params.append(last_name)

        if email:
            query += " AND c.email = %s"
            params.append(email)

        if phone:
            query += " AND cp.phone_number = %s"
            params.append(phone)

        cur.execute(query, params)

        return cur.fetchall()

with psycopg2.connect(database='postgres', user='postgres', password='VMakarov-26011988') as conn:
    print(f'Создаем базу данных...')
    createdb(conn)

    print(f'Добавляем клиентов...')

    client1 = add_client(conn, 'Vladimir', 'Makarov', 'vmacar@yandex.ru')
    client2 = add_client(conn, 'Kirill', 'Makarov', 'kirill_makarov@gmail.com', ['+79217664676'])
    client3 = add_client(conn, 'Sofia', 'Vasilieva', 'sofia_zos@yandex.ru', ['+79697007159', '+79013788190'])
   
    # Добавлен телефон клиенту 1
    add_phone(conn, client1, '+79218850006')
    print(f'Добавлен телефон клиенту {client1}')

    # Изменил имя клиенту 1
    change_client(conn, client1, first_name='Vova')
    print(f'Изменено имя клиента {client1}')
    
    # Удалил один телефон у клиента 3 
    delete_phone(conn, client3, '+79013788190')
    print(f'Удален телефон клиента {client3}')
    
    # ищем по имени клиента
    clients_name1 = find_client(conn, first_name = 'Kirill')
    print(f'Найден клиент {clients_name1}')

    # ищем по имени клиента, которому меняли имя ранее
    clients_name2 = find_client(conn, last_name = 'Vasilieva')
    print(f'Найден клиент {clients_name2}')

    # ищем по email клиента
    clients_name3 = find_client(conn, email = 'vmacar@yandex.ru')
    print(f'Найден клиент c почтой: {clients_name3}')

    # ищем по телефону клиента
    clients_name4 = find_client(conn, phone = '+79697007159')
    print(f'Найден клиент с телефоном: {clients_name4}')

    # удаление клиента
    delete_client(conn, client2)
    print(f'Удален клиент: {client2}')