tessl/pypi-tortoise-orm

Easy async ORM for Python, built with relations in mind

Overview

Eval results

Files

Database Functions and Expressions

Name: tessl/pypi-tortoise-orm
Author: tessl

Database function support including text functions, aggregate functions, and custom expressions for advanced queries. These functions provide database-level operations that can be used in queries, annotations, and aggregations.

Capabilities

Function Base Classes

Base classes for creating and using database functions in queries.

class Expression:
    """Base class for query expressions."""
    
    def __init__(self, *args, **kwargs):
        """
        Initialize expression.
        
        Args:
            *args: Expression arguments
            **kwargs: Expression options
        """

class Function(Expression):
    """Base class for database functions."""
    
    def __init__(self, *args, **kwargs):
        """
        Initialize function with arguments.
        
        Args:
            *args: Function arguments (field names, values)
            **kwargs: Function options
        """

class Aggregate(Function):
    """Base class for aggregate functions."""
    
    def __init__(self, field, distinct=False, **kwargs):
        """
        Initialize aggregate function.
        
        Args:
            field (str): Field name to aggregate
            distinct (bool): Use DISTINCT in aggregation
            **kwargs: Additional options
        """

Text Functions

Functions for manipulating text and string data.

class Trim:
    """Trim whitespace from edges of text."""
    def __init__(self, field): ...

class Length:
    """Get length of text or binary data."""
    def __init__(self, field): ...

class Lower:
    """Convert text to lowercase."""
    def __init__(self, field): ...

class Upper:
    """Convert text to uppercase.""" 
    def __init__(self, field): ...

class Coalesce:
    """Return first non-null value from arguments."""
    def __init__(self, *fields): ...

class Concat:
    """Concatenate multiple text values."""
    def __init__(self, *fields, separator=""): ...

class Substring:
    """Extract substring from text."""
    def __init__(self, field, start, length=None): ...

class Replace:
    """Replace occurrences of substring."""
    def __init__(self, field, search, replace): ...

Mathematical Functions

Functions for mathematical operations and calculations.

class Abs:
    """Absolute value of number."""
    def __init__(self, field): ...

class Ceil:
    """Ceiling (round up) of number."""
    def __init__(self, field): ...

class Floor:
    """Floor (round down) of number."""
    def __init__(self, field): ...

class Round:
    """Round number to specified decimal places."""
    def __init__(self, field, precision=0): ...

class Mod:
    """Modulo operation."""
    def __init__(self, field, divisor): ...

class Power:
    """Raise number to power."""
    def __init__(self, field, exponent): ...

class Sqrt:
    """Square root of number."""
    def __init__(self, field): ...

Date and Time Functions

Functions for working with date and time values.

class Now:
    """Current date and time."""
    def __init__(self): ...

class Extract:
    """Extract part of date/time value."""
    def __init__(self, field, lookup_type):
        """
        Args:
            field (str): Date/time field name
            lookup_type (str): Part to extract ('year', 'month', 'day', 'hour', etc.)
        """

class DateDiff:
    """Difference between two dates."""
    def __init__(self, field1, field2, interval='day'): ...

class DateAdd:
    """Add interval to date."""
    def __init__(self, field, interval, amount): ...

class DateFormat:
    """Format date as string."""
    def __init__(self, field, format_string): ...

Aggregate Functions

Functions that compute single values from multiple rows.

class Count:
    """Count number of rows or non-null values."""
    def __init__(self, field="*", distinct=False): ...

class Sum:
    """Sum of numeric values."""
    def __init__(self, field, distinct=False): ...

class Avg:
    """Average of numeric values."""
    def __init__(self, field, distinct=False): ...

class Max:
    """Maximum value."""
    def __init__(self, field): ...

class Min:
    """Minimum value."""
    def __init__(self, field): ...

class StdDev:
    """Standard deviation."""
    def __init__(self, field): ...

class Variance:
    """Variance."""
    def __init__(self, field): ...

Conditional Functions

Functions for conditional logic in queries.

class Case:
    """Conditional CASE expression."""
    def __init__(self, *cases, default=None):
        """
        Args:
            *cases: Tuple pairs of (condition, result)
            default: Default value if no conditions match
        """

class When:
    """Condition for CASE expression."""
    def __init__(self, condition, then): ...

class Greatest:
    """Return greatest value from multiple fields."""
    def __init__(self, *fields): ...

class Least:
    """Return smallest value from multiple fields."""
    def __init__(self, *fields): ...

Usage Examples

Text Function Examples

from tortoise.functions import Trim, Length, Lower, Upper, Coalesce, Concat

class User(Model):
    id = fields.IntField(pk=True)
    first_name = fields.CharField(max_length=50)
    last_name = fields.CharField(max_length=50)
    email = fields.CharField(max_length=100)
    bio = fields.TextField(null=True)

# Text manipulation
users_with_clean_names = await User.annotate(
    clean_first_name=Trim('first_name'),
    name_length=Length('first_name'),
    lower_email=Lower('email'),
    upper_name=Upper('first_name')
).all()

# Concatenation
users_with_full_name = await User.annotate(
    full_name=Concat('first_name', 'last_name', separator=' ')
).all()

# Handle null values
users_with_bio = await User.annotate(
    display_bio=Coalesce('bio', 'No bio available')
).all()

Mathematical Function Examples

from tortoise.functions import Abs, Round, Mod

class Product(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=100)
    price = fields.DecimalField(max_digits=10, decimal_places=2)
    discount = fields.DecimalField(max_digits=5, decimal_places=2, default=0)

# Mathematical operations
products_with_calculations = await Product.annotate(
    abs_discount=Abs('discount'),
    rounded_price=Round('price', 2),
    price_mod=Mod('price', 10)
).all()

Date Function Examples

from tortoise.functions import Extract, Now

class Order(Model):
    id = fields.IntField(pk=True)
    created_at = fields.DatetimeField(auto_now_add=True)
    total = fields.DecimalField(max_digits=10, decimal_places=2)

# Date operations
orders_by_year = await Order.annotate(
    year=Extract('created_at', 'year'),
    month=Extract('created_at', 'month'),
    day=Extract('created_at', 'day')
).all()

# Current time
current_time = await Order.annotate(
    current_time=Now()
).first()

Aggregate Function Examples

from tortoise.functions import Count, Sum, Avg, Max, Min

class Post(Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(max_length=200)
    author = fields.ForeignKeyField('models.User', related_name='posts')
    view_count = fields.IntField(default=0)
    created_at = fields.DatetimeField(auto_now_add=True)

# Simple aggregation
stats = await Post.aggregate(
    total_posts=Count('id'),
    total_views=Sum('view_count'),
    avg_views=Avg('view_count'),
    max_views=Max('view_count'),
    min_views=Min('view_count')
)

# Aggregate with grouping
author_stats = await User.annotate(
    post_count=Count('posts'),
    total_views=Sum('posts__view_count'),
    avg_views=Avg('posts__view_count')
).filter(post_count__gt=0).all()

# Distinct aggregation
unique_authors = await Post.aggregate(
    author_count=Count('author', distinct=True)
)

Conditional Function Examples

from tortoise.functions import Case, When
from tortoise.queryset import Q

class User(Model):
    id = fields.IntField(pk=True)
    age = fields.IntField()
    is_premium = fields.BooleanField(default=False)
    posts_count = fields.IntField(default=0)

# Conditional logic
users_with_category = await User.annotate(
    age_category=Case(
        When(Q(age__lt=18), then='Minor'),
        When(Q(age__lt=65), then='Adult'),
        default='Senior'
    ),
    user_type=Case(
        When(Q(is_premium=True), then='Premium'),
        When(Q(posts_count__gt=10), then='Active'),
        default='Regular'
    )
).all()

# Complex conditions
users_with_status = await User.annotate(
    status=Case(
        When(Q(is_premium=True) & Q(posts_count__gt=50), then='VIP'),
        When(Q(is_premium=True), then='Premium'),
        When(Q(posts_count__gt=20), then='Active'),
        When(Q(posts_count__gt=5), then='Regular'),
        default='New'
    )
).all()

Window Functions

Advanced analytical functions for complex calculations.

from tortoise.functions import RowNumber, Rank, DenseRank

class Sale(Model):
    id = fields.IntField(pk=True)
    product_name = fields.CharField(max_length=100)
    amount = fields.DecimalField(max_digits=10, decimal_places=2)
    sale_date = fields.DateField()
    region = fields.CharField(max_length=50)

# Window functions (if supported by database)
sales_with_ranking = await Sale.annotate(
    row_number=RowNumber().over(order_by=['amount']),
    rank_by_amount=Rank().over(order_by=['-amount']),
    dense_rank=DenseRank().over(
        partition_by=['region'],
        order_by=['-amount']
    )
).all()

Custom Functions

Creating custom database functions for specific needs.

from tortoise.expressions import Function

class CustomFunction(Function):
    """Custom database function."""
    database_func = 'CUSTOM_FUNC'  # SQL function name
    
    def __init__(self, field, param1, param2=None):
        super().__init__(field, param1, param2)

# Usage
results = await MyModel.annotate(
    custom_result=CustomFunction('my_field', 'param_value')
).all()

Function Composition

Combining multiple functions for complex operations.

from tortoise.functions import Lower, Trim, Length, Concat

# Compose multiple functions
users_processed = await User.annotate(
    # Chain text operations
    clean_email=Lower(Trim('email')),
    
    # Use function results in other functions
    full_name_length=Length(
        Concat('first_name', 'last_name', separator=' ')
    ),
    
    # Nested function calls
    formatted_name=Upper(
        Trim(
            Concat('first_name', 'last_name', separator=' ')
        )
    )
).all()

Database-Specific Functions

Using database-specific functions through contrib modules.

# PostgreSQL-specific functions
from tortoise.contrib.postgres.functions import ArrayAgg, JsonAgg

# MySQL-specific functions  
from tortoise.contrib.mysql.functions import GroupConcat

# SQLite-specific functions
from tortoise.contrib.sqlite.functions import Julianday

# PostgreSQL array aggregation
tags_by_post = await Post.annotate(
    tag_names=ArrayAgg('tags__name')
).prefetch_related('tags').all()

# MySQL group concatenation
user_emails = await User.aggregate(
    all_emails=GroupConcat('email', separator=';')
)

Install with Tessl CLI