gviz_api.py (45627B)
1 #!/usr/bin/python 2 # 3 # Copyright (c) 2016, Alliance for Open Media. All rights reserved. 4 # 5 # This source code is subject to the terms of the BSD 2 Clause License and 6 # the Alliance for Open Media Patent License 1.0. If the BSD 2 Clause License 7 # was not distributed with this source code in the LICENSE file, you can 8 # obtain it at www.aomedia.org/license/software. If the Alliance for Open 9 # Media Patent License 1.0 was not distributed with this source code in the 10 # PATENTS file, you can obtain it at www.aomedia.org/license/patent. 11 # 12 13 """Converts Python data into data for Google Visualization API clients. 14 15 This library can be used to create a google.visualization.DataTable usable by 16 visualizations built on the Google Visualization API. Output formats are raw 17 JSON, JSON response, JavaScript, CSV, and HTML table. 18 19 See http://code.google.com/apis/visualization/ for documentation on the 20 Google Visualization API. 21 """ 22 23 __author__ = "Amit Weinstein, Misha Seltzer, Jacob Baskin" 24 25 import cgi 26 import cStringIO 27 import csv 28 import datetime 29 try: 30 import json 31 except ImportError: 32 import simplejson as json 33 import types 34 35 36 class DataTableException(Exception): 37 """The general exception object thrown by DataTable.""" 38 pass 39 40 41 class DataTableJSONEncoder(json.JSONEncoder): 42 """JSON encoder that handles date/time/datetime objects correctly.""" 43 44 def __init__(self): 45 json.JSONEncoder.__init__(self, 46 separators=(",", ":"), 47 ensure_ascii=False) 48 49 def default(self, o): 50 if isinstance(o, datetime.datetime): 51 if o.microsecond == 0: 52 # If the time doesn't have ms-resolution, leave it out to keep 53 # things smaller. 54 return "Date(%d,%d,%d,%d,%d,%d)" % ( 55 o.year, o.month - 1, o.day, o.hour, o.minute, o.second) 56 else: 57 return "Date(%d,%d,%d,%d,%d,%d,%d)" % ( 58 o.year, o.month - 1, o.day, o.hour, o.minute, o.second, 59 o.microsecond / 1000) 60 elif isinstance(o, datetime.date): 61 return "Date(%d,%d,%d)" % (o.year, o.month - 1, o.day) 62 elif isinstance(o, datetime.time): 63 return [o.hour, o.minute, o.second] 64 else: 65 return super(DataTableJSONEncoder, self).default(o) 66 67 68 class DataTable(object): 69 """Wraps the data to convert to a Google Visualization API DataTable. 70 71 Create this object, populate it with data, then call one of the ToJS... 72 methods to return a string representation of the data in the format described. 73 74 You can clear all data from the object to reuse it, but you cannot clear 75 individual cells, rows, or columns. You also cannot modify the table schema 76 specified in the class constructor. 77 78 You can add new data one or more rows at a time. All data added to an 79 instantiated DataTable must conform to the schema passed in to __init__(). 80 81 You can reorder the columns in the output table, and also specify row sorting 82 order by column. The default column order is according to the original 83 table_description parameter. Default row sort order is ascending, by column 84 1 values. For a dictionary, we sort the keys for order. 85 86 The data and the table_description are closely tied, as described here: 87 88 The table schema is defined in the class constructor's table_description 89 parameter. The user defines each column using a tuple of 90 (id[, type[, label[, custom_properties]]]). The default value for type is 91 string, label is the same as ID if not specified, and custom properties is 92 an empty dictionary if not specified. 93 94 table_description is a dictionary or list, containing one or more column 95 descriptor tuples, nested dictionaries, and lists. Each dictionary key, list 96 element, or dictionary element must eventually be defined as 97 a column description tuple. Here's an example of a dictionary where the key 98 is a tuple, and the value is a list of two tuples: 99 {('a', 'number'): [('b', 'number'), ('c', 'string')]} 100 101 This flexibility in data entry enables you to build and manipulate your data 102 in a Python structure that makes sense for your program. 103 104 Add data to the table using the same nested design as the table's 105 table_description, replacing column descriptor tuples with cell data, and 106 each row is an element in the top level collection. This will be a bit 107 clearer after you look at the following examples showing the 108 table_description, matching data, and the resulting table: 109 110 Columns as list of tuples [col1, col2, col3] 111 table_description: [('a', 'number'), ('b', 'string')] 112 AppendData( [[1, 'z'], [2, 'w'], [4, 'o'], [5, 'k']] ) 113 Table: 114 a b <--- these are column ids/labels 115 1 z 116 2 w 117 4 o 118 5 k 119 120 Dictionary of columns, where key is a column, and value is a list of 121 columns {col1: [col2, col3]} 122 table_description: {('a', 'number'): [('b', 'number'), ('c', 'string')]} 123 AppendData( data: {1: [2, 'z'], 3: [4, 'w']} 124 Table: 125 a b c 126 1 2 z 127 3 4 w 128 129 Dictionary where key is a column, and the value is itself a dictionary of 130 columns {col1: {col2, col3}} 131 table_description: {('a', 'number'): {'b': 'number', 'c': 'string'}} 132 AppendData( data: {1: {'b': 2, 'c': 'z'}, 3: {'b': 4, 'c': 'w'}} 133 Table: 134 a b c 135 1 2 z 136 3 4 w 137 """ 138 139 def __init__(self, table_description, data=None, custom_properties=None): 140 """Initialize the data table from a table schema and (optionally) data. 141 142 See the class documentation for more information on table schema and data 143 values. 144 145 Args: 146 table_description: A table schema, following one of the formats described 147 in TableDescriptionParser(). Schemas describe the 148 column names, data types, and labels. See 149 TableDescriptionParser() for acceptable formats. 150 data: Optional. If given, fills the table with the given data. The data 151 structure must be consistent with schema in table_description. See 152 the class documentation for more information on acceptable data. You 153 can add data later by calling AppendData(). 154 custom_properties: Optional. A dictionary from string to string that 155 goes into the table's custom properties. This can be 156 later changed by changing self.custom_properties. 157 158 Raises: 159 DataTableException: Raised if the data and the description did not match, 160 or did not use the supported formats. 161 """ 162 self.__columns = self.TableDescriptionParser(table_description) 163 self.__data = [] 164 self.custom_properties = {} 165 if custom_properties is not None: 166 self.custom_properties = custom_properties 167 if data: 168 self.LoadData(data) 169 170 @staticmethod 171 def CoerceValue(value, value_type): 172 """Coerces a single value into the type expected for its column. 173 174 Internal helper method. 175 176 Args: 177 value: The value which should be converted 178 value_type: One of "string", "number", "boolean", "date", "datetime" or 179 "timeofday". 180 181 Returns: 182 An item of the Python type appropriate to the given value_type. Strings 183 are also converted to Unicode using UTF-8 encoding if necessary. 184 If a tuple is given, it should be in one of the following forms: 185 - (value, formatted value) 186 - (value, formatted value, custom properties) 187 where the formatted value is a string, and custom properties is a 188 dictionary of the custom properties for this cell. 189 To specify custom properties without specifying formatted value, one can 190 pass None as the formatted value. 191 One can also have a null-valued cell with formatted value and/or custom 192 properties by specifying None for the value. 193 This method ignores the custom properties except for checking that it is a 194 dictionary. The custom properties are handled in the ToJSon and ToJSCode 195 methods. 196 The real type of the given value is not strictly checked. For example, 197 any type can be used for string - as we simply take its str( ) and for 198 boolean value we just check "if value". 199 Examples: 200 CoerceValue(None, "string") returns None 201 CoerceValue((5, "5$"), "number") returns (5, "5$") 202 CoerceValue(100, "string") returns "100" 203 CoerceValue(0, "boolean") returns False 204 205 Raises: 206 DataTableException: The value and type did not match in a not-recoverable 207 way, for example given value 'abc' for type 'number'. 208 """ 209 if isinstance(value, tuple): 210 # In case of a tuple, we run the same function on the value itself and 211 # add the formatted value. 212 if (len(value) not in [2, 3] or 213 (len(value) == 3 and not isinstance(value[2], dict))): 214 raise DataTableException("Wrong format for value and formatting - %s." % 215 str(value)) 216 if not isinstance(value[1], types.StringTypes + (types.NoneType,)): 217 raise DataTableException("Formatted value is not string, given %s." % 218 type(value[1])) 219 js_value = DataTable.CoerceValue(value[0], value_type) 220 return (js_value,) + value[1:] 221 222 t_value = type(value) 223 if value is None: 224 return value 225 if value_type == "boolean": 226 return bool(value) 227 228 elif value_type == "number": 229 if isinstance(value, (int, long, float)): 230 return value 231 raise DataTableException("Wrong type %s when expected number" % t_value) 232 233 elif value_type == "string": 234 if isinstance(value, unicode): 235 return value 236 else: 237 return str(value).decode("utf-8") 238 239 elif value_type == "date": 240 if isinstance(value, datetime.datetime): 241 return datetime.date(value.year, value.month, value.day) 242 elif isinstance(value, datetime.date): 243 return value 244 else: 245 raise DataTableException("Wrong type %s when expected date" % t_value) 246 247 elif value_type == "timeofday": 248 if isinstance(value, datetime.datetime): 249 return datetime.time(value.hour, value.minute, value.second) 250 elif isinstance(value, datetime.time): 251 return value 252 else: 253 raise DataTableException("Wrong type %s when expected time" % t_value) 254 255 elif value_type == "datetime": 256 if isinstance(value, datetime.datetime): 257 return value 258 else: 259 raise DataTableException("Wrong type %s when expected datetime" % 260 t_value) 261 # If we got here, it means the given value_type was not one of the 262 # supported types. 263 raise DataTableException("Unsupported type %s" % value_type) 264 265 @staticmethod 266 def EscapeForJSCode(encoder, value): 267 if value is None: 268 return "null" 269 elif isinstance(value, datetime.datetime): 270 if value.microsecond == 0: 271 # If it's not ms-resolution, leave that out to save space. 272 return "new Date(%d,%d,%d,%d,%d,%d)" % (value.year, 273 value.month - 1, # To match JS 274 value.day, 275 value.hour, 276 value.minute, 277 value.second) 278 else: 279 return "new Date(%d,%d,%d,%d,%d,%d,%d)" % (value.year, 280 value.month - 1, # match JS 281 value.day, 282 value.hour, 283 value.minute, 284 value.second, 285 value.microsecond / 1000) 286 elif isinstance(value, datetime.date): 287 return "new Date(%d,%d,%d)" % (value.year, value.month - 1, value.day) 288 else: 289 return encoder.encode(value) 290 291 @staticmethod 292 def ToString(value): 293 if value is None: 294 return "(empty)" 295 elif isinstance(value, (datetime.datetime, 296 datetime.date, 297 datetime.time)): 298 return str(value) 299 elif isinstance(value, unicode): 300 return value 301 elif isinstance(value, bool): 302 return str(value).lower() 303 else: 304 return str(value).decode("utf-8") 305 306 @staticmethod 307 def ColumnTypeParser(description): 308 """Parses a single column description. Internal helper method. 309 310 Args: 311 description: a column description in the possible formats: 312 'id' 313 ('id',) 314 ('id', 'type') 315 ('id', 'type', 'label') 316 ('id', 'type', 'label', {'custom_prop1': 'custom_val1'}) 317 Returns: 318 Dictionary with the following keys: id, label, type, and 319 custom_properties where: 320 - If label not given, it equals the id. 321 - If type not given, string is used by default. 322 - If custom properties are not given, an empty dictionary is used by 323 default. 324 325 Raises: 326 DataTableException: The column description did not match the RE, or 327 unsupported type was passed. 328 """ 329 if not description: 330 raise DataTableException("Description error: empty description given") 331 332 if not isinstance(description, (types.StringTypes, tuple)): 333 raise DataTableException("Description error: expected either string or " 334 "tuple, got %s." % type(description)) 335 336 if isinstance(description, types.StringTypes): 337 description = (description,) 338 339 # According to the tuple's length, we fill the keys 340 # We verify everything is of type string 341 for elem in description[:3]: 342 if not isinstance(elem, types.StringTypes): 343 raise DataTableException("Description error: expected tuple of " 344 "strings, current element of type %s." % 345 type(elem)) 346 desc_dict = {"id": description[0], 347 "label": description[0], 348 "type": "string", 349 "custom_properties": {}} 350 if len(description) > 1: 351 desc_dict["type"] = description[1].lower() 352 if len(description) > 2: 353 desc_dict["label"] = description[2] 354 if len(description) > 3: 355 if not isinstance(description[3], dict): 356 raise DataTableException("Description error: expected custom " 357 "properties of type dict, current element " 358 "of type %s." % type(description[3])) 359 desc_dict["custom_properties"] = description[3] 360 if len(description) > 4: 361 raise DataTableException("Description error: tuple of length > 4") 362 if desc_dict["type"] not in ["string", "number", "boolean", 363 "date", "datetime", "timeofday"]: 364 raise DataTableException( 365 "Description error: unsupported type '%s'" % desc_dict["type"]) 366 return desc_dict 367 368 @staticmethod 369 def TableDescriptionParser(table_description, depth=0): 370 """Parses the table_description object for internal use. 371 372 Parses the user-submitted table description into an internal format used 373 by the Python DataTable class. Returns the flat list of parsed columns. 374 375 Args: 376 table_description: A description of the table which should comply 377 with one of the formats described below. 378 depth: Optional. The depth of the first level in the current description. 379 Used by recursive calls to this function. 380 381 Returns: 382 List of columns, where each column represented by a dictionary with the 383 keys: id, label, type, depth, container which means the following: 384 - id: the id of the column 385 - name: The name of the column 386 - type: The datatype of the elements in this column. Allowed types are 387 described in ColumnTypeParser(). 388 - depth: The depth of this column in the table description 389 - container: 'dict', 'iter' or 'scalar' for parsing the format easily. 390 - custom_properties: The custom properties for this column. 391 The returned description is flattened regardless of how it was given. 392 393 Raises: 394 DataTableException: Error in a column description or in the description 395 structure. 396 397 Examples: 398 A column description can be of the following forms: 399 'id' 400 ('id',) 401 ('id', 'type') 402 ('id', 'type', 'label') 403 ('id', 'type', 'label', {'custom_prop1': 'custom_val1'}) 404 or as a dictionary: 405 'id': 'type' 406 'id': ('type',) 407 'id': ('type', 'label') 408 'id': ('type', 'label', {'custom_prop1': 'custom_val1'}) 409 If the type is not specified, we treat it as string. 410 If no specific label is given, the label is simply the id. 411 If no custom properties are given, we use an empty dictionary. 412 413 input: [('a', 'date'), ('b', 'timeofday', 'b', {'foo': 'bar'})] 414 output: [{'id': 'a', 'label': 'a', 'type': 'date', 415 'depth': 0, 'container': 'iter', 'custom_properties': {}}, 416 {'id': 'b', 'label': 'b', 'type': 'timeofday', 417 'depth': 0, 'container': 'iter', 418 'custom_properties': {'foo': 'bar'}}] 419 420 input: {'a': [('b', 'number'), ('c', 'string', 'column c')]} 421 output: [{'id': 'a', 'label': 'a', 'type': 'string', 422 'depth': 0, 'container': 'dict', 'custom_properties': {}}, 423 {'id': 'b', 'label': 'b', 'type': 'number', 424 'depth': 1, 'container': 'iter', 'custom_properties': {}}, 425 {'id': 'c', 'label': 'column c', 'type': 'string', 426 'depth': 1, 'container': 'iter', 'custom_properties': {}}] 427 428 input: {('a', 'number', 'column a'): { 'b': 'number', 'c': 'string'}} 429 output: [{'id': 'a', 'label': 'column a', 'type': 'number', 430 'depth': 0, 'container': 'dict', 'custom_properties': {}}, 431 {'id': 'b', 'label': 'b', 'type': 'number', 432 'depth': 1, 'container': 'dict', 'custom_properties': {}}, 433 {'id': 'c', 'label': 'c', 'type': 'string', 434 'depth': 1, 'container': 'dict', 'custom_properties': {}}] 435 436 input: { ('w', 'string', 'word'): ('c', 'number', 'count') } 437 output: [{'id': 'w', 'label': 'word', 'type': 'string', 438 'depth': 0, 'container': 'dict', 'custom_properties': {}}, 439 {'id': 'c', 'label': 'count', 'type': 'number', 440 'depth': 1, 'container': 'scalar', 'custom_properties': {}}] 441 442 input: {'a': ('number', 'column a'), 'b': ('string', 'column b')} 443 output: [{'id': 'a', 'label': 'column a', 'type': 'number', 'depth': 0, 444 'container': 'dict', 'custom_properties': {}}, 445 {'id': 'b', 'label': 'column b', 'type': 'string', 'depth': 0, 446 'container': 'dict', 'custom_properties': {}} 447 448 NOTE: there might be ambiguity in the case of a dictionary representation 449 of a single column. For example, the following description can be parsed 450 in 2 different ways: {'a': ('b', 'c')} can be thought of a single column 451 with the id 'a', of type 'b' and the label 'c', or as 2 columns: one named 452 'a', and the other named 'b' of type 'c'. We choose the first option by 453 default, and in case the second option is the right one, it is possible to 454 make the key into a tuple (i.e. {('a',): ('b', 'c')}) or add more info 455 into the tuple, thus making it look like this: {'a': ('b', 'c', 'b', {})} 456 -- second 'b' is the label, and {} is the custom properties field. 457 """ 458 # For the recursion step, we check for a scalar object (string or tuple) 459 if isinstance(table_description, (types.StringTypes, tuple)): 460 parsed_col = DataTable.ColumnTypeParser(table_description) 461 parsed_col["depth"] = depth 462 parsed_col["container"] = "scalar" 463 return [parsed_col] 464 465 # Since it is not scalar, table_description must be iterable. 466 if not hasattr(table_description, "__iter__"): 467 raise DataTableException("Expected an iterable object, got %s" % 468 type(table_description)) 469 if not isinstance(table_description, dict): 470 # We expects a non-dictionary iterable item. 471 columns = [] 472 for desc in table_description: 473 parsed_col = DataTable.ColumnTypeParser(desc) 474 parsed_col["depth"] = depth 475 parsed_col["container"] = "iter" 476 columns.append(parsed_col) 477 if not columns: 478 raise DataTableException("Description iterable objects should not" 479 " be empty.") 480 return columns 481 # The other case is a dictionary 482 if not table_description: 483 raise DataTableException("Empty dictionaries are not allowed inside" 484 " description") 485 486 # To differentiate between the two cases of more levels below or this is 487 # the most inner dictionary, we consider the number of keys (more then one 488 # key is indication for most inner dictionary) and the type of the key and 489 # value in case of only 1 key (if the type of key is string and the type of 490 # the value is a tuple of 0-3 items, we assume this is the most inner 491 # dictionary). 492 # NOTE: this way of differentiating might create ambiguity. See docs. 493 if (len(table_description) != 1 or 494 (isinstance(table_description.keys()[0], types.StringTypes) and 495 isinstance(table_description.values()[0], tuple) and 496 len(table_description.values()[0]) < 4)): 497 # This is the most inner dictionary. Parsing types. 498 columns = [] 499 # We sort the items, equivalent to sort the keys since they are unique 500 for key, value in sorted(table_description.items()): 501 # We parse the column type as (key, type) or (key, type, label) using 502 # ColumnTypeParser. 503 if isinstance(value, tuple): 504 parsed_col = DataTable.ColumnTypeParser((key,) + value) 505 else: 506 parsed_col = DataTable.ColumnTypeParser((key, value)) 507 parsed_col["depth"] = depth 508 parsed_col["container"] = "dict" 509 columns.append(parsed_col) 510 return columns 511 # This is an outer dictionary, must have at most one key. 512 parsed_col = DataTable.ColumnTypeParser(table_description.keys()[0]) 513 parsed_col["depth"] = depth 514 parsed_col["container"] = "dict" 515 return ([parsed_col] + 516 DataTable.TableDescriptionParser(table_description.values()[0], 517 depth=depth + 1)) 518 519 @property 520 def columns(self): 521 """Returns the parsed table description.""" 522 return self.__columns 523 524 def NumberOfRows(self): 525 """Returns the number of rows in the current data stored in the table.""" 526 return len(self.__data) 527 528 def SetRowsCustomProperties(self, rows, custom_properties): 529 """Sets the custom properties for given row(s). 530 531 Can accept a single row or an iterable of rows. 532 Sets the given custom properties for all specified rows. 533 534 Args: 535 rows: The row, or rows, to set the custom properties for. 536 custom_properties: A string to string dictionary of custom properties to 537 set for all rows. 538 """ 539 if not hasattr(rows, "__iter__"): 540 rows = [rows] 541 for row in rows: 542 self.__data[row] = (self.__data[row][0], custom_properties) 543 544 def LoadData(self, data, custom_properties=None): 545 """Loads new rows to the data table, clearing existing rows. 546 547 May also set the custom_properties for the added rows. The given custom 548 properties dictionary specifies the dictionary that will be used for *all* 549 given rows. 550 551 Args: 552 data: The rows that the table will contain. 553 custom_properties: A dictionary of string to string to set as the custom 554 properties for all rows. 555 """ 556 self.__data = [] 557 self.AppendData(data, custom_properties) 558 559 def AppendData(self, data, custom_properties=None): 560 """Appends new data to the table. 561 562 Data is appended in rows. Data must comply with 563 the table schema passed in to __init__(). See CoerceValue() for a list 564 of acceptable data types. See the class documentation for more information 565 and examples of schema and data values. 566 567 Args: 568 data: The row to add to the table. The data must conform to the table 569 description format. 570 custom_properties: A dictionary of string to string, representing the 571 custom properties to add to all the rows. 572 573 Raises: 574 DataTableException: The data structure does not match the description. 575 """ 576 # If the maximal depth is 0, we simply iterate over the data table 577 # lines and insert them using _InnerAppendData. Otherwise, we simply 578 # let the _InnerAppendData handle all the levels. 579 if not self.__columns[-1]["depth"]: 580 for row in data: 581 self._InnerAppendData(({}, custom_properties), row, 0) 582 else: 583 self._InnerAppendData(({}, custom_properties), data, 0) 584 585 def _InnerAppendData(self, prev_col_values, data, col_index): 586 """Inner function to assist LoadData.""" 587 # We first check that col_index has not exceeded the columns size 588 if col_index >= len(self.__columns): 589 raise DataTableException("The data does not match description, too deep") 590 591 # Dealing with the scalar case, the data is the last value. 592 if self.__columns[col_index]["container"] == "scalar": 593 prev_col_values[0][self.__columns[col_index]["id"]] = data 594 self.__data.append(prev_col_values) 595 return 596 597 if self.__columns[col_index]["container"] == "iter": 598 if not hasattr(data, "__iter__") or isinstance(data, dict): 599 raise DataTableException("Expected iterable object, got %s" % 600 type(data)) 601 # We only need to insert the rest of the columns 602 # If there are less items than expected, we only add what there is. 603 for value in data: 604 if col_index >= len(self.__columns): 605 raise DataTableException("Too many elements given in data") 606 prev_col_values[0][self.__columns[col_index]["id"]] = value 607 col_index += 1 608 self.__data.append(prev_col_values) 609 return 610 611 # We know the current level is a dictionary, we verify the type. 612 if not isinstance(data, dict): 613 raise DataTableException("Expected dictionary at current level, got %s" % 614 type(data)) 615 # We check if this is the last level 616 if self.__columns[col_index]["depth"] == self.__columns[-1]["depth"]: 617 # We need to add the keys in the dictionary as they are 618 for col in self.__columns[col_index:]: 619 if col["id"] in data: 620 prev_col_values[0][col["id"]] = data[col["id"]] 621 self.__data.append(prev_col_values) 622 return 623 624 # We have a dictionary in an inner depth level. 625 if not data.keys(): 626 # In case this is an empty dictionary, we add a record with the columns 627 # filled only until this point. 628 self.__data.append(prev_col_values) 629 else: 630 for key in sorted(data): 631 col_values = dict(prev_col_values[0]) 632 col_values[self.__columns[col_index]["id"]] = key 633 self._InnerAppendData((col_values, prev_col_values[1]), 634 data[key], col_index + 1) 635 636 def _PreparedData(self, order_by=()): 637 """Prepares the data for enumeration - sorting it by order_by. 638 639 Args: 640 order_by: Optional. Specifies the name of the column(s) to sort by, and 641 (optionally) which direction to sort in. Default sort direction 642 is asc. Following formats are accepted: 643 "string_col_name" -- For a single key in default (asc) order. 644 ("string_col_name", "asc|desc") -- For a single key. 645 [("col_1","asc|desc"), ("col_2","asc|desc")] -- For more than 646 one column, an array of tuples of (col_name, "asc|desc"). 647 648 Returns: 649 The data sorted by the keys given. 650 651 Raises: 652 DataTableException: Sort direction not in 'asc' or 'desc' 653 """ 654 if not order_by: 655 return self.__data 656 657 proper_sort_keys = [] 658 if isinstance(order_by, types.StringTypes) or ( 659 isinstance(order_by, tuple) and len(order_by) == 2 and 660 order_by[1].lower() in ["asc", "desc"]): 661 order_by = (order_by,) 662 for key in order_by: 663 if isinstance(key, types.StringTypes): 664 proper_sort_keys.append((key, 1)) 665 elif (isinstance(key, (list, tuple)) and len(key) == 2 and 666 key[1].lower() in ("asc", "desc")): 667 proper_sort_keys.append((key[0], key[1].lower() == "asc" and 1 or -1)) 668 else: 669 raise DataTableException("Expected tuple with second value: " 670 "'asc' or 'desc'") 671 672 def SortCmpFunc(row1, row2): 673 """cmp function for sorted. Compares by keys and 'asc'/'desc' keywords.""" 674 for key, asc_mult in proper_sort_keys: 675 cmp_result = asc_mult * cmp(row1[0].get(key), row2[0].get(key)) 676 if cmp_result: 677 return cmp_result 678 return 0 679 680 return sorted(self.__data, cmp=SortCmpFunc) 681 682 def ToJSCode(self, name, columns_order=None, order_by=()): 683 """Writes the data table as a JS code string. 684 685 This method writes a string of JS code that can be run to 686 generate a DataTable with the specified data. Typically used for debugging 687 only. 688 689 Args: 690 name: The name of the table. The name would be used as the DataTable's 691 variable name in the created JS code. 692 columns_order: Optional. Specifies the order of columns in the 693 output table. Specify a list of all column IDs in the order 694 in which you want the table created. 695 Note that you must list all column IDs in this parameter, 696 if you use it. 697 order_by: Optional. Specifies the name of the column(s) to sort by. 698 Passed as is to _PreparedData. 699 700 Returns: 701 A string of JS code that, when run, generates a DataTable with the given 702 name and the data stored in the DataTable object. 703 Example result: 704 "var tab1 = new google.visualization.DataTable(); 705 tab1.addColumn("string", "a", "a"); 706 tab1.addColumn("number", "b", "b"); 707 tab1.addColumn("boolean", "c", "c"); 708 tab1.addRows(10); 709 tab1.setCell(0, 0, "a"); 710 tab1.setCell(0, 1, 1, null, {"foo": "bar"}); 711 tab1.setCell(0, 2, true); 712 ... 713 tab1.setCell(9, 0, "c"); 714 tab1.setCell(9, 1, 3, "3$"); 715 tab1.setCell(9, 2, false);" 716 717 Raises: 718 DataTableException: The data does not match the type. 719 """ 720 721 encoder = DataTableJSONEncoder() 722 723 if columns_order is None: 724 columns_order = [col["id"] for col in self.__columns] 725 col_dict = dict([(col["id"], col) for col in self.__columns]) 726 727 # We first create the table with the given name 728 jscode = "var %s = new google.visualization.DataTable();\n" % name 729 if self.custom_properties: 730 jscode += "%s.setTableProperties(%s);\n" % ( 731 name, encoder.encode(self.custom_properties)) 732 733 # We add the columns to the table 734 for i, col in enumerate(columns_order): 735 jscode += "%s.addColumn(%s, %s, %s);\n" % ( 736 name, 737 encoder.encode(col_dict[col]["type"]), 738 encoder.encode(col_dict[col]["label"]), 739 encoder.encode(col_dict[col]["id"])) 740 if col_dict[col]["custom_properties"]: 741 jscode += "%s.setColumnProperties(%d, %s);\n" % ( 742 name, i, encoder.encode(col_dict[col]["custom_properties"])) 743 jscode += "%s.addRows(%d);\n" % (name, len(self.__data)) 744 745 # We now go over the data and add each row 746 for (i, (row, cp)) in enumerate(self._PreparedData(order_by)): 747 # We add all the elements of this row by their order 748 for (j, col) in enumerate(columns_order): 749 if col not in row or row[col] is None: 750 continue 751 value = self.CoerceValue(row[col], col_dict[col]["type"]) 752 if isinstance(value, tuple): 753 cell_cp = "" 754 if len(value) == 3: 755 cell_cp = ", %s" % encoder.encode(row[col][2]) 756 # We have a formatted value or custom property as well 757 jscode += ("%s.setCell(%d, %d, %s, %s%s);\n" % 758 (name, i, j, 759 self.EscapeForJSCode(encoder, value[0]), 760 self.EscapeForJSCode(encoder, value[1]), cell_cp)) 761 else: 762 jscode += "%s.setCell(%d, %d, %s);\n" % ( 763 name, i, j, self.EscapeForJSCode(encoder, value)) 764 if cp: 765 jscode += "%s.setRowProperties(%d, %s);\n" % ( 766 name, i, encoder.encode(cp)) 767 return jscode 768 769 def ToHtml(self, columns_order=None, order_by=()): 770 """Writes the data table as an HTML table code string. 771 772 Args: 773 columns_order: Optional. Specifies the order of columns in the 774 output table. Specify a list of all column IDs in the order 775 in which you want the table created. 776 Note that you must list all column IDs in this parameter, 777 if you use it. 778 order_by: Optional. Specifies the name of the column(s) to sort by. 779 Passed as is to _PreparedData. 780 781 Returns: 782 An HTML table code string. 783 Example result (the result is without the newlines): 784 <html><body><table border="1"> 785 <thead><tr><th>a</th><th>b</th><th>c</th></tr></thead> 786 <tbody> 787 <tr><td>1</td><td>"z"</td><td>2</td></tr> 788 <tr><td>"3$"</td><td>"w"</td><td></td></tr> 789 </tbody> 790 </table></body></html> 791 792 Raises: 793 DataTableException: The data does not match the type. 794 """ 795 table_template = "<html><body><table border=\"1\">%s</table></body></html>" 796 columns_template = "<thead><tr>%s</tr></thead>" 797 rows_template = "<tbody>%s</tbody>" 798 row_template = "<tr>%s</tr>" 799 header_cell_template = "<th>%s</th>" 800 cell_template = "<td>%s</td>" 801 802 if columns_order is None: 803 columns_order = [col["id"] for col in self.__columns] 804 col_dict = dict([(col["id"], col) for col in self.__columns]) 805 806 columns_list = [] 807 for col in columns_order: 808 columns_list.append(header_cell_template % 809 cgi.escape(col_dict[col]["label"])) 810 columns_html = columns_template % "".join(columns_list) 811 812 rows_list = [] 813 # We now go over the data and add each row 814 for row, unused_cp in self._PreparedData(order_by): 815 cells_list = [] 816 # We add all the elements of this row by their order 817 for col in columns_order: 818 # For empty string we want empty quotes (""). 819 value = "" 820 if col in row and row[col] is not None: 821 value = self.CoerceValue(row[col], col_dict[col]["type"]) 822 if isinstance(value, tuple): 823 # We have a formatted value and we're going to use it 824 cells_list.append(cell_template % cgi.escape(self.ToString(value[1]))) 825 else: 826 cells_list.append(cell_template % cgi.escape(self.ToString(value))) 827 rows_list.append(row_template % "".join(cells_list)) 828 rows_html = rows_template % "".join(rows_list) 829 830 return table_template % (columns_html + rows_html) 831 832 def ToCsv(self, columns_order=None, order_by=(), separator=","): 833 """Writes the data table as a CSV string. 834 835 Output is encoded in UTF-8 because the Python "csv" module can't handle 836 Unicode properly according to its documentation. 837 838 Args: 839 columns_order: Optional. Specifies the order of columns in the 840 output table. Specify a list of all column IDs in the order 841 in which you want the table created. 842 Note that you must list all column IDs in this parameter, 843 if you use it. 844 order_by: Optional. Specifies the name of the column(s) to sort by. 845 Passed as is to _PreparedData. 846 separator: Optional. The separator to use between the values. 847 848 Returns: 849 A CSV string representing the table. 850 Example result: 851 'a','b','c' 852 1,'z',2 853 3,'w','' 854 855 Raises: 856 DataTableException: The data does not match the type. 857 """ 858 859 csv_buffer = cStringIO.StringIO() 860 writer = csv.writer(csv_buffer, delimiter=separator) 861 862 if columns_order is None: 863 columns_order = [col["id"] for col in self.__columns] 864 col_dict = dict([(col["id"], col) for col in self.__columns]) 865 866 writer.writerow([col_dict[col]["label"].encode("utf-8") 867 for col in columns_order]) 868 869 # We now go over the data and add each row 870 for row, unused_cp in self._PreparedData(order_by): 871 cells_list = [] 872 # We add all the elements of this row by their order 873 for col in columns_order: 874 value = "" 875 if col in row and row[col] is not None: 876 value = self.CoerceValue(row[col], col_dict[col]["type"]) 877 if isinstance(value, tuple): 878 # We have a formatted value. Using it only for date/time types. 879 if col_dict[col]["type"] in ["date", "datetime", "timeofday"]: 880 cells_list.append(self.ToString(value[1]).encode("utf-8")) 881 else: 882 cells_list.append(self.ToString(value[0]).encode("utf-8")) 883 else: 884 cells_list.append(self.ToString(value).encode("utf-8")) 885 writer.writerow(cells_list) 886 return csv_buffer.getvalue() 887 888 def ToTsvExcel(self, columns_order=None, order_by=()): 889 """Returns a file in tab-separated-format readable by MS Excel. 890 891 Returns a file in UTF-16 little endian encoding, with tabs separating the 892 values. 893 894 Args: 895 columns_order: Delegated to ToCsv. 896 order_by: Delegated to ToCsv. 897 898 Returns: 899 A tab-separated little endian UTF16 file representing the table. 900 """ 901 return (self.ToCsv(columns_order, order_by, separator="\t") 902 .decode("utf-8").encode("UTF-16LE")) 903 904 def _ToJSonObj(self, columns_order=None, order_by=()): 905 """Returns an object suitable to be converted to JSON. 906 907 Args: 908 columns_order: Optional. A list of all column IDs in the order in which 909 you want them created in the output table. If specified, 910 all column IDs must be present. 911 order_by: Optional. Specifies the name of the column(s) to sort by. 912 Passed as is to _PreparedData(). 913 914 Returns: 915 A dictionary object for use by ToJSon or ToJSonResponse. 916 """ 917 if columns_order is None: 918 columns_order = [col["id"] for col in self.__columns] 919 col_dict = dict([(col["id"], col) for col in self.__columns]) 920 921 # Creating the column JSON objects 922 col_objs = [] 923 for col_id in columns_order: 924 col_obj = {"id": col_dict[col_id]["id"], 925 "label": col_dict[col_id]["label"], 926 "type": col_dict[col_id]["type"]} 927 if col_dict[col_id]["custom_properties"]: 928 col_obj["p"] = col_dict[col_id]["custom_properties"] 929 col_objs.append(col_obj) 930 931 # Creating the rows jsons 932 row_objs = [] 933 for row, cp in self._PreparedData(order_by): 934 cell_objs = [] 935 for col in columns_order: 936 value = self.CoerceValue(row.get(col, None), col_dict[col]["type"]) 937 if value is None: 938 cell_obj = None 939 elif isinstance(value, tuple): 940 cell_obj = {"v": value[0]} 941 if len(value) > 1 and value[1] is not None: 942 cell_obj["f"] = value[1] 943 if len(value) == 3: 944 cell_obj["p"] = value[2] 945 else: 946 cell_obj = {"v": value} 947 cell_objs.append(cell_obj) 948 row_obj = {"c": cell_objs} 949 if cp: 950 row_obj["p"] = cp 951 row_objs.append(row_obj) 952 953 json_obj = {"cols": col_objs, "rows": row_objs} 954 if self.custom_properties: 955 json_obj["p"] = self.custom_properties 956 957 return json_obj 958 959 def ToJSon(self, columns_order=None, order_by=()): 960 """Returns a string that can be used in a JS DataTable constructor. 961 962 This method writes a JSON string that can be passed directly into a Google 963 Visualization API DataTable constructor. Use this output if you are 964 hosting the visualization HTML on your site, and want to code the data 965 table in Python. Pass this string into the 966 google.visualization.DataTable constructor, e.g,: 967 ... on my page that hosts my visualization ... 968 google.setOnLoadCallback(drawTable); 969 function drawTable() { 970 var data = new google.visualization.DataTable(_my_JSon_string, 0.6); 971 myTable.draw(data); 972 } 973 974 Args: 975 columns_order: Optional. Specifies the order of columns in the 976 output table. Specify a list of all column IDs in the order 977 in which you want the table created. 978 Note that you must list all column IDs in this parameter, 979 if you use it. 980 order_by: Optional. Specifies the name of the column(s) to sort by. 981 Passed as is to _PreparedData(). 982 983 Returns: 984 A JSon constructor string to generate a JS DataTable with the data 985 stored in the DataTable object. 986 Example result (the result is without the newlines): 987 {cols: [{id:"a",label:"a",type:"number"}, 988 {id:"b",label:"b",type:"string"}, 989 {id:"c",label:"c",type:"number"}], 990 rows: [{c:[{v:1},{v:"z"},{v:2}]}, c:{[{v:3,f:"3$"},{v:"w"},{v:null}]}], 991 p: {'foo': 'bar'}} 992 993 Raises: 994 DataTableException: The data does not match the type. 995 """ 996 997 encoder = DataTableJSONEncoder() 998 return encoder.encode( 999 self._ToJSonObj(columns_order, order_by)).encode("utf-8") 1000 1001 def ToJSonResponse(self, columns_order=None, order_by=(), req_id=0, 1002 response_handler="google.visualization.Query.setResponse"): 1003 """Writes a table as a JSON response that can be returned as-is to a client. 1004 1005 This method writes a JSON response to return to a client in response to a 1006 Google Visualization API query. This string can be processed by the calling 1007 page, and is used to deliver a data table to a visualization hosted on 1008 a different page. 1009 1010 Args: 1011 columns_order: Optional. Passed straight to self.ToJSon(). 1012 order_by: Optional. Passed straight to self.ToJSon(). 1013 req_id: Optional. The response id, as retrieved by the request. 1014 response_handler: Optional. The response handler, as retrieved by the 1015 request. 1016 1017 Returns: 1018 A JSON response string to be received by JS the visualization Query 1019 object. This response would be translated into a DataTable on the 1020 client side. 1021 Example result (newlines added for readability): 1022 google.visualization.Query.setResponse({ 1023 'version':'0.6', 'reqId':'0', 'status':'OK', 1024 'table': {cols: [...], rows: [...]}}); 1025 1026 Note: The URL returning this string can be used as a data source by Google 1027 Visualization Gadgets or from JS code. 1028 """ 1029 1030 response_obj = { 1031 "version": "0.6", 1032 "reqId": str(req_id), 1033 "table": self._ToJSonObj(columns_order, order_by), 1034 "status": "ok" 1035 } 1036 encoder = DataTableJSONEncoder() 1037 return "%s(%s);" % (response_handler, 1038 encoder.encode(response_obj).encode("utf-8")) 1039 1040 def ToResponse(self, columns_order=None, order_by=(), tqx=""): 1041 """Writes the right response according to the request string passed in tqx. 1042 1043 This method parses the tqx request string (format of which is defined in 1044 the documentation for implementing a data source of Google Visualization), 1045 and returns the right response according to the request. 1046 It parses out the "out" parameter of tqx, calls the relevant response 1047 (ToJSonResponse() for "json", ToCsv() for "csv", ToHtml() for "html", 1048 ToTsvExcel() for "tsv-excel") and passes the response function the rest of 1049 the relevant request keys. 1050 1051 Args: 1052 columns_order: Optional. Passed as is to the relevant response function. 1053 order_by: Optional. Passed as is to the relevant response function. 1054 tqx: Optional. The request string as received by HTTP GET. Should be in 1055 the format "key1:value1;key2:value2...". All keys have a default 1056 value, so an empty string will just do the default (which is calling 1057 ToJSonResponse() with no extra parameters). 1058 1059 Returns: 1060 A response string, as returned by the relevant response function. 1061 1062 Raises: 1063 DataTableException: One of the parameters passed in tqx is not supported. 1064 """ 1065 tqx_dict = {} 1066 if tqx: 1067 tqx_dict = dict(opt.split(":") for opt in tqx.split(";")) 1068 if tqx_dict.get("version", "0.6") != "0.6": 1069 raise DataTableException( 1070 "Version (%s) passed by request is not supported." 1071 % tqx_dict["version"]) 1072 1073 if tqx_dict.get("out", "json") == "json": 1074 response_handler = tqx_dict.get("responseHandler", 1075 "google.visualization.Query.setResponse") 1076 return self.ToJSonResponse(columns_order, order_by, 1077 req_id=tqx_dict.get("reqId", 0), 1078 response_handler=response_handler) 1079 elif tqx_dict["out"] == "html": 1080 return self.ToHtml(columns_order, order_by) 1081 elif tqx_dict["out"] == "csv": 1082 return self.ToCsv(columns_order, order_by) 1083 elif tqx_dict["out"] == "tsv-excel": 1084 return self.ToTsvExcel(columns_order, order_by) 1085 else: 1086 raise DataTableException( 1087 "'out' parameter: '%s' is not supported" % tqx_dict["out"])