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