Hybrid property expression concatenation for both MySQL and SQLite-CodePudding

flask-sqlalchemy model:

from sqlalchemy import extract
from sqlalchemy.sql import func
from app import db

class Entry(db.Model):
   date_order = db.Column(db.Date, nullable=False)
   version_number = db.Column(db.Integer, nullable=False, default=1)

   @hybrid_property
   def display_name(self):
       return f"{self.date_order.year} ({self.version_number})"

   @display_name.expression
   def display_name(cls):
       return func.concat(extract("year", cls.date_order), " (", cls.version_number, ")")

This works with MySQL. I had to use func.concat because with the simple addition it would cast the date year to an integer and just add them together instead of concatenation. I tested with my custom API and Flask shell:

In [1]: dp = Entry.query.first().display_name
In [2]: Entry.query.filter_by(display_name=dp).all()
Out[2]: [...returns a bunch of entries with that display name...]

But my testing environment runs an SQLite instance. I have this unit test:

# `create_entry` fixture commits the instance to db

def test_user_display_name_expression(create_entry):
    entry = create_entry(date_order=date(2022, 11, 11), version_number=3)

    filtered = Entry.query.filter_by(display_name="2022 (3)").one()

    assert filtered == entry

This returns an error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such function: concat

Is there any way to create this concatenation expression so both SQL implementation would query on it?

CodePudding user response：

After many inputs from PChemGuy I managed to find the issue:

Neither func.concat or adding non-casted properties work as concatenation.

Instead we need sqlalchemy.sql.expression.cast to cast every property to sqlalchemy.String:

from sqlalchemy import extract, String
from sqlalchemy.sql.expression import cast

    @display_name.expression
    def display_name(cls):
        return (
            cast(extract("year", cls.date_order), String)
              " ("
              cast(cls.version_number, String)
              ")"
        )

Both MysQL and SQLite can understand this.