def main(table): import db_access3 DB = "ECM_Acts_CBGM" TABLE = table dba = db_access3.DBA("remote") cursor = dba.cursor() cmd = "select count(distinct anfadr, 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 hsnr) from %s.%s " % (DB, TABLE) cursor.execute(cmd) res = cursor.fetchone() HSNRCOUNT = res[0] print "%s hat %d Handschriften." % (TABLE, HSNRCOUNT) cmd = "select distinct hsnr from %s.%s " % (DB, TABLE) cursor.execute(cmd) hsnrs = cursor.fetchall() cmd = "select distinct anfadr, endadr from %s.%s " % (DB, TABLE) cursor.execute(cmd) passages = cursor.fetchall() for hsnr in hsnrs: for passage in passages: cmd = "select count(id) from %s.%s " % (DB, TABLE) cmd += "where hsnr = %d " % (hsnr[0]) cmd += "and anfadr = %d and endadr = %d " % (passage[0], passage[1]) cursor.execute(cmd) res = cursor.fetchone() if res[0] != 1: print "Fehler bei Handschrift %d an Stelle %d/%d. " % ( hsnr[0], passage[0], passage[1]) cursor.close() dba.close()
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_9(db1, db2, tab1, tab2, mode="remote"): import db_access3 dba = db_access3.DBA(mode) cursor = dba.cursor() sourcetable = tab1 lactable = sourcetable + "Lac" # 0. Truncate table cmd = "truncate %s.%s " % (db1, lactable) cursor.execute(cmd) dba.commit() # 1.1. passages, passcount = dba.getPassages(db1, sourcetable) # 1.2. cmd = "select distinct hs from %s.%s " % (db2, tab2) cmd += "order by hsnr " cursor.execute(cmd) mss = cursor.fetchall() # 1.3. for ms in mss: hs = ms[0] for passage in passages: anfadr = passage[0] endadr = passage[1] cmd = "select count(id) from %s.%s " % (db2, tab2) cmd += "where anfadr <= %d and endadr >= %d " % (anfadr, endadr) cmd += "and hs = '%s' " % (hs) cursor.execute(cmd) result = cursor.fetchone() rescount = result[0] if rescount > 0: enter2LocalLacList(cursor, hs, db1, lactable, anfadr, endadr) dba.commit() # it's an InnoDB table 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(table): import db_access3 DB = "ECM_Acts_CBGMPh2" TABLE = table dba = db_access3.DBA("remote") cursor = dba.cursor() cmd = "select count(distinct anfadr, 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 hsnr) 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 hsnr from %s.%s " % (DB, TABLE) cursor.execute(cmd) hsnrs = cursor.fetchall() for hsnr in hsnrs: cmd = "select count(hsnr) from %s.%s where hsnr = %d " % ( DB, TABLE, hsnr[0]) cursor.execute(cmd) c = cursor.fetchone() if int(c[0]) != PASSCOUNT: print hsnr if int(c[0]) > PASSCOUNT: # Jede Handschrift darf es pro variierter Stelle nur einmal geben passages, void = dba.getPassages(DB, TABLE) for passage in passages: cmd = "select id from %s.%s " % (DB, TABLE) cmd += "where anfadr = %d and endadr = %d and hsnr = %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" % (c[0]) cursor.close() dba.close()
def __init__(self, _edition=28): ''' Konstruktor Die Datenbank enthält den Nestletext der Auflagen 28 und der gerade entstehenden 29. Auflage (= ECM Acta). Andere Auflagen können nicht abgefragt werden. ''' #import MySQLdb self.fehlverse = Fehlverse.Fehlvers() dba = db_access3.DBA("remote") self.cursor = dba.cursor() self.edition = _edition if self.edition < 28 or self.edition > 29: print "Selected edition is not available!" print "Using edition 28 instead." self.edition = 28
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(database, table, mode="remote"): import db_access3 dba = db_access3.DBA(mode) cursor = dba.cursor() # get the passages cmd = "select distinct begadr, endadr from `%s`.`%s` " % (database, table) cursor.execute(cmd) rows = cursor.fetchall() for r in rows: anfadr = r[0] endadr = r[1] # get the readings and their sources cmd = "select varid, varnew from `%s`.`%s` " % (database, table) cmd += "where begadr = %d and endadr = %d " % (anfadr, endadr) cmd += "and (varnew like '%%1' or " cmd += "varnew like '%%2' or " cmd += "varnew like '%%3' or " cmd += "varnew like '%%4' or " cmd += "varnew like '%%5' or " cmd += "varnew like '%%6' or " cmd += "varnew like '%%7' or " cmd += "varnew like '%%8' or " cmd += "varnew like '%%9') " cursor.execute(cmd) result = cursor.fetchall() for n in result: varid = n[0] varnew = n[1] # are there any readings having varid instead of varnew as S1 or S2? cmd = "select distinct varid, varnew, s1, s2 from `%s`.`%s` " % ( database, table) cmd += "where begadr = %d and endadr = %d " % (anfadr, endadr) cmd += "and (s1 = '%s' or s2 = '%s') " % (varid, varid) cmd += "and varid not like 'z%%' " cursor.execute(cmd) mistakes = cursor.fetchall() for m in mistakes: print "%d/%d:%s has the wrong source(s): %s" % (anfadr, endadr, varnew, m[2]) 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_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(mode="remote"): # static variables PHASE1 = "`ECM_ActsPh2`.`Acts15GVZ`" PHASE2 = "`ECM_ActsPh4`.`Acts15GVZ`" # configure database connection import db_access3, Address from NestleAland import getBookName conn = db_access3.DBA(mode) cursor = conn.cursor() # print temporary table def printTable(): cmd = "select anfadr, endadr, labez, labezsuf, reading1 " cmd += "from `ECM_Acts`.`TempTable` order by anfadr asc, endadr desc, labez asc; " cursor.execute(cmd) rows = cursor.fetchall() for r in rows: anf = r[0] end = r[1] lab = r[2] las = r[3] rd1 = r[4] b, bc, bv, bw, ec, ev, ew = Address.decodeAdr(anf, end) sb = getBookName(b) s = db_access3.formatAdr(sb, bc, bv, bw, ec, ev, ew) s1 = s2 = "" if rd1 != None: s1 = "\n\t>" + rd1.decode('utf8') + "< " print "%s%s%s%s " % (s, lab, las, s1) # file to generate sql statements for patristic citation database fd = open("deleatur.sql", "w") # create temporary table cmd = "drop table if exists `ECM_Acts`.`TempTable`; " cursor.execute(cmd) cmd = "CREATE TABLE `ECM_Acts`.`TempTable` (" cmd += "`anfadr` int(11) NOT NULL, " cmd += "`endadr` int(11) NOT NULL, " cmd += "`labez` varchar(8) NOT NULL, " cmd += "`labezsuf` varchar(16) default '', " cmd += "`reading1` varchar(1024) character set utf8 collate utf8_bin default NULL" cmd += ") ENGINE=MyISAM DEFAULT CHARSET=latin1" cursor.execute(cmd) print "old:" cmd = "select distinct anfadr, endadr, labez, labezsuf from %s order by anfadr, endadr desc; " % ( PHASE1) cursor.execute(cmd) rows = cursor.fetchall() for row in rows: anf = row[0] end = row[1] lab = row[2] las = row[3] cmd = "select count(*) from %s " % (PHASE2) cmd += "where anfadr = %s and endadr = %s " % (anf, end) cmd += "and labez = '%s' and labezsuf = '%s' " % (lab, las) cursor.execute(cmd) res = cursor.fetchone() if res[0] == 0: cmd = "insert into `ECM_Acts`.`TempTable` " cmd += "(anfadr, endadr, labez, labezsuf) values " cmd += "(%d, %d, '%s', '%s'); " % (anf, end, lab, las) cursor.execute(cmd) # generate sql file s = "delete from `Apparat_Zitate`.`Cit2Reading` " s += "where anfadr = %d and endadr = %d " % (anf, end) s += "and labez = '%s' and labezsuf = '%s'; \n" % (lab, las) fd.write(s) cmd = "select distinct a.anfadr, a.endadr, a.labez, a.labezsuf, a.lesart from %s a, %s b " % ( PHASE1, PHASE2) cmd += "where a.anfadr = b.anfadr and a.endadr = b.endadr " cmd += "and a.labez = b.labez and a.labezsuf = b.labezsuf " cmd += "and a.lesart <> b.lesart " cmd += "order by b.anfadr, b.endadr desc, b.labez; " cursor.execute(cmd) rows = cursor.fetchall() for row in rows: anf = row[0] end = row[1] lab = row[2] las = row[3] try: rdg = row[4].decode('utf8') except: rdg = "" # check if already entered in the table # cmd = "select count(*) from `ECM_Acts`.`TempTable` " # cmd += "where anfadr = %s and endadr = %s " % (anf, end) # cmd += "and labez = '%s' and labezsuf = '%s'; " % (lab, las) # count = cursor.execute(cmd) # if count == 0: cmd = "insert into `ECM_Acts`.`TempTable` " cmd += "(anfadr, endadr, labez, labezsuf, reading1) values " cmd += "(%d, %d, '%s', '%s', '%s'); " % (anf, end, lab, las, rdg) cursor.execute(cmd) printTable() # truncate temp table cmd = "truncate `ECM_Acts`.`TempTable`; " cursor.execute(cmd) print "" print "new:" cmd = "select distinct anfadr, endadr, labez, labezsuf from %s order by anfadr, endadr desc; " % ( PHASE2) cursor.execute(cmd) rows = cursor.fetchall() for row in rows: anf = row[0] end = row[1] lab = row[2] las = row[3] cmd = "select count(*) from %s " % (PHASE1) cmd += "where anfadr = %s and endadr = %s " % (anf, end) cmd += "and labez = '%s' and labezsuf = '%s' " % (lab, las) cursor.execute(cmd) res = cursor.fetchone() if res[0] == 0: cmd = "insert into `ECM_Acts`.`TempTable` " cmd += "(anfadr, endadr, labez, labezsuf) values " cmd += "(%d, %d, '%s', '%s'); " % (anf, end, lab, las) cursor.execute(cmd) cmd = "select distinct b.anfadr, b.endadr, b.labez, b.labezsuf, b.lesart from %s a, %s b " % ( PHASE1, PHASE2) cmd += "where a.anfadr = b.anfadr and a.endadr = b.endadr " cmd += "and a.labez = b.labez and a.labezsuf = b.labezsuf " cmd += "and a.lesart <> b.lesart " cmd += "order by b.anfadr, b.endadr desc, b.labez; " cursor.execute(cmd) rows = cursor.fetchall() for row in rows: anf = row[0] end = row[1] lab = row[2] las = row[3] try: rdg = row[4].decode('utf8') except: rdg = "" # check if already entered in the table # cmd = "select count(*) from `ECM_Acts`.`TempTable` " # cmd += "where anfadr = %s and endadr = %s " % (anf, end) # cmd += "and labez = '%s' and labezsuf = '%s'; " % (lab, las) # count = cursor.execute(cmd) # if count == 0: cmd = "insert into `ECM_Acts`.`TempTable` " cmd += "(anfadr, endadr, labez, labezsuf, reading1) values " cmd += "(%d, %d, '%s', '%s', '%s'); " % (anf, end, lab, las, rdg) cursor.execute(cmd) # read temporary table printTable() # drop temporary table cmd = "drop table `ECM_Acts`.`TempTable`; " cursor.execute(cmd) # closing database cursor.close() conn.close() # close file handle fd.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_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()