-
Notifications
You must be signed in to change notification settings - Fork 0
/
Task1.py
109 lines (104 loc) · 3.19 KB
/
Task1.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
import csv
class StockIn:
def __init__(self, pool):
self.__pool = pool
def insert_data(self):
conn = self.__pool.get_conn()
cur = conn.cursor()
self.__task1_create_function(conn)
self.__create_table(conn)
# conn.autocommit = True
with open('task/task1_final.csv') as task1:
reader = csv.reader(task1)
reader.__next__()
for line in reader:
line = str(line).lstrip('[').rstrip(']')
try:
cur.execute('insert into stockIn values (' + line + ')')
conn.commit()
except Exception as e:
conn.rollback()
# print(e)
continue
# conn.autocommit = False
# conn.commit()
cur.close()
conn.close()
def __task1_create_function(self, conn):
cur = conn.cursor()
cur.execute('drop table if exists stockIn;')
cur.execute('drop function if exists task1_1;')
cur.execute('drop function if exists task1_2;')
cur.execute('''create function task1_1(center_name varchar, salesman_id char(8), product_model varchar, qt integer)
returns bool as
$$
declare
result varchar;
ch varchar;
begin
select s.supply_center
into result
from staff s
where s.number = salesman_id;
if result = center_name
then
select model
into ch
from stock
where model = product_model
and center = center_name;
if ch is not null
then
update stock
set quantity = quantity + qt
where model = product_model
and center = center_name;
update stock
set current_quantity = current_quantity + qt
where model = product_model
and center = center_name;
else
insert into stock values (product_model, center_name, qt, qt);
end if;
return true;
else
return false;
end if;
end;
$$ language plpgsql;''')
cur.execute('''create function task1_2(salesman_num char(8))
returns bool as
$$
declare
result varchar;
begin
select s.type into result from staff s where s.number = salesman_num;
if (result = 'Supply Staff')
then
return true;
else
return false;
end if;
end;
$$ language plpgsql;''')
conn.commit()
cur.close()
def __create_table(self, conn):
cur = conn.cursor()
cur.execute('''create table if not exists stockIn
(
id integer primary key,
supply_center varchar not null,
product_model varchar not null,
supply_staff char(8) not null,
date date not null,
purchase_price numeric not null,
quantity integer not null,
foreign key (supply_center) references center (name),
foreign key (product_model) references product (model),
foreign key (supply_staff) references staff (number),
check ( task1_2(supply_staff) ),
check ( task1_1(supply_center, supply_staff, product_model, quantity) )
);''')
conn.commit()
cur.close()