endi.statistics package¶

Submodules¶

endi.statistics.inspect module¶

class endi.statistics.inspect.Column¶

Bases : dict

The column object wrapping the model’s attribute

class endi.statistics.inspect.StatisticInspector(model, excludes=(), exclude_relationships=False)¶

Bases : BaseSqlaInspector

A sqla inspector made for statistics

model

The model we want to inspect

excludes

The name of the attributes we want to exclude from inspection

exclude_relationships

Should we exclude relationships (usefull for limiting recursive inspection)

>>> inspector = StatisticInspector(UserDatas)
>>> inspector.__json__()

>>> {
>>>     'attributes': {'key': {column statistic definition dict}},
>>>     "relationships": {
>>>         'key': {
>>>             'table': {'the rel statistic definition dict (label
...)}
>>>             'attributes: {....},
>>>             'relationships': {....},
>>>         }
>>>     }
>>> }

>>> column_description = inspector.get(key)

config_key = 'stats'¶

get(key)¶

Return the inspected datas for the key

Paramètres:: key (str) – A column name

endi.statistics.inspect.get_data_type(datas)¶

Returns the type of datas

Paramètres:: prop (obj) – The column object returned by the sqlalchemy model

inspection :returns: A string representing the type of the column

endi.statistics.inspect.get_inspector(model=<class 'endi.models.user.userdatas.UserDatas'>)¶: Return a statistic inspector for the given model

endi.statistics.query_helper module¶

class endi.statistics.query_helper.AndQueryFactory(model, criteria, inspector, root=True, id_key='id')¶

Bases : OrQueryFactory

An independant AND query factory

All children of the or query are requested indepedantly End clause is done Python side

get_ids()¶: Compute the and clause on the independant query factories resulting ids list

class endi.statistics.query_helper.BoolCriterionQueryHelper(model, criterion_model, inspector)¶

Bases : CriterionQueryHelper

filter_false(attr)¶

filter_true(attr)¶

class endi.statistics.query_helper.CriterionQueryHelper(model, criterion_model, inspector)¶

Bases : object

Statistic criterion

Compound of :: a model we query on a field name a condition key conditions values a type

filter_eq(attr)¶: equal

filter_neq(attr)¶: not equal

filter_nll(attr)¶: null

filter_nnll(attr)¶: not null

gen_filter()¶

gen_having_clause()¶: Generate a “having” clause and its associated group_by clause

get_join_class()¶: Return a related class if this criterion used one

key = None¶

model = None¶

none_values = (None, '')¶

search1 = None¶

type = 'str'¶

class endi.statistics.query_helper.DateCriterionQueryHelper(model, criterion_model, inspector)¶

Bases : CriterionQueryHelper

Statistic criterion related to Dates

filter_dr(attr)¶

filter_ndr(attr)¶

filter_previous_month(attr)¶: Last month

filter_previous_year(attr)¶: Last year

filter_this_month(attr)¶: This month

filter_this_year(attr)¶: This year

get_first_day_of_previous_month(today, nb_months=1)¶

Return the first day of this month - nb_months Handle year switch

Paramètres:: nb_months (int) – the number of months to go back

having_first_dr(attr)¶

having_first_previous_month(attr)¶

having_first_previous_year(attr)¶

having_first_this_month(attr)¶

having_first_this_year(attr)¶

having_last_dr(attr)¶

having_last_previous_month(attr)¶

having_last_previous_year(attr)¶

having_last_this_month(attr)¶

having_last_this_year(attr)¶

class endi.statistics.query_helper.EntryQueryFactory(model, entry_model, inspector)¶

Bases : object

Statistic entry

Compound of :: a label a description a list of criteria

Paramètres:

model (obj) – The model we’re building stats on (UserDatas)
entry_model (obj) – The StatisticEntry model instance
inspector (obj) – The model’s sqlalchemy inspector used to retrieve

database related informations

Return a unique sqlalchemy query object If needed, we make independant queries that we group in the main query filtering on the resulting ids

query()¶

Renvoie:: A sqla query matching the selected criteria in the form

[(model.id, model_instance)…] :rtype: A query object

render_row()¶: Returns the datas expected for statistics rendering

exception endi.statistics.query_helper.MissingDatasError(message, *args, **kwargs)¶

Bases : Exception

Custom exception raised when some datas is missing for filtering

class endi.statistics.query_helper.NumericCriterionQueryHelper(model, criterion_model, inspector)¶

Bases : CriterionQueryHelper

Statistic criterion for filtering numeric datas

bw(attr)¶

filter_gt(attr)¶

filter_gte(attr)¶

filter_lt(attr)¶

filter_lte(attr)¶

nbw(attr)¶

class endi.statistics.query_helper.OneToManyQueryFactory(model, criterion, inspector, root=True)¶

Bases : QueryFactory

An independant OneToMany query factory

Query on a related table and retrieve foreign key values (ids)

class endi.statistics.query_helper.OptRelCriterionQueryHelper(model, criterion_model, inspector)¶

Bases : StaticOptRelCriterionQueryHelper

Statistic criterion related to related options

class endi.statistics.query_helper.OrQueryFactory(model, criteria, inspector, root=True, id_key='id')¶

Bases : QueryFactory

An independant OR query factory All children of the or query are requested indepedantly Or clause is done Python side

get_ids()¶: Compute the or clause on the independant query factories resulting ids list

class endi.statistics.query_helper.QueryFactory(model, criteria, inspector, root=True, id_key='id')¶

Bases : object

A query factory that produce a sqlalchemy query, can combine multiple query factories or/and query helpers

Attr list criteria:: The list of StatisticCriterion handled by this object
Attr obj model:: The Sqlalchemy model we’re talking about
Attr obj inspector:: The Statistic SQLA inspector used to collect columns

Attr bool root:: Is this object at the top level of our entry

count()¶: Return the number of entries matching this query

get_ids()¶: Return the ids matched by the current query

query()¶

Return the main query used to find objects

e.g:

query = DBSESSION().query(distinct(UserDatas.id), UserDatas) query = query.filter(UserDatas.name.startswith(“test”)) query = query.filter(UserDatas.conseiller_id.in_([“1”, “2”])) query = query.filter(

UserDatas.id.in_(
[list of ids retrieved from independant queries]

)

)

class endi.statistics.query_helper.RelatedCriterionQueryHelper(model, criterion_model, inspector)¶: Bases : CriterionQueryHelper

class endi.statistics.query_helper.SheetQueryFactory(model, sheet, inspector)¶

Bases : object

Statistics sheet

Compound of :: a list of entries

Rendering:

A sheet should be rendered as a csv file, the headers :
(“label”, “count”, “description”)

each row matches an entry

Paramètres:

model (obj) – The model we’re building stats on (UserDatas)
sheet (obj) – The StatisticSheet instance we’re mapping
inspector (obj) – The model’s sqlalchemy inspector used to retrieve

database related informations

entries = []¶

property headers¶

property rows¶: Return the rows of our sheet output

class endi.statistics.query_helper.StaticOptRelCriterionQueryHelper(model, criterion_model, inspector)¶

Bases : CriterionQueryHelper

filter_ioo(attr)¶: is one of

filter_nioo(attr)¶: is not one of

class endi.statistics.query_helper.StrCriterionQueryHelper(model, criterion_model, inspector)¶

Bases : CriterionQueryHelper

Statistic criterion related to strings

filter_ew(attr)¶: endswith

filter_has(attr)¶: contains

filter_nhas(attr)¶: not contains

filter_sw(attr)¶: startswith

endi.statistics.query_helper.get_query(model, where)¶

Return a query on the given model based on a filter list

E.g :

get_query(
UserDatas, [

{
« key »: »created_at », « method »: »dr », « type »: »date », « search1 »: »1999-01-01 », « search2 »: »2016-12-31 »

}

]

)

Filter syntax

key

The model attribute

method

See endi.statistics.__init__.py for keywords and their meaning

type

One of
string number bool

search1

The first search entry

search2

Regarding the method of this filter, we may need a second parameter

Paramètres:

model (cls) – The model to get items from
where (list) – List of criteria in dict form

Renvoie:

A SQLA query

Type renvoyé:

obj

endi.statistics.query_helper.get_query_factory(model, criterion_model, inspector)¶

Return a query factory for the given criterion_model

Paramètres:

model – The model we are building stats on (e.g: UserDatas)
criterion_model – The criterion we want to build a helper from
inspector – A SQLAlchemy inspection class

Renvoie:

A QueryFactory instance

endi.statistics.query_helper.get_query_helper(model, criterion_model, inspector)¶

Return the appropriate helper class used to build filters on a given criterion

Paramètres:

model – The model we are building stats on (e.g: UserDatas)
criterion_model – The criterion we want to build a helper from
inspector – A SQLAlchemy inspection class

Renvoie:

A CriterionQueryHelper instance

Module contents¶

Main classes for statistics computation Allows to generate and combine queries

1- Build SQLAlchemy query objects 2- Combine the result of multiple query objects to merge them python-side (this way we avoid conflict in group/having/join clauses)

Stats are composed of Sheets which contains Entries which are composed with Criteria

For each entry, we build a main EntryQueryFactory, which contains a query_object that is the root of our query tree

To be able to build queries

On doit pouvoir générer des query :

1- db().query(

distinct(models.user.UserDatas.id)

).filter(: models.user.UserDatas.coordonnees_lastname.startswith(“tje”)

).count()

2- Des relations M2o Les objets remotes existent déjà, c’est forcément par l’id qu’on match

db().query(: distinct(models.user.UserDatas.id)
).filter(: models.user.UserDatas.parcours_status_id.in_((1,2,3))

).count()

3- Des relations o2M On doit passer par les attributs des objets remotes pour filtrer filtre sur un attribut cible :

db().query(: distinct(models.user.UserDatas.id)
).outerjoin(: models.user.UserDatas.parcours_date_diagnostic
).filter(: models.user.DateDiagnosticDatas.date<datetime.datetime.now()

).count()

filtre sur deux attributs cibles :

db().query(: distinct(models.user.UserDatas.id), models.user.UserDatas ).outerjoin( models.user.UserDatas.statut_external_activity ).filter( models.user.ExternalActivityDatas.brut_salary>100 ).filter(models.user.ExternalActivityDatas.hours<8).count()

TODO :

In [3]: query = db().query(UserDatas.id).outerjoin(: UserDatas.parcours_convention_cape)

In [4]: query = query.group_by(UserDatas.id)

In [5]: query = query.having(: func.max(DateConventionCAPEDatas.date) > datetime.date.today())

In [6]: query.count()

Cas 1 :

Attribut de la classe UserDatas :

String / Booléen / Date / Option Statique / ForeignKey

Cas 2 :

Attribut d’une relation O2M :

String / Booléen / Date / Option Statique / ForeignKey de la classe

endi.statistics package¶

Submodules¶

endi.statistics.filter_options module¶

endi.statistics.inspect module¶

endi.statistics.query_helper module¶

Module contents¶

Table des matières

Sujet précédent

Sujet suivant

Cette page