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 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.
--
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.
