def _get_nodes(node_id: int, id_col: IA, pid_col: IA, to_root: bool, *other_col):
"""
get all child nodes or all parent nodes for a given node.
node_id: start node's id value;
id_col: the id column of a sqlalchemy class, often is `id`;
pid_col: the parent id column of a sqlalchemy class, often is `parent_id`;
to_root: to root node or to children node;
other_col: other columns you want to select when query the nodes;
"""
class_model = id_col.class_
other_col_names = [col.name for col in other_col]
hierarchy = (
select(
[
id_col.label('id'),
pid_col.label('parent_id'),
*other_col,
literal(0).label('level'),
array((id_col,)).label('path'), # array need tuple
literal(False).label('cycle'),
]
)
.where(id_col == node_id)
.cte(name='hierarchy', recursive=True)
)
next_alias = aliased(class_model, name='next_level')
alias_id_col = getattr(next_alias, id_col.name)
alias_pid_col = getattr(next_alias, pid_col.name)
alias_other_col = [getattr(next_alias, col.name) for col in other_col]
if to_root is True:
'第一层的 parent_id 是下一层的 id'
join_condition = hierarchy.c.parent_id == alias_id_col
else:
'第一层的 id 是下一层的 parent_id'
join_condition = hierarchy.c.id == alias_pid_col
hierarchy = hierarchy.union_all(
select(
[
alias_id_col.label('id'),
alias_pid_col.label('parent_id'),
*alias_other_col,
(hierarchy.c.level + 1).label('level'),
(hierarchy.c.path + array((alias_id_col,))).label('path'),
(alias_id_col == any_(hierarchy.c.path)).label('cycle'),
]
)
.where(hierarchy.c.cycle.is_(False))
.select_from(hierarchy.join(next_alias, join_condition, isouter=False))
)
q = sa.select(
[column('id'), column('parent_id'), *[column(name) for name in other_col_names]]
).select_from(hierarchy)
return session.execute(q)
def adapted(self, adapter):
'''
@see: InstrumentedAttribute.adapted
We need to adjust this in order to be able to alias.
'''
adapted = InstrumentedAttribute(self.prop, self.mapper, adapter)
adapted.comparator = self.comparator.adapted(adapter)
adapted.class_ = self.class_
return adapted
def adapted(self, adapter):
'''
@see: InstrumentedAttribute.adapted
We need to adjust this in order to be able to alias.
'''
adapted = InstrumentedAttribute(self.prop, self.mapper, adapter)
adapted.comparator = self.comparator.adapted(adapter)
adapted.class_ = self.class_
return adapted
def _match(column_instance: InstrumentedAttribute,
compare_value: str,
invert=False) -> BinaryExpression:
"""
Create Column based Compare logic
For building filters, the Column should be filtered for
records that match the given value. If the compare value
contains a `'%'` or `'*'`, a LIKE clause instrumenting this
wildcard will be used, otherwise an exact match.
If the string startswith `!`, the filter will be inverted.
Parameters
----------
column_instance : sqlalchemy.Column
The column that the filter should be build upon
compare_value : str
Matching string that should be used.
invert : bool
If True, a unary `not` will be placed on the comparison.
This is not actually used in the models. Defaults to False.
Returns
-------
expression : sqlalchemy.BinaryExpression
The returned BinaryExpression can directly be passed to
sqlalchemy's filter function on Query objects.
TODO
----
here, we could check the content for `[]` and apply regex.
"""
# check invert
if compare_value.startswith('!'):
invert = True
compare_value = compare_value[1:]
# check for asterisk
if '*' in compare_value:
compare_value = compare_value.replace('*', '%')
# check for the right variant
if '%' in compare_value:
if invert:
return column_instance.notlike(compare_value)
else:
return column_instance.like(compare_value)
else:
if invert:
return column_instance != compare_value
else:
return column_instance == compare_value
def __get__(self, instance, owner):
if instance is None:
return self
dict_ = instance_dict(instance)
if self.key in dict_:
return dict_[self.key]
else:
return InstrumentedAttributeOld.__get__(self, instance, owner)
'in': Column.in_,
'is': Column.__eq__,
'is not': Column.__ne__,
'<=': Column.__le__,
'not is': Column.__ne__
}
_orm_where_op = {
'=': iattr.__eq__,
'>': iattr.__gt__,
'<': iattr.__lt__,
'>=': iattr.__ge__,
'!=': iattr.__ne__,
'<>': iattr.__ne__,
'like': iattr.like,
'not like': lambda *fvs: not_(iattr.like(*fvs)),
'in': iattr.in_,
'is': iattr.__eq__,
'is not': iattr.__ne__,
'not is': iattr.__ne__,
'<=': iattr.__le__
}
ENGINE_STRING = '%(dialect)s+%(driver)s://%(user)s:%(password)s@%(host)s/%(dbname)s?%(paras)s'
_n1 = 'eq,ne,gt,ge,lt,le,nu,nn,in,ni'.split(',')
_o1 = '=,<>,>,>=,<,<=,is null,not is null,is not null,in,not in'.split(',')
_n2 = 'bw,bn,ew,en,cn,nc'.split(',')
_o2 = 'like,not like,like,not like,like,not like'.split(',')
_v2 = '%s%%,%s%%,%%%s,%%%s,%%%s%%,%%%s%%'.split(',')
def _add_list_filter_to_query(self, filter: List[str], query: Query,
column: InstrumentedAttribute) -> Query:
return query.filter(or_(*[column.like(item) for item in filter]))
def address_entity_search_query(query: str, entity: InstrumentedAttribute,
session: Session, limit: int):
return (session.query().add_columns(entity).distinct().filter(
entity.ilike(f"%{query}%")).order_by(entity.asc()).limit(limit))
from datetime import datetime
try:
from collections import OrderedDict
except:
from ordereddict import OrderedDict
#数据库已创建表，select * from pg_statio_user_tables
SessionMaker=sessionmaker()
_sql_rl=_sql_relate={'and':and_,'or':or_}
_exp_where_op={'=':Column.__eq__,'>':Column.__gt__,'<':Column.__lt__,'>=':Column.__ge__,'!=':Column.__ne__,'<>':Column.__ne__,'like':Column.like,'not like':lambda *fvs:not_(Column.like(*fvs)),'in':Column.in_,'is':Column.__eq__,'is not':Column.__ne__,'<=':Column.__le__,'not is':Column.__ne__}
_orm_where_op={'=':iattr.__eq__,'>':iattr.__gt__,'<':iattr.__lt__,'>=':iattr.__ge__,'!=':iattr.__ne__,'<>':iattr.__ne__,'like':iattr.like,'not like':lambda *fvs:not_(iattr.like(*fvs)),'in':iattr.in_,'is':iattr.__eq__,'is not':iattr.__ne__,'not is':iattr.__ne__,'<=':iattr.__le__}
ENGINE_STRING='%(dialect)s+%(driver)s://%(user)s:%(password)s@%(host)s/%(dbname)s?%(paras)s'
_n1='eq,ne,gt,ge,lt,le,nu,nn,in,ni'.split(',')
_o1='=,<>,>,>=,<,<=,is null,not is null,is not null,in,not in'.split(',')
_n2='bw,bn,ew,en,cn,nc'.split(',')
_o2='like,not like,like,not like,like,not like'.split(',')
_v2='%s%%,%s%%,%%%s,%%%s,%%%s%%,%%%s%%'.split(',')
_op1=dict(zip(_n1,_o1))
_op2=dict(zip(_n2,zip(_o2,_v2)))
def sql_filter(field_filter):
'''
将字典类型的查询语句转为列表
def _apply_agg_query(agg_field_exp: SQLalchemyExpression, label: str,
type: SQLalchemyType = String) -> SQLAlchemyLabelExpression:
return case([(func.array_agg(agg_field_exp).filter(agg_field_exp.isnot(None)).isnot(None),
cast(func.array_agg(func.distinct(agg_field_exp)).filter(agg_field_exp.isnot(None)), ARRAY(type)))],
else_=cast(array([]), ARRAY(type))).label(label)