[sqlalchemy] backref questions
Hello,
First of all, I'll apologize if this is a really basic question. I've not been
using SQLAlchemy for long, and I've only very recently picked up Python. Even
though I've looked everywhere I can think of for an answer to this question,
I'm almost certain that it is not a difficult one. I'm happy to read through
any documentation you can point me to, but I've not been able to see what might
be relevant to this particular question.
That said, here goes. Here's the relevant bit of code:
-- BEGIN CODE --
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
uid = Column(Integer, unique=True)
def __init__(self, uid):
self.uid = uid
def __repr__(self):
return(User('uid: %d')) % (self.uid)
class Filesystem(Base):
__tablename__ = 'filesystem'
id = Column(Integer, primary_key=True)
filesystem = Column(String(255))
mountpoint = Column(String(255))
def __init__(self, filesystem, mountpoint):
self.filesystem = filesystem
self.mountpoint = mountpoint
def __repr__(self):
return(Filesystem('%s', '%s')) % (self.filesystem, self.mountpoint)
class Usage(Base):
__tablename__ = 'usage_data'
id = Column(Integer, primary_key=True)
fs_id= Column(None, ForeignKey('filesystem.id'))
user_id = Column(None, ForeignKey('user.id'))
datetime = Column(DateTime)
inodes = Column(MSBigInteger, nullable=False)
kbytes = Column(MSBigInteger, nullable=False)
user = relation(User, backref=backref('usage_data', order_by=datetime))
fs = relation(Filesystem, backref=backref('usage_data', order_by=datetime))
def __init__(self, user, fs, datetime, inodes, kbytes):
self.user = user
self.fs = fs
self.datetime = datetime
self.inodes = inodes
self.kbytes = kbytes
def __repr__(self):
return(Usage('%s', '%s', '%s', %d:%d KB)) % (self.user, self.fs,
self.datetime, self.inodes, self.kbytes)
-- END CODE --
So you can kind of see what it does. This bit is a rudimentary filesystem space
tracking application. You have a list of users in the 'user' table, a list of
filesystems in the 'filesystem' table, and then you have an application that is
periodically inserting records into the 'usage_data' table. Each record
includes a user reference, a filesystem reference, and some data.
The backrefs from Usage objects work perfectly. When I have a User object, say
user_obj, I can access user_obj.usage_data and get a datetime-ordered list of
all Usage objects associated with this particular user.
What I want is an easy way to access a user's n most recent Usage objects for
each filesystem. (For the purpose of this e-mail, we can take n=1.) It would be
easy enough for me to just take the last m records in the user_obj.usage_data
list (where m is the number of filesystems for which this user has records),
but that's not really what I want. One filesystem might be storing records
every hour, and another might be storing them every day. In that case, it would
be hard to know how many records I would need to take from user_obj.usage_data
to have the most recent record from each filesystem.
It wouldn't be hard to actually use a session object to build a query for this,
but I'd really like to have this all taken care of in the
objects/maps/relations/whatever themselves. I'd like to have some attribute in
the User object that's like user_obj.usage_data, but instead gives me a list of
only the most recent Usage object from each filesystem, so I'd see something
like:
user_obj.most_recent_usage
[Usage('user', 'fs1', 'some-date', foo:bar),
Usage('user', 'fs2', 'some-other-date', f0o:bAr),
Usage('user', 'fs3', 'some-third-date', fo0:b4r)]
I hope I was clear in my description. If I've left anything out, I'll be happy
to clarify.
Thanks!!
David
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] backref questions
Hi,
On Feb 10, 2010, at 6:54 PM, David Ressman wrote:
Hello,
First of all, I'll apologize if this is a really basic question. I've not
been using SQLAlchemy for long, and I've only very recently picked up Python.
Even though I've looked everywhere I can think of for an answer to this
question, I'm almost certain that it is not a difficult one. I'm happy to
read through any documentation you can point me to, but I've not been able to
see what might be relevant to this particular question.
...
What I want is an easy way to access a user's n most recent Usage objects for
each filesystem. (For the purpose of this e-mail, we can take n=1.) It would
be easy enough for me to just take the last m records in the
user_obj.usage_data list (where m is the number of filesystems for which this
user has records), but that's not really what I want. One filesystem might be
storing records every hour, and another might be storing them every day. In
that case, it would be hard to know how many records I would need to take
from user_obj.usage_data to have the most recent record from each filesystem.
It wouldn't be hard to actually use a session object to build a query for
this, but I'd really like to have this all taken care of in the
objects/maps/relations/whatever themselves. I'd like to have some attribute
in the User object that's like user_obj.usage_data, but instead gives me a
list of only the most recent Usage object from each filesystem, so I'd see
something like:
user_obj.most_recent_usage
[Usage('user', 'fs1', 'some-date', foo:bar),
Usage('user', 'fs2', 'some-other-date', f0o:bAr),
Usage('user', 'fs3', 'some-third-date', fo0:b4r)]
I hope I was clear in my description. If I've left anything out, I'll be
happy to clarify.
What you likely want to dig into is Query enabled properties
(http://www.sqlalchemy.org/docs/mappers.html?highlight=property%20association#building-query-enabled-properties).
I used this a lot of times to tie what appears to be a relationship but that
is driven by an underlying query. I don't have time right now to whip
up the action property, but if you play with it a bit I'm sure you'll be able
to get there.
Michael
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] backref questions
On Feb 10, 2010, at 7:38 PM, Michael Trier wrote: What you likely want to dig into is Query enabled properties (http://www.sqlalchemy.org/docs/mappers.html?highlight=property%20association#building-query-enabled-properties). I used this a lot of times to tie what appears to be a relationship but that is driven by an underlying query. I don't have time right now to whip up the action property, but if you play with it a bit I'm sure you'll be able to get there. Perfect! I added these to my User object, and it works great: def _recent_usage(self, last_num_items=1): session = object_session(self) return_list = [] fs_list = session.query(Filesystem).all() for filesystem in fs_list: usage_objs = session.query(Usage).\ filter_by(user=self).\ filter_by(fs=filesystem).\ order_by(desc(Usage.datetime)).\ limit(last_num_items).\ all() for usage in usage_objs: return_list.append(usage) return(return_list) last_usage_objects = property(_recent_usage) Thanks very much for your help! David -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
