def bq2table(bqdata): def getType(type_str): if type_str == "INTEGER": return int elif type_str == "FLOAT": return float elif type_str == "STRING": return str else: return eval(type_str) table = Table() for x in bqdata["schema"]["fields"]: table.add_column(x["name"], getType(x["type"]), x["name"]) if "rows" in bqdata: for row in bqdata["rows"]: row_data = [] for x, t in zip(row["f"], bqdata["schema"]["fields"]): val = getType(t["type"])(x["v"] or 0) if t["type"] == "FLOAT": val = float("{0:.2f}".format(val)) row_data.append(val) # add row only if it's not all zeros if row_data.count(0) < (len(row_data) - 1): table.append(row_data) logging.info("FINAL BOOTTIMEDATA---") return encode(table)
def _bq2geo(self, bqdata): # geodata output for region maps must be in the format region, value. # Assume the query output is in this format, get names from schema. logging.info(bqdata) table = Table() NameGeo = bqdata["schema"]["fields"][0]["name"] NameVal = bqdata["schema"]["fields"][1]["name"] table.add_column(NameGeo, unicode, NameGeo) table.add_column(NameVal, float, NameVal) for row in bqdata["rows"]: table.append(["US-" + row["f"][0]["v"], float(row["f"][1]["v"])]) logging.info("FINAL GEODATA---") logging.info(table) return encode(table)
def data(): folder = os.path.split(__file__)[0] db = sqlite3.connect(os.path.join(folder, "sample.db")) c = db.cursor() c.execute("SELECT name, salary FROM employees") cols = [ dict(id=col[0], label=col[0].capitalize(), type=col[1]) for col in c.description ] # sqlite3 unfortunately does not provide type information cols[0]['type'] = unicode cols[1]['type'] = float t = Table(cols) for r in c.fetchall(): name, value = r label = "${0}".format(value) t.append([name, (value, label)]) return encoder.encode(t)