SfyMTscb

1import psycopg2
2from psycopg2 import sql
3from datetime import datetime
4conn = psycopg2.connect(DB_URL)
5cursor = conn.cursor()
6
7table_orders = """
8CREATE TABLE IF NOT EXISTS orders(
9order_id SERIAL PRIMARY KEY,
10create_dt DATE NOT NULL,
11upd_dt DATE,
12order_type TEXT NOT NULL,
13description TEXT NOT NULL,
14ord_status TEXT NOT NULL,
15serial_num INTEGER NOT NULL,
16creator_id INTEGER NOT NULL,
17FOREIGN KEY (creator_id) REFERENCES employees (employee_id)
18);"""
19
20table_employees = """
21CREATE TABLE IF NOT EXISTS employees(
22employee_id SERIAL PRIMARY KEY,
23fio TEXT NOT NULL,
24employee_position TEXT NOT NULL,
25department_id integer NOT NULL,
26FOREIGN KEY (department_id) REFERENCES departments (department_id) 	
27);"""
28
29table_departments = """
30CREATE TABLE IF NOT EXISTS departments(
31department_id SERIAL PRIMARY KEY,
32department_name TEXT NOT NULL
33);"""
34
35
36orders_data = [
37    (datetime.now(), 'ААА', 'заказ ААА', 'active', 111, 1,),
38    (datetime.now(), 'BBB', 'zakaz BBB', 'active', 222, 2,),
39    (datetime.now(), 'ZZZ', 'zakaz ZZZ', 'closed', 333, 3,)
40]
41
42employees = [
43    ('Levchenko Daniil', 'Sales', 1),
44    ('Vorona Yaroslav', 'IT', 2),
45    ('Medvedyuk Karina', 'Human Resources', 3)
46]
47
48departments = [('Sales',), ('IT',), ('Human Resources',)]
49
50INSERT_QUERY_ORDERS = sql.SQL('''
51INSERT INTO orders(create_dt, order_type, description, ord_status, serial_num, creator_id)
52VALUES (%s, %s, %s, %s, %s, %s''')
53
54INSERT_QUERY_DEPARTMENTS = sql.SQL('''
55INSERT INTO departments(department_name)
56VALUES (%s)''')
57
58INSERT_QUERY_EMPLOYEES = sql.SQL('''
59INSERT INTO employees(fio, employee_position, department_id) VALUES (%s, %s, %s)''')
60
61with conn, conn.cursor() as cursor:
62    cursor.execute(table_departments)
63    cursor.execute(table_employees)
64    cursor.execute(table_orders)
65    for department in departments:
66        cursor.execute(INSERT_QUERY_DEPARTMENTS, department)
67    for employee in employees:
68        cursor.execute(INSERT_QUERY_EMPLOYEES, employee)
69    for order in orders_data:
70        cursor.execute(INSERT_QUERY_ORDERS, order)
71