mirror of
https://github.com/d3vyce/sqlalchemy-pgview.git
synced 2026-03-01 16:30:47 +01:00
60 lines
1.3 KiB
Python
60 lines
1.3 KiB
Python
from sqlalchemy import (
|
|
Column,
|
|
DateTime,
|
|
ForeignKey,
|
|
Integer,
|
|
MetaData,
|
|
Numeric,
|
|
String,
|
|
Table,
|
|
create_engine,
|
|
func,
|
|
select,
|
|
)
|
|
|
|
from sqlalchemy_pgview import MaterializedView, View
|
|
|
|
# Create engine and metadata
|
|
engine = create_engine("postgresql://postgres:postgres@localhost/postgres")
|
|
metadata = MetaData()
|
|
|
|
# Define some tables
|
|
users = Table(
|
|
"users",
|
|
metadata,
|
|
Column("id", Integer, primary_key=True),
|
|
Column("name", String(100)),
|
|
Column("email", String(100)),
|
|
)
|
|
|
|
orders = Table(
|
|
"orders",
|
|
metadata,
|
|
Column("id", Integer, primary_key=True),
|
|
Column("user_id", Integer, ForeignKey("users.id")),
|
|
Column("total", Numeric(10, 2)),
|
|
Column("created_at", DateTime, server_default=func.now()),
|
|
)
|
|
|
|
active_users = View(
|
|
"active_users",
|
|
select(users.c.id, users.c.name, users.c.email),
|
|
metadata=metadata,
|
|
)
|
|
|
|
monthly_sales = MaterializedView(
|
|
"monthly_sales",
|
|
select(
|
|
func.date_trunc("month", orders.c.created_at).label("month"),
|
|
func.count(orders.c.id).label("order_count"),
|
|
func.sum(orders.c.total).label("revenue"),
|
|
).group_by(func.date_trunc("month", orders.c.created_at)),
|
|
with_data=True,
|
|
metadata=metadata,
|
|
)
|
|
|
|
# Create tables
|
|
metadata.create_all(engine)
|
|
|
|
# metadata.drop_all(engine)
|