Телефонная книжка

Denjamin

May 5th, 2024

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

Python 6.35 KB | None | 0 0

raw download clone embed print report

import psycopg2
# Комплексное решение для создания базы данных эдакой телефонной книги.
# Настройки бд чтобы не писать всё одно и тоже в каждой функции.
# DATABASE = "phonebook"
# USER = "postgres"
# PASSWORD = "619620qazwsx"
# HOST = "localhost" # локалхост пусть будет
# PORT = "5432" # Порт стандартный
#
#
# def connect_to_db():
# conn = None
# try:
# conn = psycopg2.connect(
# dbname=DATABASE,
# user=USER,
# password=PASSWORD,
# host=HOST,
# port=PORT
# )
# except (Exception, psycopg2.DatabaseError) as error:
# print(error)
# return conn
# Функция создания табличек
def create_db(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 phone_numbers (
phone_id SERIAL PRIMARY KEY,
client_id INTEGER NOT NULL,
phone_number VARCHAR(20),
FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE CASCADE
);
""")
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 phone_numbers (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 phone_numbers (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):
items = []
parameters = []
if first_name:
items.append(f"first_name = %s")
parameters.append(first_name)
if last_name:
items.append(f"last_name = %s")
parameters.append(last_name)
if email:
items.append(f"email = %s")
parameters.append(email)
with conn.cursor() as cur:
if items:
cur.execute(f"""
UPDATE clients SET {', '.join(items)} WHERE client_id = %s;
""", parameters + [client_id])
if phones is not None:
cur.execute("""
DELETE FROM phone_numbers WHERE client_id = %s;
""", (client_id,))
for phone in phones:
cur.execute("""
INSERT INTO phone_numbers (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 phone_numbers 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()
# Крайне простой поиск клиента по любому из полей, но не вычисление по IP
def find_client(conn, first_name=None, last_name=None, email=None, phone=None):
with conn.cursor() as cur:
query = """
SELECT c.* FROM clients c
LEFT JOIN phone_numbers p ON c.client_id = p.client_id
WHERE
"""
params = []
conditions = []
if first_name:
conditions.append(f"c.first_name = %s")
params.append(first_name)
if last_name:
conditions.append(f"c.last_name = %s")
params.append(last_name)
if email:
conditions.append(f"c.email = %s")
params.append(email)
if phone:
conditions.append(f"p.phone_number = %s")
params.append(phone)
query += " OR ".join(conditions) + ";"
cur.execute(query, tuple(params))
return cur.fetchall()
# А вот тут самое веселье, здесь инициируем подключение к базе данных.
with psycopg2.connect(database="phonebook", user="postgres", password="619620qazwsx",
host="localhost, port=5432") as conn:
# Создадим базу данных
create_db(conn)
# Добавляем данные клиента
client_id1 = add_client(conn, "Иван", "Иванов", "[email protected]", ["+7-900-123-45-67"])
client_id2 = add_client(conn, "Петр", "Петров", "[email protected]")
# Добавим телефоны
add_phone(conn, client_id1, "+7-920-123-45-89")
add_phone(conn, client_id2, "+7-930-123-45-99")
# редактируем данные
change_client(conn, client_id1, email="[email protected]", phones=["+7-950-123-45-77"])
# Поищем клиента и напечатаем на экране.
print(find_client(conn, first_name="Иван"))
print(find_client(conn, phone="+7-950-123-45-77"))
# Удалим телефон
delete_phone(conn, client_id1, "+7-950-123-45-77")
# Удалим клиента из базы данных
delete_client(conn, 1) # Удаляем по ID клиента
'''
THE END - Всех с праздниками!
'''

Add Comment

Please, Sign In to add comment