Repository: kayak/pypika
Pull request #759 (2023-10-09 16:03:29)

add pipe operator on QueryBuilder
pipe method like in pandas in order to do some chaining. Related to this issue [here](https://github.com/kayak/pypika/issues/723).

coveralls: 
[![Coverage Status](https://coveralls.io/builds/63202333/badge)](https://coveralls.io/builds/63202333)

coverage: 98.385% (+0.003%) from 98.382% when pulling **b0abc3565902ae9b3e6f88e3e2b85057adb4315e on wd60622:pipe_operator** into **f46ec8522c36911bff76f0c36a00206ce2e31049 on kayak:master**.

AzisK: Thanks for you work!

Patch:
diff --git a/.gitignore b/.gitignore
index f7aa02f..2ef1831 100644
--- a/.gitignore
+++ b/.gitignore
@@ -70,6 +70,7 @@ target/
 # virtualenv
 venv/
 ENV/
+.venv/
 
 # OS X
 .DS_Store
diff --git a/CONTRIBUTING.rst b/CONTRIBUTING.rst
new file mode 100644
index 0000000..efbb472
--- /dev/null
+++ b/CONTRIBUTING.rst
@@ -0,0 +1,101 @@
+Guidelines for Contributing
+===========================
+
+PyPika welcomes contributions in all forms. These may be bugs, feature requests, documentation, or examples. Please feel free to:
+
+#. Submitting an issue
+#. Opening a pull request
+#. Helping with outstanding issues and pull requests 
+
+Open an issue
+-------------
+
+If you find a bug or have a feature request, please `open an issue <https://github.com/kayak/pypika/issues>`_ on GitHub. Please just check that the issue doesn't already exist before opening a new one.
+
+Local development steps
+-----------------------
+
+Create a forked branch of the repo
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+Do this once but keep it up to date
+
+#. `Fork the kayak/PyPika repo GitHub <https://github.com/kayak/pypika/fork>`_
+#. Clone forked repo and set upstream
+
+    .. code-block:: bash
+
+        git clone git@github.com:<your-username>/pypika.git
+        cd pypika
+        git remote add upstream git@github.com:kayak/pypika.git
+
+Setup local development environment
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+#. Setup up python virtual environment
+
+    Create and activate the environment. Here is an example using ``venv`` from the standard library:
+
+    .. code-block:: bash
+
+        python -m venv .venv
+        source .venv/bin/activate
+
+#. Install python dependencies for development
+
+    With the virtual environment activated, install the development requirements, pre-commit, and the package itself:
+
+    .. code-block:: bash
+
+        make install
+
+#. Run the tests
+
+    The unit tests are run with ``unittest`` via ``tox``. To run the tests locally:
+
+    .. code-block:: bash 
+
+        make test
+
+    These tests will also run on GitHub Actions when you open a pull request.
+
+#. Build the docs locally
+
+    Our docs are built with Sphinx. To build the docs locally:
+
+    .. code-block:: bash 
+
+        make docs.build
+
+    Open the docs in your browser. For instance, on macOS:
+
+    .. code-block:: bash
+
+        open docs/_build/index.html
+
+Pull Request checklist
+----------------------
+
+Please check that your pull request meets the following criteria:
+
+- Unit tests pass
+- pre-commit hooks pass
+- Docstring and examples and checking for correct render in the docs
+
+Documentation
+-------------
+
+Documentation is built with Sphinx and hosted on `Read the Docs <https://pypika.readthedocs.io/en/latest/>`_. The latest builds are displayed on their site `here <https://readthedocs.org/projects/pypika/builds/>`_.
+
+The code documentation is to be written in the docstrings of the code itself or in the various ``.rst`` files in project root or the ``docs/`` directory.
+
+The docstrings can be in either `Numpy <https://numpydoc.readthedocs.io/en/latest/format.html>`_ or `Sphinx <https://sphinx-rtd-tutorial.readthedocs.io/en/latest/docstrings.html>`_ format.
+
+Automations
+-----------
+
+We use `pre-commit <https://pre-commit.com/>`_ to automate format checks. Install the pre-commit hooks with the ``make install`` command described above.
+
+GitHub Actions runs both format checks and unit tests on every pull request.
+
+**NOTE:** The hosted documentation is built separately from the GitHub Actions workflow. To build the docs locally, use the ``make docs.build`` command described above.
\ No newline at end of file
diff --git a/Makefile b/Makefile
new file mode 100644
index 0000000..aec52ac
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,23 @@
+# You can set these variables from the command line.
+SPHINXBUILD   = sphinx-build
+SOURCEDIR     = docs
+BUILDDIR      = docs/_build
+
+
+help:            ## Show this help.
+	@fgrep -h "##" $(MAKEFILE_LIST) | fgrep -v fgrep | sed -e 's/\\$$//' | sed -e 's/##//'
+
+install:         ## Install development dependencies
+	pip install -r requirements-dev.txt pre-commit -e . 
+	pre-commit install
+
+test:            ## Run tests
+	tox
+
+docs.build:      ## Build the documentation
+	$(SPHINXBUILD) $(SOURCEDIR) $(BUILDDIR) -b html -E
+	@echo
+	@echo "Build finished. The HTML pages are in $(BUILDDIR)."
+
+docs.clean:      ## Remove the generated documentation
+	rm -rf $(BUILDDIR)
diff --git a/README.rst b/README.rst
index 534d76d..31bb94f 100644
--- a/README.rst
+++ b/README.rst
@@ -1368,11 +1368,71 @@ This produces:
 
     DROP INDEX IF EXISTS my_index
 
+
+Chaining Functions
+^^^^^^^^^^^^^^^^^^
+
+The ``QueryBuilder.pipe`` method gives a more readable alternative while chaining functions.
+
+.. code-block:: python 
+
+    # This 
+    (
+        query
+        .pipe(func1, *args)
+        .pipe(func2, **kwargs)
+        .pipe(func3)
+    )
+
+    # Is equivalent to this
+    func3(func2(func1(query, *args), **kwargs))
+
+Or for a more concrete example:
+
+.. code-block:: python 
+
+    from pypika import Field, Query, functions as fn
+    from pypika.queries import QueryBuilder
+
+    def filter_days(query: QueryBuilder, col, num_days: int) -> QueryBuilder: 
+        if isinstance(col, str): 
+            col = Field(col)
+
+        return query.where(col > fn.Now() - num_days)
+
+    def count_groups(query: QueryBuilder, *groups) -> QueryBuilder: 
+        return query.groupby(*groups).select(*groups, fn.Count("*").as_("n_rows"))
+
+    base_query = Query.from_("table")
+
+    query = (
+        base_query
+        .pipe(filter_days, "date", num_days=7)
+        .pipe(count_groups, "col1", "col2")
+    )
+
+This produces: 
+
+.. code-block:: sql
+
+    SELECT "col1","col2",COUNT(*) n_rows 
+    FROM "table" 
+    WHERE "date">NOW()-7 
+    GROUP BY "col1","col2"
+
 .. _tutorial_end:
 
+.. _contributing_start: 
 
-.. _license_start:
+Contributing
+------------
+
+We welcome community contributions to |Brand|. Please see the `contributing guide <6_contributing.html>`_ to more info.
 
+.. _contributing_end:
+
+
+.. _license_start:
 
 License
 -------
diff --git a/docs/6_contributing.rst b/docs/6_contributing.rst
new file mode 100644
index 0000000..3bdd7dc
--- /dev/null
+++ b/docs/6_contributing.rst
@@ -0,0 +1,1 @@
+.. include:: ../CONTRIBUTING.rst
\ No newline at end of file
diff --git a/docs/api/pypika.analytics.rst b/docs/api/pypika.analytics.rst
new file mode 100644
index 0000000..36ae82e
--- /dev/null
+++ b/docs/api/pypika.analytics.rst
@@ -0,0 +1,7 @@
+pypika.analytics module
+=======================
+
+.. automodule:: pypika.analytics
+    :members:
+    :undoc-members:
+    :show-inheritance:
\ No newline at end of file
diff --git a/docs/api/pypika.dialects.rst b/docs/api/pypika.dialects.rst
new file mode 100644
index 0000000..0c10e70
--- /dev/null
+++ b/docs/api/pypika.dialects.rst
@@ -0,0 +1,7 @@
+pypika.dialects module
+======================
+
+.. automodule:: pypika.dialects
+    :members:
+    :undoc-members:
+    :show-inheritance:
\ No newline at end of file
diff --git a/docs/api/pypika.pseudocolumns.rst b/docs/api/pypika.pseudocolumns.rst
new file mode 100644
index 0000000..dc57833
--- /dev/null
+++ b/docs/api/pypika.pseudocolumns.rst
@@ -0,0 +1,7 @@
+pypika.pseudocolumns module
+===========================
+
+.. automodule:: pypika.pseudocolumns
+    :members:
+    :undoc-members:
+    :show-inheritance:
\ No newline at end of file
diff --git a/docs/api/pypika.rst b/docs/api/pypika.rst
index bff77c9..2628284 100644
--- a/docs/api/pypika.rst
+++ b/docs/api/pypika.rst
@@ -3,9 +3,11 @@ pypika package
 
 .. toctree::
 
-
+   pypika.analytics
+   pypika.dialects
    pypika.enums
    pypika.functions
+   pypika.pseudocolumns
    pypika.queries
    pypika.terms
    pypika.utils
diff --git a/docs/conf.py b/docs/conf.py
index 9f49b4f..57f8d39 100644
--- a/docs/conf.py
+++ b/docs/conf.py
@@ -39,6 +39,7 @@ extensions = [
     'sphinx.ext.coverage',
     'sphinx.ext.imgmath',
     'sphinx.ext.viewcode',
+    'numpydoc',  
 ]
 
 # Add any paths that contain templates here, relative to this directory.
diff --git a/docs/index.rst b/docs/index.rst
index 9d7a3c4..72e39ea 100644
--- a/docs/index.rst
+++ b/docs/index.rst
@@ -9,6 +9,10 @@ PyPika - Python Query Builder
    :start-after: _appendix_start:
    :end-before:  _appendix_end:
 
+.. include:: ../README.rst
+   :start-after: _contributing_start:
+   :end-before:  _contributing_end:
+
 Contents
 --------
 
@@ -20,6 +24,7 @@ Contents
     3_advanced
     4_extending
     5_reference
+    6_contributing
 
 Indices and tables
 ------------------
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 538bdfb..a875ae0 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -1,35 +1,49 @@
 """
 PyPika is divided into a couple of modules, primarily the ``queries`` and ``terms`` modules.
 
-pypika.queries
---------------
+pypika.analytics
+----------------
 
-This is where the ``Query`` class can be found which is the core class in PyPika.  Also, other top level classes such
-as ``Table`` can be found here.  ``Query`` is a container that holds all of the ``Term`` types together and also
-serializes the builder to a string.
+Wrappers for SQL analytic functions
 
-pypika.terms
+pypika.dialects
+---------------
+
+This contains all of the dialect specific implementations of the ``Query`` class.
+
+pypika.enums
 ------------
 
-This module contains the classes which represent individual parts of queries that extend the ``Term`` base class.
+Enumerated values are kept in this package which are used as options for Queries and Terms.
 
 pypika.functions
 ----------------
 
 Wrappers for common SQL functions are stored in this package.
 
-pypika.enums
-------------
+pypika.pseudocolumns
+--------------------
 
-Enumerated values are kept in this package which are used as options for Queries and Terms.
+Wrappers for common SQL pseudocolumns are stored in this package.
+
+pypika.queries
+--------------
+
+This is where the ``Query`` class can be found which is the core class in PyPika.  Also, other top level classes such
+as ``Table`` can be found here.  ``Query`` is a container that holds all of the ``Term`` types together and also
+serializes the builder to a string.
 
+pypika.terms
+------------
+
+This module contains the classes which represent individual parts of queries that extend the ``Term`` base class.
 
 pypika.utils
 ------------
 
 This contains all of the utility classes such as exceptions and decorators.
-
 """
+
 # noinspection PyUnresolvedReferences
 from pypika.dialects import (
     ClickHouseQuery,
diff --git a/pypika/queries.py b/pypika/queries.py
index 76eeea2..c51c6b2 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -350,6 +350,15 @@ class Query:
     pattern.
 
     This class is immutable.
+
+    Examples
+    --------
+    Simple query
+
+    .. code-block:: python
+
+        from pypika import Query, Field
+        q = Query.from_('customers').select('*').where(Field("id") == 1)
     """
 
     @classmethod
@@ -367,7 +376,7 @@ class Query:
 
             An instance of a Table object or a string table name.
 
-        :returns QueryBuilder
+        :return: QueryBuilder
         """
         return cls._builder(**kwargs).from_(table)
 
@@ -458,7 +467,7 @@ class Query:
 
             An instance of a Table object or a string table name.
 
-        :returns QueryBuilder
+        :return QueryBuilder
         """
         return cls._builder(**kwargs).into(table)
 
@@ -478,7 +487,7 @@ class Query:
             A list of terms to select.  These can be any type of int, float, str, bool, or Term.  They cannot be a Field
             unless the function ``Query.from_`` is called first.
 
-        :returns QueryBuilder
+        :return: QueryBuilder
         """
         return cls._builder(**kwargs).select(*terms)
 
@@ -493,7 +502,7 @@ class Query:
 
             An instance of a Table object or a string table name.
 
-        :returns QueryBuilder
+        :return: QueryBuilder
         """
         return cls._builder(**kwargs).update(table)
 
@@ -507,7 +516,7 @@ class Query:
 
             A string table name.
 
-        :returns Table
+        :return: Table
         """
         kwargs["query_cls"] = cls
         return Table(table_name, **kwargs)
@@ -523,7 +532,7 @@ class Query:
 
             A list of string table names, or name and alias tuples.
 
-        :returns Table
+        :return: Table
         """
         kwargs["query_cls"] = cls
         return make_tables(*names, **kwargs)
@@ -1551,6 +1560,54 @@ class QueryBuilder(Selectable, Term):
             )
         )
 
+    def pipe(self, func, *args, **kwargs):
+        """Call a function on the current object and return the result.
+
+        Example usage:
+
+        .. code-block:: python
+
+            from pypika import Query, functions as fn
+            from pypika.queries import QueryBuilder
+
+            def rows_by_group(query: QueryBuilder, *groups) -> QueryBuilder:
+                return (
+                    query
+                    .select(*groups, fn.Count("*").as_("n_rows"))
+                    .groupby(*groups)
+                )
+
+            base_query = Query.from_("table")
+
+            col1_agg = base_query.pipe(rows_by_group, "col1")
+            col2_agg = base_query.pipe(rows_by_group, "col2")
+            col1_col2_agg = base_query.pipe(rows_by_group, "col1", "col2")
+
+        Makes chaining functions together easier, especially when the functions are
+        defined elsewhere. For example, you could define a function that filters
+        rows by a date range and then group by a set of columns:
+
+
+        .. code-block:: python
+
+            from datetime import datetime, timedelta
+
+            from pypika import Field
+
+            def days_since(query: QueryBuilder, n_days: int) -> QueryBuilder:
+                return (
+                    query
+                    .where("date" > fn.Date(datetime.now().date() - timedelta(days=n_days)))
+                )
+
+            (
+                base_query
+                .pipe(days_since, n_days=7)
+                .pipe(rows_by_group, "col1", "col2")
+            )
+        """
+        return func(self, *args, **kwargs)
+
 
 class Joiner:
     def __init__(
diff --git a/requirements-dev.txt b/requirements-dev.txt
index 3926afa..e8f8d7f 100644
--- a/requirements-dev.txt
+++ b/requirements-dev.txt
@@ -2,6 +2,8 @@
 Sphinx==1.6.5
 sphinx-rtd-theme==0.2.4
 bumpversion==0.5.3
+jinja2==3.0.3
+numpydoc==1.1.0
 
 # Testing
 parameterized==0.7.0


Test patch:
diff --git a/pypika/tests/test_query.py b/pypika/tests/test_query.py
index 460b7d7..ae8dc01 100644
--- a/pypika/tests/test_query.py
+++ b/pypika/tests/test_query.py
@@ -1,6 +1,6 @@
 import unittest
 
-from pypika import Case, Query, Tables, Tuple, functions
+from pypika import Case, Query, Tables, Tuple, functions, Field
 from pypika.dialects import (
     ClickHouseQuery,
     ClickHouseQueryBuilder,
@@ -204,3 +204,39 @@ class QueryBuilderTests(unittest.TestCase):
 
         with self.subTest('OracleQueryBuilder'):
             self.assertEqual(OracleQuery, OracleQueryBuilder.QUERY_CLS)
+
+    def test_pipe(self) -> None:
+        base_query = Query.from_("test")
+
+        def select(query: QueryBuilder) -> QueryBuilder:
+            return query.select("test1", "test2")
+
+        def count_group(query: QueryBuilder, *groups) -> QueryBuilder:
+            return query.groupby(*groups).select(*groups, functions.Count("*"))
+
+        for func, args, kwargs, expected_str in [
+            (select, [], {}, 'SELECT "test1","test2" FROM "test"'),
+            (
+                count_group,
+                ["test1", "test2"],
+                {},
+                'SELECT "test1","test2",COUNT(*) FROM "test" GROUP BY "test1","test2"',
+            ),
+            (count_group, ["test1"], {}, 'SELECT "test1",COUNT(*) FROM "test" GROUP BY "test1"'),
+        ]:
+            result_str = str(base_query.pipe(func, *args, **kwargs))
+            self.assertEqual(result_str, str(func(base_query, *args, **kwargs)))
+            self.assertEqual(result_str, expected_str)
+
+        def where_clause(query: QueryBuilder, num_days: int) -> QueryBuilder:
+            return query.where(Field("date") > functions.Now() - num_days)
+
+        result_str = str(base_query.pipe(select).pipe(where_clause, num_days=1))
+        self.assertEqual(
+            result_str,
+            str(select(where_clause(base_query, num_days=1))),
+        )
+        self.assertEqual(
+            result_str,
+            'SELECT "test1","test2" FROM "test" WHERE "date">NOW()-1',
+        )