SQLAlchemy code for calling parameters

Question

I'm using the following function to process API parameters for an specific endpoint. Using SQLAlchemy and Flask, I want to get feedback in terms of simplifying calling SQL code and nested statements.

date_filter = r'\d{4}-((1[0-2]|[1-9])|0[1-9])-((3[01]|[12][0-9]|[1-9])|0[1-9])'

def bits_filter(request):
    """

    :param request:
    :return:(Model.Bits) List of bits
    """
    # Get parameters.
    log.info('/bits request: %s', request)
    # Date parameters.
    date_value = request.args.get('date')
    # Bit or newsletter.
    bit_type_value = request.args.get('bit_type')

    # Process parameters.
    date_value = date_value or settings.DATE_VALUE
    bit_type = bit_type_value or settings.BIT_TYPE    
    log.info('bit_type: %r', bit_type)

    if date_value and isinstance(date_value, unicode):
        # Bit type.
        if date_value.lower() == u'latest':
            return Model.Bits.query.filter(Model.Bits.bit_type == bit_type).order_by(
                Model.Bits.published.desc()).limit(settings.max_bits).all()
        # Dated format.
        elif re.match(date_filter, date_value):
            return Model.Bits.query.filter(
                Model.Bits.bit_type == bit_type & Model.Bits.published == date_value).limit(
                settings.max_bits).all()
        else:
            log.error('Invalid date value parameter: %r', date_value)
            return None        
    else:
        return Model.Bits.query.filter(Model.Bits.bit_type == bit_type).order_by(
            Model.Bits.published.desc()).limit(settings.max_bits).all()

Answer 1

I would re-structure code to improve on readability and avoiding code duplication while decreasing the nestedness depth level - remember that "Flat is better than nested".

I would probably first fail fast if date value is given and has an invalid format. Then, I would define a queryset variable which will help us keep the common parts of the query while building it.

Also, you may pre-compile the regular expression and define it as a proper Python constant - in upper case with underscores.

Something along these lines:

# fail fast if the date value is invalid
if date_value and not DATE_FILTER_PATTERN.match(date_value):
    log.error('Invalid date value parameter: %r', date_value)
    return None

queryset = Model.Bits.query.filter(Model.Bits.bit_type == bit_type)
if not date_value or date_value.lower() == 'latest':
    queryset = queryset.order_by(Model.Bits.published.desc())
else:
    queryset = queryset.filter(Model.Bits.published == date_value)

return queryset.limit(settings.max_bits).all()

where DATE_FILTER_PATTERN is defined as:

DATE_FILTER_PATTERN = re.compile(r'\d{4}-((1[0-2]|[1-9])|0[1-9])-((3[01]|[12][0-9]|[1-9])|0[1-9])')