From ddf643353fcafaf19dd579d51de682386ad30e0f Mon Sep 17 00:00:00 2001 From: naath Date: Sat, 12 Apr 2014 09:11:17 +0100 Subject: [PATCH] refactoring SELECTs --- cgiFiles/ancestorGraph.py | 29 +++++++------------ cgiFiles/jointAncestorGraph.py | 20 ++++--------- cgiFiles/smallGraph.py | 9 +----- familyTree/askQuestion.py | 52 ++++++++++++++++++---------------- 4 files changed, 45 insertions(+), 65 deletions(-) diff --git a/cgiFiles/ancestorGraph.py b/cgiFiles/ancestorGraph.py index 4adb104..327754a 100755 --- a/cgiFiles/ancestorGraph.py +++ b/cgiFiles/ancestorGraph.py @@ -9,43 +9,34 @@ import askQuestion as aQ import make_dot as d -def add_parents(ID,name): +def add_parents(ID,Self): [parents, parentIDs,parentNames] = aQ.find_parents(ID) pair = [] for i in range(len(parents)): - newName = parents[i] + newSelf = parents[i] if parentIDs[i]!=0: newID = parentIDs[i] - if not d.has_node(newName): - d.add_person(newName) - add_parents(newID,newName) + if not d.has_node(newSelf): + d.add_person(newSelf) + add_parents(newID,newSelf) else: - d.add_person(newName) - pair.append(newName) + d.add_person(newSelf) + pair.append(newSelf) - d.add_marriage(pair[0],pair[1],[name],1) + d.add_marriage(pair[0],pair[1],[Self],1) def make_graph(ID): global allAncestors d.start_dot() - [out, allAncestors,trackLevel,aDict] = \ - aQ.all_ancestors(ID,'\n') - - - Self = allAncestors[0] - - s = "SELECT name||', '|| id FROM people WHERE ID=?" - t = (Self,) - for row in aQ.run_query(s,t): - Self = row[0] + Self = aQ.find_person(ID)[0] d.add_highlight(Self) - add_parents(allAncestors[0],Self) + add_parents(ID,Self) d.add_subgraphs() diff --git a/cgiFiles/jointAncestorGraph.py b/cgiFiles/jointAncestorGraph.py index 0279bc9..01a6319 100755 --- a/cgiFiles/jointAncestorGraph.py +++ b/cgiFiles/jointAncestorGraph.py @@ -37,22 +37,14 @@ def make_graph(ID,ID2,LA,LB): d.start_dot() if int(LA)!=0: - s = "SELECT name, id FROM people WHERE ID = ?;" - for row in aQ.run_query(s,(ID,)): - thisN = row[0]+' ' + str(row[1]) - d.add_highlight(thisN) - newName = (thisN) - newID = row[1] - add_parents(newID,newName,0,LA) + [Self, selfID, selfName] = aQ.find_person(ID) + d.add_highlight(Self) + add_parents(selfID,Self,0,LA) if int(LB)!=0: - s = "SELECT name, id FROM people WHERE ID = ?;" - for row in aQ.run_query(s,(ID2,)): - thisN = row[0] +' '+ str(row[1]) - d.add_highlight(thisN) - newName = (thisN) - newID = row[1] - add_parents(newID,newName,0,LB) + [Self, selfID, selfName] = aQ.find_person(ID2) + d.add_highlight(Self) + add_parents(selfID,Self,0,LB) d.add_subgraphs() d.end_dot() diff --git a/cgiFiles/smallGraph.py b/cgiFiles/smallGraph.py index add7211..393d6df 100755 --- a/cgiFiles/smallGraph.py +++ b/cgiFiles/smallGraph.py @@ -8,20 +8,13 @@ sys.path.append('/home/naath/familyTreeProject/familyTree') import askQuestion as aQ cgitb.enable() -def find_person(ID): - s = "SELECT name || ','||ID FROM people WHERE ID=?" - t = (ID,) - - for row in aQ.run_query(s,t): - Self = row[0] - return Self #def make_graph(Self,parents,children,otherparents,spouses): def make_graph(ID): d.start_dot() - Self = find_person(ID) + Self = aQ.find_person(ID)[0] d.add_highlight(Self) diff --git a/familyTree/askQuestion.py b/familyTree/askQuestion.py index 9135bef..c6273cc 100755 --- a/familyTree/askQuestion.py +++ b/familyTree/askQuestion.py @@ -103,6 +103,8 @@ def name_html(row,html): if row[0] == None: return row[1] + elif row[1]==0: + return row[0] else: if html==1: script = "person.py?ID=" + str(row[1]) @@ -229,10 +231,8 @@ def list_people_parents(): [parents, parentIDs,parentNames] = find_parents(ID) [spouses,spousesID,spousesNames] = find_spouses(ID) - - myName = row[0] - myID = str(row[1]) - output.append([myName+ ','+ myID,parents,spouses]) + [self,myID,myName] = find_person(ID) + output.append([self,parents,spouses]) return output @@ -369,7 +369,7 @@ def search_name(name,newLine): def people_with_name(name,newLine): s = "SELECT name, ID"\ +" FROM people"\ - +" WHERE firstname LIKE ?;" + +" WHERE firstname = ?;" out = '' @@ -480,19 +480,13 @@ def people_died_at_age(age,newLine): def all_ancestors(personID,newLine): #find parents - s = "SELECT people.Name,parents.parentID FROM"\ - +" parents LEFT JOIN people"\ - +" ON parents.parentID = people.ID"\ - +" WHERE parents.ID = ?"\ - +" AND parents.parentID <> '.';" - ancestors = [personID] allAncestors = [personID] trackLevel = [0] level = 0 - t = "SELECT name,id FROM people WHERE id==?" + t = "SELECT name,id FROM people WHERE id=?" id = (personID,) out = "Ancestors of " @@ -507,14 +501,17 @@ def all_ancestors(personID,newLine): thisout = newLine + parent_level(level,'parent') +\ ':' + newLine for ancestor in ancestors: - id = (ancestor,) - for row in run_query(s,id): + [parents, parentIDs,parentNames] \ + = find_parents(ancestor) + for i in range(len(parents)): + r = [parentNames[i],parentIDs[i]] thisout = thisout + \ - name_html(row,newLine)+newLine - if row[1] not in allAncestors\ - and is_number(row[1]): - newA.append(row[1]) - allAncestors.append(row[1]) + name_html(r,newLine)+newLine + + if r[1] not in allAncestors\ + and r[1]!=0: + newA.append(r[1]) + allAncestors.append(r[1]) trackLevel.append(level) ancestors = newA @@ -575,8 +572,6 @@ def common_ancestors(IDA, IDB,newLine): s = s+ "?," if len(common)>0: s = s[:-1] - - s = s+") ORDER BY bornyear;" @@ -734,6 +729,15 @@ def rulers_of(aTerritory,newLine): return out +def find_person(ID): + s = "SELECT name || ','||ID, name, ID FROM people WHERE ID=?" + t = (ID,) + + for row in run_query(s,t): + Self = row[0] + selfName = row[1] + selfID = row[2] + return [Self, selfID,selfName] def find_parents(ID): s = "SELECT name, parentID"\ @@ -758,7 +762,7 @@ def find_parents(ID): parents.append(p) parentIDs.append(pID) parentNames.append(pN) - + if parents[1]==parents[0]: parents[1] = parents[1] + ' 2' @@ -820,7 +824,7 @@ def find_children(ID): otherparentsNames=[] for row in run_query(s,t): - c = row[0] + ', ' + str(row[1]) + c = row[0] + ',' + str(row[1]) cID = row[1] cName = row[0] born = row[4] @@ -829,7 +833,7 @@ def find_children(ID): childrenNames.append(cName) childrenBorn.append(born) if row[3]!=None: - op = row[3] + ', ' + str(row[2]) + op = row[3] + ',' + str(row[2]) opID = row[2] opN = row[3] else: -- 2.30.2