familyTree/text2SQL.py

   1 #!/usr/bin/python
   2 import findYear
   3
   4 def add_quotes(s):
   5         return '\"'+s+'\"'
   6
   7 def is_number(s):
   8     try:
   9         float(s)
  10         return s
  11     except ValueError:
  12         return add_quotes(s)
  13
  14 def make_insert(table,fields):
  15         s = 'INSERT INTO ' + table + ' VALUES('
  16         for f in fields:
  17                 s = s + str(f) + ','
  18         s = s[:-1]
  19         s = s+');'
  20         return s
  21
  22 f = open('tree','r')
  23
  24 lastline='';
  25 finishedRecord = 0;
  26 hasStyle = 0;
  27 terr = 0;
  28 for line in f:
  29         thisline = line
  30         if thisline[-2:] == '\r\n':
  31                 thisline = thisline[:-2]
  32         if lastline == 'ID:':
  33                 thisID = thisline
  34         if lastline == 'Name:':
  35                 names = thisline.split()
  36                 if len(names)>0:
  37                         notNames = ['Prince','Princess','St']
  38                         if names[0] in notNames:
  39                                 firstName = add_quotes(names[1])
  40                         else:
  41                                 firstName = add_quotes(names[0])
  42                 else:
  43                         firstName = ''
  44                 thisName = add_quotes(thisline)
  45         if lastline == 'Born:':
  46                 yb = findYear.find_year(thisline)
  47                 mb = findYear.find_month(thisline)
  48                 thisBorn =  add_quotes(thisline)
  49         if lastline == 'Died:':
  50                 yd = findYear.find_year(thisline)
  51                 md = findYear.find_month(thisline)
  52                 thisDied =  add_quotes(thisline)
  53                 finishedRecord=1
  54         if lastline == 'URL:':
  55                 url = add_quotes(thisline)
  56         if lastline == 'Picture:':
  57                 picture = add_quotes(thisline)
  58         if lastline == 'Father:':
  59                 a = is_number(thisline)
  60                 s = make_insert('parents',[thisID, a])
  61                 print s
  62         if lastline=='Mother:':
  63                 a=is_number(thisline)
  64                 s = make_insert('parents',[thisID, a])
  65                 print s
  66
  67         if finishedRecord ==1:
  68                 s = make_insert('people',\
  69                 [thisID,thisName,firstName,thisBorn,yb,\
  70                         thisDied,yd,mb,md,url,picture])
  71                 print s
  72                 finishedRecord = 0
  73         if lastline == 'Style:':
  74                 thisStyle =  add_quotes(thisline)
  75                 hasStyle = 1;
  76         if terr ==1:
  77                 if thisline=='':
  78                         terr=0;
  79                 else:
  80                         thisTerr.append(add_quotes(thisline))
  81         if lastline == 'Territories:':
  82                 thisTerr=[add_quotes(thisline)]
  83                 terr = 1;
  84         if hasStyle == 1:
  85                 if lastline=='From:':
  86                         yf = findYear.find_year(thisline)
  87                         thisFrom =  add_quotes(thisline)
  88                 if lastline =='To:':
  89                         yt = findYear.find_year(thisline)
  90                         thisTo =  add_quotes(thisline)
  91                         s = make_insert('styles',[thisID,thisStyle,thisFrom,\
  92                                 yf,thisTo,yt])
  93                         print s
  94
  95                         for terr in thisTerr:
  96                                 s = make_insert('territories',[thisID,terr,\
  97                                         thisFrom,yf,thisTo,yt])
  98
  99                                 print s
 100
 101                         hasStyle = 0
 102
 103         lastline = thisline
 104
 105