html=1
elif html=='\n':
html=0
- if html == 1:
- script = "person.py?ID=" + str(row[1])
- name = row[0]
- return link_Template.substitute(script = script, text = name)
+
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_tagged_name(relationship,row,newLine):
if row[0]==None:
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)
century = row[2]/100 + 1
out = out +newLine+ 'born in '
- out = out +ordinal_numbers(century) + ' century:' + newLine
+ 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
+
+
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"\
while len(ancestors)>0:
level = level+1
newA =[]
- out = out+newLine + parent_level(level,'parent') +':' + newLine
+ thisout = newLine + parent_level(level,'parent') +':' + newLine
for ancestor in ancestors:
id = (ancestor,)
for row in run_query(s,id):
- out = out + name_html(row,newLine)+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]
output = output + 'ID: '+str(row[0]) +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"\
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_tagged_name('Child',row,newLine)
ids = (row[1],t[0])
- for row in run_query(u,ids):
- output = output + print_tagged_name('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