import sqlite3
import findYear
+from string import Template
+
+global link_Template
+link_Template= Template("<a href = http://www.chiark.greenend.org.uk/ucgi/~naath/$script>$text</a>")
def run_query(s,t):
c = make_cursor()
except ValueError:
return 0
-def print_relation(relationship,s,t,newLine):
+def print_tagged_query(relationship,s,t,newLine):
mine = ''
for row in run_query(s,t):
- mine = mine + print_none_value(relationship,row,newLine)
+ mine = mine + print_tagged_name(relationship,row,newLine)
return mine
+
def relationship_html(ID,ID2,newLine):
if newLine=='<br>':
relationship = common_ancestors(ID,ID2,newLine)[2]
- url="http://www.chiark.greenend.org.uk/ucgi/~naath/ancestors.py?ID="+ID+"&ID2="\
- +str(ID2)
- return relationship + ':<a href='+url+'> Common Ancestors</a>'+newLine
+ script = "ancestors.py?ID="+str(ID)+"&ID2="+str(ID2)
+ url = link_Template.substitute(script = script,text = "Common ancestors")
+ return relationship + ' '+url + newLine
else:
return ''
def terr_html(terr,newLine):
if newLine=='<br>':
- url = "http://www.chiark.greenend.org.uk/ucgi/~naath/territory.py?terr="
- return '<a href ='+url + terr+'>'+terr+'</a>'
-
+ return link_Template.substitute(script = "territory.py?terr="+terr, text=terr)
else:
return terr
-def row_html(row,html):
- if html == 1:
- url = "http://www.chiark.greenend.org.uk/ucgi/~naath/person.py?ID=" + str(row[1])
- name = row[0]
- return "<a href = "+url+">"+name+"</a>"
+def name_html(row,html):
+ if html=='<br>':
+ html=1
+ elif html=='\n':
+ html=0
+
if row[0] == None:
return row[1]
else:
- return row[0] + "," +str(row[1])
+ if html==1:
+ script = "person.py?ID=" + str(row[1])
+ name = row[0]
+ return link_Template.substitute(script = script, text = name)
+ else:
+ return row[0] + "," +str(row[1])
-def print_none_value(relationship,row,newLine):
+def print_tagged_name(relationship,row,newLine):
if row[0]==None:
out = relationship + " not yet entered: " + row[1]
else:
else:
html=0
if relationship =='':
- out = row_html(row,html) + ' '
+ out = name_html(row,html) + ' '
else:
- out = relationship + ": " + row_html(row,html)
+ out = relationship + ": " + name_html(row,html)
return out + newLine
+def month_numbers(monthN):
+ if monthN == 0:
+ month ='unknown month'
+ elif monthN == 1:
+ month ='January'
+ elif monthN==2:
+ month ='February'
+ elif monthN==3:
+ month ='March'
+ elif monthN==4:
+ month ='April'
+ elif monthN==5:
+ month ='May'
+ elif monthN==6:
+ month ='June'
+ elif monthN==7:
+ month ='July'
+ elif monthN==8:
+ month ='August'
+ elif monthN==9:
+ month ='September'
+ elif monthN==10:
+ month ='October'
+ elif monthN==11:
+ month ='November'
+ elif monthN==12:
+ month ='December'
+ else:
+ month = 'Incorrectly entered month ' + str(monthN)
+ return month
+
+def ordinal_numbers(number):
+ number = int(number)
+ if number % 10==1 and number/10 % 10 !=1:
+ out = str(number) +'st'
+ elif number % 10==2 and number/10 % 10 !=1:
+ out = str(number) +'nd'
+ elif number % 10==3 and number/10 % 10 !=1:
+ out = str(number) +'rd'
+ else:
+ out = str(number) +'th'
+ return out
+
def list_territories(newLine):
s = "SELECT DISTINCT territory"\
+" FROM territories"\
if row[2]/100!=year/100:
century = row[2]/100 + 1
- out = out +newLine+ 'born in ' + str(century)
+ out = out +newLine+ 'born in '
+
+ out = out +ordinal_numbers(century) \
+ + ' century:' + newLine
+
+ out = out + name_html(row,newLine) +newLine
+
+ if row[2] == 0: #unknown year
+
+ t = (row[1],) #person ID
+
+
+ #died
+ u = "SELECT diedyear FROM people WHERE ID = ?;"
+
+ bornAfter = 0
+ for r in run_query(u,t):
+ if r[0] !=0:
+ out = out + "died: "\
+ + str(r[0]) + newLine
+ bornAfter = r[0] -100
+
+ #find children
+ u = "Select people.bornYear from"\
+ +" people INNER JOIN parents"\
+ +" ON people.ID = parents.ID"\
+ +" WHERE parents.parentID = ?"\
+ + " ORDER BY people.bornYear;"
+
+ hadChild=[]
+
+ for r in run_query(u,t):
+ if r[0] != 0:
+ hadChild.append(r[0])
+
+ bornBefore = 0
+ if len(hadChild)!=0:
+ out = out + "had children in: "
+ for c in hadChild:
+ out = out + str(c) + ','
+ out = out[:-1] + newLine
+
+ bornBefore = hadChild[0]-12
+ if bornAfter==0:
+ bornAfter = hadChild[0]-100
+
+ if bornAfter!=0:
+ if bornBefore == 0:
+ out = out + "probably born "\
+ +"after " + str(bornAfter)
+ else:
+ out = out + "probably born "\
+ +"betwen " + str(bornAfter)\
+ +" and " + str(bornBefore)
+ out = out + newLine
- if century==21:
- out = out + 'st'
- elif century==2:
- out = out + 'nd'
- else:
- out = out + 'th'
- out = out + ' century:' + newLine
-
- out = out + print_none_value('',row,newLine)
year = row[2]
return out
return out
+def count_birth_month(newLine):
+ s = "SELECT bornMonth, count(*)"\
+ +" FROM people"\
+ +" GROUP BY bornMonth"\
+ +" ORDER BY bornMonth;"
+
+ t = "SELECT * FROM people WHERE bornMonth = ?;"
+
+ out = ''
+ for row in run_query(s,()):
+ month = month_numbers(row[0])
+ out = out + month + ': ' + str(row[1]) + newLine
+
+ if row[0]>12:
+ u = (row[0],)
+ out = out +print_query(t,u,newLine)
+
+ return out
+
+def count_death_month(newLine):
+ s = "SELECT diedMonth, count(*)"\
+ +" FROM people"\
+ +" GROUP BY diedMonth"\
+ +" ORDER BY diedMonth;"
+
+ t = "SELECT * FROM people WHERE diedMonth = ?;"
+
+ out = ''
+ for row in run_query(s,()):
+ month = month_numbers(row[0])
+ out = out + month + ': ' + str(row[1]) + newLine
+
+ if row[0]>12:
+ u = (row[0],)
+ out = out +print_query(t,u,newLine)
+
+ return out
+
+def count_age_at_child(newLine):
+
+ s = "select p1.bornYear - p2.bornYear as age, count(*)"\
+ +" FROM"\
+ +" parents INNER JOIN people p1"\
+ +" ON parents.ID = p1.ID"\
+ +" INNER JOIN people p2"\
+ +" ON parents.parentID = p2.ID"\
+ +" WHERE p1.bornYear <> 0 and p2.bornYear<>0"\
+ +" GROUP BY age;"
+
+ out = print_query(s,(),newLine)
+ return out
+
def all_ancestors(personID,newLine):
#find parents
s = "SELECT people.Name,parents.parentID FROM"\
out = "Ancestors of "
for row in run_query(t,id):
- out = out + print_none_value('',row,newLine)
+ out = out + name_html(row,newLine)+newLine
while len(ancestors)>0:
level = level+1
newA =[]
- out = out+newLine + parent_level(level) +':' + newLine
+ thisout = newLine + parent_level(level,'parent') +':' + newLine
for ancestor in ancestors:
id = (ancestor,)
for row in run_query(s,id):
- out = out + print_none_value('',row,newLine)
+ thisout = thisout + name_html(row,newLine)+newLine
if row[1] not in allAncestors and is_number(row[1])!=0:
newA.append(row[1])
allAncestors.append(row[1])
trackLevel.append(level)
ancestors = newA
+ if len(ancestors)>0:
+ out = out+thisout
return [out, allAncestors,trackLevel]
for id in (IDA,IDB):
t = (id,)
for row in run_query(s,t):
- out = out + print_none_value('',row,newLine)
+ out = out + name_html(row,newLine)+newLine
names.append(row[0])
if id==IDA:
out = out + 'and'
return [out, common,related]
- out = out + print_relation('',s,common,newLine)
+ out = out + print_tagged_query('',s,common,newLine)
indexA=[]
indexB=[]
out = out + newLine + 'Most Recent Common Ancestors:' + newLine
for a in indexA:
t = (common[a],)
- out = out + print_relation('',s,t,newLine)
+ out = out + print_tagged_query('',s,t,newLine)
if a!=indexA[-1]:
out = out + 'and' + newLine
- out = out + parent_level(aLevels[indexA[0]])
+ out = out + parent_level(aLevels[indexA[0]],'parent')
if len(indexA) >1:
out = out + 's'
- out = out + ' of ' + print_none_value('',[names[0],IDA],newLine)
+ out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
out = out + newLine
for b in indexB:
t = (common[b],)
- out = out + print_relation('',s,t,newLine)
+ out = out + print_tagged_query('',s,t,newLine)
if b!=indexB[-1]:
out = out + 'and' + newLine
- out = out + parent_level(bLevels[indexB[0]])
+ out = out + parent_level(bLevels[indexB[0]],'parent')
if len(indexB)>1:
out = out + 's'
- out = out + ' of ' + print_none_value('',[names[1],IDB],newLine)
+ out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
al = aLevels[indexA[0]]
if level1==0 and level2==0:
return names[0] + ' is ' +names[1]
if level1==0:
- return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2)
+ return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2,'parent')
if level2==0:
- return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1)
+ return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1,'parent')
- if level1==level2:
- remove = 0
- if level1==1:
- return names[0] +' and '+names[1] +' are '+' siblings'
- else:
- cousinNum = level1-1
- if level1>level2:
+ if level1>=level2:
remove = level1-level2
cousinNum = level2-1
else:
cousinNum = level1-1
if cousinNum==0:
- if level1< level2:
- return names[0] + ' is ' + names[1] + '\'s uncle or aunt'
+ uaLevel = parent_level(remove,'uncle or aunt')
+ if level1<= level2:
+ return names[0] + ' is ' + names[1] + '\'s ' + uaLevel
if level2<level1:
- return names[1] + ' is ' + names[0] + '\'s uncle or aunt'
-
- if cousinNum ==1:
- c = '1st'
- elif cousinNum==2:
- c = '2nd'
- elif cousinNum==3:
- c = '3rd'
- else:
- c = str(cousinNum)+'th'
+ return names[1] + ' is ' + names[0] + '\'s ' + uaLevel
+ c=ordinal_numbers(cousinNum)
if remove == 1:
rem = 'once'
elif remove ==2:
return r
-def parent_level(level):
+def parent_level(level,type):
if level == 0:
- return 'self'
- out = 'parent'
+ if type=='parent':
+ return 'self'
+ else:
+ return 'sibling'
+ out = type
if level ==1:
return out
- out = 'grand '+out
+ if type =='parent':
+ out = 'grand '+out
+ else:
+ out = 'great '+out
if level ==2:
return out
for i in range(2,level):
out = out + 'Rulers of '+terr_html(last,newLine) +':'+ newLine +thisT +newLine
thisT = ''
- thisT = thisT +print_none_value('',row,newLine)
- thisT = thisT[:-4] + ' from ' + str(row[2])+' to '+str(row[3]) + newLine
+ thisT = thisT +name_html(row,newLine)
+ thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
last = row[4]
out = out + 'Rulers of '+terr_html(row[4],newLine) +':'+ newLine +thisT
s = "SELECT * FROM people WHERE ID = ?"
for row in run_query(s,t):
output = output + 'ID: '+str(row[0]) +newLine
- output = output + 'Name: '+print_none_value('',[row[1], row[0]],newLine) +newLine
+ output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
output = output + 'Born: '+row[3] + newLine
+ bornYear = row[4]
output = output + 'Died: '+row[5] + newLine
s = "SELECT * FROM styles WHERE ID = ?"
output = output + 'From: '+row[2] + newLine
output = output + 'To: '+row[4] + newLine
+ s = "SELECT people.Name,consort "\
+ +"FROM consorts LEFT JOIN people"\
+ +" ON people.ID = consorts.consort"\
+ +" WHERE consorts.ID = ?"
+ for row in run_query(s,t):
+ output = output + print_tagged_name('Consort',row,newLine)
+
output = output + newLine
#find parents
s = "SELECT people.Name,parents.parentID FROM"\
+" ON parents.parentID = people.ID"\
+" WHERE parents.ID = ?"
for row in run_query(s,t):
- output = output + print_none_value('Parent',row,newLine)
+ output = output + print_tagged_name('Parent',row,newLine)
#find spouses
s = "SELECT people.NAME, marriages.IDb from"\
+" WHERE marriages.IDa = ?"
for row in run_query(s,t):
output = output + newLine
- output = output + print_none_value('Spouse',row,newLine)
+ output = output + print_tagged_name('Spouse',row,newLine)
output = output + relationship_html(personID,row[1],newLine)
s = "SELECT people.NAME, marriages.IDa from"\
+" WHERE marriages.IDb = ?"
for row in run_query(s,t):
output = output + newLine
- output = output + print_none_value('Spouse',row,newLine)
+ output = output + print_tagged_name('Spouse',row,newLine)
output = output + relationship_html(personID,row[1],newLine)
output = output + newLine
#find children
- s = "Select people.NAME, people.ID from"\
- +" people INNER JOIN parents"\
+ s = "Select people.NAME, people.ID ,people.bornYear"\
+ +" FROM people INNER JOIN parents"\
+" ON people.ID = parents.ID"\
- +" WHERE parents.parentID = ?"
+ +" WHERE parents.parentID = ?"\
+ +" ORDER BY people.bornYear;"
for row in run_query(s,t):
- output = output + print_none_value('Child',row,newLine)
+ output = output + print_tagged_name('Child',row,newLine)
#find children's other parent
u = "Select people.NAME, parents.parentID FROM"\
ids = (row[1],t[0])
- for row in run_query(u,ids):
- output = output + print_none_value('With',row,newLine)
+ for r in run_query(u,ids):
+ output = output + print_tagged_name('With',r,newLine)
+
+ #age when child born
+ age = row[2]-bornYear
+ output = output[:-4] + " at the age of "+str(age) + newLine
output = output + newLine
def connect():
global conn
- conn = sqlite3.connect('/home/naath/familyTree/tree.db')
+ conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
return conn
def make_cursor():
~naath / familyTree.git / blobdiff