<commit_before>.. _querying:

Querying
========

.. _expressions:

.. include:: expressions.rst

Constructing queries
--------------------

Queries in peewee are constructed one piece at a time.

The "pieces" of a peewee query are generally representative of clauses you might
find in a SQL query.  Most methods are chainable, so you build your query up
one clause at a time.  This way, rather complex queries are possible.

Here is a barebones select query:

.. code-block:: pycon

    >>> user_q = User.select() # <-- query is not executed
    >>> user_q
    <peewee.SelectQuery object at 0x7f6b0810c610>

    >>> [u.username for u in user_q] # <-- query is evaluated here
    [u'admin', u'staff', u'editor']

We can build up the query by adding some clauses to it:

.. code-block:: pycon

    >>> user_q = user_q.where(User.username << ['admin', 'editor'])
    >>> user_q = user_q.order_by(User.username.desc())
    >>> [u.username for u in user_q] # <-- query is re-evaluated here
    [u'editor', u'admin']

.. _query_compare:

Looking at some simple queries
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Get active users:

.. code-block:: python

    User.select().where(User.active == True)

Get users who are either staff or superusers:

.. code-block:: python

    User.select().where(
        (User.is_staff == True) | (User.is_superuser == True))

Get tweets by user named "charlie":

.. code-block:: python

    Tweet.select().join(User).where(User.username == 'charlie')

Get tweets by staff or superusers (assumes FK relationship):

.. code-block:: python

    Tweet.select().join(User).where(
        (User.is_staff == True) | (User.is_superuser == True))

.. _where_clause:

Where clause
------------

All queries except :py:class:`InsertQuery` and :py:class:`RawQuery` support the
:py:meth:`~Query.where` method.  If you are familiar with Django's ORM, it is
analagous to the ``filter()`` method.  Inside the where clause, you will place
one or more :ref:`expressions`.

.. code-block:: python

    User.select().where(User.is_staff == True)

.. note:: ``User.select()`` is equivalent to ``SelectQuery(User)``.

.. note::
    Multiple calls to :py:meth:`~Query.where` will be ``AND``-ed together. To
    dynamically connect clauses with ``OR``:

    .. code-block:: python

        import operator
        or_clauses = reduce(operator.or_, clauses)  # OR together all clauses

.. _column-lookups:

Column lookups
^^^^^^^^^^^^^^

The following types of comparisons are supported by peewee:

================ =======================================
Comparison       Meaning
================ =======================================
``==``           x equals y
``<``            x is less than y
``<=``           x is less than or equal to y
``>``            x is greater than y
``>=``           x is greater than or equal to y
``!=``           x is not equal to y
``<<``           x IN y, where y is a list or query
``>>``           x IS y, where y is None/NULL
``%``            x LIKE y where y may contain wildcards
``**``           x ILIKE y where y may contain wildcards
================ =======================================

You can also perform "BETWEEN" lookups by calling the ``between`` method on
a field object:

.. code-block:: python

    Employee.select().where(Employee.salary.between(50000, 60000))

.. _custom-lookups:

Adding user-defined operators
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Because I ran out of python operators to overload, there are some missing
operators in peewee, for instance `modulo <https://github.com/coleifer/peewee/issues/177>`_.
If you find that you need to support an operator that is not in the table
above, it is very easy to add your own.

Here is how you might add support for ``modulo`` and ``regexp`` in SQLite:

.. code-block:: python

    from peewee import *
    from peewee import Expr # the building block for expressions<commit_msg>Adding note on sqlite like/glob<commit_after>.. _querying:

Querying
========

.. _expressions:

.. include:: expressions.rst

Constructing queries
--------------------

Queries in peewee are constructed one piece at a time.

The "pieces" of a peewee query are generally representative of clauses you might
find in a SQL query.  Most methods are chainable, so you build your query up
one clause at a time.  This way, rather complex queries are possible.

Here is a barebones select query:

.. code-block:: pycon

    >>> user_q = User.select() # <-- query is not executed
    >>> user_q
    <peewee.SelectQuery object at 0x7f6b0810c610>

    >>> [u.username for u in user_q] # <-- query is evaluated here
    [u'admin', u'staff', u'editor']

We can build up the query by adding some clauses to it:

.. code-block:: pycon

    >>> user_q = user_q.where(User.username << ['admin', 'editor'])
    >>> user_q = user_q.order_by(User.username.desc())
    >>> [u.username for u in user_q] # <-- query is re-evaluated here
    [u'editor', u'admin']

.. _query_compare:

Looking at some simple queries
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Get active users:

.. code-block:: python

    User.select().where(User.active == True)

Get users who are either staff or superusers:

.. code-block:: python

    User.select().where(
        (User.is_staff == True) | (User.is_superuser == True))

Get tweets by user named "charlie":

.. code-block:: python

    Tweet.select().join(User).where(User.username == 'charlie')

Get tweets by staff or superusers (assumes FK relationship):

.. code-block:: python

    Tweet.select().join(User).where(
        (User.is_staff == True) | (User.is_superuser == True))

.. _where_clause:

Where clause
------------

All queries except :py:class:`InsertQuery` and :py:class:`RawQuery` support the
:py:meth:`~Query.where` method.  If you are familiar with Django's ORM, it is
analagous to the ``filter()`` method.  Inside the where clause, you will place
one or more :ref:`expressions`.

.. code-block:: python

    User.select().where(User.is_staff == True)

.. note:: ``User.select()`` is equivalent to ``SelectQuery(User)``.

.. note::
    Multiple calls to :py:meth:`~Query.where` will be ``AND``-ed together. To
    dynamically connect clauses with ``OR``:

    .. code-block:: python

        import operator
        or_clauses = reduce(operator.or_, clauses)  # OR together all clauses

.. _column-lookups:

Column lookups
^^^^^^^^^^^^^^

The following types of comparisons are supported by peewee:

================ =======================================
Comparison       Meaning
================ =======================================
``==``           x equals y
``<``            x is less than y
``<=``           x is less than or equal to y
``>``            x is greater than y
``>=``           x is greater than or equal to y
``!=``           x is not equal to y
``<<``           x IN y, where y is a list or query
``>>``           x IS y, where y is None/NULL
``%``            x LIKE y where y may contain wildcards
``**``           x ILIKE y where y may contain wildcards
================ =======================================

You can also perform "BETWEEN" lookups by calling the ``between`` method on
a field object:

.. code-block:: python

    Employee.select().where(Employee.salary.between(50000, 60000))

.. note::
    Because SQLite's ``LIKE`` operation is case-insensitive by default,
    peewee will use the SQLite ``GLOB`` operation for case-sensitive searches.
    The glob operation uses asterisks for wildcards as opposed to the usual
    percent-sign.  **If you are using SQLite and want case-sensitive partial
    string matching, remember to use asterisks for the wildcard (``*``).**

.. _custom-lookups:

Adding user-defined operators
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Because I ran out of python operators to overload, there are some missing
operators in peewee, for instance `modulo <https://github.com/coleifer/peewee/issues/177>`_.
If you find that you need to support an operator that is not in the table
above, it is very easy to add your own.

Here is how you might add support for ``modulo`` and ``regexp`` in SQLite:

.. code-block:: python

    from peewee import *
    from peewee import Expr # the building block for expressions
{'fraction_non_alphanumeric': 0.17870257037943696, 'fraction_numerical': 0.005630354957160343, 'mean_word_length': 3.2579468473163105, 'pattern_counts': {'":': 2, '<': 25, '<?xml version=': 0, '>': 51, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1647414', 'n_tokens_mistral': 2501, 'n_tokens_neox': 2374, 'n_words': 903}