3 # Copyright (C) 2009 Google Inc.
5 # Licensed under the Apache License, Version 2.0 (the "License");
6 # you may not use this file except in compliance with the License.
7 # You may obtain a copy of the License at
9 # http://www.apache.org/licenses/LICENSE-2.0
11 # Unless required by applicable law or agreed to in writing, software
12 # distributed under the License is distributed on an "AS IS" BASIS,
13 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 # See the License for the specific language governing permissions and
15 # limitations under the License.
17 """Converts Python data into data for Google Visualization API clients.
19 This library can be used to create a google.visualization.DataTable usable by
20 visualizations built on the Google Visualization API. Output formats are raw
21 JSON, JSON response, and JavaScript.
23 See http://code.google.com/apis/visualization/ for documentation on the
24 Google Visualization API.
27 __author__
= "Amit Weinstein, Misha Seltzer"
34 class DataTableException(Exception):
35 """The general exception object thrown by DataTable."""
39 class DataTable(object):
40 """Wraps the data to convert to a Google Visualization API DataTable.
42 Create this object, populate it with data, then call one of the ToJS...
43 methods to return a string representation of the data in the format described.
45 You can clear all data from the object to reuse it, but you cannot clear
46 individual cells, rows, or columns. You also cannot modify the table schema
47 specified in the class constructor.
49 You can add new data one or more rows at a time. All data added to an
50 instantiated DataTable must conform to the schema passed in to __init__().
52 You can reorder the columns in the output table, and also specify row sorting
53 order by column. The default column order is according to the original
54 table_description parameter. Default row sort order is ascending, by column
55 1 values. For a dictionary, we sort the keys for order.
57 The data and the table_description are closely tied, as described here:
59 The table schema is defined in the class constructor's table_description
60 parameter. The user defines each column using a tuple of
61 (id[, type[, label[, custom_properties]]]). The default value for type is
62 string, label is the same as ID if not specified, and custom properties is
63 an empty dictionary if not specified.
65 table_description is a dictionary or list, containing one or more column
66 descriptor tuples, nested dictionaries, and lists. Each dictionary key, list
67 element, or dictionary element must eventually be defined as
68 a column description tuple. Here's an example of a dictionary where the key
69 is a tuple, and the value is a list of two tuples:
70 {('a', 'number'): [('b', 'number'), ('c', 'string')]}
72 This flexibility in data entry enables you to build and manipulate your data
73 in a Python structure that makes sense for your program.
75 Add data to the table using the same nested design as the table's
76 table_description, replacing column descriptor tuples with cell data, and
77 each row is an element in the top level collection. This will be a bit
78 clearer after you look at the following examples showing the
79 table_description, matching data, and the resulting table:
81 Columns as list of tuples [col1, col2, col3]
82 table_description: [('a', 'number'), ('b', 'string')]
83 AppendData( [[1, 'z'], [2, 'w'], [4, 'o'], [5, 'k']] )
85 a b <--- these are column ids/labels
91 Dictionary of columns, where key is a column, and value is a list of
92 columns {col1: [col2, col3]}
93 table_description: {('a', 'number'): [('b', 'number'), ('c', 'string')]}
94 AppendData( data: {1: [2, 'z'], 3: [4, 'w']}
100 Dictionary where key is a column, and the value is itself a dictionary of
101 columns {col1: {col2, col3}}
102 table_description: {('a', 'number'): {'b': 'number', 'c': 'string'}}
103 AppendData( data: {1: {'b': 2, 'c': 'z'}, 3: {'b': 4, 'c': 'w'}}
110 def __init__(self
, table_description
, data
=None, custom_properties
=None):
111 """Initialize the data table from a table schema and (optionally) data.
113 See the class documentation for more information on table schema and data
117 table_description: A table schema, following one of the formats described
118 in TableDescriptionParser(). Schemas describe the
119 column names, data types, and labels. See
120 TableDescriptionParser() for acceptable formats.
121 data: Optional. If given, fills the table with the given data. The data
122 structure must be consistent with schema in table_description. See
123 the class documentation for more information on acceptable data. You
124 can add data later by calling AppendData().
125 custom_properties: Optional. A dictionary from string to string that
126 goes into the table's custom properties. This can be
127 later changed by changing self.custom_properties.
130 DataTableException: Raised if the data and the description did not match,
131 or did not use the supported formats.
133 self
.__columns
= self
.TableDescriptionParser(table_description
)
135 self
.custom_properties
= {}
136 if custom_properties
is not None:
137 self
.custom_properties
= custom_properties
142 def _EscapeValueForCsv(v
):
143 """Escapes the value for use in a CSV file.
145 Puts the string in double-quotes, and escapes any inner double-quotes by
149 v: The value to escape.
154 return '"%s"' % v
.replace('"', '""')
158 """Puts the string in quotes, and escapes any inner quotes and slashes."""
159 if isinstance(v
, unicode):
160 # Here we use repr as in the usual case, but on unicode strings, it
161 # also escapes the unicode characters (which we want to leave as is).
162 # So, after repr() we decode using raw-unicode-escape, which decodes
163 # only the unicode characters, and leaves all the rest (", ', \n and
165 # We don't take the first character, because repr adds a u in the
166 # beginning of the string (usual repr output for unicode is u'...').
167 return repr(v
).decode("raw-unicode-escape")[1:]
168 # Here we use python built-in escaping mechanism for string using repr.
172 def _EscapeCustomProperties(custom_properties
):
173 """Escapes the custom properties dictionary."""
175 for key
, value
in custom_properties
.iteritems():
176 l
.append("%s:%s" % (DataTable
._EscapeValue
(key
),
177 DataTable
._EscapeValue
(value
)))
178 return "{%s}" % ",".join(l
)
181 def SingleValueToJS(value
, value_type
, escape_func
=None):
182 """Translates a single value and type into a JS value.
184 Internal helper method.
187 value: The value which should be converted
188 value_type: One of "string", "number", "boolean", "date", "datetime" or
190 escape_func: The function to use for escaping strings.
193 The proper JS format (as string) of the given value according to the
194 given value_type. For None, we simply return "null".
195 If a tuple is given, it should be in one of the following forms:
196 - (value, formatted value)
197 - (value, formatted value, custom properties)
198 where the formatted value is a string, and custom properties is a
199 dictionary of the custom properties for this cell.
200 To specify custom properties without specifying formatted value, one can
201 pass None as the formatted value.
202 One can also have a null-valued cell with formatted value and/or custom
203 properties by specifying None for the value.
204 This method ignores the custom properties except for checking that it is a
205 dictionary. The custom properties are handled in the ToJSon and ToJSCode
207 The real type of the given value is not strictly checked. For example,
208 any type can be used for string - as we simply take its str( ) and for
209 boolean value we just check "if value".
211 SingleValueToJS(None, "boolean") returns "null"
212 SingleValueToJS(False, "boolean") returns "false"
213 SingleValueToJS((5, "5$"), "number") returns ("5", "'5$'")
214 SingleValueToJS((None, "5$"), "number") returns ("null", "'5$'")
217 DataTableException: The value and type did not match in a not-recoverable
218 way, for example given value 'abc' for type 'number'.
220 if escape_func
is None:
221 escape_func
= DataTable
._EscapeValue
222 if isinstance(value
, tuple):
223 # In case of a tuple, we run the same function on the value itself and
224 # add the formatted value.
225 if (len(value
) not in [2, 3] or
226 (len(value
) == 3 and not isinstance(value
[2], dict))):
227 raise DataTableException("Wrong format for value and formatting - %s." %
229 if not isinstance(value
[1], types
.StringTypes
+ (types
.NoneType
,)):
230 raise DataTableException("Formatted value is not string, given %s." %
232 js_value
= DataTable
.SingleValueToJS(value
[0], value_type
)
234 return (js_value
, None)
235 return (js_value
, escape_func(value
[1]))
237 # The standard case - no formatting.
238 t_value
= type(value
)
241 if value_type
== "boolean":
246 elif value_type
== "number":
247 if isinstance(value
, (int, long, float)):
249 raise DataTableException("Wrong type %s when expected number" % t_value
)
251 elif value_type
== "string":
252 if isinstance(value
, tuple):
253 raise DataTableException("Tuple is not allowed as string value.")
254 return escape_func(value
)
256 elif value_type
== "date":
257 if not isinstance(value
, (datetime
.date
, datetime
.datetime
)):
258 raise DataTableException("Wrong type %s when expected date" % t_value
)
259 # We need to shift the month by 1 to match JS Date format
260 return "new Date(%d,%d,%d)" % (value
.year
, value
.month
- 1, value
.day
)
262 elif value_type
== "timeofday":
263 if not isinstance(value
, (datetime
.time
, datetime
.datetime
)):
264 raise DataTableException("Wrong type %s when expected time" % t_value
)
265 return "[%d,%d,%d]" % (value
.hour
, value
.minute
, value
.second
)
267 elif value_type
== "datetime":
268 if not isinstance(value
, datetime
.datetime
):
269 raise DataTableException("Wrong type %s when expected datetime" %
271 return "new Date(%d,%d,%d,%d,%d,%d)" % (value
.year
,
272 value
.month
- 1, # To match JS
277 # If we got here, it means the given value_type was not one of the
279 raise DataTableException("Unsupported type %s" % value_type
)
282 def ColumnTypeParser(description
):
283 """Parses a single column description. Internal helper method.
286 description: a column description in the possible formats:
290 ('id', 'type', 'label')
291 ('id', 'type', 'label', {'custom_prop1': 'custom_val1'})
293 Dictionary with the following keys: id, label, type, and
294 custom_properties where:
295 - If label not given, it equals the id.
296 - If type not given, string is used by default.
297 - If custom properties are not given, an empty dictionary is used by
301 DataTableException: The column description did not match the RE.
304 raise DataTableException("Description error: empty description given")
306 if not isinstance(description
, (types
.StringTypes
, tuple)):
307 raise DataTableException("Description error: expected either string or "
308 "tuple, got %s." % type(description
))
310 if isinstance(description
, types
.StringTypes
):
311 description
= (description
,)
313 # According to the tuple's length, we fill the keys
314 # We verify everything is of type string
315 for elem
in description
[:3]:
316 if not isinstance(elem
, types
.StringTypes
):
317 raise DataTableException("Description error: expected tuple of "
318 "strings, current element of type %s." %
320 desc_dict
= {"id": description
[0],
321 "label": description
[0],
323 "custom_properties": {}}
324 if len(description
) > 1:
325 desc_dict
["type"] = description
[1].lower()
326 if len(description
) > 2:
327 desc_dict
["label"] = description
[2]
328 if len(description
) > 3:
329 if not isinstance(description
[3], dict):
330 raise DataTableException("Description error: expected custom "
331 "properties of type dict, current element "
332 "of type %s." % type(description
[3]))
333 desc_dict
["custom_properties"] = description
[3]
334 if len(description
) > 4:
335 raise DataTableException("Description error: tuple of length > 4")
339 def TableDescriptionParser(table_description
, depth
=0):
340 """Parses the table_description object for internal use.
342 Parses the user-submitted table description into an internal format used
343 by the Python DataTable class. Returns the flat list of parsed columns.
346 table_description: A description of the table which should comply
347 with one of the formats described below.
348 depth: Optional. The depth of the first level in the current description.
349 Used by recursive calls to this function.
352 List of columns, where each column represented by a dictionary with the
353 keys: id, label, type, depth, container which means the following:
354 - id: the id of the column
355 - name: The name of the column
356 - type: The datatype of the elements in this column. Allowed types are
357 described in ColumnTypeParser().
358 - depth: The depth of this column in the table description
359 - container: 'dict', 'iter' or 'scalar' for parsing the format easily.
360 - custom_properties: The custom properties for this column.
361 The returned description is flattened regardless of how it was given.
364 DataTableException: Error in a column description or in the description
368 A column description can be of the following forms:
372 ('id', 'type', 'label')
373 ('id', 'type', 'label', {'custom_prop1': 'custom_val1'})
377 'id': ('type', 'label')
378 'id': ('type', 'label', {'custom_prop1': 'custom_val1'})
379 If the type is not specified, we treat it as string.
380 If no specific label is given, the label is simply the id.
381 If no custom properties are given, we use an empty dictionary.
383 input: [('a', 'date'), ('b', 'timeofday', 'b', {'foo': 'bar'})]
384 output: [{'id': 'a', 'label': 'a', 'type': 'date',
385 'depth': 0, 'container': 'iter', 'custom_properties': {}},
386 {'id': 'b', 'label': 'b', 'type': 'timeofday',
387 'depth': 0, 'container': 'iter',
388 'custom_properties': {'foo': 'bar'}}]
390 input: {'a': [('b', 'number'), ('c', 'string', 'column c')]}
391 output: [{'id': 'a', 'label': 'a', 'type': 'string',
392 'depth': 0, 'container': 'dict', 'custom_properties': {}},
393 {'id': 'b', 'label': 'b', 'type': 'number',
394 'depth': 1, 'container': 'iter', 'custom_properties': {}},
395 {'id': 'c', 'label': 'column c', 'type': 'string',
396 'depth': 1, 'container': 'iter', 'custom_properties': {}}]
398 input: {('a', 'number', 'column a'): { 'b': 'number', 'c': 'string'}}
399 output: [{'id': 'a', 'label': 'column a', 'type': 'number',
400 'depth': 0, 'container': 'dict', 'custom_properties': {}},
401 {'id': 'b', 'label': 'b', 'type': 'number',
402 'depth': 1, 'container': 'dict', 'custom_properties': {}},
403 {'id': 'c', 'label': 'c', 'type': 'string',
404 'depth': 1, 'container': 'dict', 'custom_properties': {}}]
406 input: { ('w', 'string', 'word'): ('c', 'number', 'count') }
407 output: [{'id': 'w', 'label': 'word', 'type': 'string',
408 'depth': 0, 'container': 'dict', 'custom_properties': {}},
409 {'id': 'c', 'label': 'count', 'type': 'number',
410 'depth': 1, 'container': 'scalar', 'custom_properties': {}}]
412 # For the recursion step, we check for a scalar object (string or tuple)
413 if isinstance(table_description
, (types
.StringTypes
, tuple)):
414 parsed_col
= DataTable
.ColumnTypeParser(table_description
)
415 parsed_col
["depth"] = depth
416 parsed_col
["container"] = "scalar"
419 # Since it is not scalar, table_description must be iterable.
420 if not hasattr(table_description
, "__iter__"):
421 raise DataTableException("Expected an iterable object, got %s" %
422 type(table_description
))
423 if not isinstance(table_description
, dict):
424 # We expects a non-dictionary iterable item.
426 for desc
in table_description
:
427 parsed_col
= DataTable
.ColumnTypeParser(desc
)
428 parsed_col
["depth"] = depth
429 parsed_col
["container"] = "iter"
430 columns
.append(parsed_col
)
432 raise DataTableException("Description iterable objects should not"
435 # The other case is a dictionary
436 if not table_description
:
437 raise DataTableException("Empty dictionaries are not allowed inside"
440 # The number of keys in the dictionary separates between the two cases of
441 # more levels below or this is the most inner dictionary.
442 if len(table_description
) != 1:
443 # This is the most inner dictionary. Parsing types.
445 # We sort the items, equivalent to sort the keys since they are unique
446 for key
, value
in sorted(table_description
.items()):
447 # We parse the column type as (key, type) or (key, type, label) using
449 if isinstance(value
, tuple):
450 parsed_col
= DataTable
.ColumnTypeParser((key
,) + value
)
452 parsed_col
= DataTable
.ColumnTypeParser((key
, value
))
453 parsed_col
["depth"] = depth
454 parsed_col
["container"] = "dict"
455 columns
.append(parsed_col
)
457 # This is an outer dictionary, must have at most one key.
458 parsed_col
= DataTable
.ColumnTypeParser(table_description
.keys()[0])
459 parsed_col
["depth"] = depth
460 parsed_col
["container"] = "dict"
461 return ([parsed_col
] +
462 DataTable
.TableDescriptionParser(table_description
.values()[0],
467 """Returns the parsed table description."""
468 return self
.__columns
470 def NumberOfRows(self
):
471 """Returns the number of rows in the current data stored in the table."""
472 return len(self
.__data
)
474 def SetRowsCustomProperties(self
, rows
, custom_properties
):
475 """Sets the custom properties for given row(s).
477 Can accept a single row or an iterable of rows.
478 Sets the given custom properties for all specified rows.
481 rows: The row, or rows, to set the custom properties for.
482 custom_properties: A string to string dictionary of custom properties to
485 if not hasattr(rows
, "__iter__"):
488 self
.__data
[row
] = (self
.__data
[row
][0], custom_properties
)
490 def LoadData(self
, data
, custom_properties
=None):
491 """Loads new rows to the data table, clearing existing rows.
493 May also set the custom_properties for the added rows. The given custom
494 properties dictionary specifies the dictionary that will be used for *all*
498 data: The rows that the table will contain.
499 custom_properties: A dictionary of string to string to set as the custom
500 properties for all rows.
503 self
.AppendData(data
, custom_properties
)
505 def AppendData(self
, data
, custom_properties
=None):
506 """Appends new data to the table.
508 Data is appended in rows. Data must comply with
509 the table schema passed in to __init__(). See SingleValueToJS() for a list
510 of acceptable data types. See the class documentation for more information
511 and examples of schema and data values.
514 data: The row to add to the table. The data must conform to the table
516 custom_properties: A dictionary of string to string, representing the
517 custom properties to add to all the rows.
520 DataTableException: The data structure does not match the description.
522 # If the maximal depth is 0, we simply iterate over the data table
523 # lines and insert them using _InnerAppendData. Otherwise, we simply
524 # let the _InnerAppendData handle all the levels.
525 if not self
.__columns
[-1]["depth"]:
527 self
._InnerAppendData
(({}, custom_properties
), row
, 0)
529 self
._InnerAppendData
(({}, custom_properties
), data
, 0)
531 def _InnerAppendData(self
, prev_col_values
, data
, col_index
):
532 """Inner function to assist LoadData."""
533 # We first check that col_index has not exceeded the columns size
534 if col_index
>= len(self
.__columns
):
535 raise DataTableException("The data does not match description, too deep")
537 # Dealing with the scalar case, the data is the last value.
538 if self
.__columns
[col_index
]["container"] == "scalar":
539 prev_col_values
[0][self
.__columns
[col_index
]["id"]] = data
540 self
.__data
.append(prev_col_values
)
543 if self
.__columns
[col_index
]["container"] == "iter":
544 if not hasattr(data
, "__iter__") or isinstance(data
, dict):
545 raise DataTableException("Expected iterable object, got %s" %
547 # We only need to insert the rest of the columns
548 # If there are less items than expected, we only add what there is.
550 if col_index
>= len(self
.__columns
):
551 raise DataTableException("Too many elements given in data")
552 prev_col_values
[0][self
.__columns
[col_index
]["id"]] = value
554 self
.__data
.append(prev_col_values
)
557 # We know the current level is a dictionary, we verify the type.
558 if not isinstance(data
, dict):
559 raise DataTableException("Expected dictionary at current level, got %s" %
561 # We check if this is the last level
562 if self
.__columns
[col_index
]["depth"] == self
.__columns
[-1]["depth"]:
563 # We need to add the keys in the dictionary as they are
564 for col
in self
.__columns
[col_index
:]:
565 if col
["id"] in data
:
566 prev_col_values
[0][col
["id"]] = data
[col
["id"]]
567 self
.__data
.append(prev_col_values
)
570 # We have a dictionary in an inner depth level.
572 # In case this is an empty dictionary, we add a record with the columns
573 # filled only until this point.
574 self
.__data
.append(prev_col_values
)
576 for key
in sorted(data
):
577 col_values
= dict(prev_col_values
[0])
578 col_values
[self
.__columns
[col_index
]["id"]] = key
579 self
._InnerAppendData
((col_values
, prev_col_values
[1]),
580 data
[key
], col_index
+ 1)
582 def _PreparedData(self
, order_by
=()):
583 """Prepares the data for enumeration - sorting it by order_by.
586 order_by: Optional. Specifies the name of the column(s) to sort by, and
587 (optionally) which direction to sort in. Default sort direction
588 is asc. Following formats are accepted:
589 "string_col_name" -- For a single key in default (asc) order.
590 ("string_col_name", "asc|desc") -- For a single key.
591 [("col_1","asc|desc"), ("col_2","asc|desc")] -- For more than
592 one column, an array of tuples of (col_name, "asc|desc").
595 The data sorted by the keys given.
598 DataTableException: Sort direction not in 'asc' or 'desc'
603 proper_sort_keys
= []
604 if isinstance(order_by
, types
.StringTypes
) or (
605 isinstance(order_by
, tuple) and len(order_by
) == 2 and
606 order_by
[1].lower() in ["asc", "desc"]):
607 order_by
= (order_by
,)
609 if isinstance(key
, types
.StringTypes
):
610 proper_sort_keys
.append((key
, 1))
611 elif (isinstance(key
, (list, tuple)) and len(key
) == 2 and
612 key
[1].lower() in ("asc", "desc")):
613 proper_sort_keys
.append((key
[0], key
[1].lower() == "asc" and 1 or -1))
615 raise DataTableException("Expected tuple with second value: "
618 def SortCmpFunc(row1
, row2
):
619 """cmp function for sorted. Compares by keys and 'asc'/'desc' keywords."""
620 for key
, asc_mult
in proper_sort_keys
:
621 cmp_result
= asc_mult
* cmp(row1
[0].get(key
), row2
[0].get(key
))
626 return sorted(self
.__data
, cmp=SortCmpFunc
)
628 def ToJSCode(self
, name
, columns_order
=None, order_by
=()):
629 """Writes the data table as a JS code string.
631 This method writes a string of JS code that can be run to
632 generate a DataTable with the specified data. Typically used for debugging
636 name: The name of the table. The name would be used as the DataTable's
637 variable name in the created JS code.
638 columns_order: Optional. Specifies the order of columns in the
639 output table. Specify a list of all column IDs in the order
640 in which you want the table created.
641 Note that you must list all column IDs in this parameter,
643 order_by: Optional. Specifies the name of the column(s) to sort by.
644 Passed as is to _PreparedData.
647 A string of JS code that, when run, generates a DataTable with the given
648 name and the data stored in the DataTable object.
650 "var tab1 = new google.visualization.DataTable();
651 tab1.addColumn('string', 'a', 'a');
652 tab1.addColumn('number', 'b', 'b');
653 tab1.addColumn('boolean', 'c', 'c');
655 tab1.setCell(0, 0, 'a');
656 tab1.setCell(0, 1, 1, null, {'foo': 'bar'});
657 tab1.setCell(0, 2, true);
659 tab1.setCell(9, 0, 'c');
660 tab1.setCell(9, 1, 3, '3$');
661 tab1.setCell(9, 2, false);"
664 DataTableException: The data does not match the type.
666 if columns_order
is None:
667 columns_order
= [col
["id"] for col
in self
.__columns
]
668 col_dict
= dict([(col
["id"], col
) for col
in self
.__columns
])
670 # We first create the table with the given name
671 jscode
= "var %s = new google.visualization.DataTable();\n" % name
672 if self
.custom_properties
:
673 jscode
+= "%s.setTableProperties(%s);\n" % (
674 name
, DataTable
._EscapeCustomProperties
(self
.custom_properties
))
676 # We add the columns to the table
677 for i
, col
in enumerate(columns_order
):
678 jscode
+= "%s.addColumn('%s', '%s', '%s');\n" % (name
,
679 col_dict
[col
]["type"],
680 col_dict
[col
]["label"],
682 if col_dict
[col
]["custom_properties"]:
683 jscode
+= "%s.setColumnProperties(%d, %s);\n" % (
684 name
, i
, DataTable
._EscapeCustomProperties
(
685 col_dict
[col
]["custom_properties"]))
686 jscode
+= "%s.addRows(%d);\n" % (name
, len(self
.__data
))
688 # We now go over the data and add each row
689 for (i
, (row
, cp
)) in enumerate(self
._PreparedData
(order_by
)):
690 # We add all the elements of this row by their order
691 for (j
, col
) in enumerate(columns_order
):
692 if col
not in row
or row
[col
] is None:
695 if isinstance(row
[col
], tuple) and len(row
[col
]) == 3:
696 cell_cp
= ", %s" % DataTable
._EscapeCustomProperties
(row
[col
][2])
697 value
= self
.SingleValueToJS(row
[col
], col_dict
[col
]["type"])
698 if isinstance(value
, tuple):
699 # We have a formatted value or custom property as well
701 value
= (value
[0], "null")
702 jscode
+= ("%s.setCell(%d, %d, %s, %s%s);\n" %
703 (name
, i
, j
, value
[0], value
[1], cell_cp
))
705 jscode
+= "%s.setCell(%d, %d, %s);\n" % (name
, i
, j
, value
)
707 jscode
+= "%s.setRowProperties(%d, %s);\n" % (
708 name
, i
, DataTable
._EscapeCustomProperties
(cp
))
711 def ToHtml(self
, columns_order
=None, order_by
=()):
712 """Writes the data table as an HTML table code string.
715 columns_order: Optional. Specifies the order of columns in the
716 output table. Specify a list of all column IDs in the order
717 in which you want the table created.
718 Note that you must list all column IDs in this parameter,
720 order_by: Optional. Specifies the name of the column(s) to sort by.
721 Passed as is to _PreparedData.
724 An HTML table code string.
725 Example result (the result is without the newlines):
726 <html><body><table border='1'>
727 <thead><tr><th>a</th><th>b</th><th>c</th></tr></thead>
729 <tr><td>1</td><td>"z"</td><td>2</td></tr>
730 <tr><td>"3$"</td><td>"w"</td><td></td></tr>
732 </table></body></html>
735 DataTableException: The data does not match the type.
737 table_template
= "<html><body><table border='1'>%s</table></body></html>"
738 columns_template
= "<thead><tr>%s</tr></thead>"
739 rows_template
= "<tbody>%s</tbody>"
740 row_template
= "<tr>%s</tr>"
741 header_cell_template
= "<th>%s</th>"
742 cell_template
= "<td>%s</td>"
744 if columns_order
is None:
745 columns_order
= [col
["id"] for col
in self
.__columns
]
746 col_dict
= dict([(col
["id"], col
) for col
in self
.__columns
])
749 for col
in columns_order
:
750 columns_list
.append(header_cell_template
% col_dict
[col
]["label"])
751 columns_html
= columns_template
% "".join(columns_list
)
754 # We now go over the data and add each row
755 for row
, unused_cp
in self
._PreparedData
(order_by
):
757 # We add all the elements of this row by their order
758 for col
in columns_order
:
759 # For empty string we want empty quotes ("").
761 if col
in row
and row
[col
] is not None:
762 value
= self
.SingleValueToJS(row
[col
], col_dict
[col
]["type"])
763 if isinstance(value
, tuple):
764 # We have a formatted value and we're going to use it
765 cells_list
.append(cell_template
% cgi
.escape(value
[1]))
767 cells_list
.append(cell_template
% cgi
.escape(value
))
768 rows_list
.append(row_template
% "".join(cells_list
))
769 rows_html
= rows_template
% "".join(rows_list
)
771 return table_template
% (columns_html
+ rows_html
)
773 def ToCsv(self
, columns_order
=None, order_by
=(), separator
=", "):
774 """Writes the data table as a CSV string.
777 columns_order: Optional. Specifies the order of columns in the
778 output table. Specify a list of all column IDs in the order
779 in which you want the table created.
780 Note that you must list all column IDs in this parameter,
782 order_by: Optional. Specifies the name of the column(s) to sort by.
783 Passed as is to _PreparedData.
784 separator: Optional. The separator to use between the values.
787 A CSV string representing the table.
794 DataTableException: The data does not match the type.
796 if columns_order
is None:
797 columns_order
= [col
["id"] for col
in self
.__columns
]
798 col_dict
= dict([(col
["id"], col
) for col
in self
.__columns
])
801 for col
in columns_order
:
802 columns_list
.append(DataTable
._EscapeValueForCsv
(col_dict
[col
]["label"]))
803 columns_line
= separator
.join(columns_list
)
806 # We now go over the data and add each row
807 for row
, unused_cp
in self
._PreparedData
(order_by
):
809 # We add all the elements of this row by their order
810 for col
in columns_order
:
812 if col
in row
and row
[col
] is not None:
813 value
= self
.SingleValueToJS(row
[col
], col_dict
[col
]["type"],
814 DataTable
._EscapeValueForCsv
)
815 if isinstance(value
, tuple):
816 # We have a formatted value. Using it only for date/time types.
817 if col_dict
[col
]["type"] in ["date", "datetime", "timeofday"]:
818 cells_list
.append(value
[1])
820 cells_list
.append(value
[0])
822 # We need to quote date types, because they contain commas.
823 if (col_dict
[col
]["type"] in ["date", "datetime", "timeofday"] and
825 value
= '"%s"' % value
826 cells_list
.append(value
)
827 rows_list
.append(separator
.join(cells_list
))
828 rows
= "\n".join(rows_list
)
830 return "%s\n%s" % (columns_line
, rows
)
832 def ToTsvExcel(self
, columns_order
=None, order_by
=()):
833 """Returns a file in tab-separated-format readable by MS Excel.
835 Returns a file in UTF-16 little endian encoding, with tabs separating the
839 columns_order: Delegated to ToCsv.
840 order_by: Delegated to ToCsv.
843 A tab-separated little endian UTF16 file representing the table.
846 columns_order
, order_by
, separator
="\t").encode("UTF-16LE")
848 def ToJSon(self
, columns_order
=None, order_by
=()):
849 """Writes a JSON string that can be used in a JS DataTable constructor.
851 This method writes a JSON string that can be passed directly into a Google
852 Visualization API DataTable constructor. Use this output if you are
853 hosting the visualization HTML on your site, and want to code the data
854 table in Python. Pass this string into the
855 google.visualization.DataTable constructor, e.g,:
856 ... on my page that hosts my visualization ...
857 google.setOnLoadCallback(drawTable);
858 function drawTable() {
859 var data = new google.visualization.DataTable(_my_JSon_string, 0.6);
864 columns_order: Optional. Specifies the order of columns in the
865 output table. Specify a list of all column IDs in the order
866 in which you want the table created.
867 Note that you must list all column IDs in this parameter,
869 order_by: Optional. Specifies the name of the column(s) to sort by.
870 Passed as is to _PreparedData().
873 A JSon constructor string to generate a JS DataTable with the data
874 stored in the DataTable object.
875 Example result (the result is without the newlines):
876 {cols: [{id:'a',label:'a',type:'number'},
877 {id:'b',label:'b',type:'string'},
878 {id:'c',label:'c',type:'number'}],
879 rows: [{c:[{v:1},{v:'z'},{v:2}]}, c:{[{v:3,f:'3$'},{v:'w'},{v:null}]}],
883 DataTableException: The data does not match the type.
885 if columns_order
is None:
886 columns_order
= [col
["id"] for col
in self
.__columns
]
887 col_dict
= dict([(col
["id"], col
) for col
in self
.__columns
])
889 # Creating the columns jsons
891 for col_id
in columns_order
:
892 d
= dict(col_dict
[col_id
])
894 if col_dict
[col_id
]["custom_properties"]:
895 d
["cp"] = ",p:%s" % DataTable
._EscapeCustomProperties
(
896 col_dict
[col_id
]["custom_properties"])
898 "{id:'%(id)s',label:'%(label)s',type:'%(type)s'%(cp)s}" % d
)
900 # Creating the rows jsons
902 for row
, cp
in self
._PreparedData
(order_by
):
904 for col
in columns_order
:
905 # We omit the {v:null} for a None value of the not last column
906 value
= row
.get(col
, None)
907 if value
is None and col
!= columns_order
[-1]:
908 cells_jsons
.append("")
910 value
= self
.SingleValueToJS(value
, col_dict
[col
]["type"])
911 if isinstance(value
, tuple):
912 # We have a formatted value or custom property as well
913 if len(row
.get(col
)) == 3:
915 cells_jsons
.append("{v:%s,p:%s}" % (
917 DataTable
._EscapeCustomProperties
(row
.get(col
)[2])))
919 cells_jsons
.append("{v:%s,f:%s,p:%s}" % (value
+ (
920 DataTable
._EscapeCustomProperties
(row
.get(col
)[2]),)))
922 cells_jsons
.append("{v:%s,f:%s}" % value
)
924 cells_jsons
.append("{v:%s}" % value
)
926 rows_jsons
.append("{c:[%s],p:%s}" % (
927 ",".join(cells_jsons
), DataTable
._EscapeCustomProperties
(cp
)))
929 rows_jsons
.append("{c:[%s]}" % ",".join(cells_jsons
))
931 general_custom_properties
= ""
932 if self
.custom_properties
:
933 general_custom_properties
= (
934 ",p:%s" % DataTable
._EscapeCustomProperties
(self
.custom_properties
))
936 # We now join the columns jsons and the rows jsons
937 json
= "{cols:[%s],rows:[%s]%s}" % (",".join(cols_jsons
),
938 ",".join(rows_jsons
),
939 general_custom_properties
)
942 def ToJSonResponse(self
, columns_order
=None, order_by
=(), req_id
=0,
943 response_handler
="google.visualization.Query.setResponse"):
944 """Writes a table as a JSON response that can be returned as-is to a client.
946 This method writes a JSON response to return to a client in response to a
947 Google Visualization API query. This string can be processed by the calling
948 page, and is used to deliver a data table to a visualization hosted on
952 columns_order: Optional. Passed straight to self.ToJSon().
953 order_by: Optional. Passed straight to self.ToJSon().
954 req_id: Optional. The response id, as retrieved by the request.
955 response_handler: Optional. The response handler, as retrieved by the
959 A JSON response string to be received by JS the visualization Query
960 object. This response would be translated into a DataTable on the
962 Example result (newlines added for readability):
963 google.visualization.Query.setResponse({
964 'version':'0.6', 'reqId':'0', 'status':'OK',
965 'table': {cols: [...], rows: [...]}});
967 Note: The URL returning this string can be used as a data source by Google
968 Visualization Gadgets or from JS code.
970 table
= self
.ToJSon(columns_order
, order_by
)
971 return ("%s({'version':'0.6', 'reqId':'%s', 'status':'OK', "
972 "'table': %s});") % (response_handler
, req_id
, table
)
974 def ToResponse(self
, columns_order
=None, order_by
=(), tqx
=""):
975 """Writes the right response according to the request string passed in tqx.
977 This method parses the tqx request string (format of which is defined in
978 the documentation for implementing a data source of Google Visualization),
979 and returns the right response according to the request.
980 It parses out the "out" parameter of tqx, calls the relevant response
981 (ToJSonResponse() for "json", ToCsv() for "csv", ToHtml() for "html",
982 ToTsvExcel() for "tsv-excel") and passes the response function the rest of
983 the relevant request keys.
986 columns_order: Optional. Passed as is to the relevant response function.
987 order_by: Optional. Passed as is to the relevant response function.
988 tqx: Optional. The request string as received by HTTP GET. Should be in
989 the format "key1:value1;key2:value2...". All keys have a default
990 value, so an empty string will just do the default (which is calling
991 ToJSonResponse() with no extra parameters).
994 A response string, as returned by the relevant response function.
997 DataTableException: One of the parameters passed in tqx is not supported.
1001 tqx_dict
= dict(opt
.split(":") for opt
in tqx
.split(";"))
1002 if tqx_dict
.get("version", "0.6") != "0.6":
1003 raise DataTableException(
1004 "Version (%s) passed by request is not supported."
1005 % tqx_dict
["version"])
1007 if tqx_dict
.get("out", "json") == "json":
1008 response_handler
= tqx_dict
.get("responseHandler",
1009 "google.visualization.Query.setResponse")
1010 return self
.ToJSonResponse(columns_order
, order_by
,
1011 req_id
=tqx_dict
.get("reqId", 0),
1012 response_handler
=response_handler
)
1013 elif tqx_dict
["out"] == "html":
1014 return self
.ToHtml(columns_order
, order_by
)
1015 elif tqx_dict
["out"] == "csv":
1016 return self
.ToCsv(columns_order
, order_by
)
1017 elif tqx_dict
["out"] == "tsv-excel":
1018 return self
.ToTsvExcel(columns_order
, order_by
)
1020 raise DataTableException(
1021 "'out' parameter: '%s' is not supported" % tqx_dict
["out"])