10 def print_row(row,newLine):
13 out = out + str(item)+'|'
14 return out[:-1] + newLine
16 def print_query(s,t,newLine):
18 for row in run_query(s,t):
19 printMe = printMe + print_row(row,newLine)
29 def print_tagged_query(relationship,s,t,newLine):
31 for row in run_query(s,t):
32 mine = mine + print_tagged_name(relationship,row,newLine)
36 return "http://www.chiark.greenend.org.uk/ucgi/~naath/"
38 def relationship_html(ID,ID2,newLine):
40 relationship = common_ancestors(ID,ID2,newLine)[2]
41 url=base_url()+"ancestors.py?ID="+ID+"&ID2="\
43 return relationship + ':<a href='+url+'> Common Ancestors</a>'+newLine
47 def terr_html(terr,newLine):
49 url = base_url()+"territory.py?terr="
50 return '<a href ='+url + terr+'>'+terr+'</a>'
54 def name_html(row,html):
60 url = base_url()+"person.py?ID=" + str(row[1])
62 return "<a href = "+url+">"+str(name)+"</a>"
66 return row[0] + "," +str(row[1])
68 def print_tagged_name(relationship,row,newLine):
70 out = relationship + " not yet entered: " + row[1]
77 out = name_html(row,html) + ' '
79 out = relationship + ": " + name_html(row,html)
82 def list_territories(newLine):
83 s = "SELECT DISTINCT territory"\
85 +" ORDER BY territory;"
88 for row in run_query(s,()):
89 out =out + terr_html(row[0],newLine) +newLine
92 def list_people(newLine):
93 s = "SELECT name,id,bornyear"\
95 +" ORDER BY bornyear;"
99 out = out + 'born in unknown year:' +newLine
100 for row in run_query(s,()):
101 if row[2]!=0 and row[2]/100==0:
102 out = out +newLine+ 'born in 1st century:' +newLine
104 if row[2]/100!=year/100:
105 century = row[2]/100 + 1
106 out = out +newLine+ 'born in ' + str(century)
115 out = out + ' century:' + newLine
117 out = out + name_html(row,newLine) +newLine
121 def count_names(newLine):
122 s = "SELECT firstName, count(*)"\
124 +" GROUP BY firstName"\
125 +" ORDER BY count(*) DESC;"
127 out = print_query(s,(),newLine)
132 def all_ancestors(personID,newLine):
134 s = "SELECT people.Name,parents.parentID FROM"\
135 +" parents LEFT JOIN people"\
136 +" ON parents.parentID = people.ID"\
137 +" WHERE parents.ID = ?"\
138 +" AND parents.parentID <> '.';"
141 ancestors = [personID]
142 allAncestors = [personID]
146 t = "SELECT name,id FROM people WHERE id==?"
149 out = "Ancestors of "
150 for row in run_query(t,id):
151 out = out + name_html(row,newLine)+newLine
153 while len(ancestors)>0:
156 out = out+newLine + parent_level(level) +':' + newLine
157 for ancestor in ancestors:
159 for row in run_query(s,id):
160 out = out + name_html(row,newLine)+newLine
161 if row[1] not in allAncestors and is_number(row[1])!=0:
163 allAncestors.append(row[1])
164 trackLevel.append(level)
168 return [out, allAncestors,trackLevel]
171 def common_ancestors(IDA, IDB,newLine):
172 out = 'Common ancestors of:' + newLine
174 s = "SELECT name,id FROM people WHERE id==?"
180 for row in run_query(s,t):
181 out = out + name_html(row,newLine)+newLine
188 related = 'No details held on one party'
190 return [out,[],related]
193 a = all_ancestors(IDA,newLine)
194 b = all_ancestors(IDB,newLine)
196 ancestorsB = set(b[1])
197 ancestorsA = set(a[1])
199 common = ancestorsA.intersection(ancestorsB)
200 common = list(common)
207 aLevels.append(a[2][i])
209 bLevels.append(b[2][i])
211 s = "SELECT Name, ID, bornyear"\
214 for i in range(len(common)):
220 s = s+") ORDER BY bornyear;"
224 related = names[0]+' and '+names[1]+' are not related'
225 out = out + newLine + related
226 return [out, common,related]
229 out = out + print_tagged_query('',s,common,newLine)
234 for i in range(len(common)):
235 if aLevels[i] == min(aLevels):
237 if bLevels[i] == min(bLevels):
242 s = "SELECT name, id"\
246 out = out + newLine + 'Most Recent Common Ancestors:' + newLine
249 out = out + print_tagged_query('',s,t,newLine)
251 out = out + 'and' + newLine
253 out = out + parent_level(aLevels[indexA[0]])
257 out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
262 out = out + print_tagged_query('',s,t,newLine)
264 out = out + 'and' + newLine
266 out = out + parent_level(bLevels[indexB[0]])
269 out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
272 al = aLevels[indexA[0]]
273 bl = bLevels[indexB[0]]
275 related = relationship(al,bl,names)
276 out = out+newLine + related
278 return [out,common,related]
280 def relationship(level1, level2,names):
282 if level1==0 and level2==0:
283 return names[0] + ' is ' +names[1]
285 return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2)
287 return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1)
292 return names[0] +' and '+names[1] +' are '+' siblings'
297 remove = level1-level2
300 remove = level2-level1
305 return names[0] + ' is ' + names[1] + '\'s uncle or aunt'
308 return names[1] + ' is ' + names[0] + '\'s uncle or aunt'
317 c = str(cousinNum)+'th'
324 rem = str(remove) + ' times'
326 r = names[0] +' and '+names[1] +' are ' + c + ' cousins '
328 r = r+ rem + ' removed'
332 def parent_level(level):
341 for i in range(2,level):
345 def rulers_of(aTerritory,newLine):
347 tq = "SELECT name, people.ID, startyear,stopyear,territory"\
348 +" FROM territories INNER JOIN people"\
349 +" ON people.ID = territories.ID"\
350 +" WHERE territory LIKE ?"\
351 +" ORDER BY territory,startyear,stopyear;"
358 for row in run_query(tq,(aTerritory+'%',)):
359 if row[4]!=last and last!='':
360 out = out + 'Rulers of '+terr_html(last,newLine) +':'+ newLine +thisT +newLine
363 thisT = thisT +name_html(row,newLine)
364 thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
367 out = out + 'Rulers of '+terr_html(row[4],newLine) +':'+ newLine +thisT
371 def person_info(personID,newLine):
376 #Id, Name, Dates, Style, Style-Dates
377 s = "SELECT * FROM people WHERE ID = ?"
378 for row in run_query(s,t):
379 output = output + 'ID: '+str(row[0]) +newLine
380 output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
381 output = output + 'Born: '+row[3] + newLine
382 output = output + 'Died: '+row[5] + newLine
384 s = "SELECT * FROM styles WHERE ID = ?"
385 for row in run_query(s,t):
386 output = output +newLine+ 'Style: '+row[1] + newLine
388 output = output + 'Territories:' + newLine
390 u = "SELECT * FROM territories"\
391 +" WHERE ID =? AND startYear =? AND stopYear=?"
392 v=(personID,row[3],row[5])
395 for r in run_query(u,v):
396 output = output + terr_html(r[1],newLine) +','
399 output = output[:-1] + newLine
401 output = output + 'From: '+row[2] + newLine
402 output = output + 'To: '+row[4] + newLine
404 output = output + newLine
406 s = "SELECT people.Name,parents.parentID FROM"\
407 +" parents LEFT JOIN people"\
408 +" ON parents.parentID = people.ID"\
409 +" WHERE parents.ID = ?"
410 for row in run_query(s,t):
411 output = output + print_tagged_name('Parent',row,newLine)
414 s = "SELECT people.NAME, marriages.IDb from"\
415 +" marriages LEFT JOIN people"\
416 +" ON people.ID = marriages.IDb"\
417 +" WHERE marriages.IDa = ?"
418 for row in run_query(s,t):
419 output = output + newLine
420 output = output + print_tagged_name('Spouse',row,newLine)
421 output = output + relationship_html(personID,row[1],newLine)
423 s = "SELECT people.NAME, marriages.IDa from"\
424 +" marriages LEFT JOIN people"\
425 +" ON people.ID = marriages.IDa"\
426 +" WHERE marriages.IDb = ?"
427 for row in run_query(s,t):
428 output = output + newLine
429 output = output + print_tagged_name('Spouse',row,newLine)
430 output = output + relationship_html(personID,row[1],newLine)
432 output = output + newLine
435 s = "Select people.NAME, people.ID from"\
436 +" people INNER JOIN parents"\
437 +" ON people.ID = parents.ID"\
438 +" WHERE parents.parentID = ?"
440 for row in run_query(s,t):
441 output = output + print_tagged_name('Child',row,newLine)
443 #find children's other parent
444 u = "Select people.NAME, parents.parentID FROM"\
445 +" parents LEFT JOIN people"\
446 +" ON people.ID = parents.parentID"\
447 +" WHERE parents.ID = ? AND parents.parentID <> ?"
451 for row in run_query(u,ids):
452 output = output + print_tagged_name('With',row,newLine)
454 output = output + newLine
460 conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
471 # [c, conn] = connect()