5 from string import Template
8 link_Template= Template(\
9 "<a href = http://www.chiark.greenend.org.uk/ucgi/~naath/$script"\
10 +" title=$title>$text</a>")
19 def print_row(row,newLine):
22 out = out + str(item)+'|'
23 return out[:-1] + newLine
25 def print_query(s,t,newLine):
27 for row in run_query(s,t):
28 printMe = printMe + print_row(row,newLine)
38 def print_tagged_query(relationship,s,t,newLine):
40 for row in run_query(s,t):
41 mine = mine + print_tagged_name(relationship,row,newLine)
45 def relationship_html(ID,ID2,newLine):
47 relationship = common_ancestors(ID,ID2,newLine)[2]
49 if relationship[-11:] != 'not related':
50 script = "ancestors.py?ID="+str(ID)+"&ID2="+str(ID2)
51 url = link_Template.substitute\
52 (script = script,title = "Common ancestors"\
53 ,text = "Common ancestors")
54 return relationship + ' '+url + newLine
56 return relationship + newLine
60 def terr_html(terr,newLine,start,stop):
62 if start == 0 and stop ==0:
63 myTitle = add_quotes(terr)
66 s = "SELECT name,people.id"\
67 +" FROM people INNER JOIN territories"\
68 +" ON people.id = territories.id"\
69 +" WHERE territory = ? AND stopyear <= ?"\
70 +" ORDER BY startyear DESC;"
74 for row in run_query(s,t):
75 myTitle = myTitle +"previous - " + row[0] \
79 u = "SELECT name,people.id"\
80 +" FROM people INNER JOIN territories"\
81 +" ON people.id = territories.id"\
82 +" WHERE territory = ? AND startyear >= ?"\
83 +" ORDER BY startyear;"
86 for r in run_query(u,v):
87 myTitle = myTitle + '
' +"next - " + r[0] \
91 myTitle = add_quotes(myTitle)
93 return link_Template.substitute(\
94 script = "territory.py?terr="+terr, title=myTitle,\
98 def name_html(row,html):
108 script = "person.py?ID=" + str(row[1])
110 return link_Template.substitute(script = script\
111 ,title = add_quotes(name),text = name)
113 return row[0] + "," +str(row[1])
121 def print_age_child_count(row,newLine):
122 if newLine == '<br>':
123 script = "age.py?age="+str(row[0])
124 link = link_Template.substitute(script = \
125 script, title = add_quotes(row[0]), text = row[0])
126 out = str(row[1])+print_people(row[1])
128 out = out + 'had children at age '+ link + newLine
131 return print_row(row,newLine)
133 def print_age_death_count(row,newLine):
135 script = "ageDeath.py?age="+str(row[0])
136 link = link_Template.substitute(script = script,\
137 title = add_quotes(row[0]),text = row[0])
138 out = str(row[1])+print_people(row[1])
139 out = out + "died at age " + link + newLine
142 return print_row(row,newLine)
144 def print_name_count(row,newLine):
146 script = "name.py?name=" + row[0]
147 link = link_Template.substitute(script =\
148 script, title = add_quotes(row[0]),text = row[0])
149 return str(row[1]) + " people called "+link + newLine
151 return print_row(row,newLine)
153 def print_tagged_name(relationship,row,newLine):
155 out = relationship + " not yet entered: " + row[1]
157 if newLine == '<br>':
161 if relationship =='':
162 out = name_html(row,html) + ' '
164 out = relationship + ": " + name_html(row,html)
167 def month_numbers(monthN):
169 month ='unknown month'
195 month = 'Incorrectly entered month ' + str(monthN)
198 def ordinal_numbers(number):
200 if number % 10==1 and number/10 % 10 !=1:
201 out = str(number) +'st'
202 elif number % 10==2 and number/10 % 10 !=1:
203 out = str(number) +'nd'
204 elif number % 10==3 and number/10 % 10 !=1:
205 out = str(number) +'rd'
207 out = str(number) +'th'
210 def list_territories(newLine):
211 s = "SELECT DISTINCT territory"\
212 +" FROM territories"\
213 +" ORDER BY territory;"
216 for row in run_query(s,()):
217 out =out + terr_html(row[0],newLine,0,0) +newLine
220 def list_people_parents():
221 s = "SELECT name,id"\
226 for row in run_query(s,()):
229 [parents, parentIDs,parentNames] = find_parents(ID)
230 [spouses,spousesID,spousesNames] = find_spouses(ID)
235 output.append([myName+ ','+ myID,parents,spouses])
239 def list_people(newLine):
240 s = "SELECT name,id,bornyear"\
242 +" ORDER BY bornyear;"
246 out = out + 'born in unknown year:' +newLine
247 for row in run_query(s,()):
248 if row[2]!=0 and row[2]/100==0:
249 out = out +newLine+ 'born in 1st century:' +newLine
251 if row[2]/100!=year/100:
252 century = row[2]/100 + 1
253 out = out +newLine+ 'born in '
255 out = out +ordinal_numbers(century) \
256 + ' century:' + newLine
258 out = out + name_html(row,newLine) +newLine
260 if row[2] == 0: #unknown year
262 t = (row[1],) #person ID
266 u = "SELECT diedyear FROM people WHERE ID = ?;"
269 for r in run_query(u,t):
271 out = out + "died: "\
272 + str(r[0]) + newLine
273 bornAfter = r[0] -100
276 u = "Select people.bornYear from"\
277 +" people INNER JOIN parents"\
278 +" ON people.ID = parents.ID"\
279 +" WHERE parents.parentID = ?"\
280 + " ORDER BY people.bornYear;"
284 for r in run_query(u,t):
286 hadChild.append(r[0])
290 out = out + "had children in: "
292 out = out + str(c) + ','
293 out = out[:-1] + newLine
295 bornBefore = hadChild[0]-12
297 bornAfter = hadChild[0]-100
299 u = "Select styles.startYear, styles.style from"\
300 +" people INNER JOIN styles"\
301 +" ON people.ID = styles.ID"\
302 +" WHERE people.ID = ? and"\
303 +" styles.startYear <>0"\
304 +" ORDER BY styles.startYear;"
306 for r in run_query(u,t):
307 out = out + r[1] + " from " + str(r[0])\
310 bornAfter = r[0] -100
315 out = out + "probably born "\
316 +"after " + str(bornAfter)
318 out = out + "probably born "\
319 +"betwen " + str(bornAfter)\
320 +" and " + str(bornBefore)
326 def count_names(newLine):
327 s = "SELECT firstName, count(*)"\
329 +" GROUP BY firstName"\
330 +" ORDER BY count(*) DESC;"
333 for row in run_query(s,()):
334 out = out + print_name_count(row,newLine)
339 def search_name(name,newLine):
340 s = "SELECT name, ID"\
342 +" WHERE name LIKE ?;"
346 out = out + 'Names start with ' + name + ':' + newLine
349 for row in run_query(s,t):
350 out = out + name_html(row,newLine) + newLine
351 fullIDs.append(row[1])
352 t = ('%' + name + '%',)
353 out = out+newLine + 'Names contain ' + name + ':' + newLine
354 for row in run_query(s,t):
355 if row[1] not in fullIDs:
356 out = out + name_html(row,newLine) + newLine
358 s = "SELECT name,people.ID,style"\
359 +" FROM people INNER JOIN styles"\
360 +" ON styles.id = people.id"\
361 +" WHERE style LIKE ?;"
362 out = out +newLine+ 'Styles contain ' + name + ':' + newLine
363 for row in run_query(s,t):
364 out = out + name_html(row,newLine)+' ' + row[2] + newLine
369 def people_with_name(name,newLine):
370 s = "SELECT name, ID"\
372 +" WHERE firstname LIKE ?;"
378 for row in run_query(s,t):
379 out = out + name_html(row,newLine) + newLine
383 def count_birth_month(newLine):
384 s = "SELECT bornMonth, count(*)"\
386 +" GROUP BY bornMonth"\
387 +" ORDER BY bornMonth;"
389 t = "SELECT * FROM people WHERE bornMonth = ?;"
392 for row in run_query(s,()):
393 month = month_numbers(row[0])
394 out = out + month + ': ' + str(row[1]) + newLine
398 out = out +print_query(t,u,newLine)
402 def count_death_month(newLine):
403 s = "SELECT diedMonth, count(*)"\
405 +" GROUP BY diedMonth"\
406 +" ORDER BY diedMonth;"
408 t = "SELECT * FROM people WHERE diedMonth = ?;"
411 for row in run_query(s,()):
412 month = month_numbers(row[0])
413 out = out + month + ': ' + str(row[1]) + newLine
417 out = out +print_query(t,u,newLine)
421 def count_age_at_child(newLine):
423 s = "select p1.bornYear - p2.bornYear as age, count(*)"\
425 +" parents INNER JOIN people p1"\
426 +" ON parents.ID = p1.ID"\
427 +" INNER JOIN people p2"\
428 +" ON parents.parentID = p2.ID"\
429 +" WHERE p1.bornYear <> 0 and p2.bornYear<>0"\
433 for row in run_query(s,()):
434 out = out + print_age_child_count(row,newLine)
438 def people_had_child_at_age(age,newLine):
440 s = "select p1.bornYear - p2.bornYear as age, p1.name, p1.ID"\
441 +",p2.name,p2.ID FROM"\
442 +" parents INNER JOIN people p1"\
443 +" ON parents.ID = p1.ID"\
444 +" INNER JOIN people p2"\
445 +" ON parents.parentID = p2.ID"\
446 +" WHERE age = ? AND p1.bornYear<>0 AND p2.bornYear<>0"
451 out = 'At age ' + str(age) + ' :'
452 for row in run_query(s,t):
454 out =out + name_html([row[3],row[4]],newLine) + ' had '\
455 +name_html([row[1],row[2]],newLine)
459 def count_age_at_death(newLine):
460 s = "select diedYear-bornYear as age,count(*)"\
462 +" WHERE diedYear<>0 AND bornYear<>0"\
465 for row in run_query(s,()):
466 out = out + print_age_death_count(row,newLine)
469 def people_died_at_age(age,newLine):
470 s = "SELECT diedYear-bornYear as age, name,ID"\
472 +" WHERE age = ? AND bornYear<>0 AND diedYear<>0;"
475 out = 'These people died at age ' +str(age) + ' :'
476 for row in run_query(s,t):
478 out = out + name_html([row[1],row[2]],newLine)
481 def all_ancestors(personID,newLine):
483 s = "SELECT people.Name,parents.parentID FROM"\
484 +" parents LEFT JOIN people"\
485 +" ON parents.parentID = people.ID"\
486 +" WHERE parents.ID = ?"\
487 +" AND parents.parentID <> '.';"
490 ancestors = [personID]
491 allAncestors = [personID]
495 t = "SELECT name,id FROM people WHERE id==?"
498 out = "Ancestors of "
499 for row in run_query(t,id):
500 out = out + name_html(row,newLine)+newLine
503 aDict[level] = ancestors
504 while len(ancestors)>0:
507 thisout = newLine + parent_level(level,'parent') +\
509 for ancestor in ancestors:
511 for row in run_query(s,id):
512 thisout = thisout + \
513 name_html(row,newLine)+newLine
514 if row[1] not in allAncestors\
515 and is_number(row[1]):
517 allAncestors.append(row[1])
518 trackLevel.append(level)
522 aDict[level]=ancestors
526 image = "<img src = ancestorGraph.py?id="+str(personID)+">"
527 out = out+newLine + image+newLine
528 return [out, allAncestors,trackLevel,aDict]
531 def common_ancestors(IDA, IDB,newLine):
532 out = 'Common ancestors of:' + newLine
534 s = "SELECT name,id FROM people WHERE id==?"
540 for row in run_query(s,t):
541 out = out + name_html(row,newLine)+newLine
548 related = 'No details held on one party'
550 return [out,[],related]
553 a = all_ancestors(IDA,newLine)
554 b = all_ancestors(IDB,newLine)
556 ancestorsB = set(b[1])
557 ancestorsA = set(a[1])
559 common = ancestorsA.intersection(ancestorsB)
560 common = list(common)
567 aLevels.append(a[2][i])
569 bLevels.append(b[2][i])
571 s = "SELECT Name, ID, bornyear"\
574 for i in range(len(common)):
580 s = s+") ORDER BY bornyear;"
584 related = names[0]+' and '+names[1]+' are not related'
585 out = out + newLine + related
586 return [out, common,related]
589 out = out + print_tagged_query('',s,common,newLine)
594 for i in range(len(common)):
595 if aLevels[i] == min(aLevels):
597 if bLevels[i] == min(bLevels):
602 s = "SELECT name, id"\
606 out = out + newLine + 'Most Recent Common Ancestors:' + newLine
610 mrca.append(common[a])
611 out = out + print_tagged_query('',s,t,newLine)
613 out = out + 'and' + newLine
615 out = out + parent_level(aLevels[indexA[0]],'parent')
619 out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
624 # out = out + print_tagged_query('',s,t,newLine)
626 # out = out + 'and' + newLine
628 out = out + parent_level(bLevels[indexB[0]],'parent')
631 out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
634 al = aLevels[indexA[0]]
635 bl = bLevels[indexB[0]]
637 related = relationship(al,bl,names)
638 out = out+newLine + related
641 image = "<img src = jointAncestorGraph.py?id="+str(IDA)\
642 +"&id2="+str(IDB) + "&LA=" + str(min(aLevels)) \
643 +"&LB=" + str(min(bLevels))+">"
647 out = out+newLine + image+newLine
649 return [out,common,related]
651 def relationship(level1, level2,names):
653 if level1==0 and level2==0:
654 return names[0] + ' is ' +names[1]
656 return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2,'parent')
658 return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1,'parent')
662 remove = level1-level2
665 remove = level2-level1
669 uaLevel = parent_level(remove,'uncle or aunt')
671 return names[0] + ' is ' + names[1] + '\'s ' + uaLevel
674 return names[1] + ' is ' + names[0] + '\'s ' + uaLevel
676 c=ordinal_numbers(cousinNum)
682 rem = str(remove) + ' times'
684 r = names[0] +' and '+names[1] +' are ' + c + ' cousins '
686 r = r+ rem + ' removed'
690 def parent_level(level,type):
705 for i in range(2,level):
709 def rulers_of(aTerritory,newLine):
711 tq = "SELECT name, people.ID, startyear,stopyear,territory"\
712 +" FROM territories INNER JOIN people"\
713 +" ON people.ID = territories.ID"\
714 +" WHERE territory LIKE ?"\
715 +" ORDER BY territory,startyear,stopyear;"
722 for row in run_query(tq,(aTerritory+'%',)):
723 if row[4]!=last and last!='':
724 out = out + 'Rulers of '+terr_html(last,newLine,0,0) \
725 +':'+ newLine +thisT +newLine
728 thisT = thisT +name_html(row,newLine)
729 thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
732 out = out + 'Rulers of '+terr_html(row[4],newLine,0,0) +':'+ \
738 def find_parents(ID):
739 s = "SELECT name, parentID"\
740 +" FROM parents LEFT JOIN people"\
741 +" ON people.ID = parentID"\
742 +" WHERE parents.ID = ?;"
749 for row in run_query(s,t):
751 p = row[0] + ',' + str(row[1])
755 p = row[1] + ',p ' + str(ID)
759 parentIDs.append(pID)
760 parentNames.append(pN)
762 if parents[1]==parents[0]:
763 parents[1] = parents[1] + ' 2'
765 return [parents,parentIDs,parentNames]
767 def find_spouses(ID):
770 order = [["IDb","IDa"],["IDa","IDb"]]
776 s = "SELECT name, marriages." + o[0]\
777 +" FROM marriages LEFT JOIN people"\
778 +" ON marriages." +o[0]+" = people.ID"\
779 +" WHERE marriages."+o[1]+" = ?;"
782 for row in run_query(s,t):
784 s = row[0] + "," +str(row[1])
788 s=row[1] + ",s " +str(ID)
793 spousesID.append(sID)
794 spousesNames.append(sN)
796 return [spouses,spousesID,spousesNames]
799 def find_children(ID):
800 s = "SELECT p1.name, p1.ID,p3.parentID,p4.name,p1.bornYear"\
802 +" INNER JOIN parents p2"\
803 +" ON p1.ID = p2.ID"\
804 +" INNER JOIN parents p3"\
805 +" ON p1.ID = p3.ID"\
806 +" LEFT JOIN people"\
807 +" p4 ON p3.parentID = p4.ID"\
808 +" WHERE p2.parentID = ?"\
809 +" AND p3.parentID<>?"\
810 +" ORDER BY p1.bornYear;"
822 for row in run_query(s,t):
823 c = row[0] + ', ' + str(row[1])
828 childrenID.append(cID)
829 childrenNames.append(cName)
830 childrenBorn.append(born)
832 op = row[3] + ', ' + str(row[2])
836 op = row[2] + ',s ' + ID
839 otherparents.append(op)
840 otherparentsID.append(opID)
841 otherparentsNames.append(opN)
843 return [children,childrenID,childrenNames\
844 ,otherparents,otherparentsID,otherparentsNames\
847 def person_info(personID,newLine):
851 #Id, Name, Dates, Style, Style-Dates
852 s = "SELECT * FROM people WHERE ID = ?"
853 for row in run_query(s,t):
854 mainDiv = mainDiv + '<p>'
855 mainDiv = mainDiv + 'ID: '+str(row[0]) +newLine
856 mainDiv = mainDiv + print_tagged_name('Name',[row[1], row[0]]\
858 mainDiv = mainDiv + '</p>'
863 mainDiv = mainDiv + '<p>'
864 mainDiv = mainDiv + newLine + 'Born: '+row[3] + newLine
866 mainDiv = mainDiv + 'Died: '+row[5]
868 if row[6] != 0 and row[4] !=0:
869 mainDiv = mainDiv + ", aged " \
871 mainDiv = mainDiv + '</p>'
874 s = "SELECT * FROM styles WHERE ID = ?"
875 for row in run_query(s,t):
876 mainDiv = mainDiv + '<p>'
877 mainDiv = mainDiv +newLine+ 'Style: '+row[1] + newLine
879 mainDiv = mainDiv + 'Territories:' + newLine
881 u = "SELECT * FROM territories"\
882 +" WHERE ID =? AND startYear =? AND stopYear=?"
883 v=(personID,row[3],row[5])
886 for r in run_query(u,v):
888 + terr_html(r[1],newLine,r[3],r[5])\
892 mainDiv = mainDiv[:-1] + newLine
894 mainDiv = mainDiv + 'From: '+row[2] + newLine
895 mainDiv = mainDiv + 'To: '+row[4]
897 mainDiv = mainDiv + '</p>'
902 mainDiv = mainDiv + '<p>'
903 s = "SELECT people.Name,consort "\
904 +"FROM consorts LEFT JOIN people"\
905 +" ON people.ID = consorts.consort"\
906 +" WHERE consorts.ID = ?"
907 for row in run_query(s,t):
908 mainDiv = mainDiv + print_tagged_name\
909 ('Consort of',row,newLine)
910 mainDiv = mainDiv + '</p>'
914 [parents,parentIDs,parentNames] = find_parents(personID)
915 mainDiv = mainDiv + '<p>'
916 for i in range(len(parents)):
917 r = [parentNames[i],parentIDs[i]]
918 mainDiv = mainDiv + print_tagged_name('Parent',r,newLine)
919 mainDiv = mainDiv + "</p>"
923 [spouses,spousesID,spousesNames] = find_spouses(personID)
925 mainDiv = mainDiv + '<p>'
927 for i in range(len(spouses)):
928 r = [spousesNames[i],spousesID[i]]
929 mainDiv = mainDiv + print_tagged_name('Spouse',r,newLine)
930 mainDiv = mainDiv + \
931 relationship_html(personID,r[1],newLine)
933 mainDiv = mainDiv + '</p>'
936 [children,childrenID,childrenNames\
937 ,otherparents,otherparentsID,otherparentsNames\
939 find_children(personID)
942 for i in range(len(children)):
943 cr = [childrenNames[i],childrenID[i]]
944 thisChild = print_tagged_name('Child',cr,newLine)
946 opr=[otherparentsNames[i],otherparentsID[i]]
947 top = otherparentsNames[i]
948 if i==0 or top != otherparentsNames[i-1]:
949 mainDiv = mainDiv +'</p>'
950 mainDiv = mainDiv + '<p>'
951 mainDiv = mainDiv + print_tagged_name\
952 ('With',opr, newLine)
957 if cb!=0 and bornYear != 0:
959 thisChild = thisChild[:-4] + \
960 " at the age of "+str(age) + newLine
961 mainDiv = mainDiv + thisChild
963 mainDiv = mainDiv + '</p>'
965 output = '<div id = "main" style = " float:left">';
966 output = output + mainDiv + "</div>"
968 output = output + "<div id = 'image' "\
969 +"style = 'float:left; margin-left:20px'>"
973 imageDiv = imageDiv + "<a href=" + url+">"\
974 +"<img src=" + picture +" alt = 'wiki link'"\
975 +" title = 'wiki link'></a>"\
978 elif url!='.' and url!='. ':
979 imageDiv = imageDiv + "<a href=" + url +">"\
980 +name + " (wiki link)</a>"+newLine
982 output = output + imageDiv + "</div>"
984 graph = "smallGraph.py?ID="+str(personID)
986 graph ="<img src ="+ graph + '>'
988 output = output + "<div id = 'graph' style = 'clear:both'>"
989 output = output + graph
990 output = output + "</div>"
998 conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
1002 return conn.cursor()
1009 # [c, conn] = connect()