5 from string import Template
8 link_Template= Template("<a href = http://www.chiark.greenend.org.uk/ucgi/~naath/$script>$text</a>")
14 def print_row(row,newLine):
17 out = out + str(item)+'|'
18 return out[:-1] + newLine
20 def print_query(s,t,newLine):
22 for row in run_query(s,t):
23 printMe = printMe + print_row(row,newLine)
33 def print_tagged_query(relationship,s,t,newLine):
35 for row in run_query(s,t):
36 mine = mine + print_tagged_name(relationship,row,newLine)
40 def relationship_html(ID,ID2,newLine):
42 relationship = common_ancestors(ID,ID2,newLine)[2]
43 script = "ancestors.py?ID="+str(ID)+"&ID2="+str(ID2)
44 url = link_Template.substitute(script = script,text = "Common ancestors")
45 return relationship + ' '+url + newLine
49 def terr_html(terr,newLine):
51 return link_Template.substitute(script = "territory.py?terr="+terr, text=terr)
54 def name_html(row,html):
64 script = "person.py?ID=" + str(row[1])
66 return link_Template.substitute(script = script\
69 return row[0] + "," +str(row[1])
71 def print_age_count(row,newLine):
73 script = "age.py?age="+str(row[0])
74 link = link_Template.substitute(script = \
75 script, text = row[0])
76 return str(row[1]) + ' people had children at age '\
79 return print_row(row,newLine)
82 def print_name_count(row,newLine):
84 script = "name.py?name=" + row[0]
85 link = link_Template.substitute(script =\
86 script, text = row[0])
87 return str(row[1]) + " people called "+link + newLine
89 return print_row(row,newLine)
91 def print_tagged_name(relationship,row,newLine):
93 out = relationship + " not yet entered: " + row[1]
100 out = name_html(row,html) + ' '
102 out = relationship + ": " + name_html(row,html)
105 def month_numbers(monthN):
107 month ='unknown month'
133 month = 'Incorrectly entered month ' + str(monthN)
136 def ordinal_numbers(number):
138 if number % 10==1 and number/10 % 10 !=1:
139 out = str(number) +'st'
140 elif number % 10==2 and number/10 % 10 !=1:
141 out = str(number) +'nd'
142 elif number % 10==3 and number/10 % 10 !=1:
143 out = str(number) +'rd'
145 out = str(number) +'th'
148 def list_territories(newLine):
149 s = "SELECT DISTINCT territory"\
150 +" FROM territories"\
151 +" ORDER BY territory;"
154 for row in run_query(s,()):
155 out =out + terr_html(row[0],newLine) +newLine
159 def list_people(newLine):
160 s = "SELECT name,id,bornyear"\
162 +" ORDER BY bornyear;"
166 out = out + 'born in unknown year:' +newLine
167 for row in run_query(s,()):
168 if row[2]!=0 and row[2]/100==0:
169 out = out +newLine+ 'born in 1st century:' +newLine
171 if row[2]/100!=year/100:
172 century = row[2]/100 + 1
173 out = out +newLine+ 'born in '
175 out = out +ordinal_numbers(century) \
176 + ' century:' + newLine
178 out = out + name_html(row,newLine) +newLine
180 if row[2] == 0: #unknown year
182 t = (row[1],) #person ID
186 u = "SELECT diedyear FROM people WHERE ID = ?;"
189 for r in run_query(u,t):
191 out = out + "died: "\
192 + str(r[0]) + newLine
193 bornAfter = r[0] -100
196 u = "Select people.bornYear from"\
197 +" people INNER JOIN parents"\
198 +" ON people.ID = parents.ID"\
199 +" WHERE parents.parentID = ?"\
200 + " ORDER BY people.bornYear;"
204 for r in run_query(u,t):
206 hadChild.append(r[0])
210 out = out + "had children in: "
212 out = out + str(c) + ','
213 out = out[:-1] + newLine
215 bornBefore = hadChild[0]-12
217 bornAfter = hadChild[0]-100
219 u = "Select styles.startYear, styles.style from"\
220 +" people INNER JOIN styles"\
221 +" ON people.ID = styles.ID"\
222 +" WHERE people.ID = ? and"\
223 +" styles.startYear <>0"\
224 +" ORDER BY styles.startYear;"
226 for r in run_query(u,t):
227 out = out + r[1] + " from " + str(r[0])\
230 bornAfter = r[0] -100
235 out = out + "probably born "\
236 +"after " + str(bornAfter)
238 out = out + "probably born "\
239 +"betwen " + str(bornAfter)\
240 +" and " + str(bornBefore)
246 def count_names(newLine):
247 s = "SELECT firstName, count(*)"\
249 +" GROUP BY firstName"\
250 +" ORDER BY count(*) DESC;"
253 for row in run_query(s,()):
254 out = out + print_name_count(row,newLine)
258 def people_with_name(name,newLine):
259 s = "SELECT Name, ID"\
261 +" WHERE Name LIKE ?;"
267 for row in run_query(s,t):
268 out = out + name_html(row,newLine) + newLine
272 def count_birth_month(newLine):
273 s = "SELECT bornMonth, count(*)"\
275 +" GROUP BY bornMonth"\
276 +" ORDER BY bornMonth;"
278 t = "SELECT * FROM people WHERE bornMonth = ?;"
281 for row in run_query(s,()):
282 month = month_numbers(row[0])
283 out = out + month + ': ' + str(row[1]) + newLine
287 out = out +print_query(t,u,newLine)
291 def count_death_month(newLine):
292 s = "SELECT diedMonth, count(*)"\
294 +" GROUP BY diedMonth"\
295 +" ORDER BY diedMonth;"
297 t = "SELECT * FROM people WHERE diedMonth = ?;"
300 for row in run_query(s,()):
301 month = month_numbers(row[0])
302 out = out + month + ': ' + str(row[1]) + newLine
306 out = out +print_query(t,u,newLine)
310 def count_age_at_child(newLine):
312 s = "select p1.bornYear - p2.bornYear as age, count(*)"\
314 +" parents INNER JOIN people p1"\
315 +" ON parents.ID = p1.ID"\
316 +" INNER JOIN people p2"\
317 +" ON parents.parentID = p2.ID"\
318 +" WHERE p1.bornYear <> 0 and p2.bornYear<>0"\
322 for row in run_query(s,()):
323 out = out + print_age_count(row,newLine)
327 def people_had_child_at_age(age,newLine):
329 s = "select p1.bornYear - p2.bornYear as age, p1.name, p1.ID"\
330 +",p2.name,p2.ID FROM"\
331 +" parents INNER JOIN people p1"\
332 +" ON parents.ID = p1.ID"\
333 +" INNER JOIN people p2"\
334 +" ON parents.parentID = p2.ID"\
335 +" WHERE age = ? AND p1.bornYear<>0 AND p2.bornYear<>0"
340 out = 'At age ' + str(age) + ' :'
341 for row in run_query(s,t):
343 out =out + name_html([row[3],row[4]],newLine) + ' had '\
344 +name_html([row[1],row[2]],newLine)
349 def all_ancestors(personID,newLine):
351 s = "SELECT people.Name,parents.parentID FROM"\
352 +" parents LEFT JOIN people"\
353 +" ON parents.parentID = people.ID"\
354 +" WHERE parents.ID = ?"\
355 +" AND parents.parentID <> '.';"
358 ancestors = [personID]
359 allAncestors = [personID]
363 t = "SELECT name,id FROM people WHERE id==?"
366 out = "Ancestors of "
367 for row in run_query(t,id):
368 out = out + name_html(row,newLine)+newLine
370 while len(ancestors)>0:
373 thisout = newLine + parent_level(level,'parent') +':' + newLine
374 for ancestor in ancestors:
376 for row in run_query(s,id):
377 thisout = thisout + name_html(row,newLine)+newLine
378 if row[1] not in allAncestors and is_number(row[1])!=0:
380 allAncestors.append(row[1])
381 trackLevel.append(level)
387 return [out, allAncestors,trackLevel]
390 def common_ancestors(IDA, IDB,newLine):
391 out = 'Common ancestors of:' + newLine
393 s = "SELECT name,id FROM people WHERE id==?"
399 for row in run_query(s,t):
400 out = out + name_html(row,newLine)+newLine
407 related = 'No details held on one party'
409 return [out,[],related]
412 a = all_ancestors(IDA,newLine)
413 b = all_ancestors(IDB,newLine)
415 ancestorsB = set(b[1])
416 ancestorsA = set(a[1])
418 common = ancestorsA.intersection(ancestorsB)
419 common = list(common)
426 aLevels.append(a[2][i])
428 bLevels.append(b[2][i])
430 s = "SELECT Name, ID, bornyear"\
433 for i in range(len(common)):
439 s = s+") ORDER BY bornyear;"
443 related = names[0]+' and '+names[1]+' are not related'
444 out = out + newLine + related
445 return [out, common,related]
448 out = out + print_tagged_query('',s,common,newLine)
453 for i in range(len(common)):
454 if aLevels[i] == min(aLevels):
456 if bLevels[i] == min(bLevels):
461 s = "SELECT name, id"\
465 out = out + newLine + 'Most Recent Common Ancestors:' + newLine
468 out = out + print_tagged_query('',s,t,newLine)
470 out = out + 'and' + newLine
472 out = out + parent_level(aLevels[indexA[0]],'parent')
476 out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
481 out = out + print_tagged_query('',s,t,newLine)
483 out = out + 'and' + newLine
485 out = out + parent_level(bLevels[indexB[0]],'parent')
488 out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
491 al = aLevels[indexA[0]]
492 bl = bLevels[indexB[0]]
494 related = relationship(al,bl,names)
495 out = out+newLine + related
497 return [out,common,related]
499 def relationship(level1, level2,names):
501 if level1==0 and level2==0:
502 return names[0] + ' is ' +names[1]
504 return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2,'parent')
506 return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1,'parent')
510 remove = level1-level2
513 remove = level2-level1
517 uaLevel = parent_level(remove,'uncle or aunt')
519 return names[0] + ' is ' + names[1] + '\'s ' + uaLevel
522 return names[1] + ' is ' + names[0] + '\'s ' + uaLevel
524 c=ordinal_numbers(cousinNum)
530 rem = str(remove) + ' times'
532 r = names[0] +' and '+names[1] +' are ' + c + ' cousins '
534 r = r+ rem + ' removed'
538 def parent_level(level,type):
553 for i in range(2,level):
557 def rulers_of(aTerritory,newLine):
559 tq = "SELECT name, people.ID, startyear,stopyear,territory"\
560 +" FROM territories INNER JOIN people"\
561 +" ON people.ID = territories.ID"\
562 +" WHERE territory LIKE ?"\
563 +" ORDER BY territory,startyear,stopyear;"
570 for row in run_query(tq,(aTerritory+'%',)):
571 if row[4]!=last and last!='':
572 out = out + 'Rulers of '+terr_html(last,newLine) +':'+ newLine +thisT +newLine
575 thisT = thisT +name_html(row,newLine)
576 thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
579 out = out + 'Rulers of '+terr_html(row[4],newLine) +':'+ newLine +thisT
583 def person_info(personID,newLine):
588 #Id, Name, Dates, Style, Style-Dates
589 s = "SELECT * FROM people WHERE ID = ?"
590 for row in run_query(s,t):
591 output = output + 'ID: '+str(row[0]) +newLine
592 output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
593 output = output + 'Born: '+row[3] + newLine
595 output = output + 'Died: '+row[5] + newLine
597 s = "SELECT * FROM styles WHERE ID = ?"
598 for row in run_query(s,t):
599 output = output +newLine+ 'Style: '+row[1] + newLine
601 output = output + 'Territories:' + newLine
603 u = "SELECT * FROM territories"\
604 +" WHERE ID =? AND startYear =? AND stopYear=?"
605 v=(personID,row[3],row[5])
608 for r in run_query(u,v):
609 output = output + terr_html(r[1],newLine) +','
612 output = output[:-1] + newLine
614 output = output + 'From: '+row[2] + newLine
615 output = output + 'To: '+row[4] + newLine
617 s = "SELECT people.Name,consort "\
618 +"FROM consorts LEFT JOIN people"\
619 +" ON people.ID = consorts.consort"\
620 +" WHERE consorts.ID = ?"
621 for row in run_query(s,t):
622 output = output + print_tagged_name('Consort',row,newLine)
624 output = output + newLine
626 s = "SELECT people.Name,parents.parentID FROM"\
627 +" parents LEFT JOIN people"\
628 +" ON parents.parentID = people.ID"\
629 +" WHERE parents.ID = ?"
630 for row in run_query(s,t):
631 output = output + print_tagged_name('Parent',row,newLine)
634 s = "SELECT people.NAME, marriages.IDb from"\
635 +" marriages LEFT JOIN people"\
636 +" ON people.ID = marriages.IDb"\
637 +" WHERE marriages.IDa = ?"
638 for row in run_query(s,t):
639 output = output + newLine
640 output = output + print_tagged_name('Spouse',row,newLine)
641 output = output + relationship_html(personID,row[1],newLine)
643 s = "SELECT people.NAME, marriages.IDa from"\
644 +" marriages LEFT JOIN people"\
645 +" ON people.ID = marriages.IDa"\
646 +" WHERE marriages.IDb = ?"
647 for row in run_query(s,t):
648 output = output + newLine
649 output = output + print_tagged_name('Spouse',row,newLine)
650 output = output + relationship_html(personID,row[1],newLine)
652 output = output + newLine
655 s = "Select people.NAME, people.ID ,people.bornYear"\
656 +" FROM people INNER JOIN parents"\
657 +" ON people.ID = parents.ID"\
658 +" WHERE parents.parentID = ?"\
659 +" ORDER BY people.bornYear;"
661 for row in run_query(s,t):
662 output = output + print_tagged_name('Child',row,newLine)
664 #find children's other parent
665 u = "Select people.NAME, parents.parentID FROM"\
666 +" parents LEFT JOIN people"\
667 +" ON people.ID = parents.parentID"\
668 +" WHERE parents.ID = ? AND parents.parentID <> ?"
672 for r in run_query(u,ids):
673 output = output + print_tagged_name('With',r,newLine)
676 if row[2] !=0 and bornYear != 0:
677 age = row[2]-bornYear
678 output = output[:-4] + " at the age of "+str(age) + newLine
680 output = output + newLine
686 conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
697 # [c, conn] = connect()