statistics/add-google-record.py

   1 #!/usr/bin/python
   2
   3 import gdata.spreadsheet.text_db
   4 import gdata.spreadsheet.service
   5 import simplejson
   6 import time
   7 import sys
   8
   9 def get_key_for_dbname(gd_client, dbname):
  10     feed = gd_client.GetSpreadsheetsFeed()
  11     for i, entry in enumerate(feed.entry):
  12         if entry.title.text == dbname:
  13             # get key for this db.
  14             return feed.entry[i].id.text.rsplit('/', 1)[1]
  15     return None
  16
  17 def get_worksheet_for_key(gd_client, key, sheetname):
  18     feed = gd_client.GetWorksheetsFeed(key)
  19     for i, entry in enumerate(feed.entry):
  20         if entry.title.text == sheetname:
  21             # get id for worksheet
  22             return feed.entry[i].id.text.rsplit('/', 1)[1]
  23     return None
  24
  25
  26 def get_row_for_update(gd_client, key, wksht_id, date):
  27     feed = gd_client.GetListFeed(key, wksht_id)
  28     for i, entry in enumerate(feed.entry):
  29         #print entry.custom['date'].text
  30         if date in entry.custom['date'].text:
  31             e = {}
  32             for k in entry.custom: e[k] = entry.custom[k].text
  33             return (i, e)
  34     return (0, {})
  35
  36
  37 def update_row(gd_client, key, wksht_id, index, row_data):
  38     feed = gd_client.GetListFeed(key, wksht_id)
  39     entry = gd_client.UpdateRow( feed.entry[index], row_data)
  40     if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
  41         print 'Updated!'
  42
  43
  44 def get_db(client, name):
  45     db_list = client.GetDatabases(name=name)
  46     if len(db_list) == 0:
  47         db = client.CreateDatabase(name)
  48     else:
  49         db = db_list[0]
  50     return db
  51
  52 def get_table(db, table_name, type_list=None):
  53     try:
  54         table_list = db.GetTables(name=table_name)
  55     except:
  56         table_list = []
  57
  58     if len(table_list) == 0:
  59         table = db.CreateTable(table_name, type_list)
  60     else:
  61         table = table_list[0]
  62     return table
  63
  64
  65 def add_record(table, data):
  66     row = table.AddRecord(data)
  67     return row
  68
  69 def main():
  70     from optparse import OptionParser
  71     parser = OptionParser()
  72
  73     parser.set_defaults(database="MonitorStats",
  74                         sheet="",
  75                         labels="date,good,offline,down,online,disabled,failboot,safeboot",
  76                         values=None,
  77                         valuelist=None,
  78                         update=None,
  79                         email=None,
  80                         password=None,
  81                         prefix="",
  82                         create=False)
  83     parser.add_option("", "--email", dest="email", help="")
  84     parser.add_option("", "--password", dest="password", help="")
  85     parser.add_option("", "--database", dest="database", help="")
  86     parser.add_option("", "--create", dest="create", action="store_true", help="")
  87     parser.add_option("", "--sheet",  dest="sheet", help="")
  88     parser.add_option("", "--labels", dest="labels", help="")
  89     parser.add_option("", "--values", dest="values", help="")
  90     parser.add_option("", "--valuelist", dest="valuelist", help="")
  91     parser.add_option("", "--update", dest="update", help="")
  92     parser.add_option("", "--prefix", dest="prefix", help="add a prefix to numeric headers")
  93
  94     (config, args) = parser.parse_args()
  95     if len(sys.argv) == 1:
  96         parser.print_help()
  97         sys.exit(1)
  98
  99     email = config.email
 100     password = config.password
 101     client = gdata.spreadsheet.text_db.DatabaseClient(email, password)
 102     gd_client = gdata.spreadsheet.service.SpreadsheetsService() # text_db.DatabaseClient(email, password)
 103     gd_client.email = email
 104     gd_client.password = password
 105     gd_client.source = "add-record-1"
 106     gd_client.ProgrammaticLogin()
 107
 108     if config.labels:
 109         config.labels = config.labels.split(',')
 110     config.labels = [config.labels[0] ] + [ config.prefix + l for l in config.labels[1:] ]
 111
 112     data_list = []
 113     if config.values:
 114         config.values = config.values.replace("+", " ")
 115         config.values = config.values.split(',')
 116         data_list = [dict(zip(config.labels, config.values))]
 117         print data_list
 118
 119     elif config.valuelist:
 120         vl_file = open(config.valuelist, 'r')
 121         for line in vl_file:
 122             line = line.strip()
 123             values = line.split(',')
 124             data_list.append(dict(zip(config.labels, values)))
 125
 126     if config.update:
 127         key = get_key_for_dbname(gd_client, config.database)
 128         wksht_id = get_worksheet_for_key(gd_client, key, config.sheet)
 129         (index, row_data) = get_row_for_update(gd_client, key, wksht_id, config.update)
 130         if index == 0 : print "failed to find row!"; sys.exit(1)
 131         # update values from command-line over existing values from row
 132         row_data.update( dict(zip(config.labels, config.values)) )
 133         update_row(gd_client, key, wksht_id, index, row_data)
 134
 135     else:
 136         db = get_db(client, config.database)
 137         table = get_table(db, config.sheet, config.labels)
 138         for data in data_list:
 139             print "Adding data: %s" % data
 140             add_record(table, data)
 141     #else:
 142     #    obj = simplejson.load(sys.stdin)
 143     #    print obj
 144     #    for o in obj:
 145     #        sheet = o['sheet']
 146     #        del o['sheet']
 147     #        data = dict(zip(config.labels.split(','), config.values.split(',')))
 148     #        add_record(table, data)
 149     #        #add_record(config, o, sheet)
 150
 151 if __name__ == '__main__':
 152     main()