def update_course_sections(conn: RealDictConnection, semester_id: str,
course_sections: List[CourseSection]):
c = conn.cursor()
c.execute("DELETE FROM course_section_periods WHERE semester_id=%s",
(semester_id, ))
c.execute("DELETE FROM course_sections WHERE semester_id=%s",
(semester_id, ))
print(f"Adding {len(course_sections)} sections")
for course_section in course_sections:
record = course_section.to_record()
# Add new record
q = Query \
.into(course_sections_t) \
.columns(*record.keys()) \
.insert(*record.values())
print(str(q))
c.execute(str(q))
# Add course sections
if len(course_section.periods) > 0:
q = Query \
.into(periods_t) \
.columns(*course_section.periods[0].dict().keys())
for period in course_section.periods:
q = q.insert(*period.to_record().values())
# https://github.com/kayak/pypika/issues/527
workaround = str(q).replace("ARRAY[]", "'{}'")
print(workaround)
c.execute(workaround)
conn.commit()
def update_course_sections(conn: RealDictConnection, semester_id: str, course_sections: List[CourseSection]):
c = conn.cursor()
c.execute(
"DELETE FROM course_section_periods WHERE semester_id=%s", (semester_id,))
c.execute("DELETE FROM course_sections WHERE semester_id=%s", (semester_id,))
print(f"Adding {len(course_sections)} sections...", flush=True)
for course_section in course_sections:
record = course_section.to_record()
# Add new record
q = Query \
.into(course_sections_t) \
.columns(*record.keys()) \
.insert(*record.values())
c.execute(str(q))
# Add course sections
if len(course_section.periods) > 0:
q = Query \
.into(periods_t) \
.columns(*course_section.periods[0].dict().keys())
for period in course_section.periods:
q = q.insert(*period.to_record().values())
c.execute(str(q))
if len(course_sections) > 0:
conn.commit()
print("Done!", flush=True)
else:
print("No course sections found... rolling back any changes")
print i, data
query = """\
create table usuarios_%(data)s
(like donor_work_old including constraints including defaults)
;
alter table usuarios_%(data)s add constraint b%(data)s
check (data = %(data)s)
;
insert into usuarios_%(data)s (data, usuario, wus, pontos)
select data, usuario, wus, pontos
from donors_old_back
where data = %(data)s
;
alter table usuarios_%(data)s
add constraint fk_usuarios_%(data)s foreign key (data) references datas (data_serial)
;
create unique index ndx_usuarios_%(data)s on usuarios_%(data)s (usuario)
;
alter table usuarios_%(data)s inherit donor_work_old
;
""" % {
'data': data
}
cursor.execute(query)
cursor.execute('commit;')
connection.commit()
connection.close()
d for d in serial_dates if d['d0'] not in l and d['d1'] is not None
]
del (l)
for d in serial_dates:
query = """
insert into team_active_members_history
(team_number, active_members, serial_date)
select team_number, active_members, serial_date
from (
select
count(u0.pontos > coalesce(u1.pontos, 0) or null) as active_members,
u0.data as serial_date,
ui.n_time as team_number
from usuarios u0
left outer join usuarios u1 on u0.usuario = u1.usuario and u1.data = %s
inner join usuarios_indice as ui on u0.usuario = ui.usuario_serial
where u0.data = %s
group by ui.n_time, u0.data
) s
where active_members > 0
""" % (d['d1'], d['d0'])
print query
#cursor.execute(query)
cursor.close()
db.commit()
db.close()
