Python sqlalchemy.func.cast() Examples

def get_job_count(self):
        today = datetime.datetime.utcnow().date()
        return Job.query.filter(
            Job.contact_list_id == self.id,
            func.coalesce(
                Job.processing_started, Job.created_at
            ) >= today - func.coalesce(ServiceDataRetention.days_of_retention, 7)
        ).outerjoin(
            ServiceDataRetention, and_(
                self.service_id == ServiceDataRetention.service_id,
                func.cast(self.template_type, String) == func.cast(ServiceDataRetention.notification_type, String)
            )
        ).count() 

def get_inline_queries_statistics(session):
    """Create a plot showing the inline usage statistics."""
    # Get all queries over time
    all_queries = (
        session.query(cast(InlineQuery.created_at, Date), func.count(InlineQuery.id))
        .group_by(cast(InlineQuery.created_at, Date))
        .all()
    )
    all_queries = [("all", q[0], q[1]) for q in all_queries]

    # Get all successful queries over time
    successful_queries = (
        session.query(cast(InlineQuery.created_at, Date), func.count(InlineQuery.id))
        .filter(InlineQuery.sticker_file_id.isnot(None))
        .group_by(cast(InlineQuery.created_at, Date))
        .all()
    )
    successful_queries = [("successful", q[0], q[1]) for q in successful_queries]

    # Get all unsuccessful queries over time
    unsuccessful_queries = (
        session.query(cast(InlineQuery.created_at, Date), func.count(InlineQuery.id))
        .filter(InlineQuery.sticker_file_id.is_(None))
        .group_by(cast(InlineQuery.created_at, Date))
        .all()
    )
    unsuccessful_queries = [("unsuccessful", q[0], q[1]) for q in unsuccessful_queries]

    # Combine the results in a single dataframe and name the columns
    inline_queries = all_queries + successful_queries + unsuccessful_queries
    dataframe = pandas.DataFrame(inline_queries, columns=["type", "date", "queries"])

    # 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="queries", label=key)

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

def get_inline_query_performance_statistics(session):
    """Plot statistics regarding performance of inline query requests."""
    creation_date = func.cast(InlineQueryRequest.created_at, Date).label(
        "creation_date"
    )
    # Group the started users by date
    strict_search_subquery = (
        session.query(
            creation_date, func.avg(InlineQueryRequest.duration).label("count")
        )
        .group_by(creation_date)
        .order_by(creation_date)
        .all()
    )
    strict_queries = [("strict", q[0], q[1]) for q in strict_search_subquery]

    # Combine the results in a single dataframe and name the columns
    request_statistics = strict_queries
    dataframe = pandas.DataFrame(
        request_statistics, columns=["type", "date", "duration"]
    )

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

    # 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="bar", x="date", y="duration", label=key)
        ax.xaxis.set_major_locator(months)
        ax.xaxis.set_major_formatter(months_fmt)

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

def daily_profit_select():
        return select([func.cast(func.sum((SalesOrderLine.unit_price - Product.purchase_price) * SalesOrderLine.quantity)
                                 / func.greatest(func.cast(func.date_part('DAY', func.current_date() - Product.create_date), Integer), 1), Numeric)]).as_scalar() 

def daily_amount_select():
        return select([func.cast(func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity)
                                 /func.greatest(func.cast(func.date_part('DAY', func.current_date() - Product.create_date),Integer), 1), Numeric)]).as_scalar() 

def daily_profit_select():
        return select([func.cast(func.sum((SalesOrderLine.unit_price - Product.purchase_price) * SalesOrderLine.quantity)
                    / func.greatest(func.cast(func.date_part('DAY', func.current_date() - Supplier.create_date), Integer), 1), Integer)]).as_scalar() 

def daily_amount_select():
        return select([func.cast(func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity)
                       /func.greatest(func.cast(func.date_part('DAY', func.current_date() - Supplier.create_date),Integer), 1), Integer)]).as_scalar() 

def test_non_functions(self):
        expr = func.cast("foo", Integer)
        self.assert_compile(expr, "CAST(:param_1 AS INTEGER)")

        expr = func.extract("year", datetime.date(2010, 12, 5))
        self.assert_compile(expr, "EXTRACT(year FROM :param_1)") 

def fetch_notification_status_for_service_for_today_and_7_previous_days(service_id, by_template=False, limit_days=7):
    start_date = midnight_n_days_ago(limit_days)
    now = datetime.utcnow()
    stats_for_7_days = db.session.query(
        FactNotificationStatus.notification_type.label('notification_type'),
        FactNotificationStatus.notification_status.label('status'),
        *([FactNotificationStatus.template_id.label('template_id')] if by_template else []),
        FactNotificationStatus.notification_count.label('count')
    ).filter(
        FactNotificationStatus.service_id == service_id,
        FactNotificationStatus.bst_date >= start_date,
        FactNotificationStatus.key_type != KEY_TYPE_TEST
    )

    stats_for_today = db.session.query(
        Notification.notification_type.cast(db.Text),
        Notification.status,
        *([Notification.template_id] if by_template else []),
        func.count().label('count')
    ).filter(
        Notification.created_at >= get_london_midnight_in_utc(now),
        Notification.service_id == service_id,
        Notification.key_type != KEY_TYPE_TEST
    ).group_by(
        Notification.notification_type,
        *([Notification.template_id] if by_template else []),
        Notification.status
    )

    all_stats_table = stats_for_7_days.union_all(stats_for_today).subquery()

    query = db.session.query(
        *([
            Template.name.label("template_name"),
            Template.is_precompiled_letter,
            all_stats_table.c.template_id
        ] if by_template else []),
        all_stats_table.c.notification_type,
        all_stats_table.c.status,
        func.cast(func.sum(all_stats_table.c.count), Integer).label('count'),
    )

    if by_template:
        query = query.filter(all_stats_table.c.template_id == Template.id)

    return query.group_by(
        *([Template.name, Template.is_precompiled_letter, all_stats_table.c.template_id] if by_template else []),
        all_stats_table.c.notification_type,
        all_stats_table.c.status,
    ).all() 

def fetch_notification_status_totals_for_all_services(start_date, end_date):
    stats = db.session.query(
        FactNotificationStatus.notification_type.label('notification_type'),
        FactNotificationStatus.notification_status.label('status'),
        FactNotificationStatus.key_type.label('key_type'),
        func.sum(FactNotificationStatus.notification_count).label('count')
    ).filter(
        FactNotificationStatus.bst_date >= start_date,
        FactNotificationStatus.bst_date <= end_date
    ).group_by(
        FactNotificationStatus.notification_type,
        FactNotificationStatus.notification_status,
        FactNotificationStatus.key_type,
    )
    today = get_london_midnight_in_utc(datetime.utcnow())
    if start_date <= datetime.utcnow().date() <= end_date:
        stats_for_today = db.session.query(
            Notification.notification_type.cast(db.Text).label('notification_type'),
            Notification.status,
            Notification.key_type,
            func.count().label('count')
        ).filter(
            Notification.created_at >= today
        ).group_by(
            Notification.notification_type.cast(db.Text),
            Notification.status,
            Notification.key_type,
        )
        all_stats_table = stats.union_all(stats_for_today).subquery()
        query = db.session.query(
            all_stats_table.c.notification_type,
            all_stats_table.c.status,
            all_stats_table.c.key_type,
            func.cast(func.sum(all_stats_table.c.count), Integer).label('count'),
        ).group_by(
            all_stats_table.c.notification_type,
            all_stats_table.c.status,
            all_stats_table.c.key_type,
        ).order_by(
            all_stats_table.c.notification_type
        )
    else:
        query = stats.order_by(
            FactNotificationStatus.notification_type
        )
    return query.all() 

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