attribute for the size of a LargeBinary

[Andre]

Hello,
I have a class that stores files in a LarbeBinary attribute, such as:


class CommonBase(object):
    # Lets declare all the columns that are common to all
    # tables in our database.
    id = Column(Integer, primary_key=True)

...

Base = declarative_base(cls=CommonBase)

class DocumentBase(Base):
    __abstract__ = True

    filename = Column(String)

    @declared_attr
    def file_object(cls):
        return deferred(Column('file_object', LargeBinary))

This all works well but when I try to get the size of the stored file using the python len() function on the file_object attribute above, it takes a while because, to the best of my understanding, the binary object is first loaded into memory, and then len() computes its size.

I would like to make this more efficient by adding an attribute to the DocumentBase class above, which simply does a SQL query to get the size of the file_object at the DB level, thinking that this will not require to load the binary object into memory to compute its size.  Of course, this behavior could be done by adding an extra field (such as file_size) to my DB tables with binary object, which would be set when the binary object is stored in the database, but I thought that it would be more elegant if the DB didn't have that extra column just to keep track of the binary object size.


After reading and searching the web, I think one way to do this is using a column_property attribute in my DocumentBase class, such as:

    @declared_attr
    def file_size(cls):
        return column_property(select([func.length(cls.file_object)]).where(cls.id == id))

But the above is not working for me, and gives me the following error:

 File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 388, in visit_column
    raise exc.CompileError("Cannot compile Column object until "
CompileError: Cannot compile Column object until it's 'name' is assigned.

I'm sure I'm missing something very basic here.  Or maybe there is a simpler way of doing this?


(I'm using sqlalchemy 0.7)

Thanks a lot,
  Andre

[Michael Bayer]

On Oct 5, 2012, at 10:26 AM, Andre wrote:

I would like to make this more efficient by adding an attribute to the DocumentBase class above, which simply does a SQL query to get the size of the file_object at the DB level, thinking that this will not require to load the binary object into memory to compute its size.  Of course, this behavior could be done by adding an extra field (such as file_size) to my DB tables with binary object, which would be set when the binary object is stored in the database, but I thought that it would be more elegant if the DB didn't have that extra column just to keep track of the binary object size.

IMO there's no question you'd use SQL side functions to get this length, unless they were found to be inefficient.   Storing the length separately arguably breaks normalization.

After reading and searching the web, I think one way to do this is using a column_property attribute in my DocumentBase class, such as:

    @declared_attr
    def file_size(cls):
        return column_property(select([func.length(cls.file_object)]).where(cls.id == id))

But the above is not working for me, and gives me the following error:

 File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 388, in visit_column
    raise exc.CompileError("Cannot compile Column object until "
CompileError: Cannot compile Column object until it's 'name' is assigned.

I'm sure I'm missing something very basic here.  Or maybe there is a simpler way of doing this?

this was a little confusing because I kept assuming it was calling func.length(cls.file_object) that was throwing things off.  But it's not (well it is, but separately), it's that WHERE clause.  The column_property() is an expression that's loaded inline with your class, so unless you are correlating a different table back onto the parent table, you don't need a WHERE clause for it.  the usage of WHERE here would at most describe selecting from an alias of the table correlated against its unaliased form, but that's not needed here and also the mechanics of column_property() + declarative mixins + declared_attr are already getting into territories where things are more prone to not being possible, due to Python class mechanics (which also give us all these great mixin capabilities in the first place, not to detract from them!).

In fact the "cls.id == id" there is creating a binary expression against the Python built-in function "id()".

Anyway, there's no need to embed a SELECT in here, you just need len(col), so remove the select(), and also we need to navigate around that deferred() to get at the Column  (this is something maybe I can improve, I added a ticket for that):

class DocumentBase(Base):

    __abstract__ = True

    filename = Column(String)

    @declared_attr

    def file_object(cls):

        return deferred(Column('file_object', LargeBinary))

    @declared_attr

    def file_size(cls):

        return column_property(func.length(cls.file_object.columns[0]))

class Document(DocumentBase):

    __tablename__ = 'document'

print Session().query(Document)

SQL:

SELECT length(:length_2) AS length_1, document.id AS document_id, document.filename AS document_filename 

FROM document

now even if you wanted it to work as, "when I call file_size, it emits a SQL query", you can do that too - just defer() that expression also:

    @declared_attr

    def file_size(cls):

        return deferred(func.length(cls.file_object.columns[0]))

then you'll get SQL output like this when you say some_document.file_size:

SELECT length(file_object) AS length_1 

FROM document 

WHERE document.id = ?

(I'm using sqlalchemy 0.7)

Thanks a lot,
  Andre

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/3XisZc_YH9IJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Andre Charbonneau]

Thanks Michael for the quick and detailed reply.
This works and is exactly what I needed.
Many thanks. Much appreciated.

Regards,
Andre

>
>
>
>
>
>
>
>
>
>
>
> >
> >
> > (I'm using sqlalchemy 0.7)
> >
> > Thanks a lot,
> > Andre
> >
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To view this discussion on the web visit
> > https://groups.google.com/d/msg/sqlalchemy/-/3XisZc_YH9IJ.
> > To post to this group, send email to sqlal...@googlegroups.com

> > <mailto:sqlal...@googlegroups.com>.

> > To unsubscribe from this group, send email to
> > sqlalchemy+...@googlegroups.com

> > <mailto:sqlalchemy+...@googlegroups.com>.

> > For more options, visit this group at
> > http://groups.google.com/group/sqlalchemy?hl=en.
>

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.

> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.

--
Andre Charbonneau
Shared Services Canada
Telephone: 613-993-3129