-
Notifications
You must be signed in to change notification settings - Fork 8
/
Copy pathupdate_db.py
151 lines (117 loc) · 8.14 KB
/
update_db.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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
from UnoCPI import sqlfiles,settings
import psycopg2
import os
sql = sqlfiles
global connection
global cursor
try:
connection = psycopg2.connect(user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'],
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
database=settings.DATABASES['default']['NAME'],
sslmode="require")
if connection:
print("Postgres SQL Database successful connection")
cursor = connection.cursor()
print("Executing Community Partner status inactive")
#UPDATE Community partner to show status 'Inactive' from newly added Partner Status Table
update_comm_partner_status_to_inactive_from_table= """Update partners_communitypartner
Set partner_status_id =(select id from partners_partnerstatus
where name = 'Inactive')
Where id in (select id
from partners_communitypartner
where not active);"""
# create a temp table with all projects start and end dates
cursor.execute(update_comm_partner_status_to_inactive_from_table)
connection.commit()
print("Completed Community Partner status inactive")
print("Executing Community Partner status active")
#UPDATE Community partner to show status 'Active' from newly added Partner Status Table
update_comm_partner_status_to_active_from_table= """Update partners_communitypartner
Set partner_status_id =(select id from partners_partnerstatus
where name = 'Active')
Where id in (select id
from partners_communitypartner
where active);"""
# create a temp table with all projects start and end dates
cursor.execute(update_comm_partner_status_to_active_from_table)
connection.commit()
print("Completed Community Partner status active")
print("Executing Campus Partner status inactive")
#UPDATE Community partner to show status 'Inactive' from newly added Partner Status Table
update_campus_partner_status_to_inactive_from_table= """Update partners_campuspartner
Set partner_status_id =(select id from partners_partnerstatus
where name = 'Inactive')
Where id in (select id
from partners_campuspartner
where not active);"""
# create a temp table with all projects start and end dates
cursor.execute(update_comm_partner_status_to_inactive_from_table)
connection.commit()
print("Completed Campus Partner status inactive")
print("Executing Campus Partner status Active")
#UPDATE Community partner to show status 'Inactive' from newly added Partner Status Table
update_campus_partner_status_to_active_from_table= """Update partners_campuspartner
Set partner_status_id =(select id from partners_partnerstatus
where name = 'Active')
Where id in (select id
from partners_campuspartner
where active);"""
# create a temp table with all projects start and end dates
cursor.execute(update_campus_partner_status_to_active_from_table)
connection.commit()
print("completed Campus Partner status Active")
print("Executing Community Partner CEC status Current")
# UPDATE Community partner to show status 'Current' from newly added Partner Status Table
update_comm_partner_cec_status_to_current_from_table = """Update partners_communitypartner
Set cec_partner_status_id =(select id from partners_cecpartnerstatus
where name = 'Current')
Where id in (select id
from partners_communitypartner
where weitz_cec_part='Yes');"""
# create a temp table with all projects start and end dates
cursor.execute(update_comm_partner_cec_status_to_current_from_table)
connection.commit()
print("Completed Community Partner CEC status Current")
print("Executing Community Partner CEC status Former")
# UPDATE Community partner to show status 'Active' from newly added Partner Status Table
update_comm_partner_cec_status_to_former_from_table = """Update partners_communitypartner
Set cec_partner_status_id =(select id from partners_cecpartnerstatus
where name = 'Never')
Where id in (select id
from partners_communitypartner
where weitz_cec_part='No' or weitz_cec_part = 'False');"""
# create a temp table with all projects start and end dates
cursor.execute(update_comm_partner_cec_status_to_former_from_table)
connection.commit()
print("Completed Community Partner CEC status Former")
print("Executing Campus Partner CEC status Current")
# UPDATE Community partner to show status 'Current' from newly added Partner Status Table
update_comm_partner_cec_status_to_current_from_table = """Update partners_campuspartner
Set cec_partner_status_id =(select id from partners_cecpartnerstatus
where name = 'Current')
Where id in (select id
from partners_campuspartner
where weitz_cec_part='Yes');"""
# create a temp table with all projects start and end dates
cursor.execute(update_comm_partner_cec_status_to_current_from_table)
connection.commit()
print("Completed Campus Partner CEC status Current")
print("Executing Camous Partner CEC status Former")
# UPDATE Community partner to show status 'Active' from newly added Partner Status Table
update_comm_partner_cec_status_to_former_from_table = """Update partners_campuspartner
Set cec_partner_status_id =(select id from partners_cecpartnerstatus
where name = 'Never')
Where id in (select id
from partners_campuspartner
where weitz_cec_part='No' or weitz_cec_part = 'False');"""
# create a temp table with all projects start and end dates
cursor.execute(update_comm_partner_cec_status_to_former_from_table)
connection.commit()
print("Completed Campus Partner CEC status Former")
except (psycopg2.Error) as error:
print("Error while connecting to Postgres SQL", error)
cursor.close()
connection.close()
print("Postgres SQL connection is closed")