column_property not supported

[andreossido]

from pprint import pprint as pp
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy.orm import column_property
from sqlalchemy import select, func, literal_column

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_ECHO'] = False
db = SQLAlchemy(app)

from sqlalchemy import Column, Integer, String

class Book(db.Model):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    author_id = Column(Integer)


class Author(db.Model):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    books_count = column_property( select([func.count()]).where(Book.author_id==id) )


db.create_all()


author = Author(name='Andrea')
db.session.add(author)
db.session.commit()
pp(author)

book = Book(name='Random book name', author_id=author.id)
db.session.add(book)
db.session.commit()
pp(book)


pp(("Andrea's books_count", author.books_count))

from pydantic_sqlalchemy import sqlalchemy_to_pydantic
pp(sqlalchemy_to_pydantic(Book))
pp(sqlalchemy_to_pydantic(Author))

Calling sqlalchemy_to_pydantic(Author) goes in error:

Traceback (most recent call last):
  File "/home/andreossido/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 747, in __getattr__
    return getattr(self.comparator, key)
AttributeError: 'Comparator' object has no attribute 'default'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "script.py", line 49, in <module>
    pp(sqlalchemy_to_pydantic(Author))
  File "/home/andreossido/.local/lib/python3.8/site-packages/pydantic_sqlalchemy/main.py", line 32, in sqlalchemy_to_pydantic
    if column.default is None and not column.nullable:
  File "/home/andreossido/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 749, in __getattr__
    util.raise_(
  File "/home/andreossido/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
AttributeError: Neither 'Label' object nor 'Comparator' object has an attribute 'default'

I also tried with code below

column_property( db.Column(db.Integer), select([func.count()]).where(Book.author_id==id) )

With Integer column sqlalchemy_to_pydantic doesn't throws exceptions, but author.books_count is wrong Because query select field authors.books_count (that not exists)

[andreossido]

I wrote a very dirty solution, but works

from sqlalchemy.orm import column_property
from functools import wraps 
def custom_column_property(func): 
    @wraps(func) 
    def wrapper(*args, default=None, nullable=True, **kwargs): 
        v = func(*args, **kwargs)
        for column in v.columns:
            column.default = default
            column.nullable = nullable
        return v
    return wrapper 
column_property = custom_column_property(column_property)

In this way you can declare column_property in models as always and calling sqlalchemy_to_pydantic doesn't throws errors

[andreossido]

Just for instance, you can call column_property in these ways:

books_count = column_property( select([func.count()]).where(Book.author_id==id) )
books_count = column_property( select([func.count()]).where(Book.author_id==id), default=-1 )
books_count = column_property( select([func.count()]).where(Book.author_id==id), nullable=False )
books_count = column_property( select([func.count()]).where(Book.author_id==id), default=-1, nullable=False )