tessl/pypi-pyairtable

Python client for the Airtable API providing comprehensive database operations, ORM functionality, enterprise features, and testing utilities

—

Pending

Overview

Eval results

Files

Formula System

Name: tessl/pypi-pyairtable
Author: tessl

Comprehensive formula building and expression system supporting all Airtable formula functions, logical operators, comparisons, and field references. Enables complex queries and data filtering using Airtable's formula language.

Capabilities

Formula Construction

Core classes for building formula expressions with proper escaping, type conversion, and operator support.

class Formula:
    def __init__(self, value: str):
        """
        Create formula from string expression.
        
        Parameters:
        - value: Formula string (e.g., "{Name} = 'John'")
        """
    
    def flatten(self) -> Formula:
        """
        Flatten nested boolean statements for optimization.
        
        Returns:
        Optimized Formula instance
        """
    
    def eq(self, value) -> Formula:
        """Build equality comparison."""
    
    def ne(self, value) -> Formula:
        """Build not-equal comparison."""
    
    def gt(self, value) -> Formula:
        """Build greater-than comparison."""
    
    def lt(self, value) -> Formula:
        """Build less-than comparison."""
    
    def gte(self, value) -> Formula:
        """Build greater-than-or-equal comparison."""
    
    def lte(self, value) -> Formula:
        """Build less-than-or-equal comparison."""

class Field(Formula):
    def __init__(self, name: str):
        """
        Create field reference.
        
        Parameters:
        - name: Field name to reference
        """

def to_formula(value) -> Formula:
    """
    Convert value to Formula object.
    
    Parameters:
    - value: Python value (str, int, bool, datetime, Formula, etc.)
    
    Returns:
    Formula object with proper escaping and type conversion
    """

def to_formula_str(value) -> str:
    """
    Convert value to formula string.
    
    Parameters:
    - value: Python value to convert
    
    Returns:
    String representation suitable for Airtable formulas
    """

Logical Operations

Boolean logic functions for combining multiple conditions with AND, OR, and NOT operations.

def AND(*components, **fields) -> Formula:
    """
    Create AND condition combining multiple expressions.
    
    Parameters:
    - components: Formula objects or values to combine
    - fields: Keyword arguments as field=value equality conditions
    
    Returns:
    Formula representing AND condition
    """

def OR(*components, **fields) -> Formula:
    """
    Create OR condition for alternative expressions.
    
    Parameters:
    - components: Formula objects or values to combine
    - fields: Keyword arguments as field=value equality conditions
    
    Returns:
    Formula representing OR condition
    """

def NOT(component=None, **fields) -> Formula:
    """
    Create NOT condition to negate expression.
    
    Parameters:
    - component: Formula object to negate
    - fields: Single field=value as keyword argument
    
    Returns:
    Formula representing negated condition
    """

def match(field_values: dict, *, match_any: bool = False) -> Formula:
    """
    Create equality conditions for multiple fields.
    
    Parameters:
    - field_values: Dict mapping field names to values or (operator, value) tuples
    - match_any: If True, use OR logic. If False, use AND logic
    
    Returns:
    Formula with equality or comparison conditions
    """

Mathematical Functions

Comprehensive set of mathematical operations including arithmetic, statistical, and utility functions.

# Arithmetic functions
def ABS(value) -> Formula:
    """Absolute value of number."""

def SUM(number, *numbers) -> Formula:
    """Sum of all numbers."""

def AVERAGE(number, *numbers) -> Formula:
    """Average of all numbers."""

def MAX(number, *numbers) -> Formula:
    """Maximum value from numbers."""

def MIN(number, *numbers) -> Formula:
    """Minimum value from numbers."""

def COUNT(number, *numbers) -> Formula:
    """Count of numeric values."""

def COUNTA(value, *values) -> Formula:
    """Count of non-empty values."""

# Rounding functions
def ROUND(value, precision) -> Formula:
    """Round to specified decimal places."""

def ROUNDUP(value, precision) -> Formula:
    """Round up to specified decimal places."""

def ROUNDDOWN(value, precision) -> Formula:
    """Round down to specified decimal places."""

def CEILING(value, significance=None) -> Formula:
    """Round up to nearest multiple of significance."""

def FLOOR(value, significance=None) -> Formula:
    """Round down to nearest multiple of significance."""

def INT(value) -> Formula:
    """Greatest integer less than or equal to value."""

# Advanced math
def MOD(value, divisor) -> Formula:
    """Remainder after division."""

def POWER(base, power) -> Formula:
    """Base raised to power."""

def SQRT(value) -> Formula:
    """Square root of value."""

def EXP(power) -> Formula:
    """e raised to power."""

def LOG(number, base=None) -> Formula:
    """Logarithm with optional base (default 10)."""

def EVEN(value) -> Formula:
    """Smallest even integer >= value."""

def ODD(value) -> Formula:
    """Smallest odd integer >= value."""

Text Functions

String manipulation and text processing functions for working with text fields.

def CONCATENATE(text, *texts) -> Formula:
    """Join text strings together."""

def LEFT(string, how_many) -> Formula:
    """Extract characters from start of string."""

def RIGHT(string, how_many) -> Formula:
    """Extract characters from end of string."""

def MID(string, where_to_start, count) -> Formula:
    """Extract substring from middle of string."""

def FIND(string_to_find, where_to_search, start_from_position=None) -> Formula:
    """Find position of substring (case-sensitive)."""

def SEARCH(string_to_find, where_to_search, start_from_position=None) -> Formula:
    """Find position of substring (case-insensitive)."""

def LEN(string) -> Formula:
    """Length of string."""

def TRIM(string) -> Formula:
    """Remove leading and trailing whitespace."""

def UPPER(string) -> Formula:
    """Convert to uppercase."""

def LOWER(string) -> Formula:
    """Convert to lowercase."""

def SUBSTITUTE(string, old_text, new_text, index=None) -> Formula:
    """Replace occurrences of old_text with new_text."""

def REPLACE(string, start_character, number_of_characters, replacement) -> Formula:
    """Replace portion of string."""

def REPT(string, number) -> Formula:
    """Repeat string specified number of times."""

def REGEX_MATCH(string, regex) -> Formula:
    """Test if string matches regular expression."""

def REGEX_EXTRACT(string, regex) -> Formula:
    """Extract first substring matching regex."""

def REGEX_REPLACE(string, regex, replacement) -> Formula:
    """Replace all regex matches with replacement."""

def ENCODE_URL_COMPONENT(component_string) -> Formula:
    """URL-encode string for use in URLs."""

Date and Time Functions

Comprehensive date/time manipulation including formatting, parsing, and arithmetic operations.

def NOW() -> Formula:
    """Current date and time."""

def TODAY() -> Formula:
    """Current date (time set to midnight)."""

def DATEADD(date, number, units) -> Formula:
    """Add time units to date."""

def DATETIME_DIFF(date1, date2, units) -> Formula:
    """Difference between dates in specified units."""

def DATETIME_FORMAT(date, output_format=None) -> Formula:
    """Format date as string."""

def DATETIME_PARSE(date, input_format=None, locale=None) -> Formula:
    """Parse string as date."""

def DATESTR(date) -> Formula:
    """Format date as YYYY-MM-DD string."""

def TIMESTR(timestamp) -> Formula:
    """Format time as HH:mm:ss string."""

def SET_TIMEZONE(date, tz_identifier) -> Formula:
    """Set timezone for date (use with DATETIME_FORMAT)."""

def SET_LOCALE(date, locale_modifier) -> Formula:
    """Set locale for date (use with DATETIME_FORMAT)."""

# Date component extraction
def YEAR(date) -> Formula:
    """Extract year from date."""

def MONTH(date) -> Formula:
    """Extract month from date (1-12)."""

def DAY(date) -> Formula:
    """Extract day from date (1-31)."""

def WEEKDAY(date, start_day_of_week=None) -> Formula:
    """Day of week (0-6, Sunday=0)."""

def WEEKNUM(date, start_day_of_week=None) -> Formula:
    """Week number in year."""

def HOUR(datetime) -> Formula:
    """Extract hour from datetime (0-23)."""

def MINUTE(datetime) -> Formula:
    """Extract minute from datetime (0-59)."""

def SECOND(datetime) -> Formula:
    """Extract second from datetime (0-59)."""

# Date comparisons
def IS_BEFORE(date1, date2) -> Formula:
    """Check if date1 is before date2."""

def IS_AFTER(date1, date2) -> Formula:
    """Check if date1 is after date2."""

def IS_SAME(date1, date2, unit) -> Formula:
    """Check if dates are same up to time unit."""

# Relative dates
def FROMNOW(date) -> Formula:
    """Days from current date to specified date."""

def TONOW(date) -> Formula:
    """Days from specified date to current date."""

# Business date functions
def WORKDAY(start_date, num_days, holidays=None) -> Formula:
    """Add working days to date, excluding weekends and holidays."""

def WORKDAY_DIFF(start_date, end_date, holidays=None) -> Formula:
    """Count working days between dates."""

Conditional and Utility Functions

Control flow, data type handling, and utility functions for complex formula logic.

def IF(expression, if_true, if_false) -> Formula:
    """Conditional expression - return if_true if expression is true."""

def SWITCH(expression, pattern, result, *pattern_results) -> Formula:
    """Switch statement with pattern matching."""

def ISERROR(expr) -> Formula:
    """Check if expression causes an error."""

def BLANK() -> Formula:
    """Return blank/empty value."""

def ERROR() -> Formula:
    """Return error value."""

def TRUE() -> Formula:
    """Boolean true value."""

def FALSE() -> Formula:
    """Boolean false value."""

def T(value) -> Formula:
    """Return value if it's text, blank otherwise."""

def VALUE(text) -> Formula:
    """Convert text to number."""

def RECORD_ID() -> Formula:
    """ID of current record."""

def CREATED_TIME() -> Formula:
    """Creation time of current record."""

def LAST_MODIFIED_TIME(*fields) -> Formula:
    """Last modification time of record or specific fields."""

Usage Examples

Basic Formula Construction

from pyairtable import Api
from pyairtable.formulas import Formula, Field, match, AND, OR

api = Api('your_token')
table = api.table('base_id', 'table_name')

# Simple string formula
formula = Formula("{Status} = 'Active'")
records = table.all(formula=formula)

# Using Field objects
name_field = Field('Name')
status_field = Field('Status')
formula = AND(
    name_field.eq('John'),
    status_field.ne('Inactive')
)

# Using match() helper
formula = match({
    'Department': 'Engineering',
    'Level': ('>=', 5),  # Comparison tuple
    'Status': 'Active'
})

Complex Logical Expressions

from pyairtable.formulas import AND, OR, NOT, match

# Complex conditions
engineering_seniors = AND(
    match({'Department': 'Engineering'}),
    OR(
        match({'Level': ('>=', 5)}),
        match({'Title': 'Senior Engineer'})
    )
)

# Exclusion patterns
not_interns = NOT(match({'Title': 'Intern'}))

# Multiple department filter
tech_departments = OR(
    match({'Department': 'Engineering'}),
    match({'Department': 'Data Science'}),
    match({'Department': 'DevOps'})
)

records = table.all(formula=AND(engineering_seniors, not_interns))

Mathematical Formulas

from pyairtable.formulas import SUM, AVERAGE, IF, ROUND

# Calculate totals and averages
total_formula = SUM(Field('Q1_Sales'), Field('Q2_Sales'), Field('Q3_Sales'), Field('Q4_Sales'))

# Conditional calculations
bonus_formula = IF(
    Field('Sales_Total').gt(100000),
    Field('Sales_Total') * 0.1,  # 10% bonus
    0
)

# Rounded averages
avg_score = ROUND(AVERAGE(Field('Score1'), Field('Score2'), Field('Score3')), 2)

Date and Time Operations

from pyairtable.formulas import DATEADD, DATETIME_DIFF, DATETIME_FORMAT, IS_AFTER
import datetime

# Date arithmetic
deadline_formula = DATEADD(Field('Start_Date'), 30, 'days')

# Date comparisons
overdue_formula = IS_AFTER(Formula('TODAY()'), Field('Due_Date'))

# Date formatting
formatted_date = DATETIME_FORMAT(Field('Created_Date'), 'MM/DD/YYYY')

# Time calculations
project_duration = DATETIME_DIFF(Field('End_Date'), Field('Start_Date'), 'days')

Text Processing

from pyairtable.formulas import CONCATENATE, UPPER, LEFT, FIND

# String concatenation
full_name = CONCATENATE(Field('First_Name'), ' ', Field('Last_Name'))

# Text formatting
formatted_code = CONCATENATE('ID-', UPPER(LEFT(Field('Name'), 3)))

# Text search and validation
has_email = FIND('@', Field('Email')).gt(0)

Advanced Pattern Matching

# Using match() with multiple operators
advanced_filter = match({
    'Salary': ('>', 75000),
    'Experience': ('>=', 3),
    'Department': 'Engineering',
    'Status': 'Active'
}, match_any=False)  # All conditions must match

# Alternative matching (any condition)
priority_candidates = match({
    'Referral': True,
    'Experience': ('>', 10),
    'Education': 'PhD'
}, match_any=True)  # Any condition matches

# Combining with other logic
final_filter = AND(
    advanced_filter,
    OR(priority_candidates, match({'Urgency': 'High'}))
)

Field Reference Utilities

# Utility functions for field names and escaping
from pyairtable.formulas import field_name, quoted

# Handle field names with special characters
special_field = field_name("Field with spaces & symbols")
# Results in: {Field with spaces & symbols}

# Properly quote string values
search_value = quoted("Text with 'quotes' and \\ backslashes")
# Results in: 'Text with \'quotes\' and \\\\ backslashes'

# Convert Python values to formula strings  
from pyairtable.formulas import to_formula_str

formula_string = to_formula_str(datetime.date(2023, 12, 25))
# Results in: DATETIME_PARSE('2023-12-25')

Install with Tessl CLI