familyTree/askQuestion.py

   1 #!/usr/bin/python
   2
   3 import sqlite3
   4 import findYear
   5 from string import Template
   6
   7 global link_Template
   8 link_Template= Template("<a href = http://www.chiark.greenend.org.uk/ucgi/~naath/$script>$text</a>")
   9 def run_query(s,t):
  10         c = make_cursor()
  11         return c.execute(s,t)
  12
  13 def print_row(row,newLine):
  14         out = ''
  15         for item in row:
  16                 out = out + str(item)+'|'
  17         return out[:-1] + newLine
  18
  19 def print_query(s,t,newLine):
  20         printMe = ''
  21         for row in run_query(s,t):
  22                 printMe = printMe + print_row(row,newLine)
  23         return printMe
  24
  25 def is_number(s):
  26     try:
  27         float(s)
  28         return 1
  29     except ValueError:
  30         return 0
  31
  32 def print_tagged_query(relationship,s,t,newLine):
  33         mine = ''
  34         for row in run_query(s,t):
  35                 mine = mine + print_tagged_name(relationship,row,newLine)
  36         return mine
  37
  38
  39 def relationship_html(ID,ID2,newLine):
  40         if newLine=='<br>':
  41                 relationship = common_ancestors(ID,ID2,newLine)[2]
  42                 script = "ancestors.py?ID="+str(ID)+"&ID2="+str(ID2)
  43                 url = link_Template.substitute(script = script,text = "Common ancestors")
  44                 return relationship + ' '+url + newLine
  45         else:
  46                 return ''
  47
  48 def terr_html(terr,newLine):
  49         if newLine=='<br>':
  50                 return link_Template.substitute(script = "territory.py?terr="+terr, text=terr)
  51         else:
  52                 return terr
  53 def name_html(row,html):
  54         if html=='<br>':
  55                 html=1
  56         elif html=='\n':
  57                 html=0
  58
  59         if row[0] == None:
  60                 return row[1]
  61         else:
  62                 if html==1:
  63                         script = "person.py?ID=" + str(row[1])
  64                         name = row[0]
  65                         return link_Template.substitute(script = script\
  66                                 , text = name)
  67                 else:
  68                         return row[0] + "," +str(row[1])
  69
  70 def print_people(n):
  71         if n>1:
  72                 return ' people '
  73         else:
  74                 return ' person '
  75
  76 def print_age_child_count(row,newLine):
  77         if newLine == '<br>':
  78                 script = "age.py?age="+str(row[0])
  79                 link = link_Template.substitute(script = \
  80                         script, text = row[0])
  81                 out = str(row[1])+print_people(row[1])
  82
  83                 out = out + 'had children at age '+ link + newLine
  84                 return out
  85         else:
  86                 return print_row(row,newLine)
  87
  88 def print_age_death_count(row,newLine):
  89         if newLine =='<br>':
  90                 script = "ageDeath.py?age="+str(row[0])
  91                 link = link_Template.substitute(script = script,text = row[0])
  92                 out = str(row[1])+print_people(row[1])
  93                 out = out + "died at age " + link + newLine
  94                 return out
  95         else:
  96                 return print_row(row,newLine)
  97
  98 def print_name_count(row,newLine):
  99         if newLine=='<br>':
 100                 script = "name.py?name=" + row[0]
 101                 link = link_Template.substitute(script =\
 102                         script, text = row[0])
 103                 return str(row[1]) + " people called "+link + newLine
 104         else:
 105                 return print_row(row,newLine)
 106
 107 def print_tagged_name(relationship,row,newLine):
 108         if row[0]==None:
 109                 out = relationship + " not yet entered: " + row[1]
 110         else:
 111                 if newLine == '<br>':
 112                         html = 1
 113                 else:
 114                         html=0
 115                 if relationship =='':
 116                         out = name_html(row,html) + '   '
 117                 else:
 118                         out = relationship + ": " + name_html(row,html)
 119         return out + newLine
 120
 121 def month_numbers(monthN):
 122         if monthN == 0:
 123                 month ='unknown month'
 124         elif monthN == 1:
 125                 month ='January'
 126         elif monthN==2:
 127                 month ='February'
 128         elif monthN==3:
 129                 month ='March'
 130         elif monthN==4:
 131                 month ='April'
 132         elif monthN==5:
 133                 month ='May'
 134         elif monthN==6:
 135                 month ='June'
 136         elif monthN==7:
 137                 month ='July'
 138         elif monthN==8:
 139                 month ='August'
 140         elif monthN==9:
 141                 month ='September'
 142         elif monthN==10:
 143                 month ='October'
 144         elif monthN==11:
 145                 month ='November'
 146         elif monthN==12:
 147                 month ='December'
 148         else:
 149                 month = 'Incorrectly entered month ' + str(monthN)
 150         return month
 151
 152 def ordinal_numbers(number):
 153         number = int(number)
 154         if number % 10==1 and number/10 % 10 !=1:
 155                 out = str(number) +'st'
 156         elif number % 10==2 and number/10 % 10 !=1:
 157                 out = str(number) +'nd'
 158         elif number % 10==3 and number/10 % 10 !=1:
 159                 out = str(number) +'rd'
 160         else:
 161                 out = str(number) +'th'
 162         return out
 163
 164 def list_territories(newLine):
 165         s = "SELECT DISTINCT territory"\
 166         +" FROM territories"\
 167         +" ORDER BY territory;"
 168
 169         out = ''
 170         for row in run_query(s,()):
 171                 out =out + terr_html(row[0],newLine) +newLine
 172         return out
 173
 174 def list_people_parents():
 175         s = "SELECT name,id"\
 176                 +" FROM people"\
 177                 +" ORDER BY id;"
 178
 179         output = []
 180         for row in run_query(s,()):
 181                 t = "SELECT parentid"\
 182                         +" FROM parents"\
 183                         +" WHERE id = ?;"
 184
 185                 u = "SELECT name,id"\
 186                         +" FROM people"\
 187                         +" WHERE id = ?";
 188
 189                 parents =[]
 190                 for r in run_query(t,(row[1],)):
 191                         parentID = r[0]
 192                         hasParent = 0
 193                         for q in run_query(u,(r[0],)):
 194                                 parents.append(q[0] + ' ' + str(q[1]))
 195                                 hasParent=1
 196                         if hasParent==0:
 197                                 parents.append(r[0] + ' p' +\
 198                                  str(row[1]))
 199
 200                 spouses=[]
 201                 v = "SELECT name,idb"\
 202                         +" FROM marriages LEFT JOIN people"\
 203                         +" ON idb = id"\
 204                         +" WHERE ida = ?"
 205                 for r in run_query(v,(row[1],)):
 206                         if r[0]!=None:
 207                                 spouses.append(r[0]+ ' '+str(r[1]))
 208                         else:
 209                                 spouses.append(r[1] + ' s' +\
 210                                 str(row[1]))
 211
 212
 213                 myName = row[0]
 214                 myID = str(row[1])
 215                 output.append([myName+ ' '+ myID,parents,spouses])
 216         return output
 217
 218
 219 def list_people(newLine):
 220         s = "SELECT name,id,bornyear"\
 221         +" FROM people"\
 222         +" ORDER BY bornyear;"
 223
 224         out = ''
 225         year = 0
 226         out = out + 'born in unknown year:' +newLine
 227         for row in run_query(s,()):
 228                 if row[2]!=0 and row[2]/100==0:
 229                         out = out +newLine+ 'born in 1st century:' +newLine
 230
 231                 if row[2]/100!=year/100:
 232                         century = row[2]/100 + 1
 233                         out = out +newLine+ 'born in '
 234
 235                         out = out +ordinal_numbers(century) \
 236                                 + ' century:' + newLine
 237
 238                 out = out + name_html(row,newLine) +newLine
 239
 240                 if row[2] == 0: #unknown year
 241
 242                         t = (row[1],) #person ID
 243
 244
 245                         #died
 246                         u = "SELECT diedyear FROM people WHERE ID = ?;"
 247
 248                         bornAfter = 0
 249                         for r in run_query(u,t):
 250                                 if r[0] !=0:
 251                                         out = out + "died: "\
 252                                          + str(r[0]) + newLine
 253                                         bornAfter = r[0] -100
 254
 255                         #find children
 256                         u = "Select people.bornYear from"\
 257                                 +" people INNER JOIN parents"\
 258                                 +" ON people.ID = parents.ID"\
 259                                 +" WHERE parents.parentID = ?"\
 260                                 + " ORDER BY people.bornYear;"
 261
 262                         hadChild=[]
 263
 264                         for r in run_query(u,t):
 265                                 if r[0] != 0:
 266                                         hadChild.append(r[0])
 267
 268                         bornBefore = 0
 269                         if len(hadChild)!=0:
 270                                 out = out + "had children in: "
 271                                 for c in hadChild:
 272                                         out = out + str(c) + ','
 273                                 out = out[:-1] + newLine
 274
 275                                 bornBefore = hadChild[0]-12
 276                                 if bornAfter==0:
 277                                         bornAfter = hadChild[0]-100
 278
 279                         u = "Select styles.startYear, styles.style from"\
 280                                 +" people INNER JOIN styles"\
 281                                 +" ON people.ID = styles.ID"\
 282                                 +" WHERE people.ID = ? and"\
 283                                 +" styles.startYear <>0"\
 284                                 +" ORDER BY styles.startYear;"
 285
 286                         for r in run_query(u,t):
 287                                 out = out + r[1] + " from " + str(r[0])\
 288                                 + newLine
 289                                 if bornAfter ==0:
 290                                         bornAfter = r[0] -100
 291                                 break
 292
 293                         if bornAfter!=0:
 294                                 if bornBefore == 0:
 295                                         out = out + "probably born "\
 296                                                 +"after " + str(bornAfter)
 297                                 else:
 298                                         out = out + "probably born "\
 299                                                 +"betwen " + str(bornAfter)\
 300                                                 +" and " + str(bornBefore)
 301                                 out = out + newLine
 302
 303                 year = row[2]
 304         return out
 305
 306 def count_names(newLine):
 307         s = "SELECT firstName, count(*)"\
 308         +" FROM people"\
 309         +" GROUP BY firstName"\
 310         +" ORDER BY count(*) DESC;"
 311
 312         out = ''
 313         for row in run_query(s,()):
 314                 out = out + print_name_count(row,newLine)
 315
 316         return out
 317
 318 def people_with_name(name,newLine):
 319         s = "SELECT Name, ID"\
 320         +" FROM people"\
 321         +" WHERE Name LIKE ?;"
 322
 323         out = ''
 324
 325         t = (name + '%',)
 326
 327         for row in run_query(s,t):
 328                 out = out + name_html(row,newLine) + newLine
 329
 330         return out
 331
 332 def count_birth_month(newLine):
 333         s = "SELECT bornMonth, count(*)"\
 334                 +" FROM people"\
 335                 +" GROUP BY bornMonth"\
 336                 +" ORDER BY bornMonth;"
 337
 338         t = "SELECT * FROM people WHERE bornMonth = ?;"
 339
 340         out = ''
 341         for row in run_query(s,()):
 342                 month = month_numbers(row[0])
 343                 out = out + month + ': ' + str(row[1]) + newLine
 344
 345                 if row[0]>12:
 346                         u = (row[0],)
 347                         out =  out +print_query(t,u,newLine)
 348
 349         return out
 350
 351 def count_death_month(newLine):
 352         s = "SELECT diedMonth, count(*)"\
 353                 +" FROM people"\
 354                 +" GROUP BY diedMonth"\
 355                 +" ORDER BY diedMonth;"
 356
 357         t = "SELECT * FROM people WHERE diedMonth = ?;"
 358
 359         out = ''
 360         for row in run_query(s,()):
 361                 month = month_numbers(row[0])
 362                 out = out + month + ': ' + str(row[1]) + newLine
 363
 364                 if row[0]>12:
 365                         u = (row[0],)
 366                         out =  out +print_query(t,u,newLine)
 367
 368         return out
 369
 370 def count_age_at_child(newLine):
 371
 372         s = "select p1.bornYear - p2.bornYear as age, count(*)"\
 373                 +" FROM"\
 374                 +" parents INNER JOIN people p1"\
 375                 +" ON parents.ID = p1.ID"\
 376                 +" INNER JOIN people p2"\
 377                 +" ON parents.parentID = p2.ID"\
 378                 +" WHERE p1.bornYear <> 0 and p2.bornYear<>0"\
 379                 +" GROUP BY age;"
 380
 381         out = ''
 382         for row in run_query(s,()):
 383                 out = out + print_age_child_count(row,newLine)
 384
 385         return out
 386
 387 def people_had_child_at_age(age,newLine):
 388
 389         s = "select p1.bornYear - p2.bornYear as age, p1.name, p1.ID"\
 390                 +",p2.name,p2.ID FROM"\
 391                 +" parents INNER JOIN people p1"\
 392                 +" ON parents.ID = p1.ID"\
 393                 +" INNER JOIN people p2"\
 394                 +" ON parents.parentID = p2.ID"\
 395                 +" WHERE age = ? AND p1.bornYear<>0 AND p2.bornYear<>0"
 396
 397         t = (int(age),)
 398
 399         out = ''
 400         out = 'At age ' + str(age) + ' :'
 401         for row in run_query(s,t):
 402                 out = out + newLine
 403                 out =out + name_html([row[3],row[4]],newLine) + ' had '\
 404                         +name_html([row[1],row[2]],newLine)
 405
 406         return out
 407
 408 def count_age_at_death(newLine):
 409         s = "select diedYear-bornYear as age,count(*)"\
 410                 +" FROM people"\
 411                 +" WHERE diedYear<>0 AND bornYear<>0"\
 412                 +" GROUP BY age;"
 413         out=''
 414         for row in run_query(s,()):
 415                 out = out + print_age_death_count(row,newLine)
 416
 417         return out
 418 def people_died_at_age(age,newLine):
 419         s = "SELECT diedYear-bornYear as age, name,ID"\
 420                 +" FROM people"\
 421                 +" WHERE age = ? AND bornYear<>0 AND diedYear<>0;"
 422         t = (int(age),)
 423         out =''
 424         out = 'These people died at age ' +str(age) + ' :'
 425         for row in run_query(s,t):
 426                 out = out +newLine
 427                 out = out + name_html([row[1],row[2]],newLine)
 428         return out
 429
 430 def all_ancestors(personID,newLine):
 431         #find parents
 432         s = "SELECT people.Name,parents.parentID FROM"\
 433                 +" parents LEFT JOIN people"\
 434                 +" ON parents.parentID = people.ID"\
 435                 +" WHERE parents.ID = ?"\
 436                 +" AND parents.parentID <> '.';"
 437
 438
 439         ancestors = [personID]
 440         allAncestors = [personID]
 441         trackLevel = [0]
 442         level = 0
 443
 444         t = "SELECT name,id FROM people WHERE id==?"
 445         id = (personID,)
 446
 447         out = "Ancestors of "
 448         for row in run_query(t,id):
 449                 out = out + name_html(row,newLine)+newLine
 450
 451         while len(ancestors)>0:
 452                 level = level+1
 453                 newA =[]
 454                 thisout = newLine + parent_level(level,'parent') +':' + newLine
 455                 for ancestor in ancestors:
 456                         id = (ancestor,)
 457                         for row in run_query(s,id):
 458                                 thisout = thisout + \
 459                                 name_html(row,newLine)+newLine
 460                                 if row[1] not in allAncestors and \
 461                                 is_number(row[1])!=0:
 462                                         newA.append(row[1])
 463                                         allAncestors.append(row[1])
 464                                         trackLevel.append(level)
 465                 ancestors = newA
 466                 out  = out+thisout
 467
 468
 469         image = "<img src = ancestorGraph.py?id="+str(personID)+">"
 470         out = out+newLine + image+newLine
 471         return [out, allAncestors,trackLevel]
 472
 473
 474 def common_ancestors(IDA, IDB,newLine):
 475         out = 'Common ancestors of:' + newLine
 476
 477         s = "SELECT name,id FROM people WHERE id==?"
 478
 479
 480         names=[]
 481         for id in (IDA,IDB):
 482                 t = (id,)
 483                 for row in run_query(s,t):
 484                         out = out + name_html(row,newLine)+newLine
 485                         names.append(row[0])
 486                 if id==IDA:
 487                         out = out + 'and'
 488                 out = out + newLine
 489
 490         if len(names)!=2:
 491                 related = 'No details held on one party'
 492                 out = out + related
 493                 return [out,[],related]
 494
 495
 496         a = all_ancestors(IDA,newLine)
 497         b = all_ancestors(IDB,newLine)
 498
 499         ancestorsB = set(b[1])
 500         ancestorsA = set(a[1])
 501
 502         common = ancestorsA.intersection(ancestorsB)
 503         common = list(common)
 504
 505
 506         aLevels=[]
 507         bLevels=[]
 508         for c in common:
 509                 i = a[1].index(c)
 510                 aLevels.append(a[2][i])
 511                 i = b[1].index(c)
 512                 bLevels.append(b[2][i])
 513
 514         s = "SELECT Name, ID, bornyear"\
 515         +" FROM people"\
 516         +" WHERE ID IN ("
 517         for i in range(len(common)):
 518                 s = s+ "?,"
 519         if len(common)>0:
 520                 s = s[:-1]
 521
 522
 523         s = s+") ORDER BY bornyear;"
 524
 525
 526         if len(common)==0:
 527                 related = names[0]+' and '+names[1]+' are not related'
 528                 out = out + newLine + related
 529                 return [out, common,related]
 530
 531
 532         out = out + print_tagged_query('',s,common,newLine)
 533
 534         indexA=[]
 535         indexB=[]
 536
 537         for i in range(len(common)):
 538                 if aLevels[i] == min(aLevels):
 539                         indexA.append(i)
 540                 if bLevels[i] == min(bLevels):
 541                         indexB.append(i)
 542
 543
 544
 545         s = "SELECT name, id"\
 546         +" FROM people"\
 547         +" WHERE id=?"
 548
 549         out  = out + newLine + 'Most Recent Common Ancestors:' + newLine
 550         mrca = []
 551         for a in indexA:
 552                 t = (common[a],)
 553                 mrca.append(common[a])
 554                 out = out + print_tagged_query('',s,t,newLine)
 555                 if a!=indexA[-1]:
 556                         out = out + 'and' + newLine
 557
 558         out = out + parent_level(aLevels[indexA[0]],'parent')
 559         if len(indexA) >1:
 560                 out = out + 's'
 561
 562         out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
 563
 564         #out = out + newLine
 565         #for b in indexB:
 566         #       t = (common[b],)
 567         #       out = out + print_tagged_query('',s,t,newLine)
 568         #       if b!=indexB[-1]:
 569         #               out = out + 'and' + newLine
 570
 571         out = out + parent_level(bLevels[indexB[0]],'parent')
 572         if len(indexB)>1:
 573                 out = out + 's'
 574         out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
 575
 576
 577         al = aLevels[indexA[0]]
 578         bl = bLevels[indexB[0]]
 579
 580         related = relationship(al,bl,names)
 581         out = out+newLine + related
 582
 583
 584         image = "<img src = jointAncestorGraph.py?id="+str(IDA)\
 585                 +"&id2="+str(IDB) + "&LA=" + str(min(aLevels)) \
 586                 +"&LB=" + str(min(bLevels))+">"
 587
 588
 589
 590         out = out+newLine + image+newLine
 591
 592         return [out,common,related]
 593
 594 def relationship(level1, level2,names):
 595
 596         if level1==0 and level2==0:
 597                 return names[0] + ' is ' +names[1]
 598         if level1==0:
 599                 return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2,'parent')
 600         if level2==0:
 601                 return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1,'parent')
 602
 603
 604         if level1>=level2:
 605                 remove = level1-level2
 606                 cousinNum = level2-1
 607         else:
 608                 remove = level2-level1
 609                 cousinNum = level1-1
 610
 611         if cousinNum==0:
 612                 uaLevel =  parent_level(remove,'uncle or aunt')
 613                 if level1<= level2:
 614                         return names[0] + ' is ' + names[1] + '\'s ' + uaLevel
 615
 616                 if level2<level1:
 617                         return names[1] + ' is ' + names[0] + '\'s ' + uaLevel
 618
 619         c=ordinal_numbers(cousinNum)
 620         if remove == 1:
 621                 rem = 'once'
 622         elif remove ==2:
 623                 rem = 'twice'
 624         else:
 625                 rem = str(remove) + ' times'
 626
 627         r = names[0] +' and '+names[1] +' are ' + c + ' cousins '
 628         if remove !=0:
 629                 r = r+ rem + ' removed'
 630
 631         return r
 632
 633 def parent_level(level,type):
 634         if level == 0:
 635                 if type=='parent':
 636                         return 'self'
 637                 else:
 638                         return 'sibling'
 639         out = type
 640         if level ==1:
 641                 return out
 642         if type =='parent':
 643                 out = 'grand '+out
 644         else:
 645                 out = 'great '+out
 646         if level ==2:
 647                 return out
 648         for i in range(2,level):
 649                 out = 'great '+out
 650         return out
 651
 652 def rulers_of(aTerritory,newLine):
 653
 654         tq = "SELECT name, people.ID, startyear,stopyear,territory"\
 655                 +" FROM territories INNER JOIN people"\
 656                 +" ON people.ID = territories.ID"\
 657                 +" WHERE territory LIKE ?"\
 658                 +" ORDER BY territory,startyear,stopyear;"
 659
 660
 661
 662         thisT  = ''
 663         last = ''
 664         out = ''
 665         for row in run_query(tq,(aTerritory+'%',)):
 666                 if row[4]!=last and last!='':
 667                         out  = out + 'Rulers of '+terr_html(last,newLine) +':'+ newLine +thisT +newLine
 668                         thisT = ''
 669
 670                 thisT = thisT +name_html(row,newLine)
 671                 thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
 672                 last = row[4]
 673
 674         out  = out + 'Rulers of '+terr_html(row[4],newLine) +':'+ newLine +thisT
 675
 676         return out
 677
 678 def person_info(personID,newLine):
 679         t = (personID,)
 680
 681         output = '';
 682
 683         #Id, Name, Dates, Style, Style-Dates
 684         s = "SELECT * FROM people WHERE ID = ?"
 685         for row in run_query(s,t):
 686                 output = output + 'ID: '+str(row[0]) +newLine
 687                 output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
 688                 name = row[1]
 689                 output = output + 'Born: '+row[3] + newLine
 690                 bornYear = row[4]
 691                 output = output + 'Died: '+row[5] + ", aged " \
 692                         +str(row[6]-row[4]) +newLine
 693
 694         s = "SELECT * FROM styles WHERE ID = ?"
 695         for row in run_query(s,t):
 696                 output = output +newLine+ 'Style: '+row[1] + newLine
 697
 698                 output = output + 'Territories:' + newLine
 699
 700                 u = "SELECT * FROM territories"\
 701                 +"  WHERE ID =? AND startYear =? AND stopYear=?"
 702                 v=(personID,row[3],row[5])
 703
 704                 any = 0
 705                 for r in run_query(u,v):
 706                         output = output + terr_html(r[1],newLine) +','
 707                         any = 1
 708                 if any ==1:
 709                         output = output[:-1] + newLine
 710
 711                 output = output +  'From: '+row[2] + newLine
 712                 output = output +  'To: '+row[4] + newLine
 713
 714         s = "SELECT people.Name,consort "\
 715                 +"FROM consorts LEFT JOIN people"\
 716                 +" ON people.ID = consorts.consort"\
 717                 +" WHERE consorts.ID = ?"
 718         for row in run_query(s,t):
 719                 output = output + print_tagged_name('Consort',row,newLine)
 720
 721         output = output + newLine
 722         #find parents
 723         s = "SELECT people.Name,parents.parentID FROM"\
 724                 +" parents LEFT JOIN people"\
 725                 +" ON parents.parentID = people.ID"\
 726                 +" WHERE parents.ID = ?"
 727
 728         parents =[]
 729         for row in run_query(s,t):
 730                 output = output + print_tagged_name('Parent',row,newLine)
 731                 if row[0]!=None:
 732                         parents.append(row[0] + ' ' + row[1])
 733                 else:
 734                         parents.append(row[1] + ' p' + personID)
 735
 736         #find spouses
 737         s = "SELECT people.NAME, marriages.IDb from"\
 738                 +" marriages LEFT JOIN people"\
 739                 +" ON people.ID = marriages.IDb"\
 740                 +" WHERE marriages.IDa = ?"\
 741                 +" ORDER BY IDb;"
 742         spouses = []
 743         for row in run_query(s,t):
 744                 if row[0]!=None:
 745                         spouses.append(row[0] + ' '+str(row[1]))
 746                 else:
 747                         spouses.append(row[1] + ' s' + personID)
 748                 output = output + newLine
 749                 output = output + print_tagged_name('Spouse',row,newLine)
 750                 output = output + relationship_html(personID,row[1],newLine)
 751
 752         s = "SELECT people.NAME, marriages.IDa from"\
 753                 +" marriages LEFT JOIN people"\
 754                 +" ON people.ID = marriages.IDa"\
 755                 +" WHERE marriages.IDb = ?"\
 756                 +" ORDER BY IDa;"
 757         for row in run_query(s,t):
 758                 if row[0]!=None:
 759                         spouses.append(row[0] + ' '+str(row[1]))
 760                 else:
 761                         spouses.append(row[1] + ' s' + personID)
 762                 output = output + newLine
 763                 output = output + print_tagged_name('Spouse',row,newLine)
 764                 output = output + relationship_html(personID,row[1],newLine)
 765
 766         output = output + newLine
 767
 768         #find children
 769         s = "Select people.NAME, people.ID ,people.bornYear"\
 770                 +" FROM people INNER JOIN parents"\
 771                 +" ON people.ID = parents.ID"\
 772                 +" WHERE parents.parentID = ?"\
 773                 +" ORDER BY people.bornYear;"
 774
 775         children = []
 776         ops =[]
 777         for row in run_query(s,t):
 778                 output = output  + print_tagged_name('Child',row,newLine)
 779                 children.append(row[0] + ' ' + str(row[1]))
 780
 781                  #find children's other parent
 782                 u = "Select people.NAME, parents.parentID FROM"\
 783                 +" parents INNER JOIN people"\
 784                 +" ON people.ID = parents.parentID"\
 785                 +" WHERE parents.ID = ? AND parents.parentID <>?;"
 786
 787                 ids = (row[1],t[0])
 788
 789                 op = 0
 790                 for r in run_query(u,ids):
 791                         output = output + print_tagged_name('With',r,newLine)
 792                         op = 1
 793                         ops.append(r[0] + ' ' + str(r[1]))
 794                 if op==0:
 795                         ops.append('?' + ' s' + personID)
 796
 797                 #age when child born
 798                 if row[2] !=0 and bornYear != 0:
 799                         age = row[2]-bornYear
 800                         output = output[:-4] + " at the age of "+str(age) + newLine
 801
 802
 803         Self = name +' ' + str(personID)
 804
 805         image =  "smallGraph.py?Self="+Self
 806         for p in parents:
 807                 image = image + '&p='+p
 808         for c in children:
 809                 image = image + '&c='+c
 810         for op in ops:
 811                 if op !=None:
 812                         image = image + '&op='+op
 813         for s in spouses:
 814                 if s !=None:
 815                         image = image + '&s='+str(s)
 816
 817         image = image.replace(' ','%20')
 818         image ="<img src ="+ image + '>'
 819
 820
 821         output = newLine+ output+ image+newLine
 822
 823
 824
 825         return output
 826
 827 def connect():
 828         global conn
 829         conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
 830         return conn
 831
 832 def make_cursor():
 833         return conn.cursor()
 834
 835 def close(conn):
 836         conn.close
 837
 838 #def main():
 839
 840 #       [c, conn] = connect()
 841 #
 842 #       person_info(1,c)
 843 #       person_info(17,c)
 844 #       person_info(38,c)
 845 #       person_info(90,c)
 846 #
 847 #       close(conn)