def testToJSonResponse(self): description = ["col1", "col2", "col3"] data = [("1", "2", "3"), ("a", "b", "c"), ("One", "Two", "Three")] req_id = 4 table = DataTable(description, data) start_str_default = r"google.visualization.Query.setResponse" start_str_handler = r"MyHandlerFunction" json_str = table.ToJSon().strip() json_response = table.ToJSonResponse(req_id=req_id) self.assertEqual(json_response.find(start_str_default + "("), 0) json_response_obj = json.loads(json_response[len(start_str_default) + 1:-2]) self.assertEqual(json_response_obj["table"], json.loads(json_str)) self.assertEqual(json_response_obj["version"], "0.6") self.assertEqual(json_response_obj["reqId"], str(req_id)) self.assertEqual(json_response_obj["status"], "ok") json_response = table.ToJSonResponse( req_id=req_id, response_handler=start_str_handler) self.assertEqual(json_response.find(start_str_handler + "("), 0) json_response_obj = json.loads(json_response[len(start_str_handler) + 1:-2]) self.assertEqual(json_response_obj["table"], json.loads(json_str))
def testToJSonResponse(self): description = ["col1", "col2", "col3"] data = [("1", "2", "3"), ("a", "b", "c"), ("One", "Two", "Three")] req_id = 4 table = DataTable(description, data) start_str_default = r"google.visualization.Query.setResponse" start_str_handler = r"MyHandlerFunction" default_params = ( r"\s*'version'\s*:\s*'0.6'\s*,\s*'reqId'\s*:\s*'%s'\s*," r"\s*'status'\s*:\s*'OK'\s*" % req_id) regex1 = re.compile("%s\(\s*\{%s,\s*'table'\s*:\s*{(.*)}\s*\}\s*\);" % (start_str_default, default_params)) regex2 = re.compile("%s\(\s*\{%s,\s*'table'\s*:\s*{(.*)}\s*\}\s*\);" % (start_str_handler, default_params)) json_str = table.ToJSon().strip() json_response = table.ToJSonResponse(req_id=req_id) match = regex1.findall(json_response) self.assertEquals(len(match), 1) # We want to match against the json_str without the curly brackets. self.assertEquals(match[0], json_str[1:-1]) json_response = table.ToJSonResponse( req_id=req_id, response_handler=start_str_handler) match = regex2.findall(json_response) self.assertEquals(len(match), 1) # We want to match against the json_str without the curly brackets. self.assertEquals(match[0], json_str[1:-1])
def sql_to_data_table(table_name, data_types=None) -> DataTable: """Convert a SQL table to a google table. Parameters ---------- table_name : str The name of the table. data_types : str The DataTable type. """ if data_types is None: data_types = {} table_data = pd.read_sql_query(f'select * from {table_name};', database.connection) columns = {} date_columns = [] for column in table_data.columns: data_type = data_types.get(column) if data_type == 'date': date_columns.append(column) elif data_type is None: if table_data.dtypes[column] == np.int64: data_type = 'number' else: data_type = 'string' columns[column] = (data_type, column) table = DataTable(columns) for column in date_columns: table_data[column] = pd.to_datetime(table_data[column]).fillna(date.today()) table.LoadData(table_data.to_dict('index').values()) return table
def testToCsv(self): init_data_csv = "\n".join( ['"A", "b", "c"', '1, "", ""', '"", "zz\'top", true']) table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[(1, "$1")], [None, "zz'top", True]]) self.assertEqual(init_data_csv, table.ToCsv()) table.AppendData([[-1, "w", False]]) init_data_csv = "%s\n%s" % (init_data_csv, '-1, "w", false') self.assertEquals(init_data_csv, table.ToCsv()) init_data_csv = "\n".join([ '"T", "d", "dt"', '"[1,2,3]", "new Date(1,1,3)", ""', '"time ""2 3 4""", "new Date(2,2,4)", "new Date(1,1,3,4,5,6)"', '"", "new Date(3,3,5)", ""' ]) table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1, 2, 3): [time(1, 2, 3)], date(2, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(init_data_csv, table.ToCsv(columns_order=["t", "d", "dt"]))
def testToCsv(self): init_data_csv = "\r\n".join( ["A,\"b\"\"\",c", "1,,", ",zz'top,true", ""]) table = DataTable([("a", "number", "A"), "b\"", ("c", "boolean")], [[(1, "$1")], [None, "zz'top", True]]) self.assertEqual(init_data_csv, table.ToCsv()) table.AppendData([[-1, "w", False]]) init_data_csv = "%s%s\r\n" % (init_data_csv, "-1,w,false") self.assertEqual(init_data_csv, table.ToCsv()) init_data_csv = "\r\n".join([ "T,d,dt", "01:02:03,1901-02-03,", "\"time \"\"2 3 4\"\"\",1902-03-04,1901-02-03 04:05:06", ",1903-04-05,", "" ]) table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1901, 2, 3): [time(1, 2, 3)], date(1902, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1901, 2, 3, 4, 5, 6)], date(1903, 4, 5): [] }) self.assertEqual(init_data_csv, table.ToCsv(columns_order=["t", "d", "dt"]))
def testToTsvExcel(self): table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1901, 2, 3): [time(1, 2, 3)], date(1902, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1901, 2, 3, 4, 5, 6)], date(1903, 4, 5): [] }) self.assertEqual(table.ToCsv().replace(",", "\t").encode("UTF-16LE"), table.ToTsvExcel())
def get(self, request, *args, **kwargs): if not request.is_ajax(): raise SuspiciousOperation( 'This endpoint should only be called from JavaScript.') search_terms = self.get_search_terms(request.session) qs = get_queryset_filtered_by_search_terms(self.get_pixels_queryset(), search_terms=search_terms) dt = DataTable(self.get_headers()) dt.LoadData(qs.values(*self.get_columns())) return HttpResponse(dt.ToJSon(), content_type='application/json')
def testToTsvExcel(self): table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1901, 2, 3): [time(1, 2, 3)], date(1902, 3, 4): [(time(2, 3, 4), 'time "2 3 4"'), datetime(1901, 2, 3, 4, 5, 6)], date(1903, 4, 5): [] }) csv_string = table.ToCsv() if not isinstance(csv_string, six.text_type): csv_string = csv_string.decode("utf-8") self.assertEqual( csv_string.replace(",", "\t").encode("UTF-16LE"), table.ToTsvExcel())
def testDifferentStrings(self): # Checking escaping of strings problematic_strings = [ "control", "new\nline", "", "single'quote", 'double"quote', r"one\slash", r"two\\slash", u"unicode eng", u"unicode \u05e2\u05d1\u05e8\u05d9\u05ea" ] for s in problematic_strings: js_value = DataTable.SingleValueToJS(s, "string") if isinstance(js_value, unicode): js_value = "u%s" % js_value self.assertEquals(s, eval(js_value)) js_value = DataTable.SingleValueToJS(("str", s), "string")[1] if isinstance(js_value, unicode): js_value = "u%s" % js_value self.assertEquals(s, eval(js_value))
def testDifferentCustomProperties(self): # Checking escaping of custom properties problematic_cps = [{"control": "test"}, {"unicode": "value"}, {"key": "unicode"}, {"unicode": "unicode"}, {"regular": "single'quote"}, {"unicode": "s'quote"}] for cp in problematic_cps: js_value = DataTable._EscapeCustomProperties(cp) self.assertEquals(cp, eval(js_value))
def testDifferentStrings(self): # Checking escaping of strings in JSON output the_strings = [ "new\nline", r"one\slash", r"two\\slash", "unicode eng", "unicode \\u05e2\\u05d1\\u05e8\\u05d9\\u05ea", "unicode \\u05e2\\u05d1\\u05e8\\u05d9\\u05ea".encode("utf-8"), "\u05e2\u05d1\\\u05e8\u05d9\u05ea" ] table = DataTable([("a", "string")], [[x] for x in the_strings]) json_obj = json.loads(table.ToJSon()) for i, row in enumerate(json_obj["rows"]): utf8_str = the_strings[i] if isinstance(utf8_str, str): utf8_str = utf8_str.encode("utf-8") out_str = row["c"][0]["v"] self.assertEqual(out_str.encode("utf-8"), utf8_str)
def returnData(self,tqx): alldata = [] descr = [] for yr in self.yrs: (descr, data) = self.merge(csvlist[yr]) for row in data: row[0] = datetime.strptime(row[0],"%Y-%m-%d %H:%M:%S+00:00") alldata.append(row) pass pass descr[0] = ("Date","datetime") for i in range(1,1+len(descr[1:])): descr[i]=(descr[i],"number") pass table = DataTable(descr, alldata) expires = datetime.now() + timedelta(days=1) self.response.headers['Content-type'] = 'text/plain' self.response.headers['Expires'] = expires.strftime( '%a, %d %b %Y %H:%M:%S %Z') self.response.headers['Cache-control'] = 'public' self.response.out.write(table.ToResponse(tqx=tqx)) return
def testToJSCode(self): table = DataTable([("a", "number", "A'"), "b\"", ("c", "timeofday")], [[1], [None, "z", time(1, 2, 3)], [(2, "2$"), "w", time(2, 3, 4)]]) self.assertEqual(3, table.NumberOfRows()) self.assertEqual( ("var mytab = new google.visualization.DataTable();\n" "mytab.addColumn(\"number\", \"A'\", \"a\");\n" "mytab.addColumn(\"string\", \"b\\\"\", \"b\\\"\");\n" "mytab.addColumn(\"timeofday\", \"c\", \"c\");\n" "mytab.addRows(3);\n" "mytab.setCell(0, 0, 1);\n" "mytab.setCell(1, 1, \"z\");\n" "mytab.setCell(1, 2, [1,2,3]);\n" "mytab.setCell(2, 0, 2, \"2$\");\n" "mytab.setCell(2, 1, \"w\");\n" "mytab.setCell(2, 2, [2,3,4]);\n"), table.ToJSCode("mytab")) table = DataTable({("a", "number"): { "b": "date", "c": "datetime" }}, { 1: {}, 2: { "b": date(1, 2, 3) }, 3: { "c": datetime(1, 2, 3, 4, 5, 6, 555000) }, 4: { "c": datetime(1, 2, 3, 4, 5, 6) } }) self.assertEqual(4, table.NumberOfRows()) self.assertEqual( ("var mytab2 = new google.visualization.DataTable();\n" 'mytab2.addColumn("datetime", "c", "c");\n' 'mytab2.addColumn("date", "b", "b");\n' 'mytab2.addColumn("number", "a", "a");\n' 'mytab2.addRows(4);\n' 'mytab2.setCell(0, 2, 1);\n' 'mytab2.setCell(1, 1, new Date(1,1,3));\n' 'mytab2.setCell(1, 2, 2);\n' 'mytab2.setCell(2, 0, new Date(1,1,3,4,5,6,555));\n' 'mytab2.setCell(2, 2, 3);\n' 'mytab2.setCell(3, 0, new Date(1,1,3,4,5,6));\n' 'mytab2.setCell(3, 2, 4);\n'), table.ToJSCode("mytab2", columns_order=["c", "b", "a"]))
def testColumnTypeParser(self): # Checking several wrong formats self.assertRaises(DataTableException, DataTable.ColumnTypeParser, 5) self.assertRaises(DataTableException, DataTable.ColumnTypeParser, ("a", 5, "c")) self.assertRaises(DataTableException, DataTable.ColumnTypeParser, ("a", "blah")) self.assertRaises(DataTableException, DataTable.ColumnTypeParser, ("a", "number", "c", "d")) # Checking several legal formats self.assertEqual( { "id": "abc", "label": "abc", "type": "string", "custom_properties": {} }, DataTable.ColumnTypeParser("abc")) self.assertEqual( { "id": "abc", "label": "abc", "type": "string", "custom_properties": {} }, DataTable.ColumnTypeParser(("abc", ))) self.assertEqual( { "id": "abc", "label": "bcd", "type": "string", "custom_properties": {} }, DataTable.ColumnTypeParser(("abc", "string", "bcd"))) self.assertEqual( { "id": "a", "label": "b", "type": "number", "custom_properties": {} }, DataTable.ColumnTypeParser(("a", "number", "b"))) self.assertEqual( { "id": "a", "label": "a", "type": "number", "custom_properties": {} }, DataTable.ColumnTypeParser(("a", "number"))) self.assertEqual( { "id": "i", "label": "l", "type": "string", "custom_properties": { "key": "value" } }, DataTable.ColumnTypeParser(("i", "string", "l", { "key": "value" })))
def testToJSCode(self): table = DataTable([("a", "number", "A"), "b", ("c", "timeofday")], [[1], [None, "z", time(1, 2, 3)], [(2, "2$"), "w", time(2, 3, 4)]]) self.assertEqual(3, table.NumberOfRows()) self.assertEqual(("var mytab = new google.visualization.DataTable();\n" "mytab.addColumn('number', 'A', 'a');\n" "mytab.addColumn('string', 'b', 'b');\n" "mytab.addColumn('timeofday', 'c', 'c');\n" "mytab.addRows(3);\n" "mytab.setCell(0, 0, 1);\n" "mytab.setCell(1, 1, 'z');\n" "mytab.setCell(1, 2, [1,2,3]);\n" "mytab.setCell(2, 0, 2, '2$');\n" "mytab.setCell(2, 1, 'w');\n" "mytab.setCell(2, 2, [2,3,4]);\n"), table.ToJSCode("mytab")) table = DataTable({("a", "number"): { "b": "date", "c": "datetime" }}, { 1: {}, 2: { "b": date(1, 2, 3) }, 3: { "c": datetime(1, 2, 3, 4, 5, 6) } }) self.assertEqual(3, table.NumberOfRows()) self.assertEqual( ("var mytab2 = new google.visualization.DataTable();\n" "mytab2.addColumn('datetime', 'c', 'c');\n" "mytab2.addColumn('date', 'b', 'b');\n" "mytab2.addColumn('number', 'a', 'a');\n" "mytab2.addRows(3);\n" "mytab2.setCell(0, 2, 1);\n" "mytab2.setCell(1, 1, new Date(1,1,3));\n" "mytab2.setCell(1, 2, 2);\n" "mytab2.setCell(2, 0, new Date(1,1,3,4,5,6));\n" "mytab2.setCell(2, 2, 3);\n"), table.ToJSCode("mytab2", columns_order=["c", "b", "a"]))
def testDifferentCustomProperties(self): # Checking escaping of custom properties problematic_cps = [{ "control": "test" }, { u"unicode": "value" }, { "key": u"unicode" }, { u"unicode": u"unicode" }, { "regular": "single'quote" }, { "unicode": u"s'quote" }] for cp in problematic_cps: js_value = DataTable._EscapeCustomProperties(cp) self.assertEquals(cp, eval(js_value))
def testCustomProperties(self): # The json of the initial data we load to the table. json = ( "{cols:" "[{id:'a',label:'A',type:'number',p:{'col_cp':'col_v'}}," "{id:'b',label:'b',type:'string'}," "{id:'c',label:'c',type:'boolean'}]," "rows:[" "{c:[{v:1},,{v:null,p:{'null_cp':'null_v'}}],p:{'row_cp':'row_v'}}," "{c:[,{v:'z',p:{'cell_cp':'cell_v'}},{v:true}]}," "{c:[{v:3},,{v:null}],p:{'row_cp2':'row_v2'}}]," "p:{'global_cp':'global_v'}" "}") jscode = ("var mytab = new google.visualization.DataTable();\n" "mytab.setTableProperties({'global_cp':'global_v'});\n" "mytab.addColumn('number', 'A', 'a');\n" "mytab.setColumnProperties(0, {'col_cp':'col_v'});\n" "mytab.addColumn('string', 'b', 'b');\n" "mytab.addColumn('boolean', 'c', 'c');\n" "mytab.addRows(3);\n" "mytab.setCell(0, 0, 1);\n" "mytab.setCell(0, 2, null, null, {'null_cp':'null_v'});\n" "mytab.setRowProperties(0, {'row_cp':'row_v'});\n" "mytab.setCell(1, 1, 'z', null, {'cell_cp':'cell_v'});\n" "mytab.setCell(1, 2, true);\n" "mytab.setCell(2, 0, 3);\n" "mytab.setRowProperties(2, {'row_cp2':'row_v2'});\n") table = DataTable([("a", "number", "A", { "col_cp": "col_v" }), "b", ("c", "boolean")], custom_properties={"global_cp": "global_v"}) table.AppendData([[1, None, (None, None, { "null_cp": "null_v" })]], custom_properties={"row_cp": "row_v"}) table.AppendData([[None, ("z", None, { "cell_cp": "cell_v" }), True], [3]]) table.SetRowsCustomProperties(2, {"row_cp2": "row_v2"}) self.assertEqual(json, table.ToJSon()) self.assertEqual(jscode, table.ToJSCode("mytab"))
def testToHtml(self): html_table_header = "<html><body><table border='1'>" html_table_footer = "</table></body></html>" init_data_html = html_table_header + ( "<thead><tr>" "<th>A</th><th>b</th><th>c</th>" "</tr></thead>" "<tbody>" "<tr><td>'$1'</td><td></td><td></td></tr>" "<tr><td></td><td>'<z>'</td><td>true</td></tr>" "</tbody>") + html_table_footer table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[(1, "$1")], [None, "<z>", True]]) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml()) init_data_html = html_table_header + ( "<thead><tr>" "<th>T</th><th>d</th><th>dt</th>" "</tr></thead>" "<tbody>" "<tr><td>[1,2,3]</td><td>new Date(1,1,3)</td><td></td></tr>" "<tr><td>'time 2 3 4'</td><td>new Date(2,2,4)</td>" "<td>new Date(1,1,3,4,5,6)</td></tr>" "<tr><td></td><td>new Date(3,3,5)</td><td></td></tr>" "</tbody>") + html_table_footer table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1, 2, 3): [time(1, 2, 3)], date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml(columns_order=["t", "d", "dt"]))
def testToHtml(self): html_table_header = "<html><body><table border=\"1\">" html_table_footer = "</table></body></html>" init_data_html = html_table_header + ( "<thead><tr>" "<th>A<</th><th>b></th><th>c</th>" "</tr></thead>" "<tbody>" "<tr><td>$1</td><td></td><td></td></tr>" "<tr><td></td><td><z></td><td>true</td></tr>" "</tbody>") + html_table_footer table = DataTable([("a", "number", "A<"), "b>", ("c", "boolean")], [[(1, "$1")], [None, "<z>", True]]) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml()) init_data_html = html_table_header + ( "<thead><tr>" "<th>T</th><th>d</th><th>dt</th>" "</tr></thead>" "<tbody>" "<tr><td>01:02:03</td><td>0001-02-03</td><td></td></tr>" "<tr><td>time 2 3 4</td><td>0002-03-04</td>" "<td>0001-02-03 04:05:06</td></tr>" "<tr><td></td><td>0003-04-05</td><td></td></tr>" "</tbody>") + html_table_footer table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({ date(1, 2, 3): [time(1, 2, 3)], date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(init_data_html.replace("\n", ""), table.ToHtml(columns_order=["t", "d", "dt"]))
def testToJSon(self): json_obj = { "cols": [{ "id": "a", "label": "A", "type": "number" }, { "id": "b", "label": "b", "type": "string" }, { "id": "c", "label": "c", "type": "boolean" }], "rows": [{ "c": [{ "v": 1 }, None, None] }, { "c": [None, { "v": "z" }, { "v": True }] }, { "c": [None, { "v": "\\u05d0" }, None] }, { "c": [None, { "v": "\\u05d1" }, None] }] } table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[1], [None, "z", True], [None, "\\u05d0"], [None, "\\u05d1".encode("utf-8")]]) self.assertEqual(4, table.NumberOfRows()) self.assertEqual( json.dumps(json_obj, separators=(",", ":"), ensure_ascii=False).encode("utf-8"), table.ToJSon()) table.AppendData([[-1, "w", False]]) self.assertEqual(5, table.NumberOfRows()) json_obj["rows"].append({"c": [{"v": -1}, {"v": "w"}, {"v": False}]}) self.assertEqual( json.dumps(json_obj, separators=(",", ":"), ensure_ascii=False).encode("utf-8"), table.ToJSon()) json_obj = { "cols": [{ "id": "t", "label": "T", "type": "timeofday" }, { "id": "d", "label": "d", "type": "date" }, { "id": "dt", "label": "dt", "type": "datetime" }], "rows": [{ "c": [{ "v": [1, 2, 3] }, { "v": "Date(1,1,3)" }, None] }] } table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({date(1, 2, 3): [time(1, 2, 3)]}) self.assertEqual(1, table.NumberOfRows()) self.assertEqual(json.dumps(json_obj, separators=(",", ":")), table.ToJSon(columns_order=["t", "d", "dt"])) json_obj["rows"] = [{ "c": [{ "v": [2, 3, 4], "f": "time 2 3 4" }, { "v": "Date(2,2,4)" }, { "v": "Date(1,1,3,4,5,6,555)" }] }, { "c": [None, { "v": "Date(3,3,5)" }, None] }] table.LoadData({ date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6, 555000)], date(3, 4, 5): [] }) self.assertEqual(2, table.NumberOfRows()) self.assertEqual(json.dumps(json_obj, separators=(",", ":")), table.ToJSon(columns_order=["t", "d", "dt"])) json_obj = { "cols": [{ "id": "a\"", "label": "a\"", "type": "string" }, { "id": "b", "label": "bb\"", "type": "number" }], "rows": [{ "c": [{ "v": "a1" }, { "v": 1 }] }, { "c": [{ "v": "a2" }, { "v": 2 }] }, { "c": [{ "v": "a3" }, { "v": 3 }] }] } table = DataTable({"a\"": ("b", "number", "bb\"", {})}, { "a1": 1, "a2": 2, "a3": 3 }) self.assertEqual(3, table.NumberOfRows()) self.assertEqual(json.dumps(json_obj, separators=(",", ":")), table.ToJSon())
def testToResponse(self): description = ["col1", "col2", "col3"] data = [("1", "2", "3"), ("a", "b", "c"), ("One", "Two", "Three")] table = DataTable(description, data) self.assertEqual(table.ToResponse(), table.ToJSonResponse()) self.assertEqual(table.ToResponse(tqx="out:csv"), table.ToCsv()) self.assertEqual(table.ToResponse(tqx="out:html"), table.ToHtml()) self.assertRaises(DataTableException, table.ToResponse, tqx="version:0.1") self.assertEqual( table.ToResponse(tqx="reqId:4;responseHandler:handle"), table.ToJSonResponse(req_id=4, response_handler="handle")) self.assertEqual(table.ToResponse(tqx="out:csv;reqId:4"), table.ToCsv()) self.assertEqual(table.ToResponse(order_by="col2"), table.ToJSonResponse(order_by="col2")) self.assertEqual( table.ToResponse(tqx="out:html", columns_order=("col3", "col2", "col1")), table.ToHtml(columns_order=("col3", "col2", "col1"))) self.assertRaises(ValueError, table.ToResponse, tqx="SomeWrongTqxFormat") self.assertRaises(DataTableException, table.ToResponse, tqx="out:bad")
def testToJSon(self): # The json of the initial data we load to the table. init_data_json = ("{cols:" "[{id:'a',label:'A',type:'number'}," "{id:'b',label:'b',type:'string'}," "{id:'c',label:'c',type:'boolean'}]," "rows:[" "{c:[{v:1},,{v:null}]}," "{c:[,{v:'z'},{v:true}]}" "]}") table = DataTable([("a", "number", "A"), "b", ("c", "boolean")], [[1], [None, "z", True]]) self.assertEqual(2, table.NumberOfRows()) self.assertEqual(init_data_json, table.ToJSon()) table.AppendData([[-1, "w", False]]) self.assertEqual(3, table.NumberOfRows()) self.assertEqual( init_data_json[:-2] + ",{c:[{v:-1},{v:'w'},{v:false}]}]}", table.ToJSon()) cols_json = ("{cols:" "[{id:'t',label:'T',type:'timeofday'}," "{id:'d',label:'d',type:'date'}," "{id:'dt',label:'dt',type:'datetime'}],") table = DataTable({ ("d", "date"): [("t", "timeofday", "T"), ("dt", "datetime")] }) table.LoadData({date(1, 2, 3): [time(1, 2, 3)]}) self.assertEqual(1, table.NumberOfRows()) self.assertEqual( cols_json + "rows:[{c:[{v:[1,2,3]},{v:new Date(1,1,3)},{v:null}]}]}", table.ToJSon(columns_order=["t", "d", "dt"])) table.LoadData({ date(2, 3, 4): [(time(2, 3, 4), "time 2 3 4"), datetime(1, 2, 3, 4, 5, 6)], date(3, 4, 5): [] }) self.assertEqual(2, table.NumberOfRows()) self.assertEqual((cols_json + "rows:[" "{c:[{v:[2,3,4],f:'time 2 3 4'},{v:new Date(2,2,4)}," "{v:new Date(1,1,3,4,5,6)}]}," "{c:[,{v:new Date(3,3,5)},{v:null}]}]}"), table.ToJSon(columns_order=["t", "d", "dt"])) json = ("{cols:[{id:'a',label:'a',type:'string'}," "{id:'b',label:'b',type:'number'}]," "rows:[{c:[{v:'a1'},{v:1}]},{c:[{v:'a2'},{v:2}]}," "{c:[{v:'a3'},{v:3}]}]}") table = DataTable({"a": ("b", "number")}, {"a1": 1, "a2": 2, "a3": 3}) self.assertEqual(3, table.NumberOfRows()) self.assertEqual(json, table.ToJSon())
def testOrderBy(self): data = [("b", 3), ("a", 3), ("a", 2), ("b", 1)] description = ["col1", ("col2", "number", "Second Column")] table = DataTable(description, data) table_num_sorted = DataTable(description, sorted(data, key=lambda x: (x[1], x[0]))) table_str_sorted = DataTable(description, sorted(data, key=lambda x: x[0])) table_diff_sorted = DataTable( description, sorted(sorted(data, key=lambda x: x[1]), key=lambda x: x[0], reverse=True)) self.assertEqual(table_num_sorted.ToJSon(), table.ToJSon(order_by=("col2", "col1"))) self.assertEqual(table_num_sorted.ToJSCode("mytab"), table.ToJSCode("mytab", order_by=("col2", "col1"))) self.assertEqual(table_str_sorted.ToJSon(), table.ToJSon(order_by="col1")) self.assertEqual(table_str_sorted.ToJSCode("mytab"), table.ToJSCode("mytab", order_by="col1")) self.assertEqual(table_diff_sorted.ToJSon(), table.ToJSon(order_by=[("col1", "desc"), "col2"])) self.assertEqual( table_diff_sorted.ToJSCode("mytab"), table.ToJSCode("mytab", order_by=[("col1", "desc"), "col2"]))
data = pd.read_sql_query('select * from people;', database.connection) # Replace null values with -1 to indicate not specified. for column in ['Husband', 'Wife', 'Father', 'Mother']: data[column][data[column].copy().isnull()] = -1 columns = {} for column in data.columns: # if 'date' in column: # data_type = 'date' if data.dtypes[column] in ['O']: data_type = 'string' else: data_type = 'number' columns[column] = (data_type, column) table = DataTable(columns) table.LoadData(data.to_dict('index').values()) timelines = sql_to_data_table('stays', {'Start': 'date', 'End': 'date'}) locations = sql_to_data_table('locations') with open('table.js', 'w') as output_file: code = f"""function loadFamilyData() {{ {table.ToJSCode('familyTable')} return familyTable; }} function loadTimelines()
def testSingleValueToJS(self): # We first check that given an unknown type it raises exception self.assertRaises(DataTableException, DataTable.SingleValueToJS, 1, "no_such_type") # If we give a type which does not match the value, we expect it to fail self.assertRaises(DataTableException, DataTable.SingleValueToJS, "a", "number") self.assertRaises(DataTableException, DataTable.SingleValueToJS, "b", "timeofday") self.assertRaises(DataTableException, DataTable.SingleValueToJS, 10, "date") # A tuple for value and formatted value should be of length 2 self.assertRaises(DataTableException, DataTable.SingleValueToJS, (5, "5$", "6$"), "string") # Some good examples from all the different types self.assertEqual("true", DataTable.SingleValueToJS(True, "boolean")) self.assertEqual("false", DataTable.SingleValueToJS(False, "boolean")) self.assertEqual("true", DataTable.SingleValueToJS(1, "boolean")) self.assertEqual("null", DataTable.SingleValueToJS(None, "boolean")) self.assertEqual(("false", "'a'"), DataTable.SingleValueToJS((False, "a"), "boolean")) self.assertEqual("1", DataTable.SingleValueToJS(1, "number")) self.assertEqual("1.0", DataTable.SingleValueToJS(1., "number")) self.assertEqual("-5", DataTable.SingleValueToJS(-5, "number")) self.assertEqual("null", DataTable.SingleValueToJS(None, "number")) self.assertEqual(("5", "'5$'"), DataTable.SingleValueToJS((5, "5$"), "number")) self.assertEqual("'-5'", DataTable.SingleValueToJS(-5, "string")) self.assertEqual("'abc'", DataTable.SingleValueToJS("abc", "string")) self.assertEqual("null", DataTable.SingleValueToJS(None, "string")) self.assertEqual("new Date(2010,0,2)", DataTable.SingleValueToJS(date(2010, 1, 2), "date")) self.assertEqual( "new Date(2001,1,3)", DataTable.SingleValueToJS(datetime(2001, 2, 3, 4, 5, 6), "date")) self.assertEqual("null", DataTable.SingleValueToJS(None, "date")) self.assertEqual( "[10,11,12]", DataTable.SingleValueToJS(time(10, 11, 12), "timeofday")) self.assertEqual( "[3,4,5]", DataTable.SingleValueToJS(datetime(2010, 1, 2, 3, 4, 5), "timeofday")) self.assertEqual("null", DataTable.SingleValueToJS(None, "timeofday")) self.assertEqual( "new Date(2001,1,3,4,5,6)", DataTable.SingleValueToJS(datetime(2001, 2, 3, 4, 5, 6), "datetime")) self.assertEqual("null", DataTable.SingleValueToJS(None, "datetime")) self.assertEqual(("null", "'none'"), DataTable.SingleValueToJS((None, "none"), "string"))
def testCustomProperties(self): # The json of the initial data we load to the table. json_obj = { "cols": [{ "id": "a", "label": "A", "type": "number", "p": { "col_cp": "col_v" } }, { "id": "b", "label": "b", "type": "string" }, { "id": "c", "label": "c", "type": "boolean" }], "rows": [{ "c": [{ "v": 1 }, None, { "v": None, "p": { "null_cp": "null_v" } }], "p": { "row_cp": "row_v" } }, { "c": [None, { "v": "z", "p": { "cell_cp": "cell_v" } }, { "v": True }] }, { "c": [{ "v": 3 }, None, None], "p": { "row_cp2": "row_v2" } }], "p": { "global_cp": "global_v" } } jscode = ( "var mytab = new google.visualization.DataTable();\n" "mytab.setTableProperties({\"global_cp\":\"global_v\"});\n" "mytab.addColumn(\"number\", \"A\", \"a\");\n" "mytab.setColumnProperties(0, {\"col_cp\":\"col_v\"});\n" "mytab.addColumn(\"string\", \"b\", \"b\");\n" "mytab.addColumn(\"boolean\", \"c\", \"c\");\n" "mytab.addRows(3);\n" "mytab.setCell(0, 0, 1);\n" "mytab.setCell(0, 2, null, null, {\"null_cp\":\"null_v\"});\n" "mytab.setRowProperties(0, {\"row_cp\":\"row_v\"});\n" "mytab.setCell(1, 1, \"z\", null, {\"cell_cp\":\"cell_v\"});\n" "mytab.setCell(1, 2, true);\n" "mytab.setCell(2, 0, 3);\n" "mytab.setRowProperties(2, {\"row_cp2\":\"row_v2\"});\n") table = DataTable([("a", "number", "A", { "col_cp": "col_v" }), "b", ("c", "boolean")], custom_properties={"global_cp": "global_v"}) table.AppendData([[1, None, (None, None, { "null_cp": "null_v" })]], custom_properties={"row_cp": "row_v"}) table.AppendData([[None, ("z", None, { "cell_cp": "cell_v" }), True], [3]]) table.SetRowsCustomProperties(2, {"row_cp2": "row_v2"}) self.assertEqual(json.dumps(json_obj, separators=(",", ":")), table.ToJSon()) self.assertEqual(jscode, table.ToJSCode("mytab"))
def testCoerceValue(self): # We first check that given an unknown type it raises exception self.assertRaises(DataTableException, DataTable.CoerceValue, 1, "no_such_type") # If we give a type which does not match the value, we expect it to fail self.assertRaises(DataTableException, DataTable.CoerceValue, "a", "number") self.assertRaises(DataTableException, DataTable.CoerceValue, "b", "timeofday") self.assertRaises(DataTableException, DataTable.CoerceValue, 10, "date") # A tuple for value and formatted value should be of length 2 self.assertRaises(DataTableException, DataTable.CoerceValue, (5, "5$", "6$"), "string") # Some good examples from all the different types self.assertEqual(True, DataTable.CoerceValue(True, "boolean")) self.assertEqual(False, DataTable.CoerceValue(False, "boolean")) self.assertEqual(True, DataTable.CoerceValue(1, "boolean")) self.assertEqual(None, DataTable.CoerceValue(None, "boolean")) self.assertEqual((False, "a"), DataTable.CoerceValue((False, "a"), "boolean")) self.assertEqual(1, DataTable.CoerceValue(1, "number")) self.assertEqual(1., DataTable.CoerceValue(1., "number")) self.assertEqual(-5, DataTable.CoerceValue(-5, "number")) self.assertEqual(None, DataTable.CoerceValue(None, "number")) self.assertEqual((5, "5$"), DataTable.CoerceValue((5, "5$"), "number")) self.assertEqual("-5", DataTable.CoerceValue(-5, "string")) self.assertEqual("abc", DataTable.CoerceValue("abc", "string")) self.assertEqual(None, DataTable.CoerceValue(None, "string")) self.assertEqual(date(2010, 1, 2), DataTable.CoerceValue(date(2010, 1, 2), "date")) self.assertEqual( date(2001, 2, 3), DataTable.CoerceValue(datetime(2001, 2, 3, 4, 5, 6), "date")) self.assertEqual(None, DataTable.CoerceValue(None, "date")) self.assertEqual(time(10, 11, 12), DataTable.CoerceValue(time(10, 11, 12), "timeofday")) self.assertEqual( time(3, 4, 5), DataTable.CoerceValue(datetime(2010, 1, 2, 3, 4, 5), "timeofday")) self.assertEqual(None, DataTable.CoerceValue(None, "timeofday")) self.assertEqual( datetime(2001, 2, 3, 4, 5, 6, 555000), DataTable.CoerceValue(datetime(2001, 2, 3, 4, 5, 6, 555000), "datetime")) self.assertEqual(None, DataTable.CoerceValue(None, "datetime")) self.assertEqual((None, "none"), DataTable.CoerceValue((None, "none"), "string"))
def testAppendData(self): # We check a few examples where the format of the data does not match the # description and hen a few valid examples. The test for the content itself # is done inside the ToJSCode and ToJSon functions. table = DataTable([("a", "number"), ("b", "string")]) self.assertEqual(0, table.NumberOfRows()) self.assertRaises(DataTableException, table.AppendData, [[1, "a", True]]) self.assertRaises(DataTableException, table.AppendData, { 1: ["a"], 2: ["b"] }) self.assertEqual(None, table.AppendData([[1, "a"], [2, "b"]])) self.assertEqual(2, table.NumberOfRows()) self.assertEqual(None, table.AppendData([[3, "c"], [4]])) self.assertEqual(4, table.NumberOfRows()) table = DataTable({"a": "number", "b": "string"}) self.assertEqual(0, table.NumberOfRows()) self.assertRaises(DataTableException, table.AppendData, [[1, "a"]]) self.assertRaises(DataTableException, table.AppendData, {5: { "b": "z" }}) self.assertEqual(None, table.AppendData([{"a": 1, "b": "z"}])) self.assertEqual(1, table.NumberOfRows()) table = DataTable({("a", "number"): [("b", "string")]}) self.assertEqual(0, table.NumberOfRows()) self.assertRaises(DataTableException, table.AppendData, [[1, "a"]]) self.assertRaises(DataTableException, table.AppendData, {5: { "b": "z" }}) self.assertEqual(None, table.AppendData({5: ["z"], 6: ["w"]})) self.assertEqual(2, table.NumberOfRows()) table = DataTable({("a", "number"): {"b": "string", "c": "number"}}) self.assertEqual(0, table.NumberOfRows()) self.assertRaises(DataTableException, table.AppendData, [[1, "a"]]) self.assertRaises(DataTableException, table.AppendData, {1: ["a", 2]}) self.assertEqual( None, table.AppendData({ 5: { "b": "z", "c": 6 }, 7: { "c": 8 }, 9: {} })) self.assertEqual(3, table.NumberOfRows())
def testTableDescriptionParser(self): # We expect it to fail with empty lists or dictionaries self.assertRaises(DataTableException, DataTable.TableDescriptionParser, {}) self.assertRaises(DataTableException, DataTable.TableDescriptionParser, []) self.assertRaises(DataTableException, DataTable.TableDescriptionParser, {"a": []}) self.assertRaises(DataTableException, DataTable.TableDescriptionParser, {"a": { "b": {} }}) # We expect it to fail if we give a non-string at the lowest level self.assertRaises(DataTableException, DataTable.TableDescriptionParser, {"a": 5}) self.assertRaises(DataTableException, DataTable.TableDescriptionParser, [("a", "number"), 6]) # Some valid examples which mixes both dictionaries and lists self.assertEqual([{ "id": "a", "label": "a", "type": "date", "depth": 0, "container": "iter", "custom_properties": {} }, { "id": "b", "label": "b", "type": "timeofday", "depth": 0, "container": "iter", "custom_properties": {} }], DataTable.TableDescriptionParser([("a", "date"), ("b", "timeofday") ])) self.assertEqual([{ "id": "a", "label": "a", "type": "string", "depth": 0, "container": "dict", "custom_properties": {} }, { "id": "b", "label": "b", "type": "number", "depth": 1, "container": "iter", "custom_properties": {} }, { "id": "c", "label": "column c", "type": "string", "depth": 1, "container": "iter", "custom_properties": {} }], DataTable.TableDescriptionParser({ "a": [("b", "number"), ("c", "string", "column c")] })) self.assertEqual([{ "id": "a", "label": "column a", "type": "number", "depth": 0, "container": "dict", "custom_properties": {} }, { "id": "b", "label": "column b", "type": "string", "depth": 0, "container": "dict", "custom_properties": {} }], DataTable.TableDescriptionParser({ "a": ("number", "column a"), "b": ("string", "column b") })) self.assertEqual([{ "id": "a", "label": "column a", "type": "number", "depth": 0, "container": "dict", "custom_properties": {} }, { "id": "b", "label": "b", "type": "number", "depth": 1, "container": "dict", "custom_properties": {} }, { "id": "c", "label": "c", "type": "string", "depth": 1, "container": "dict", "custom_properties": {} }], DataTable.TableDescriptionParser({ ("a", "number", "column a"): { "b": "number", "c": "string" } })) self.assertEqual([{ "id": "a", "label": "column a", "type": "number", "depth": 0, "container": "dict", "custom_properties": {} }, { "id": "b", "label": "column b", "type": "string", "depth": 1, "container": "scalar", "custom_properties": {} }], DataTable.TableDescriptionParser({ ("a", "number", "column a"): ("b", "string", "column b") })) # Cases that might create ambiguity self.assertEqual([{ "id": "a", "label": "column a", "type": "number", "depth": 0, "container": "dict", "custom_properties": {} }], DataTable.TableDescriptionParser({"a": ("number", "column a")})) self.assertRaises(DataTableException, DataTable.TableDescriptionParser, {"a": ("b", "number")}) self.assertEqual([{ "id": "a", "label": "a", "type": "string", "depth": 0, "container": "dict", "custom_properties": {} }, { "id": "b", "label": "b", "type": "number", "depth": 1, "container": "scalar", "custom_properties": {} }], DataTable.TableDescriptionParser({"a": ("b", "number", "b", {})})) self.assertEqual([{ "id": "a", "label": "a", "type": "string", "depth": 0, "container": "dict", "custom_properties": {} }, { "id": "b", "label": "b", "type": "number", "depth": 1, "container": "scalar", "custom_properties": {} }], DataTable.TableDescriptionParser({("a", ): ("b", "number")}))
def get(self): question = self.request.get("question") if question != '': form = {"q": question} form_data = urllib.urlencode(form) resp = urlfetch.fetch(host + "ask", payload=form_data, method="POST", follow_redirects=False) if resp.status_code == 200: logging.debug("OK") logging.debug(result.content) else: logging.debug("FAILED ON QUESTION") pass country = 'US' indicator = 'TM.TAX.MRCH.SM.AR.ZS' pass else: country = self.request.get("country") indicator = self.request.get("indicator") pass start = self.request.get("start") if start == '': start = '2000' end = self.request.get("end") if end == '': end = '2012' if indicator == '': indicator = 'TM.TAX.MRCH.SM.AR.ZS' pass data = {} country_code = country.lower() indicator = indicator.upper() url = "http://api.worldbank.org/countries/" + country_code + "/indicators/" + indicator + "?" + \ "date=" + start + ":" + end + "&" + "format=" + "json" resp = urlfetch.fetch(url, method="GET", follow_redirects=True) if resp.status_code == 200: logging.debug(resp.status_code) try: data = json.loads(resp.content) except: logging.info(resp.content) pass else: logging.debug(resp.status_code) logging.debug(resp.content) pass rows = {} old_viz_data = [] viz_data = [] countries = {} try: for row in data[1]: key = row['country']['id'] countries[key] = row['country']['value'] try: rows[row['date']][key] = row['value'] except: rows[row['date']] = {} rows[row['date']][key] = row['value'] pass for yr in rows.keys(): viz_row = {"date": date(int(yr), 1, 1)} for k in rows[yr].keys(): try: viz_row[k] = float(rows[yr][k]) except: viz_row[k] = None pass pass viz_data.append(viz_row) except: pass chart_data = {} chart_data['cols'] = [{ 'id': 'date', 'label': 'Date', 'type': 'number' }, { 'id': 'value', 'label': 'Value', 'type': 'number' }] chart_data['rows'] = rows pass indicator_value = data[1][0]['indicator']['value'] viz_desc = {"date": ("date", "Date")} order = ["date"] for k in countries.keys(): viz_desc[k] = ("number", countries[k]) order.append(k) data_table = DataTable(viz_desc) data_table.LoadData(viz_data) template_values = { 'question': SafeString(question), 'start': start, 'end': end, 'country': country, 'indicator': indicator, 'data': data_table.ToJSon(columns_order=order, order_by="date"), 'title': SafeString(indicator_value) } if self.request.path == '/': path = os.path.join(os.path.dirname(__file__), 'templates/index.html') else: path = os.path.join(os.path.dirname(__file__), 'templates' + self.request.path) pass self.response.out.write(template.render(path, template_values)) return