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                         firstName = add_quotes(names[0])
  38                 else:
  39                         firstName = ''
  40                 thisName = add_quotes(thisline)
  41         if lastline == 'Born:':
  42                 yb = findYear.find_year(thisline)
  43                 thisBorn =  add_quotes(thisline)
  44         if lastline == 'Died:':
  45                 yd = findYear.find_year(thisline)
  46                 thisDied =  add_quotes(thisline)
  47                 finishedRecord = 1
  48         if lastline == 'Father:':
  49                 a = is_number(thisline)
  50                 s = make_insert('parents',[thisID, a])
  51                 print s
  52         if lastline=='Mother:':
  53                 a=is_number(thisline)
  54                 s = make_insert('parents',[thisID, a])
  55                 print s
  56
  57         if finishedRecord ==1:
  58                 s = make_insert('people',\
  59                 [thisID,thisName,firstName,thisBorn,yb,thisDied,yd])
  60                 print s
  61                 finishedRecord = 0
  62         if lastline == 'Style:':
  63                 thisStyle =  add_quotes(thisline)
  64                 hasStyle = 1;
  65         if terr ==1:
  66                 if thisline=='':
  67                         terr=0;
  68                 else:
  69                         thisTerr.append(add_quotes(thisline))
  70         if lastline == 'Territories:':
  71                 thisTerr=[add_quotes(thisline)]
  72                 terr = 1;
  73         if hasStyle == 1:
  74                 if lastline=='From:':
  75                         yf = findYear.find_year(thisline)
  76                         thisFrom =  add_quotes(thisline)
  77                 if lastline =='To:':
  78                         yt = findYear.find_year(thisline)
  79                         thisTo =  add_quotes(thisline)
  80                         s = make_insert('styles',[thisID,thisStyle,thisFrom,\
  81                                 yf,thisTo,yt])
  82                         print s
  83
  84                         for terr in thisTerr:
  85                                 s = make_insert('territories',[thisID,terr,\
  86                                         thisFrom,yf,thisTo,yt])
  87
  88                                 print s
  89
  90                         hasStyle = 0
  91
  92         lastline = thisline
  93
  94