Заключение

import pandas as pd

import psycopg2

# Подключение к базе данных:

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor() # курсор для выполнения операций с БД

print(connection.get_dsn_parameters(), "\n") # вывод свойства соединения

cursor.execute("SELECT version();") # выполнение запроса к БД

version_ps = cursor.fetchone() # получение результата запроса

print("Вы подключены к - ", version_ps, "\n")

cursor.close()

connection.close()

import pandas as pd

import psycopg2

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

cursor.execute('''CREATE TABLE if not exists public.locations

(location_id int PRIMARY KEY,

city varchar(30),

postal_code varchar(12)

); ''')

connection.commit()

cursor.close()

connection.close()

import pandas as pd

import psycopg2

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

cursor.execute('''

INSERT INTO public.locations VALUES ( 1,'Roma', '00989');

INSERT INTO public.locations VALUES ( 2,'Venice','10934');

INSERT INTO public.locations VALUES ( 3,'Tokyo', '1689');

INSERT INTO public.locations VALUES ( 4,'Hiroshima','6823');

INSERT INTO public.locations VALUES ( 5,'Southlake', '26192');

INSERT INTO public.locations VALUES ( 6,'South San Francisco', '99236');

INSERT INTO public.locations VALUES ( 7,'South Brunswick','50090');

INSERT INTO public.locations VALUES ( 8,'Seattle','98199');

INSERT INTO public.locations VALUES ( 9,'Toronto','M5V 2L7');

INSERT INTO public.locations VALUES ( 10,'Whitehorse','YSW 9T2');

''')

connection.commit()

cursor.close()

connection.close()

import psycopg2

import random

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

# Add location_id column to employees table

cursor.execute("ALTER TABLE public.employees ADD COLUMN location_id int REFERENCES public.locations (location_id)")

# Fetch all location_id from locations table

cursor.execute("SELECT location_id FROM public.locations")

location_ids = cursor.fetchall()

location_ids = [location_id[0] for location_id in location_ids]

# Update location_id for each employee randomly

for i in range(1, 54):

cursor.execute(f"UPDATE public.employees SET location_id={random.choice(location_ids)} WHERE employee_id={i}")

connection.commit()

cursor.close()

connection.close()

import psycopg2

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

query = "SELECT job_title FROM public.jobs WHERE min_salary < 2500;"

cursor.execute(query)

result = cursor.fetchall()

for record in result:

print(record[0])

connection.commit()

cursor.close()

connection.close()

import psycopg2

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

query = """

SELECT e.first_name AS employee, m.first_name AS boss

FROM public.employees e

JOIN public.employees m ON e.manager_id = m.employee_id

"""

cursor.execute(query)

result = cursor.fetchall()

for row in result:

print(row[0], "is managed by", row[1])

cursor.close()

connection.close()

import psycopg2

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

query = """

SELECT e.first_name, e.last_name, m.first_name, m.last_name

FROM public.employees e

JOIN public.employees m ON e.manager_id = m.employee_id

"""

cursor.execute(query)

result = cursor.fetchall()

for r in result:

print(f"Employee: {r[0]} {r[1]}, Manager: {r[2]} {r[3]}")

cursor.close()

connection.close()

import psycopg2

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

query = """

SELECT e.employee_id, e.first_name, e.last_name, l.city, l.postal_code

FROM public.employees e

INNER JOIN public.locations l

ON e.location_id = l.location_id;

"""

cursor.execute(query)

result = cursor.fetchall()

for row in result:

print(row)

connection.commit()

connection.close()

import psycopg2

import pandas as pd

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

cursor.callproc('select_data',[20,]) # вызов функции (название из PostgreSQL)

result = cursor.fetchall() # получение результатов

result_proc = pd.DataFrame(result) # создание датафрейма с результатом

print(result_proc)

connection.commit()

connection.close()

import psycopg2

import pandas as pd

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

#код функции

postgresql_func = """

CREATE OR REPLACE FUNCTION select_data1(id_dept int) RETURNS

SETOF departments AS $$

SELECT * FROM departments WHERE departments.department_id >

id_dept;

$$ LANGUAGE SQL;

"""

cursor.execute(postgresql_func) # выполнение запроса

connection.commit() #внесение изменений в БД

connection.close() # закрытие соединения

cursor.close() # закрытие cursor

import psycopg2

import pandas as pd

connection = psycopg2.connect(database="students",

user="postgres",

password="123",

host="127.0.0.1",

port="5432")

cursor = connection.cursor()

#код функции

postgresql_func = """

CREATE OR REPLACE FUNCTION average_salary_by_location1(location varchar)

RETURNS float AS $$

BEGIN

RETURN (

SELECT AVG(salary)

FROM employees e

JOIN locations l ON e.location_id = l.location_id

WHERE l.city = location

);

END;

$$ LANGUAGE plpgsql;

"""

cursor.execute(postgresql_func) # выполнение запроса

connection.commit() #внесение изменений в БД

connection.close() # закрытие соединения

cursor.close() # закрытие cursor