SQLAlchemy ORM adjust SQL syntax for MutableList/Array

Question

I'm using MutableList to store a python list in a SQL array as described here:

class MyMutableList(MutableList):
    @classmethod
    def coerce(cls, key, value):
        """Convert plain lists to MutableList."""
        if not isinstance(value, MutableList):
            if isinstance(value, list):
                return MutableList(value)

            return MutableList(json.loads(value))
        else:
            return value

model.py:

class Foo(Base):
    __tablename__ = "foo"

    bar = Column(MyMutableList.as_mutable(ARRAY(String(64))))

Reading the SQL array works fine. When writing (insert/update) SQLAlchemy uses this syntax:

UPDATE foo SET bar=['36','42'];

But the dialect that I'm using (databricks+pyodbc, Simba) needs this syntax:

UPDATE foo SET bar=ARRAY('36','42');

Is there a generic way of adjusting this in ORM SQLAlchemy?

Answer 1

I'm currently working with the following workaround:

def get_sql_insert(self):
"""Return SQL syntax for creating the current object."""

update = f"INSERT INTO {self.__tablename__}"
vars = []
values = []
for attr, value in dict(self).items():
    vars.append(attr)
    # set default if applicable
    column = self.__table__.c[attr]
    if value is None and column.default is not None:
        if column.default.is_scalar:
            value = column.default.arg
        else:
            value = column.default.arg("")

    if isinstance(value, list):
        values.append(f"ARRAY({','.join([repr(v) for v in value])})")
    elif isinstance(value, datetime):
        values.append(repr(str(value)))
    else:
        values.append(repr(value))

Still hoping to find a more adequate solution here...