pydough 1.0.17
Analytics DSL for Python
Requires Python: >=3.10
Keywords: analytics

# PyDough

PyDough is an alternative DSL that can be used to solve analytical problems by phrasing questions in terms of a logical document model instead of translating to relational SQL logic.

## What Is PyDough

PyDough allows expressing analytical questions with hierarchical thinking, as seen in models such as [MongoDB](https://www.mongodb.com/docs/manual/data-modeling/), since that mental model is closer to human linguistics than a relational model.
Unlike MongoDB, PyDough only uses a logical document model for abstractly explaining & interacting with data, rather than a physical document model to store the data.
PyDough code can be written in and interleaved with Python code, and practices a lazy evaluation scheme that does not qualify or execute any logic until requested.
PyDough executes by translating its logic into SQL which it can directly executing in an arbitrary database.

Consider the following information represented by the tables in a database:
- There are people; each person has a name, ssn, birth date, records of jobs they have had, and records of schools they have attended.
- There are employment records; each job record has the ssn of the person being employed, the name of the company, and the total income they made from the job.
- There are education records; each education record has the ssn of the person attending the school, the name of the school, and the total tuition they paid to that school.

Suppose I want to know for every person their name & the total income they've made from all jobs minus the total tuition paid to all schools. However, I want to include people who have never had a job or never attended any schools, and I need to account for people who could have had multiple jobs or attended multiple schools.
The following PyDough snippet solves this problem:

```py
result = People.CALCULATE(
    name,
    net_income = SUM(jobs.income_earned) - SUM(schools.tuition_paid)
)
pydough.to_df(result)
```

However, if answering the question with SQL, I would need to write the following less-intuitive SQL query:

```sql
SELECT
    P.name AS name,
    COALESCE(T1.total_income_earned, 0) - COALESCE(T2.total_tuition_paid, 0) AS net_income
FROM PEOPLE AS P
LEFT JOIN (
    SELECT person_ssn, SUM(income_earned) AS total_income_earned
    FROM EMPLOYMENT_RECORDS
    GROUP BY person_ssn
) AS J
ON P.ssn = J.person_ssn
LEFT JOIN (
    SELECT person_ssn, SUM(tuition_paid) AS total_tuition_paid
    FROM EDUCATION_RECORDS
) AS S
ON P.ssn = S.person_ssn
```

Internally, PyDough solves the question by translating the much simpler logical document model logic into SQL, which can be directly executed on a database. Even if the same SQL is generated by PyDough as the example above, all a user needs to worry about is writing the much smaller PyDough code snippet in Python.

Currently, the main mechanism to execute PyDough code is via Jupyter notebooks with a special cell magic. See the usage guide and demo notebooks for more details.

## Why Build PyDough?

PyDough as a DSL has several benefits over other solutions, both for human use and LLM generation:
- ORMs still require understanding & writing SQL, including dealing directly with joins. If a human or AI is bad at writing SQL, they will be just as bad at writing ORM-based code. PyDough, on the other hand, abstracts away joins in favor of thinking about logical relationships between collections & sub-collections.
- The complex semantics of aggregation keys, different types of joins, and aggregating before vs after joining are all abstracted away by PyDough. These details require much deeper understanding of SQL semantics than most have time to learn how to do correctly, meaning that PyDough can have a lower learning curve to write correct code for complex questions.
- When a question is being asked, the PyDough code to answer it will look more similar to the text of the question than the SQL text would. This makes LLM generation of PyDough code simpler since there is a stronger correlation between a question asked and the PyDough code to answer it.
- Often, PyDough code will be significantly more compact than equivalent SQL text, and therefore easier for a human to verify for logical correctness.
- PyDough is portable between various database execution solutions, so you are not locked into one data storage solution while using PyDough.

## Learning About PyDough

Refer to these documents to learn how to use PyDough:

- [Spec for the PyDough DSL](https://github.com/bodo-ai/PyDough/blob/main/documentation/dsl.md)
- [Spec for the PyDough metadata](https://github.com/bodo-ai/PyDough/blob/main/documentation/metadata.md)
- [List of builtin PyDough functions](https://github.com/bodo-ai/PyDough/blob/main/documentation/functions.md)
- [Usage guide for PyDough](https://github.com/bodo-ai/PyDough/blob/main/documentation/usage.md)

## Installing or Developing PyDough

PyDough releases are [available on PyPI](https://pypi.org/project/pydough/) and can be installed via pip:

```
pip install pydough
```

For local development, PyDough uses `uv` as a package manager.
Please refer to their docs for [installation](https://docs.astral.sh/uv/getting-started/).


To run testing commands after installing `uv`, run the following command:

```bash
uv run pytest <pytest_arguments>
```

If you want to skip tests that execute runtime results because they are slower,
make sure to include `-m "not execute"` in the pytest arguments.

Note: some tests may require an additional setup to run successfully.
The [demos](https://github.com/bodo-ai/PyDough/blob/main/demos/README.md) directory 
contains more information on how to setup the TPC-H sqlite database. For
testing, the `tpch.db` file must be located in the `tests` directory.
Additionally, the [`setup_defog.sh`](https://github.com/bodo-ai/PyDough/blob/main/tests/setup_defog.sh)
script must be run so that the `defog.db` file is located in the `tests` directory.

## Running CI Tests

When submitting a PR, you can control which CI tests run by adding special flags
to your **latest commit message**.

**Note:** All flags are **case-insensitive**.

- To run **PyDough CI tests**, add: `[run CI]` (only runs **SQLite tests**, no other SQL dialects)  
- To run **PyDough and all dialect tests**, add: `[run all]`  
- To run **specific dialect tests**, use the corresponding flag as described below.

### Running Snowflake Tests on CI
To run **Snowflake CI tests**, add the flag `[run SF]` to your commit message.

**Running Snowflake tests locally:**

1. Install the Snowflake Connector for Python with Pandas support
    ```bash
    pip install "snowflake-connector-python[pandas]"
    ```

2. Set your Snowflake credentials as environment variables:
    ```bash
        export SF_USERNAME="your_username"
        export SF_PASSWORD="your_password"
        export SF_ACCOUNT="your_account"
    ```

### Running MySQL Tests on CI
To run **MySQL CI tests**, add the flag `[run mysql]` to your commit message.

**Running MySQL tests locally:**

1. Make sure you have [**Docker Desktop**](https://www.docker.com/get-started/)
 installed and running.

2. Install the MySQL Connector for Python
    ```bash
    pip install mysql-connector-python
    ```

3. Set your MySQL credentials as environment variables:
    ```bash
        export MYSQL_USERNAME="your_username"
        export MYSQL_PASSWORD="your_password"

## Running Postgres Tests on CI
To run **Postgres CI tests**, add the flag `[run postgres]` to your commit message.

**Running Postgres tests locally:**

1. Make sure you have [**Docker Desktop**](https://www.docker.com/get-started/)
 installed and running.

2. Install the Postgres Connector for Python
    ```bash
    pip install psycopg2-binary
    ```
    
3. Set your Postgres credentials as environment variables:
    ```bash
        export POSTGRES_DB="your_database"
        export POSTGRES_USER="your_username"
        export POSTGRES_PASSWORD="your_password"
    ```

## Runtime Dependencies

PyDough requires having the following Python modules installed to use
the library:

- pytz, pandas, sqlglot

The full list of dependencies can be found in the `pyproject.toml` file.

## Demo Notebooks

The `demo` folder contains a series of example Jupyter Notebooks
that can be used to understand PyDough's capabilities. We recommend any new user start
with the [demo readme](https://github.com/bodo-ai/PyDough/blob/main/demos/README.md) and then walk through the example Juypter notebooks.

## Meta Visualizer

The `meta_visualizer` folder contains a simple web application that can be used to visualize the metadata of a PyDough Knowledge Graph.
It displays the collections, properties, and relationships between collections in a knowledge graph and can be used to check the relations between collections to debug PyDough queries as well as build complex queries.

Please refer to the [meta visualizer README](https://github.com/bodo-ai/PyDough/blob/main/meta_visualizer/README.md) for more information on features and usage.