A SQLAlchemy plugin to add support for PostgreSQL WINDOW clause
Project description
sqlalchemy_window
A SQLAlchemy plugin to add support for PostgreSQL WINDOW clause.
NOTE: only supports SQLAlchemy 2.0 and higher.
Example
Newer versions (>= 0.1.3) allow you to use Window.over_self
interface
for window functions:
import sqlalchemy as sa
from sqlalchemy_window import select, window
metadata = sa.MetaData()
table = sa.Table(
"prices",
metadata,
sa.Column("asset", sa.String(16), primary_key=True),
sa.Column("ts", sa.DateTime(timezone=True), primary_key=True),
sa.Column("price", sa.Numeric, nullable=False),
)
w = window("w", partition_by=table.c["asset"], order_by=table.c["ts"], range_=(None, None))
query = select(
w.over_self(sa.func.first_value(table.c["price"])).label("open"),
w.over_self(sa.func.max(table.c["price"])).label("high"),
w.over_self(sa.func.min(table.c["price"])).label("low"),
w.over_self(sa.func.last_value(table.c["price"])).label("close"),
).where(sa.func.cast(table.c["ts"], sa.Date) == '2023-01-01').window(w)
Before that, to build above query you would use over_window
factory:
query = select(
over_window(sa.func.first_value(table.c["price"]), w).label("open"),
over_window(sa.func.max(table.c["price"]), w).label("high"),
over_window(sa.func.min(table.c["price"]), w).label("low"),
over_window(sa.func.last_value(table.c["price"]), w).label("close"),
).where(sa.func.cast(table.c["ts"], sa.Date) == '2023-01-01').window(w)
You can still use over_window
in newer versions.
Development
To setup a development environment run:
python3 -m venv venv
source ./venv/bin/activate
pip install --upgrade pip
pip install -r dev-requirements.txt -e .
pre-commit install
Running tests:
make test
make coverage
A waterfountain1996 project.
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Close
Hashes for sqlalchemy_window-0.2.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | bd043141905987a79ecef0e13413190c8cb2d167c69a83a77e4b81fd8ae58495 |
|
MD5 | d8ae21ffad3878ddd89e7d9d282d79ef |
|
BLAKE2b-256 | e64a03259346785bd2cac30c559d74f1d37ef1d741caab777ae13e74c3a494e2 |