On 12-10-05 11:27 AM, Michael Bayer wrote:
> 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 = ?
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 sqlalchemy@googlegroups.com
> > <mailto:sqlalchemy@googlegroups.com>.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com
> > <mailto:sqlalchemy+unsubscr...@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 sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@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
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
