sqlalchemy.cast.label

Here are the examples of the python api sqlalchemy.cast.label taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

5 Examples 7

0 Source : test_pagination.py
with MIT License
from dialoguemd

def app(user_cls, note_cls):
    from fastapi_sqla import (
        Page,
        Paginate,
        PaginateSignature,
        Pagination,
        Session,
        setup,
    )

    app = FastAPI()
    setup(app)

    class Note(BaseModel):
        id: int
        content: str

        class Config:
            orm_mode = True

    class User(BaseModel):
        id: int
        name: str

        class Config:
            orm_mode = True

    class UserWithNotes(User):
        notes: List[Note]

        class Config:
            orm_mode = True

    class UserWithNotesCount(User):
        notes_count: int

    class Meta(BaseModel):
        notes_count: int

    class UserWithMeta(User):
        meta: Meta

    @app.get("/v1/users", response_model=Page[UserWithNotes])
    def sqla_13_all_users(session: Session = Depends(), paginate: Paginate = Depends()):
        query = (
            session.query(user_cls)
            .options(joinedload(user_cls.notes))
            .order_by(user_cls.id)
        )
        return paginate(query)

    @app.get("/v2/users", response_model=Page[UserWithNotes])
    def sqla_14_all_users(paginate: Paginate = Depends()):
        query = (
            select(user_cls).options(joinedload(user_cls.notes)).order_by(user_cls.id)
        )
        return paginate(query)

    @app.get("/v2/users-with-notes-count", response_model=Page[UserWithNotesCount])
    def sqla_14_all_users_with_notes_count(paginate: Paginate = Depends()):
        query = (
            select(
                user_cls.id, user_cls.name, func.count(note_cls.id).label("notes_count")
            )
            .join(note_cls)
            .order_by(user_cls.id)
            .group_by(user_cls)
        )
        return paginate(query, scalars=False)

    @app.get("/v2/query-with-json-result", response_model=Page[UserWithMeta])
    def query_with_JSON_result(paginate: Paginate = Depends()):
        query = (
            select(
                user_cls.id,
                user_cls.name,
                cast(
                    func.format('{"notes_count": %s}', func.count(note_cls.id)), JSON
                ).label("meta"),
            )
            .join(note_cls)
            .order_by(user_cls.id)
            .group_by(user_cls)
        )
        return paginate(query, scalars=False)

    def count_user_notes(user_id: int, session: Session = Depends()) -> int:
        return session.execute(
            select(func.count(note_cls.id)).where(note_cls.user_id == user_id)
        ).scalar()

    CustomPaginate: PaginateSignature = Pagination(query_count=count_user_notes)

    @app.get("/v2/users/{user_id}/notes", response_model=Page[Note])
    def list_user_notes_with_custom_pagination(
        user_id: int, paginate: CustomPaginate = Depends()
    ):
        return paginate(select(note_cls).where(note_cls.user_id == user_id))

    return app


@fixture

0 Source : test_selectable.py
with Apache License 2.0
from gethue

    def test_reduce_aliased_union_2(self):
        metadata = MetaData()
        page_table = Table(
            "page", metadata, Column("id", Integer, primary_key=True)
        )
        magazine_page_table = Table(
            "magazine_page",
            metadata,
            Column(
                "page_id", Integer, ForeignKey("page.id"), primary_key=True
            ),
        )
        classified_page_table = Table(
            "classified_page",
            metadata,
            Column(
                "magazine_page_id",
                Integer,
                ForeignKey("magazine_page.page_id"),
                primary_key=True,
            ),
        )

        # this is essentially the union formed by the ORM's
        # polymorphic_union function. we define two versions with
        # different ordering of selects.
        #
        # the first selectable has the "real" column
        # classified_page.magazine_page_id

        pjoin = union(
            select(
                [
                    page_table.c.id,
                    magazine_page_table.c.page_id,
                    classified_page_table.c.magazine_page_id,
                ]
            ).select_from(
                page_table.join(magazine_page_table).join(
                    classified_page_table
                )
            ),
            select(
                [
                    page_table.c.id,
                    magazine_page_table.c.page_id,
                    cast(null(), Integer).label("magazine_page_id"),
                ]
            ).select_from(page_table.join(magazine_page_table)),
        ).alias("pjoin")
        eq_(
            util.column_set(
                sql_util.reduce_columns(
                    [pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id]
                )
            ),
            util.column_set([pjoin.c.id]),
        )

        # the first selectable has a CAST, which is a placeholder for
        # classified_page.magazine_page_id in the second selectable.
        # reduce_columns needs to take into account all foreign keys
        # derived from pjoin.c.magazine_page_id. the UNION construct
        # currently makes the external column look like that of the
        # first selectable only.

        pjoin = union(
            select(
                [
                    page_table.c.id,
                    magazine_page_table.c.page_id,
                    cast(null(), Integer).label("magazine_page_id"),
                ]
            ).select_from(page_table.join(magazine_page_table)),
            select(
                [
                    page_table.c.id,
                    magazine_page_table.c.page_id,
                    classified_page_table.c.magazine_page_id,
                ]
            ).select_from(
                page_table.join(magazine_page_table).join(
                    classified_page_table
                )
            ),
        ).alias("pjoin")
        eq_(
            util.column_set(
                sql_util.reduce_columns(
                    [pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id]
                )
            ),
            util.column_set([pjoin.c.id]),
        )


class DerivedTest(fixtures.TestBase, AssertsExecutionResults):

0 Source : plot.py
with MIT License
from Nukesor

def get_user_activity(session):
    """Create a plot showing the user statistics."""
    # Create a subquery to ensure that the user fired a inline query
    # Group the new users by date
    creation_date = cast(User.created_at, Date).label("creation_date")
    all_users_subquery = (
        session.query(creation_date, func.count(User.id).label("count"))
        .filter(User.inline_queries.any())
        .group_by(creation_date)
        .subquery()
    )

    # Create a running window which sums all users up to this point for the current millennium ;P
    all_users = (
        session.query(
            all_users_subquery.c.creation_date,
            cast(
                func.sum(all_users_subquery.c.count).over(
                    partition_by=func.extract(
                        "millennium", all_users_subquery.c.creation_date
                    ),
                    order_by=all_users_subquery.c.creation_date.asc(),
                ),
                Integer,
            ).label("running_total"),
        )
        .order_by(all_users_subquery.c.creation_date)
        .all()
    )
    all_users = [("all", q[0], q[1]) for q in all_users]

    # Combine the results in a single dataframe and name the columns
    user_statistics = all_users
    dataframe = pandas.DataFrame(user_statistics, columns=["type", "date", "users"])

    # Plot each result set
    fig, ax = plt.subplots(figsize=(30, 15), dpi=120)
    for key, group in dataframe.groupby(["type"]):
        ax = group.plot(ax=ax, kind="line", x="date", y="users", label=key)

    image = image_from_figure(fig)
    image.name = "user_statistics.png"
    return image

0 Source : plot.py
with MIT License
from Nukesor

def get_user_activity(session):
    """Create a plot showing the inline usage statistics."""

    def running_window(session, subquery):
        # Create a running window which sums all users up to this point for the current millennium ;P
        users = (
            session.query(
                subquery.c.creation_date,
                cast(
                    func.sum(subquery.c.count).over(
                        partition_by=func.extract(
                            "millennium", subquery.c.creation_date
                        ),
                        order_by=subquery.c.creation_date.asc(),
                    ),
                    Integer,
                ).label("running_total"),
            )
            .order_by(subquery.c.creation_date)
            .all()
        )

        return users

    # Grid style
    plt.style.use("seaborn-whitegrid")

    creation_date = func.cast(User.created_at, Date).label("creation_date")
    # Group the started users by date
    started_users_subquery = (
        session.query(creation_date, func.count(User.id).label("count"))
        .filter(User.started.is_(True))
        .group_by(creation_date)
        .order_by(creation_date)
        .subquery()
    )
    started_users = running_window(session, started_users_subquery)
    started_users = [("started", q[0], q[1]) for q in started_users]

    # Group the started users by date
    all_users_subquery = (
        session.query(creation_date, func.count(User.id).label("count"))
        .group_by(creation_date)
        .order_by(creation_date)
        .subquery()
    )
    all_users = running_window(session, all_users_subquery)
    all_users = [("all", q[0], q[1]) for q in all_users]

    # Group the started users by date
    voting_users_subquery = (
        session.query(creation_date, func.count(User.id).label("count"))
        .filter(User.votes.any())
        .group_by(creation_date)
        .order_by(creation_date)
        .subquery()
    )
    voting_users = running_window(session, voting_users_subquery)
    voting_users = [("voted", q[0], q[1]) for q in voting_users]

    # Group the started users by date
    owning_users_subquery = (
        session.query(creation_date, func.count(User.id).label("count"))
        .filter(User.polls.any())
        .group_by(creation_date)
        .order_by(creation_date)
        .subquery()
    )
    owning_users = running_window(session, owning_users_subquery)
    owning_users = [("currently owning poll", q[0], q[1]) for q in owning_users]

    # Combine the results in a single dataframe and name the columns
    user_statistics = started_users + all_users + voting_users + owning_users
    dataframe = pandas.DataFrame(user_statistics, columns=["type", "date", "users"])

    months = mdates.MonthLocator()  # every month
    months_fmt = mdates.DateFormatter("%Y-%m")

    max_value = all_users[len(all_users) - 1][2]
    magnitude = get_magnitude(max_value)

    # Plot each result set
    fig, ax = plt.subplots(figsize=(30, 15), dpi=120)
    for key, group in dataframe.groupby(["type"]):
        ax = group.plot(ax=ax, kind="line", x="date", y="users", label=key)
        ax.xaxis.set_major_locator(months)
        ax.xaxis.set_major_formatter(months_fmt)
        ax.yaxis.set_ticks(np.arange(0, max_value, math.pow(10, magnitude - 1)))

    image = image_from_figure(fig)
    image.name = "user_statistics.png"

    return image


def get_vote_activity(session):

0 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_reduce_aliased_union_2(self):
        metadata = MetaData()
        page_table = Table(
            "page", metadata, Column("id", Integer, primary_key=True)
        )
        magazine_page_table = Table(
            "magazine_page",
            metadata,
            Column(
                "page_id", Integer, ForeignKey("page.id"), primary_key=True
            ),
        )
        classified_page_table = Table(
            "classified_page",
            metadata,
            Column(
                "magazine_page_id",
                Integer,
                ForeignKey("magazine_page.page_id"),
                primary_key=True,
            ),
        )

        # this is essentially the union formed by the ORM's
        # polymorphic_union function. we define two versions with
        # different ordering of selects.
        #
        # the first selectable has the "real" column
        # classified_page.magazine_page_id

        pjoin = union(
            select(
                page_table.c.id,
                magazine_page_table.c.page_id,
                classified_page_table.c.magazine_page_id,
            ).select_from(
                page_table.join(magazine_page_table).join(
                    classified_page_table
                )
            ),
            select(
                page_table.c.id,
                magazine_page_table.c.page_id,
                cast(null(), Integer).label("magazine_page_id"),
            ).select_from(page_table.join(magazine_page_table)),
        ).alias("pjoin")
        eq_(
            util.column_set(
                sql_util.reduce_columns(
                    [pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id]
                )
            ),
            util.column_set([pjoin.c.id]),
        )

        # the first selectable has a CAST, which is a placeholder for
        # classified_page.magazine_page_id in the second selectable.
        # reduce_columns needs to take into account all foreign keys
        # derived from pjoin.c.magazine_page_id. the UNION construct
        # currently makes the external column look like that of the
        # first selectable only.

        pjoin = union(
            select(
                page_table.c.id,
                magazine_page_table.c.page_id,
                cast(null(), Integer).label("magazine_page_id"),
            ).select_from(page_table.join(magazine_page_table)),
            select(
                page_table.c.id,
                magazine_page_table.c.page_id,
                classified_page_table.c.magazine_page_id,
            ).select_from(
                page_table.join(magazine_page_table).join(
                    classified_page_table
                )
            ),
        ).alias("pjoin")
        eq_(
            util.column_set(
                sql_util.reduce_columns(
                    [pjoin.c.id, pjoin.c.page_id, pjoin.c.magazine_page_id]
                )
            ),
            util.column_set([pjoin.c.id]),
        )


class DerivedTest(fixtures.TestBase, AssertsExecutionResults):