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, text = name)
68 return row[0] + "," +str(row[1])
70 def print_tagged_name(relationship,row,newLine):
72 out = relationship + " not yet entered: " + row[1]
79 out = name_html(row,html) + ' '
81 out = relationship + ": " + name_html(row,html)
84 def month_numbers(monthN):
86 month ='unknown month'
112 month = 'Incorrectly entered month ' + str(monthN)
115 def ordinal_numbers(number):
117 if number % 10==1 and number/10 % 10 !=1:
118 out = str(number) +'st'
119 elif number % 10==2 and number/10 % 10 !=1:
120 out = str(number) +'nd'
121 elif number % 10==3 and number/10 % 10 !=1:
122 out = str(number) +'rd'
124 out = str(number) +'th'
127 def list_territories(newLine):
128 s = "SELECT DISTINCT territory"\
129 +" FROM territories"\
130 +" ORDER BY territory;"
133 for row in run_query(s,()):
134 out =out + terr_html(row[0],newLine) +newLine
137 def list_people(newLine):
138 s = "SELECT name,id,bornyear"\
140 +" ORDER BY bornyear;"
144 out = out + 'born in unknown year:' +newLine
145 for row in run_query(s,()):
146 if row[2]!=0 and row[2]/100==0:
147 out = out +newLine+ 'born in 1st century:' +newLine
149 if row[2]/100!=year/100:
150 century = row[2]/100 + 1
151 out = out +newLine+ 'born in '
153 out = out +ordinal_numbers(century) \
154 + ' century:' + newLine
156 out = out + name_html(row,newLine) +newLine
158 if row[2] == 0: #unknown year
160 t = (row[1],) #person ID
164 u = "SELECT diedyear FROM people WHERE ID = ?;"
167 for r in run_query(u,t):
169 out = out + "died: "\
170 + str(r[0]) + newLine
171 bornAfter = r[0] -100
174 u = "Select people.bornYear from"\
175 +" people INNER JOIN parents"\
176 +" ON people.ID = parents.ID"\
177 +" WHERE parents.parentID = ?"\
178 + " ORDER BY people.bornYear;"
182 for r in run_query(u,t):
184 hadChild.append(r[0])
188 out = out + "had children in: "
190 out = out + str(c) + ','
191 out = out[:-1] + newLine
193 bornBefore = hadChild[0]-12
195 bornAfter = hadChild[0]-100
199 out = out + "probably born "\
200 +"after " + str(bornAfter)
202 out = out + "probably born "\
203 +"betwen " + str(bornAfter)\
204 +" and " + str(bornBefore)
211 def count_names(newLine):
212 s = "SELECT firstName, count(*)"\
214 +" GROUP BY firstName"\
215 +" ORDER BY count(*) DESC;"
217 out = print_query(s,(),newLine)
222 def count_birth_month(newLine):
223 s = "SELECT bornMonth, count(*)"\
225 +" GROUP BY bornMonth"\
226 +" ORDER BY bornMonth;"
228 t = "SELECT * FROM people WHERE bornMonth = ?;"
231 for row in run_query(s,()):
232 month = month_numbers(row[0])
233 out = out + month + ': ' + str(row[1]) + newLine
237 out = out +print_query(t,u,newLine)
241 def count_death_month(newLine):
242 s = "SELECT diedMonth, count(*)"\
244 +" GROUP BY diedMonth"\
245 +" ORDER BY diedMonth;"
247 t = "SELECT * FROM people WHERE diedMonth = ?;"
250 for row in run_query(s,()):
251 month = month_numbers(row[0])
252 out = out + month + ': ' + str(row[1]) + newLine
256 out = out +print_query(t,u,newLine)
261 def all_ancestors(personID,newLine):
263 s = "SELECT people.Name,parents.parentID FROM"\
264 +" parents LEFT JOIN people"\
265 +" ON parents.parentID = people.ID"\
266 +" WHERE parents.ID = ?"\
267 +" AND parents.parentID <> '.';"
270 ancestors = [personID]
271 allAncestors = [personID]
275 t = "SELECT name,id FROM people WHERE id==?"
278 out = "Ancestors of "
279 for row in run_query(t,id):
280 out = out + name_html(row,newLine)+newLine
282 while len(ancestors)>0:
285 thisout = newLine + parent_level(level,'parent') +':' + newLine
286 for ancestor in ancestors:
288 for row in run_query(s,id):
289 thisout = thisout + name_html(row,newLine)+newLine
290 if row[1] not in allAncestors and is_number(row[1])!=0:
292 allAncestors.append(row[1])
293 trackLevel.append(level)
299 return [out, allAncestors,trackLevel]
302 def common_ancestors(IDA, IDB,newLine):
303 out = 'Common ancestors of:' + newLine
305 s = "SELECT name,id FROM people WHERE id==?"
311 for row in run_query(s,t):
312 out = out + name_html(row,newLine)+newLine
319 related = 'No details held on one party'
321 return [out,[],related]
324 a = all_ancestors(IDA,newLine)
325 b = all_ancestors(IDB,newLine)
327 ancestorsB = set(b[1])
328 ancestorsA = set(a[1])
330 common = ancestorsA.intersection(ancestorsB)
331 common = list(common)
338 aLevels.append(a[2][i])
340 bLevels.append(b[2][i])
342 s = "SELECT Name, ID, bornyear"\
345 for i in range(len(common)):
351 s = s+") ORDER BY bornyear;"
355 related = names[0]+' and '+names[1]+' are not related'
356 out = out + newLine + related
357 return [out, common,related]
360 out = out + print_tagged_query('',s,common,newLine)
365 for i in range(len(common)):
366 if aLevels[i] == min(aLevels):
368 if bLevels[i] == min(bLevels):
373 s = "SELECT name, id"\
377 out = out + newLine + 'Most Recent Common Ancestors:' + newLine
380 out = out + print_tagged_query('',s,t,newLine)
382 out = out + 'and' + newLine
384 out = out + parent_level(aLevels[indexA[0]],'parent')
388 out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
393 out = out + print_tagged_query('',s,t,newLine)
395 out = out + 'and' + newLine
397 out = out + parent_level(bLevels[indexB[0]],'parent')
400 out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
403 al = aLevels[indexA[0]]
404 bl = bLevels[indexB[0]]
406 related = relationship(al,bl,names)
407 out = out+newLine + related
409 return [out,common,related]
411 def relationship(level1, level2,names):
413 if level1==0 and level2==0:
414 return names[0] + ' is ' +names[1]
416 return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2,'parent')
418 return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1,'parent')
422 remove = level1-level2
425 remove = level2-level1
429 uaLevel = parent_level(remove,'uncle or aunt')
431 return names[0] + ' is ' + names[1] + '\'s ' + uaLevel
434 return names[1] + ' is ' + names[0] + '\'s ' + uaLevel
436 c=ordinal_numbers(cousinNum)
442 rem = str(remove) + ' times'
444 r = names[0] +' and '+names[1] +' are ' + c + ' cousins '
446 r = r+ rem + ' removed'
450 def parent_level(level,type):
465 for i in range(2,level):
469 def rulers_of(aTerritory,newLine):
471 tq = "SELECT name, people.ID, startyear,stopyear,territory"\
472 +" FROM territories INNER JOIN people"\
473 +" ON people.ID = territories.ID"\
474 +" WHERE territory LIKE ?"\
475 +" ORDER BY territory,startyear,stopyear;"
482 for row in run_query(tq,(aTerritory+'%',)):
483 if row[4]!=last and last!='':
484 out = out + 'Rulers of '+terr_html(last,newLine) +':'+ newLine +thisT +newLine
487 thisT = thisT +name_html(row,newLine)
488 thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
491 out = out + 'Rulers of '+terr_html(row[4],newLine) +':'+ newLine +thisT
495 def person_info(personID,newLine):
500 #Id, Name, Dates, Style, Style-Dates
501 s = "SELECT * FROM people WHERE ID = ?"
502 for row in run_query(s,t):
503 output = output + 'ID: '+str(row[0]) +newLine
504 output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
505 output = output + 'Born: '+row[3] + newLine
507 output = output + 'Died: '+row[5] + newLine
509 s = "SELECT * FROM styles WHERE ID = ?"
510 for row in run_query(s,t):
511 output = output +newLine+ 'Style: '+row[1] + newLine
513 output = output + 'Territories:' + newLine
515 u = "SELECT * FROM territories"\
516 +" WHERE ID =? AND startYear =? AND stopYear=?"
517 v=(personID,row[3],row[5])
520 for r in run_query(u,v):
521 output = output + terr_html(r[1],newLine) +','
524 output = output[:-1] + newLine
526 output = output + 'From: '+row[2] + newLine
527 output = output + 'To: '+row[4] + newLine
529 s = "SELECT people.Name,consort "\
530 +"FROM consorts LEFT JOIN people"\
531 +" ON people.ID = consorts.consort"\
532 +" WHERE consorts.ID = ?"
533 for row in run_query(s,t):
534 output = output + print_tagged_name('Consort',row,newLine)
536 output = output + newLine
538 s = "SELECT people.Name,parents.parentID FROM"\
539 +" parents LEFT JOIN people"\
540 +" ON parents.parentID = people.ID"\
541 +" WHERE parents.ID = ?"
542 for row in run_query(s,t):
543 output = output + print_tagged_name('Parent',row,newLine)
546 s = "SELECT people.NAME, marriages.IDb from"\
547 +" marriages LEFT JOIN people"\
548 +" ON people.ID = marriages.IDb"\
549 +" WHERE marriages.IDa = ?"
550 for row in run_query(s,t):
551 output = output + newLine
552 output = output + print_tagged_name('Spouse',row,newLine)
553 output = output + relationship_html(personID,row[1],newLine)
555 s = "SELECT people.NAME, marriages.IDa from"\
556 +" marriages LEFT JOIN people"\
557 +" ON people.ID = marriages.IDa"\
558 +" WHERE marriages.IDb = ?"
559 for row in run_query(s,t):
560 output = output + newLine
561 output = output + print_tagged_name('Spouse',row,newLine)
562 output = output + relationship_html(personID,row[1],newLine)
564 output = output + newLine
567 s = "Select people.NAME, people.ID ,people.bornYear"\
568 +" FROM people INNER JOIN parents"\
569 +" ON people.ID = parents.ID"\
570 +" WHERE parents.parentID = ?"\
571 +" ORDER BY people.bornYear;"
573 for row in run_query(s,t):
574 output = output + print_tagged_name('Child',row,newLine)
576 #find children's other parent
577 u = "Select people.NAME, parents.parentID FROM"\
578 +" parents LEFT JOIN people"\
579 +" ON people.ID = parents.parentID"\
580 +" WHERE parents.ID = ? AND parents.parentID <> ?"
584 for r in run_query(u,ids):
585 output = output + print_tagged_name('With',r,newLine)
588 age = row[2]-bornYear
589 output = output[:-4] + " at the age of "+str(age) + newLine
591 output = output + newLine
597 conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
608 # [c, conn] = connect()