<commit_before>
Entire table
~~~~~~~~~~~~

The following statement retrieves the entire table as a NumPy `recarray <https://docs.scipy.org/doc/numpy/reference/generated/numpy.recarray.html>`_.

.. code-block:: python

    data = query.fetch()

To retrieve the data as a list of ``dict``:

.. code-block:: python

    data = query.fetch(as_dict=True)

In some cases, the amount of data returned by fetch can be quite large; in these cases it can be useful to use the ``size_on_disk`` attribute to determine if running a bare fetch would be wise.
Please note that it is only currently possible to query the size of entire tables stored directly in the database at this time.

As separate variables
~~~~~~~~~~~~~~~~~~~~~

.. code-block:: python

    name, img = query.fetch1('name', 'image')  # when query has exactly one entity
    name, img = query.fetch('name', 'image')  # [name, ...] [image, ...]

Primary key values
~~~~~~~~~~~~~~~~~~

.. code-block:: python

    keydict = tab.fetch1("KEY")  # single key dict when tab has exactly one entity
    keylist = tab.fetch("KEY")  # list of key dictionaries [{}, ...]

``KEY`` can also used when returning attribute values as separate variables, such that one of the returned variables contains the entire primary keys.

Sorting and limiting the results
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To sort the result, add the additional ``order_by`` argument to ``fetch`` .

.. code-block:: python

    data = query.fetch(order_by='name')

The ``order_by`` argument can be a string specifying the attribute to sort by. By default the sort is in ascending order. Use ``'attr desc'`` to sort in descending order by attribute ``attr``.  The value can also be a sequence of strings, in which case, the sort performed on all the attributes jointly in the order specified.

The special attribute name ``"KEY"`` represents the primary key attributes in order that they appear in the index. Otherwise, this name can be use as any other argument.

If an attribute happens to be an SQL reserved word, it needs to be enclosed in backquotes.  For example
...code: python
    data = query.fetch(order_by='`select` desc')

The `order_by` value is eventually passed  to the ``ORDER BY`` clause <https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html>`_

Similarly, the ``limit`` and ``offset`` arguments can be used to limit the result to a subset of entities.

For example, one could do the following:

.. code-block:: python

    data = query.fetch(order_by='name', limit=10, offset=5)

Usage with Pandas
~~~~~~~~~~~~~~~~~

The ``pandas`` `library <http://pandas.pydata.org/>`_ is a popular library for data analysis in Python which can easily be used with DataJoint query results.
Since the records returned by ``fetch()`` are contained within a ``numpy.recarray``, they can be easily converted to ``pandas.DataFrame`` objects by passing them into the ``pandas.DataFrame`` constructor.
For example:

.. code-block:: python

    import pandas as pd
    frame = pd.DataFrame(tab.fetch())

Calling ``fetch()`` with the argument ``format="frame"`` returns results as ``pandas.DataFrame`` objects indexed by the table's primary key attributes.

.. code-block:: python

  frame = tab.fetch(format="frame")

Returning results as a ``DataFrame`` is not possible when fetching a particular subset of attributes or when ``as_dict`` is set to ``True``.<commit_msg>03-Fetch_lang1.rst: add more order_by examples<commit_after>
Entire table
~~~~~~~~~~~~

The following statement retrieves the entire table as a NumPy `recarray <https://docs.scipy.org/doc/numpy/reference/generated/numpy.recarray.html>`_.

.. code-block:: python

    data = query.fetch()

To retrieve the data as a list of ``dict``:

.. code-block:: python

    data = query.fetch(as_dict=True)

In some cases, the amount of data returned by fetch can be quite large; in these cases it can be useful to use the ``size_on_disk`` attribute to determine if running a bare fetch would be wise.
Please note that it is only currently possible to query the size of entire tables stored directly in the database at this time.

As separate variables
~~~~~~~~~~~~~~~~~~~~~

.. code-block:: python

    name, img = query.fetch1('name', 'image')  # when query has exactly one entity
    name, img = query.fetch('name', 'image')  # [name, ...] [image, ...]

Primary key values
~~~~~~~~~~~~~~~~~~

.. code-block:: python

    keydict = tab.fetch1("KEY")  # single key dict when tab has exactly one entity
    keylist = tab.fetch("KEY")  # list of key dictionaries [{}, ...]

``KEY`` can also used when returning attribute values as separate variables, such that one of the returned variables contains the entire primary keys.

Sorting and limiting the results
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To sort the result, add the additional ``order_by`` argument to ``fetch`` .

.. code-block:: python

    data = query.fetch(order_by='name')

The ``order_by`` argument can be a string specifying the attribute to sort by. By default the sort is in ascending order. Use ``'attr desc'`` to sort in descending order by attribute ``attr``.  The value can also be a sequence of strings, in which case, the sort performed on all the attributes jointly in the order specified.

The special attribute name ``"KEY"`` represents the primary key attributes in order that they appear in the index. Otherwise, this name can be use as any other argument.

If an attribute happens to be an SQL reserved word, it needs to be enclosed in backquotes.  For example
...code: python
    data = query.fetch(order_by='`select` desc')

The `order_by` value is eventually passed  to the ``ORDER BY`` clause <https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html>`_

Similarly, the ``limit`` and ``offset`` arguments can be used to limit the result to a subset of entities.

For example, one could do the following:

.. code-block:: python

    data = query.fetch(order_by='name', limit=10, offset=5)
    data = query.fetch(order_by='name desc')  # sort in descending order
    data = query.fetch(order_by=('name desc', 'year'))  # by name first, year second 
    data = query.fetch(order_by='KEY')  # sort by the primary key
    data = query.fetch(order_by=('name', 'KEY desc'))  # sort by name but for same names order by primary key

Usage with Pandas
~~~~~~~~~~~~~~~~~

The ``pandas`` `library <http://pandas.pydata.org/>`_ is a popular library for data analysis in Python which can easily be used with DataJoint query results.
Since the records returned by ``fetch()`` are contained within a ``numpy.recarray``, they can be easily converted to ``pandas.DataFrame`` objects by passing them into the ``pandas.DataFrame`` constructor.
For example:

.. code-block:: python

    import pandas as pd
    frame = pd.DataFrame(tab.fetch())

Calling ``fetch()`` with the argument ``format="frame"`` returns results as ``pandas.DataFrame`` objects indexed by the table's primary key attributes.

.. code-block:: python

  frame = tab.fetch(format="frame")

Returning results as a ``DataFrame`` is not possible when fetching a particular subset of attributes or when ``as_dict`` is set to ``True``.
{'fraction_non_alphanumeric': 0.12870733072188026, 'fraction_numerical': 0.002378287632904309, 'mean_word_length': 4.187953555878084, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 4, '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': '1993766', 'n_tokens_mistral': 1982, 'n_tokens_neox': 1928, 'n_words': 907}