Pony ORM YNBool

action.py

# coding: utf-8
from pony.orm import *
from .models import attach_models
from .config import db_config_params

db = Database()
attach_models(db)
db.bind(**db_config_params)
db.generate_mapping()

things = db.Thing.select(lambda t: not t.deleted)
 

models.py

# coding: utf-8
from pony.orm import *
from .types import register_y_n_bool_type

__all__ = ["attach_models"]


# noinspection PyUnusedLocal
def attach_models(db: Database):
    register_y_n_bool_type(db)

    class Thing(db.Entity):
        # isDeleted contains a "Y" if deleted. anything else ("N" or null) means not deleted.
        deleted = Required(bool, tf="YN", column="isDeleted", default=False)

types.py

# coding: utf-8
# addding pony orm converts is not documented. For how to do it, see:
# https://stackoverflow.com/q/31395663

from typing import List, Tuple, Type

from pony.orm import Database
from pony.orm.dbapiprovider import BoolConverter, Converter

__all__ = ["YNBoolConverter", "ConverterClasses", "register_y_n_bool_type"]

ConverterClasses = List[Tuple[Type, Type[Converter]]]


# This is using the Pony ORM coding convention (converter instead of self)
# noinspection PyMethodParameters
class YNBoolConverter(Converter):
    def __init__(converter, provider, py_type, attr=None):
        converter.tf_values = (True, False)  # type: Tuple[Any, Any]
        Converter.__init__(converter, provider, py_type, attr)

    def init(converter, kwargs):
        # possible names: tf, tf_vals, tf_values, true_false
        tf_values = kwargs.pop("tf", None)
        if tf_values is not None:
            if len(tf_values) != 2:
                throw(
                    TypeError,
                    "tf_values one value for true and one value for false. Got %s."
                    "Valid tf_values examples: 'tf', 'yn', (1, 0), [True, False]"
                    % tf_values,
                )
            converter.tf_values = tuple(tf_values)
        Converter.init(converter, kwargs)

    def validate(converter, val, obj=None):
        return bool(val)

    def val2dbval(converter, val, obj=None):
        true_val, false_val = converter.tf_values
        if bool(val):
            return true_val
        return false_val

    def dbval2val(converter, dbval, obj=None):
        if dbval == converter.tf_values[0]:
            return True
        # elif dbval == converter.tf_values[1]:
        else:
            return False

    def sql_type(converter):
        return "BOOLEAN"
        # TODO: intelligently switch the sql_type based on tf_values
        # return "CHAR(1)"


def register_y_n_bool_type(db: Database):
    converter_classes: ConverterClasses = db.provider.converter_classes
    db.provider.converter_classes = [
        (t, YNBoolConverter) if t == bool else (t, c) for t, c in converter_classes
    ]

Revision 1 completely failed:
pony.orm.ormtypes.normalize_type isn't allowing my custom YNBool through saying "Unsupport type 'YNBool'"

Revision 2 partially failed.
It saved Y/N in db, and returned True/False, but the select() query (with not t.deleted instead of t.deleted != 'Y')
(typo in gist revision 2 that wasn't in my test: sql2py used val not dbval as var name.)

Revision 3 partially failed.
Same behavior as Revision 2 (select() needs t.deleted != 'Y' instead of not t.deleted)

Looks like some kind of sql translator is required.