def main_3(db, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Tabellennamen erschließen sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter cmd = "show tables from `%s` like 'Acts%s%%'; " % (db, sChapter) cursor.execute(cmd) tables = cursor.fetchall() for table in tables: # Tabellenfelder loeschen mit 'alter table ..., drop ...' cmd = "alter table `%s`.`%s` " % (db, table[0]) cmd += "drop SUFFIX1, drop ADR, drop ZUSATZ, drop LesartenKey " printer(cmd) try: cursor.execute(cmd) except: pass dba.commit() cursor.close() dba.close()
def main_5(database, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Tabellennamen aus chapter generieren sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter table = "Acts" + sChapter + "att" if database.endswith("2"): table += "_2" if database.endswith("3"): table += "_3" # Alle variierten Stellen auflisten passages, void = dba.getPassages(database, table) for passage in passages: anfadr = passage[0] endadr = passage[1] # new code # get readings without labezsuf and overlapping variants cmd = "select distinct anfadr, endadr, labez " cmd += "from `%s`.`%s` " % (database, table) cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr) cmd += "and labez not like 'z%%'; " count = cursor.execute(cmd) if count == 1: cmd = "delete from `%s`.`%s` " % (database, table) cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr) cursor.execute(cmd) # old code ## Pro variierter Stelle alle Lesarten auflisten #readings, void = dba.getReadings(database, table, anfadr, endadr) #HasVariant = False #for reading in readings: # labez = reading[2] # # Gibt es eine Lesart anders als 'a' oder 'z...'? # if labez in ("b", "c", "d", "e", "f", "g", "h", "i", "j", # "k", "l", "m", "n", "o", "p", "q", "r", "s", # "t", "u", "v", "w", "x", "y"): # HasVariant = True # break #if not HasVariant: # # Wenn nicht, dann diesen Eintrag loeschen, da es sich nicht # # um eine variierte Stelle handelt: Es gibt keine Varianten # cmd = "delete from `%s`.`%s` " % (database, table) # cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr) # printer(cmd) # cursor.execute(cmd) dba.commit() cursor.close() dba.close()
def main_1(sourceDB, targetDB, chapter=0, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3, datetime dba = db_access3.DBA(access.get()) cursor = dba.cursor() # sourceDB = "Apparat_Annette" # targetDB = "ECM_Acts_UpdatePh2" # 1.0. Loeschen der bereits vorhandenen Tabellen in der Zieldatenbank cmd = "drop database `%s` " % (targetDB) cursor.execute(cmd) cmd = "create database `%s` " % (targetDB) cursor.execute(cmd) if chapter == 0: # 1.1. Anzeige aller Tabellen in der Quelldatenbank cmd = "show tables from `%s` like 'Acts%%'; " % (sourceDB) else: # 1.1. Anzeige der (beiden) Tabellen eines Kapitels if chapter < 10: sChapter = "0" + str(chapter) else: sChapter = str(chapter) cmd = "show tables from `%s` like 'Acts%s%%'; " % (sourceDB, sChapter) printer(cmd) cursor.execute(cmd) tables = cursor.fetchall() for table in tables: # 1.2. Generieren eines Datumsstempels d = "%s" % datetime.date.today() datum = d[:4] + d[5:7] + d[8:] # 1.3. Ausschneiden der Buch-, Kapitel- und Versionsbezeichnung name = table[0][:9] # Neuen Namen zusammensetzen name += datum # Bei Bedarf das Suffix 'lac' anfuegen if table[0].endswith("lac"): name += "lac" # 1.4. Tabellen anlegen mit 'create table like ...' cmd = "create table `%s`.`%s` " % (targetDB, name) cmd += "like `%s`.`%s` " % (sourceDB, table[0]) printer(cmd) cursor.execute(cmd) # 1.5. Befuellen mit 'insert into ... select * from ...' cmd = "insert into `%s`.`%s` " % (targetDB, name) cmd += "select * from `%s`.`%s` " % (sourceDB, table[0]) printer(cmd) cursor.execute(cmd) dba.commit() cursor.close() dba.close()
def main(table): import access import db_access3 DB = "VarGenAtt_ActPh3" TABLE = table dba = db_access3.DBA(access.get()) cursor = dba.cursor() cmd = "select count(distinct begadr, endadr) from %s.%s " % (DB, TABLE) cursor.execute(cmd) res = cursor.fetchone() PASSCOUNT = res[0] print "%s hat %d variierte Stellen." % (TABLE, PASSCOUNT) cmd = "select count(distinct ms) from %s.%s " % (DB, TABLE) cursor.execute(cmd) res = cursor.fetchone() HSNRCOUNT = res[0] print "%s hat %d Handschriften." % (TABLE, HSNRCOUNT) cmd = "select count(*) from %s.%s" % (DB, TABLE) cursor.execute(cmd) res = cursor.fetchone() SUM = res[0] if SUM == PASSCOUNT * HSNRCOUNT: print "magisches Produkt stimmt" else: cmd = "select distinct ms from %s.%s " % (DB, TABLE) cursor.execute(cmd) hsnrs = cursor.fetchall() for hsnr in hsnrs: cmd = "select count(ms) from %s.%s where ms = %d " % (DB, TABLE, hsnr[0]) cursor.execute(cmd) c = cursor.fetchone() count = int(c[0]) if count != PASSCOUNT: print hsnr if count != PASSCOUNT: # Jede Handschrift darf es pro variierter Stelle nur einmal geben cmd = "select distinct begadr, endadr from %s.%s " % ( DB, TABLE) cursor.execute(cmd) passages = cursor.fetchall() for passage in passages: cmd = "select varid, witn from %s.%s " % (DB, TABLE) cmd += "where begadr = %d and endadr = %d and ms = %d " % ( passage[0], passage[1], hsnr[0]) cursor.execute(cmd) res = cursor.fetchall() if len(res) > 1: print "\tBei %d/%d ist der Zeuge mehrfach verzeichnet. Vgl. ID:" % ( passage[0], passage[1]) print "\t", res else: print "\t%s" % (count) break cursor.close() dba.close()
def main_2(db, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Tabellennamen erschließen sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter cmd = "show tables from `%s` like 'Acts%s%%'; " % (db, sChapter) cursor.execute(cmd) tables = cursor.fetchall() for table in tables: cmd = "select korr, lekt, id from %s.%s " % (db, table[0]) cursor.execute(cmd) rows = cursor.fetchall() for row in rows: korr = row[0] lekt = row[1] id = row[2] if korr is not None: if korr.find("L") >= 0: cmd = "update %s.%s " % (db, table[0]) cmd += "set lekt = '%s' where id = %d " % (korr, id) printer(cmd) cursor.execute(cmd) cmd = "update %s.%s " % (db, table[0]) cmd += "set korr = '' where id = %d " % (id) printer(cmd) cursor.execute(cmd) if lekt is not None: pos = lekt.find("C") if pos >= 0: pass else: pos = lekt.find("*") if pos >= 0: cmd = "update %s.%s " % (db, table[0]) cmd += "set korr = '%s' where id = %d " % (lekt, id) printer(cmd) cursor.execute(cmd) cmd = "update %s.%s " % (db, table[0]) cmd += "set lekt = '' where id = %d " % (id) printer(cmd) cursor.execute(cmd) dba.commit() cursor.close() dba.close()
def main_4(source, target, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Datenbank auswählen cmd = "use %s" % (source) cursor.execute(cmd) dba.commit() # Anfang des Tabellennamens generieren sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter tablename = "Acts" + sChapter # Tabellen erfragen cmd = "show tables like '%s%%'" % (tablename) cursor.execute(cmd) tables = cursor.fetchall() for t in tables: table = t[0] # Ausschneiden der Buch- und Kapitelbezeichnung name = tablename[0:6] # Neuen Namen zusammensetzen vgl. unten name += "att" if table.endswith("lac"): name += "Lac" if target.endswith("2"): name += "_2" if target.endswith("3"): name += "_3" # Evtl. vorhandene Tabelle loeschen cmd = "drop table if exists `%s`.`%s` " % (target, name) printer(cmd) cursor.execute(cmd) # Tabellen anlegen mit 'create table like ...' cmd = "create table `%s`.`%s` " % (target, name) cmd += "like `%s`.`%s` " % (source, table) printer(cmd) cursor.execute(cmd) # Befuellen mit 'insert into ... select * from ...' cmd = "insert into `%s`.`%s` " % (target, name) cmd += "select * from `%s`.`%s` " % (source, table) printer(cmd) cursor.execute(cmd) dba.commit() cursor.close() dba.close()
def main_7(database, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Tabellennamen aus chapter generieren sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter table = "Acts" + sChapter + "att" if database.endswith("2"): table += "_2" if database.endswith("3"): table += "_3" # Zu loeschende Zeichen definieren pattern = "fo_1234567890/" # Alle 'zw'-Lesarten auflisten cmd = "select labezsuf, anfadr, endadr from `%s`.`%s` " % (database, table) cmd += "where labez like 'zw' " # Fälle wie "zw e/f" müssen aber stehen bleiben, z.B. Acta 4,2/20-34 # alternativ könnte man die beiden folgenden Zeilen streichen, # und in der Schleife ein continue setzen, wenn ein RegEx zutrifft. cmd += "and labezsuf not like 'f%%' " cmd += "and labezsuf not like '%%/f%%' " cursor.execute(cmd) rows = cursor.fetchall() for row in rows: labezsuf = row[0] anfadr = row[1] endadr = row[2] s = mystrip(labezsuf, pattern) # Labez einheitlich immer gleich? b = matches(s[0], s) # Wenn wahr, dann diesen Datensatz updaten if b: cmd = "update `%s`.`%s` " % (database, table) cmd += "set labez = '%s' " % (s[0]) cmd += "where labez like 'zw' " cmd += "and labezsuf like '%s' " % (labezsuf) cmd += "and anfadr = %s and endadr = %s " % (anfadr, endadr) printer(cmd) cursor.execute(cmd) dba.commit() cursor.close() dba.close()
def main_6b(database, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Tabellennamen aus chapter generieren sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter table = "Acts" + sChapter + "att" if database.endswith("2"): table += "_2" if database.endswith("3"): table += "_3" # Alle in Frage kommenden Stellen auflisten cmd = "select anfadr, endadr, HS from `%s`.`%s` " % (database, table) cmd += "where HS like '%%V%%' or HS like '%%*%%' or HS like '%%C%%' " cursor.execute(cmd) rows = cursor.fetchall() for r in rows: anf = r[0] end = r[1] wit = r[2] # 'V', 'C' oder '*' aus der Handschriftenbezeichnung entfernen wit_new = killVStarC(wit) # Update der Datenbanktabelle cmd = "update `%s`.`%s` " % (database, table) cmd += "set HS = '%s' " % (wit_new) cmd += "where anfadr = %d and endadr = %d " % (anf, end) cmd += "and HS = '%s' " % (wit) printer(cmd) # Kontrollausgabe cursor.execute(cmd) dba.commit() cursor.close() dba.close()
def main_1b(database, chapter=0, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() if chapter == 0: # 1.1. Anzeige aller Tabellen in der Quelldatenbank cmd = "show tables from `%s` like 'Acts%%'; " % (database) else: # 1.1. Anzeige der (beiden) Tabellen eines Kapitels if int(chapter) < 10: sChapter = "0" + str(chapter) else: sChapter = str(chapter) cmd = "show tables from `%s` like 'Acts%s%%'; " % (database, sChapter) printer(cmd) cursor.execute(cmd) tables = cursor.fetchall() for table in tables: # Lückenliste überspringen if table[0].endswith("lac"): continue # 1.2. Datensätze mit Handschriftennummer > 500000 löschen cmd = "delete from `%s`.`%s` " % (database, table[0]) cmd += "where `hsnr` >= 500000 " printer(cmd) cursor.execute(cmd) dba.commit() cursor.close() dba.close()
def main_8(databaseAtt, tab, recreateTable=False, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s from NestleAland import NA import access import db_access3 na = NA() dba = db_access3.DBA(access.get()) cursor = dba.cursor() databaseList = "ECM_Acts_Mss" table = "ActsMsList_2" chapter = tab[4:6] attTable = "`ECM_Acts_CBGMPh3`.`Acts"+chapter+"att_3`" # # Anlegen der Tabelle # if recreateTable: # createMsList(cursor, databaseList, table) # # Einlesen der Handschriftennummern - mehr noch nicht # insertMss(cursor, databaseList, table) # Welche Handschriftennummern gibt es in der Tabelle? cmd = "select ms from `%s`.`%s` order by `ms` " % (databaseList, table) cursor.execute(cmd) mss = cursor.fetchall() for ms in mss: # Update der einzelnen Zellen pro Handschrift und Kapitel cmd = "select versanf, versend, wortanf, wortend " cmd += "from `%s`.`%s` " % (databaseAtt, tab) cmd += "where hsnr = %d and kapanf = %d " % (ms[0], int(chapter)) #print cmd # tab ist hier die Lueckenliste! counter = cursor.execute(cmd) #print "counter ist ",counter result = cursor.fetchone() if counter == 1: try: #print "ms ist ", ms #print result versanf = result[0] versend = result[1] wortanf = result[2] wortend = result[3] maxvers = na.getMaxVerse(5, int(chapter)) maxword = na.getMaxWord(5, int(chapter), int(maxvers)) if versanf == 1 and wortanf == 2 and versend == maxvers and wortend >= maxword: # Lacune umgreift ganzes Kapitel value = 0 else: # es ist (mindestens teilweise) Text erhalten value = checkChapter(cursor, attTable, ms) except TypeError: value = 1 else: #value = 1 value = checkChapter(cursor, attTable, ms) cmd = "update `%s`.`%s` " % (databaseList, table) cmd += "set Apg%s = %d where ms = %s " % (chapter, value, ms[0]) printer(cmd) cursor.execute(cmd) #print cmd # Update der summarischen Angabe 'Apg' for ms in mss: cmd = "select Apg01, Apg02, Apg03, Apg04, Apg05, Apg06, Apg07, Apg08, " cmd += "Apg09, Apg10, Apg11, Apg12, Apg13, Apg14, Apg15, Apg16, Apg17, Apg18, " cmd += "Apg19, Apg20, Apg21, Apg22, Apg23, Apg24, Apg25, Apg26, Apg27, Apg28 " cmd += "from `%s`.`%s` " % (databaseList, table) cmd += "where ms = %s " % (ms[0]) cursor.execute(cmd) result = cursor.fetchone() sum = 0 for n in range(28): sum += int(result[n]) # Summe == 28 d.h. alle Kapitel haben Text if sum == 28: cmd = "update `%s`.`%s` " % (databaseList, table) cmd += "set Apg = 1 where ms = %s " % (ms[0]) else: cmd = "update `%s`.`%s` " % (databaseList, table) cmd += "set Apg = 0 where ms = %s " % (ms[0]) cursor.execute(cmd) cursor.close() dba.close()
def main_5b(database, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 # Open handles dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Choose database cmd = "USE %s;" % (database) cursor.execute(cmd) dba.commit() table = getTableName(cursor, database, chapter) # Select T+number witnesses ordered by addresses cmd = "SELECT DISTINCT `ANFADR`, `ENDADR` FROM `%s`.`%s` " % (database, table) cmd += "WHERE `HS` LIKE '%%T%%' " cmd += "ORDER BY `ANFADR`, `ENDADR` DESC;" cursor.execute(cmd) addresses = cursor.fetchall() for adr in addresses: anf = adr[0] end = adr[1] cmd = "SELECT DISTINCT `HSNR` FROM `%s`.`%s` " % (database, table) cmd += "WHERE `ANFADR` = %s AND `ENDADR` = %s " % (anf, end) cmd += "AND `HS` LIKE '%%T%%';" cursor.execute(cmd) hss = cursor.fetchall() for hsnr in hss: # three lists to store information: del_ids = [] labez_s = [] labezsuf_s = [] cmd = "SELECT `ID`, `HS`, `LABEZ`, `ANFADR`, `ENDADR`, `BUCH`, " cmd += "`KAPANF`, `VERSANF`, `WORTANF`, `KAPEND`, `VERSEND`, `WORTEND`, " cmd += "`HSNR`, `LABEZSUF` " cmd += "FROM `%s`.`%s` " % (database, table) cmd += "WHERE `HS` LIKE '%%T%%' " cmd += "AND `ANFADR` = %s AND `ENDADR` = %s " % (anf, end) cmd += "AND `HSNR` = %s " % (hsnr) cmd += "ORDER BY `LABEZ`, `LABEZSUF`;" count = cursor.execute(cmd) if count == 1: # the counterpart seems to be a lacuna: treat this entry as # normal witness and delete the T notation row = cursor.fetchone() ident = row[0] hs = row[1] pos = hs.find("T") hs = hs[:len(hs) - 2] # chop off T1 etc. cmd = "UPDATE `%s`.`%s` " % (database, table) cmd += "SET HS = '%s' " % (hs) cmd += "WHERE id = %s;" % (ident) print cmd # cursor.execute(cmd) else: # count > 1 rows = cursor.fetchall() for row in rows: del_ids.append(row[0]) labez_s.append(row[2]) labezsuf_s.append(row[13]) book = row[5] bc = row[6] bv = row[7] bw = row[8] ec = row[9] ev = row[10] ew = row[11] anf = row[3] end = row[4] labez = row[2] hs = row[1] hsnr = row[12] pos = hs.find("T") hs = hs[:pos] # Delete these witnesses for i in del_ids: cmd = "DELETE FROM `%s`.`%s` " % (database, table) cmd += "WHERE `ID` = %d;" % (i) print cmd # cursor.execute(cmd) # Insert a new zw reading suffix2 = "" for n in range(len(del_ids)): l1 = labez_s[n] l2 = labezsuf_s[n] suffix2 = suffix2 + l1 if len(l2) > 0: suffix2 = suffix2 + "_" + l2 suffix2 = suffix2 + "/" suffix2 = suffix2[:len(suffix2) - 1] # chop off last slash cmd = "INSERT INTO `%s`.`%s` " % (database, table) cmd += "(BUCH, KAPANF, VERSANF, WORTANF, KAPEND, VERSEND, WORTEND, " cmd += "ANFADR, ENDADR, LABEZ, LABEZSUF, HS, HSNR) VALUES (" cmd += "%d, %d, %d, %d, %d, %d, %d, " % (book, bc, bv, bw, ec, ev, ew) cmd += "%d, %d, 'zw', '%s', '%s', %d" % (anf, end, suffix2, hs, hsnr) cmd += ");" print cmd # cursor.execute(cmd) # Inno-DB tables need an explicit commit statement dba.commit() # Close handles cursor.close() dba.close()
def main_9(db1, db2, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Generate name of newtable and lactable sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter newtable = "Acts" + sChapter + "att" lactable = newtable + "Lac" if db1.endswith("2"): newtable += "_2" lactable += "_2" if db1.endswith("3"): newtable += "_3" lactable += "_3" # Query systematic lacuna table name sys_lac_table = getSysLacTable(cursor, db2, sChapter) # 9.0. Truncate lacuna table cmd = "truncate %s.%s " % (db1, lactable) printer(cmd) cursor.execute(cmd) dba.commit() # 9.1. passages, passcount = dba.getPassages(db1, newtable) # 9.2. cmd = "select distinct hs from %s.%s " % (db2, sys_lac_table) cmd += "order by hsnr " printer(cmd) cursor.execute(cmd) mss = cursor.fetchall() # get max endadr cmd = "select max(endadr) from `%s`.`%s` " % (db2, sys_lac_table) cursor.execute(cmd) result = cursor.fetchone() max_endadr = result[0] # 9.3. for ms in mss: hs = ms[0] # 9.4. for passage in passages: anfadr = passage[0] endadr = passage[1] cmd = "select count(id) from %s.%s " % (db2, sys_lac_table) if endadr < max_endadr: cmd += "where anfadr <= %d and endadr >= %d " % (anfadr, endadr) else: cmd += "where anfadr <= %d and endadr >= %d " % (anfadr, endadr-1) cmd += "and hs = '%s' " % (hs) printer(cmd) cursor.execute(cmd) result = cursor.fetchone() rescount = result[0] # 9.5. if rescount > 0: enter2LocalLacList(cursor, hs, db1, lactable, anfadr, endadr) dba.commit() # it's an InnoDB table cursor.close() dba.close()
def main_6(database, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Tabellennamen aus chapter generieren sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter table = "Acts" + sChapter + "att" if database.endswith("2"): table += "_2" if database.endswith("3"): table += "_3" # Zuerst die einfachen Faelle: cmd = "delete from `%s`.`%s` " % (database, table) cmd += "where (lekt = 'L2' or korr in ('C', 'C1', 'C2', 'C3', 'A', 'K')) " cmd += "and suffix2 <> '%C*%' " printer(cmd) cursor.execute(cmd) # Nachbesserung notwendig: cmd = "delete from `%s`.`%s` " % (database, table) cmd += "where suffix2 like '%L2%' or suffix2 like '%A%' " cmd += "or suffix2 like '%K%' " printer(cmd) cursor.execute(cmd) # Sonderfall Selbstkorrektur: C* cmd = "select anfadr, endadr, hsnr from `%s`.`%s` " % (database, table) cmd += "where suffix2 like '%%C*' " cursor.execute(cmd) rows = cursor.fetchall() for row in rows: anfadr = row[0] endadr = row[1] hsnr = row[2] cmd = "delete from `%s`.`%s` " % (database, table) cmd += "where anfadr = %s and endadr = %s " % (anfadr, endadr) cmd += "and hsnr = %d " % (hsnr) cmd += "and suffix2 like '%%*%%' and suffix2 not like '%%C*' " printer(cmd) cursor.execute(cmd) dba.commit() # Eintraege loeschen, die nun keine Varianten mehr haben passages, void = dba.getPassages(database, table) for p in passages: anfadr = p[0] endadr = p[1] cmd = "select count(distinct labez) " cmd += "from `%s`.`%s` " % (database, table) cmd += "where anfadr = %d and endadr = %d " % (anfadr, endadr) cmd += "and labez not like 'a' and labez not like 'z%%'" cursor.execute(cmd) res = cursor.fetchone() if res[0] == 0: cmd = "delete from `%s`.`%s` " % (database, table) cmd += "where anfadr = %d and endadr = %d " % (anfadr, endadr) printer(cmd) cursor.execute(cmd) dba.commit() # Eintraege - Ende cursor.close() dba.close()
def main_9(db1, db2, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 dba = db_access3.DBA(access.get()) cursor = dba.cursor() # Generate name of newtable and lactable sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter newtable = "Acts" + sChapter + "att" lactable = newtable + "Lac" if db1.endswith("2"): newtable += "_2" lactable += "_2" if db1.endswith("3"): newtable += "_3" lactable += "_3" # Query systematic lacuna table name sys_lac_table = getSysLacTable(cursor, db2, sChapter) # 9.0. Truncate lacuna table cmd = "truncate %s.%s " % (db1, lactable) printer(cmd) cursor.execute(cmd) dba.commit() # 9.1. passages, passcount = dba.getPassages(db1, newtable) # 9.2. cmd = "select distinct hs from %s.%s " % (db2, sys_lac_table) cmd += "order by hsnr " printer(cmd) cursor.execute(cmd) mss = cursor.fetchall() # get max endadr cmd = "select max(endadr) from `%s`.`%s` " % (db2, sys_lac_table) cursor.execute(cmd) result = cursor.fetchone() max_endadr = result[0] # 9.3. for ms in mss: hs = ms[0] # 9.4. for passage in passages: anfadr = passage[0] endadr = passage[1] cmd = "select count(id) from %s.%s " % (db2, sys_lac_table) if endadr < max_endadr: cmd += "where anfadr <= %d and endadr >= %d " % (anfadr, endadr) else: cmd += "where anfadr <= %d and endadr >= %d " % (anfadr, endadr - 1) cmd += "and hs = '%s' " % (hs) printer(cmd) cursor.execute(cmd) result = cursor.fetchone() rescount = result[0] # 9.5. if rescount > 0: enter2LocalLacList(cursor, hs, db1, lactable, anfadr, endadr) dba.commit() # it's an InnoDB table cursor.close() dba.close()
def main_10(database, chapter, verbose=False): def printer(s): """ Print information if needed. """ if verbose: print s import access import db_access3 from Address import formatNumber, decodeAdr dba = db_access3.DBA(access.get()) cursor = dba.cursor() databaseAtt = database # Generate name of newtable and lactable sChapter = str(chapter) if int(chapter) < 10: sChapter = "0" + sChapter tableAtt = "Acts" + sChapter + "att" tableAttLac = tableAtt + "Lac" if database.endswith("2"): tableAtt += "_2" tableAttLac += "_2" if database.endswith("3"): tableAtt += "_3" tableAttLac += "_3" databaseList = "ECM_Acts_Mss" tableList = "ActsMsList_2" # "ActsMsList" pattern = "Apg" + formatNumber(chapter, 2) # Alle variierten Stellen mit ihren a-Lesarten cmd = "select distinct anfadr, endadr, lesart, labez, base " cmd += "from `%s`.`%s` " % (databaseAtt, tableAtt) cmd += "where `hs` = 'A' " # cmd += "where (labez like 'a' and labezsuf like '' and base like 'a') " # cmd += "or (labez like 'b' and labezsuf like '' and base like 'b') " # cmd += "order by anfadr asc, endadr desc " # print cmd cursor.execute(cmd) passages = cursor.fetchall() for passage in passages: anfadr = passage[0] endadr = passage[1] s = "Working on %d/%d." % (anfadr, endadr) printer(s) lesart = passage[2] labez = passage[3] base = passage[4] if lesart is None: lesart = "" b, bc, bv, bw, ec, ev, ew = decodeAdr(anfadr, endadr) # Alle Handschriften, die in diesem Kapitel Text, # d.h. keine Lücke haben, die das ganze Kapitel umfasst. # 1. 'a' auffuellen cmd = "insert into `%s`.`%s` " % (databaseAtt, tableAtt) cmd += "(hsnr, hs, anfadr, endadr, buch, kapanf, versanf, wortanf, " cmd += "kapend, versend, wortend, labez, labezsuf, anfalt, endalt, " cmd += "lesart, base) " cmd += "select ms, witn, %d, %d, " % (anfadr, endadr) cmd += "%d, %d, %d, %d, " % (b, bc, bv, bw) cmd += "%d, %d, %d, " % (ec, ev, ew) cmd += "'%s', '', " % (labez) cmd += "%d, " % (anfadr) cmd += "%d, " % (endadr) cmd += "'%s', " % (lesart.decode("utf-8")) cmd += "'%s' " % (base) cmd += "from `%s`.`%s` " % (databaseList, tableList) # ActsMsList cmd += "where %s = 1 " % (pattern) cmd += "and ms not in (select hsnr from `%s`.`%s` " % (databaseAtt, tableAttLac) cmd += "where anfadr >= %d and endadr <= %d) " % (anfadr, endadr) cmd += "and ms not in (select hsnr from `%s`.`%s` " % (databaseAtt, tableAtt) cmd += "where anfadr = %d and endadr = %d ) " % (anfadr, endadr) cmd += "order by ms " cursor.execute(cmd) dba.commit() # 2. 'zz' auffuellen cmd = "insert into `%s`.`%s` " % (databaseAtt, tableAtt) cmd += "(hsnr, hs, anfadr, endadr, buch, kapanf, versanf, wortanf, " cmd += "kapend, versend, wortend, labez, labezsuf, anfalt, endalt, " cmd += "lesart) " cmd += "select ms, witn, %d, %d, " % (anfadr, endadr) cmd += "%d, %d, %d, %d, " % (b, bc, bv, bw) cmd += "%d, %d, %d, 'zz', '', %d, %d, '%s' " % (ec, ev, ew, anfadr, endadr, lesart) cmd += "from `%s`.`%s` " % (databaseList, tableList) cmd += "where %s = 1 " % (pattern) cmd += "and ms not in (select hsnr from `%s`.`%s` " % (databaseAtt, tableAtt) cmd += "where anfadr = %d and endadr = %d ) " % (anfadr, endadr) cmd += "order by ms " cursor.execute(cmd) dba.commit() # Ergaenzendes Update zur korrekten Verzeichnung der Fehlverse # 3. Update der umfassten variierten Stellen # Das Feld 'comp' wird durch das Skript umfasstevarianten.py # beschrieben und kennzeichnet umfasste Varianten. cmd = "update `%s`.`%s` " % (databaseAtt, tableAtt) cmd += "set labez = 'zu', " cmd += "lesart = '' " cmd += "where comp like 'x' and base like 'a' " # Adressen der Fehlverse hier hart kodieren: # vgl. die Datenbanktabelle Apparat.Fehlverse. # Diese Adressen schließen Additamenta ein. cmd += "and (" cmd += "anfadr >= 50837002 and endadr <= 50837047 " cmd += "or anfadr >= 51534002 and endadr <= 51534013 " cmd += "or anfadr >= 52506020 and endadr <= 52408015 " cmd += "or anfadr >= 52829002 and endadr <= 52829025 " cmd += "); " cursor.execute(cmd) dba.commit() ## Weitere Ergaenzung: ## 4. Quelle der b-Lesarten gilt als urspruenglich, alle ## anderen Lesarten stammen per default von b ab. #cmd = "update `%s`.`%s` " % (databaseAtt, tableAtt) #cmd += "set s1 = '*' " #cmd += "where labez = 'b' and (" #cmd += "anfadr >= 50837002 and endadr <= 50837047 " #cmd += "or anfadr >= 51534002 and endadr <= 51534013 " #cmd += "or anfadr >= 52506020 and endadr <= 52408015 " #cmd += "or anfadr >= 52829002 and endadr <= 52829025) " #cursor.execute(cmd) # FIXME: TESTEN #cmd = "update `%s`.`%s` " % (databaseAtt, tableAtt) #cmd += "set s1 = 'b' " #cmd += "where labez <> 'b' and (" #cmd += "anfadr >= 50837002 and endadr <= 50837047 " #cmd += "or anfadr >= 51534002 and endadr <= 51534013 " #cmd += "or anfadr >= 52506020 and endadr <= 52408015 " #cmd += "or anfadr >= 52829002 and endadr <= 52829025) " #cursor.execute(cmd) # FIXME: TESTEN # Nachbesserung: zz-Zeugen haben keine Lesart cmd = "update `%s`.`%s` " % (databaseAtt, tableAtt) cmd += "set lesart = '' where labez = 'zz' " cursor.execute(cmd) dba.commit() cursor.close() dba.close()