def test_02(): """ Initialise PUPILS table from "old" raw data (creation from scratch, no pre-existing table). """ db = DB(_testyear, 'RECREATE') fpath = os.path.join(Paths.getYearPath(_testyear, 'DIR_SCHOOLDATA'), '_test', 'pupil_data_0_raw') REPORT.Test( "Initialise with raw pupil data for school-year %d from:\n %s" % (_testyear, fpath)) rpd = readRawPupils(_testyear, fpath) for klass in sorted(rpd): REPORT.Test("\n +++ Class %s" % klass) for row in rpd[klass]: REPORT.Test(" --- %s" % repr(row)) updateFromRaw(_testyear, rpd) db.renameTable('PUPILS', 'PUPILS0') db.deleteIndexes('PUPILS0') REPORT.Test("Saved in table PUPILS0")
def updateFromRaw(schoolyear, rawdata): """Update the PUPILS table from the supplied raw pupil data. Only the fields supplied in the raw data will be affected. If there is no PUPILS table, create it, leaving fields for which no data is supplied empty. <rawdata>: {klass -> [<_IndexedData> instance, ...]} """ updated = False allfields = list(CONF.TABLES.PUPILS_FIELDNAMES) db = DB(schoolyear, flag='CANCREATE') # Build a pid-indexed mapping of the existing (old) pupil data. # Note that this is read in as <sqlite3.Row> instances! oldclasses = {} classes = set(rawdata) # collect all classes, old and new if db.tableExists('PUPILS'): for pmap in db.getTable('PUPILS'): pid = pmap['PID'] klass = pmap['CLASS'] classes.add(klass) try: oldclasses[klass][pid] = pmap except: oldclasses[klass] = {pid: pmap} # Collect rows for the new PUPILS table: newpupils = [] # Run through the new data, class-by-class for klass in sorted(classes): changed = OrderedDict() # pids with changed fields try: plist = rawdata[klass] except: # Class not in new data updated = True #TODO: do I want to record this (with pupil names??)? REPORT.Warn(_CLASSGONE, klass=klass) continue try: oldpids = oldclasses[klass] except: # A new class updated = True oldpids = {} #? REPORT.Warn(_NEWCLASS, klass=klass) #TODO: only the PIDs are stored, I would need at least their names, for reporting added = [] for pdata in plist: pid = pdata['PID'] prow = [] try: pmap0 = oldpids[pid] except: # A new pupil for f in allfields: try: val = pdata[f] except: val = None prow.append(val) updated = True added.append(pid) else: del (oldpids[pid]) # remove entry for this pid diff = {} # Compare fields for f in allfields: oldval = pmap0[f] try: # Allow for this field not being present in the # new data. val = pdata[f] # Record changed fields if val != oldval: diff[f] = val except: val = oldval prow.append(val) if diff: updated = True changed[pid] = diff newpupils.append(prow) if added: REPORT.Info(_NEWPUPILS, klass=klass, pids=repr(added)) if changed: REPORT.Info(_PUPILCHANGES, klass=klass, data=repr(changed)) if oldpids: REPORT.Info(_OLDPUPILS, klass=klass, pids=repr(list(oldpids))) if updated: REPORT.Info(_REBUILDPUPILS, year=schoolyear) else: REPORT.Warn(_NOUPDATES, year=schoolyear) return # Build database table NEWPUPILS. # Use (CLASS, PSORT) as primary key, with additional index on PID. # This makes quite a small db (without rowid). indexes = db.makeTable2('NEWPUPILS', allfields, data=newpupils, force=True, pk=('CLASS', 'PSORT'), index=('PID', )) db.deleteTable('OLDPUPILS') if db.tableExists('PUPILS'): db.renameTable('PUPILS', 'OLDPUPILS') db.renameTable('NEWPUPILS', 'PUPILS') db.deleteIndexes('PUPILS') db.makeIndexes('PUPILS', indexes)