zoom.sqltools module

zoom.sql

sql utilities

zoom.sqltools.setup_test()

setup test

zoom.sqltools.summarize(table, dimensions, metrics=None)

summarize data

>>> from zoom.records import Record, RecordStore
>>> from decimal import Decimal
>>> db = setup_test()
>>> class Person(Record): pass
>>> class People(RecordStore): pass
>>> people = People(db, Person)
>>> put = people.put
>>> id = put(Person(name='Sam', age=25, kids=1, salary=Decimal('40000')))
>>> id = put(Person(name='Sally', age=55, kids=4, salary=Decimal('80000')))
>>> id = put(Person(name='Bob', age=25, kids=2, salary=Decimal('70000')))
>>> id = put(Person(name='Jane', age=25, kids=2, salary=Decimal('50000')))
>>> id = put(Person(name='Alex', age=25, kids=3, salary=Decimal('50000')))
>>> print(people)
person
_id name  age kids salary
--- ----- --- ---- ------
  1 Sam    25    1 40,000
  2 Sally  55    4 80,000
  3 Bob    25    2 70,000
  4 Jane   25    2 50,000
  5 Alex   25    3 50,000
5 person records

>>> print(summarize('person', ['age']))
select "*" age, count(*) n from person group by 1
union select age age, count(*) n from person group by 1

>>> print(db(summarize('person', ['age'])))
age n
--- -
25  4
55  1
*   5

>>> print(db(summarize('person', ['age','kids'])))
age kids n
--- ---- -
25  1    1
25  2    2
25  3    1
25  *    4
55  4    1
55  *    1
*   1    1
*   2    2
*   3    1
*   4    1
*   *    5

>>> print(db(summarize('person', ['age','kids'], ['salary'])))
age kids n salary
--- ---- - -------
25  1    1  40,000
25  2    2 120,000
25  3    1  50,000
25  *    4 210,000
55  4    1  80,000
55  *    1  80,000
*   1    1  40,000
*   2    2 120,000
*   3    1  50,000
*   4    1  80,000
*   *    5 290,000

>>> people.zap()
>>> print(people)
Empty list