Re: [sqlalchemy] Order by the sequence in_ ?
Thanks, I think that's exactly what I was looking for! .oO V Oo. On 12/28/2011 06:10 PM, Michael Bayer wrote: On Dec 27, 2011, at 8:37 PM, Vlad K. wrote: Hi all. I need to select some rows where pkey is in a sequence. How do I order by that very sequence? images_all = session.query(AdImage).filter(AdImage.image_id.in_(images)).order_by( ? ).all() Postgresql backend. typically with case(): order_by( case([ (Adimage.id == 3, A), (Adimage.id == 1, B), (Adimage.id == 9, C), ]) ) unless you can use a simpler transformation on AdImage.id that converts it into a sortable value. The above can be generalized: case([(AdImage.id == value, literal(index)) for index, value in enumerate(images)]) -- 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.
[sqlalchemy] Inserting when the row depends on id generated by the same transaction.
I need to implement a simple accounting subsytem that will record all the transactions for future auditing. This uses two tables: 'transactions' and 'entries'. Transaction-Entry is a parent-child relationship. My customer has two requeriments : All the records(entries/transaction) must be inserted in one database transaction and he wants ensurance that no gaps will ever appear on the ids generated by these two tables For the first requeriment(everything on one database transaction), I must insert to the 'transactions' table, get the generated id and use that as input for the 'inserts' in the 'entries' table, but everything must be in one transaction, so I can't invoke 'commit' after inserting to 'transactions' to get its generated id. For the second requeriment(ensure that no gaps will happen in the generated ids), I'm not sure its possible to acomplish, since if something fails after the id for 'transaction' is generated, even though that transaction will not be commited, that id will be lost, thus creating a gap. I appreciate insights on how to solve either of these problems. Thanks in advance. Thiago. -- 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.
Re: [sqlalchemy] cascade delete in relationship and session.execute(table.update())
On Jan 2, 2012, at 4:06 PM, Wubin wrote:
class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
cascade=all,
delete))
class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})
but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message.
This would indicate that PolymorphicClass is mapped to a table as well.A
DELETE or UPDATE statement, in standard SQL, doesn't support more than one
table being affected at the same time (only MySQL has an extended syntax that
supports this but it's not supported by the ORM). There's also a syntax that
supports only one table being updated, but multiple tables in the FROM clause
which on Postgresql is UPDATE..FROM, and SQLAlchemy now supports that too,
but again the ORM doesn't yet have support for that to be integrated.
So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine.
OK
But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.
I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted?
yeah just expire the collection:
session.expire(storeobject, ['name_of_products_collection'])
(2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...
Right now you can only pass in the base class, I took a look since we do
support UPDATE..FROM for supporting DBs, the controversial part here is that an
UPDATE against the child table which then refers to the base table would need
WHERE criterion to join the two together, which introduces tricky
decisionmaking. But one possibility is to just leave that up to the user in
this case.
I've added http://www.sqlalchemy.org/trac/ticket/2365 to look at this
possibility.
--
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.
Re: [sqlalchemy] Inserting when the row depends on id generated by the same transaction.
On Jan 3, 2012, at 9:52 AM, Thiago de Arruda wrote:
I need to implement a simple accounting subsytem that will record all
the transactions for future auditing. This uses two tables:
'transactions' and 'entries'. Transaction-Entry is a parent-child
relationship. My customer has two requeriments : All the
records(entries/transaction) must be inserted in one database
transaction and he wants ensurance that no gaps will ever appear on
the ids generated by these two tables
For the first requeriment(everything on one database transaction), I
must insert to the 'transactions' table, get the generated id and use
that as input for the 'inserts' in the 'entries' table, but everything
must be in one transaction, so I can't invoke 'commit' after inserting
to 'transactions' to get its generated id.
Not sure what the issue is for the first aspect here, do you just need to call
Session.flush() ? http://www.sqlalchemy.org/docs/orm/session.html#flushing
For the second requeriment(ensure that no gaps will happen in the
generated ids), I'm not sure its possible to acomplish, since if
something fails after the id for 'transaction' is generated, even
though that transaction will not be commited, that id will be lost,
thus creating a gap.
I appreciate insights on how to solve either of these problems.
Thanks in advance.
This is a common question, I was able to find a few mentions of it:
http://stackoverflow.com/questions/8361513/sequence-without-a-gaps
http://stackoverflow.com/questions/4105147/alternative-to-maxid-in-complex-primary-key
It's a PITA to create an unbroken chain of integers that is resilient against
transaction failures, because it essentially requires locking the entire table.
Usually, sequences are used to create increasing integer values, but a key
behavior of a sequence is that it never returns the same value again,
regardless of transactional state - this is specifically so that no matter what
happens, there is zero chance of a particular value ever conflicting with one
acquired from the same sequence elsewhere.
So here you need to create the id based on the max(id) of what is already in
the table, and you also need to lock the table to ensure no other processes do
the same thing at the same time. The second answer above suggests that a
simple SELECT .. FOR UPDATE can be used here, the SQLAlchemy Query object uses
with_lockmode() to do this, which...apparently is not correctly documented
so you're basically looking to send the string update here:
query(func.max(MyTable.id)).with_lockmode('update').scalar()
I would test this approach out to ensure it is in fact concurrent based on the
database in use. I'd create a script that does the above query, then waits
for you to press enter using raw_input(). While it's waiting, run the same
script in another window. Make sure the other script locks on the above
query() until script #1 is allowed to proceed.
--
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.
Re: [sqlalchemy] Inserting when the row depends on id generated by the same transaction.
On Tue, Jan 3, 2012 at 1:18 PM, Michael Bayer mike...@zzzcomputing.com wrote:
Not sure what the issue is for the first aspect here, do you just need to
call Session.flush() ?
http://www.sqlalchemy.org/docs/orm/session.html#flushing
That's exactly what I need to do :)
This is a common question, I was able to find a few mentions of it:
http://stackoverflow.com/questions/8361513/sequence-without-a-gaps
http://stackoverflow.com/questions/4105147/alternative-to-maxid-in-complex-primary-key
It's a PITA to create an unbroken chain of integers that is resilient against
transaction failures, because it essentially requires locking the entire
table. Usually, sequences are used to create increasing integer values, but
a key behavior of a sequence is that it never returns the same value again,
regardless of transactional state - this is specifically so that no matter
what happens, there is zero chance of a particular value ever conflicting
with one acquired from the same sequence elsewhere.
So here you need to create the id based on the max(id) of what is already in
the table, and you also need to lock the table to ensure no other processes
do the same thing at the same time. The second answer above suggests that a
simple SELECT .. FOR UPDATE can be used here, the SQLAlchemy Query object
uses with_lockmode() to do this, which...apparently is not correctly
documented so you're basically looking to send the string update here:
query(func.max(MyTable.id)).with_lockmode('update').scalar()
It's nice to see sqlalchemy provides a way to hold database locks.
This is a good option but my customer didn't like this since he
believes his system will be highly concurrent. I will just use
another temporary table for fast inserting, then have a daemon process
periodically read the temporary table and serialize writes on the
final tables.
Thanks for your help Michael.
Thiago.
--
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.
Re: [sqlalchemy] Inserting when the row depends on id generated by the same transaction.
On Jan 3, 2012, at 1:17 PM, Thiago Padilha wrote: It's nice to see sqlalchemy provides a way to hold database locks. This is a good option but my customer didn't like this since he believes his system will be highly concurrent. I will just use another temporary table for fast inserting, then have a daemon process periodically read the temporary table and serialize writes on the final tables. Well then you're still serializing everything and the client won't be able to see the newly generated ID since it's offloaded to a background job. If he wants the database operations themselves to be immediate and highly concurrent the requirement here has to change. My advice here is usually push back on the no gaps in the integer sequence requirement. There's no reason anyone really needs that. -- 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.
[sqlalchemy] 'TypeError: expecting numeric data' is only raised for do_executemany
The statements that are executed as a single statement make no such check (and the database engine correctly translates a string to integer), but cursor.executemany checks type: lib/sqlalchemy/engine/default.py, line 327, in do_executemany cursor.executemany(statement, parameters) TypeError: expecting numeric data You know that inconsistency? -- 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.
[sqlalchemy] Re: 'TypeError: expecting numeric data' is only raised for do_executemany
Oh. Makes sense. Then the only reason I'm starting to hit this is that you've optimized the orm to use executemany() more often, correct? On Jan 3, 3:09 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 3, 2012, at 1:58 PM, Kent wrote: The statements that are executed as a single statement make no such check (and the database engine correctly translates a string to integer), but cursor.executemany checks type: lib/sqlalchemy/engine/default.py, line 327, in do_executemany cursor.executemany(statement, parameters) TypeError: expecting numeric data You know that inconsistency? cursor is the DBAPI cursor, so any inconsistencies there are on the DBAPI side. I don't know what the numeric data in question would be here. -- 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.
Re: [sqlalchemy] Re: 'TypeError: expecting numeric data' is only raised for do_executemany
On Jan 3, 2012, at 3:20 PM, Kent wrote: Oh. Makes sense. Then the only reason I'm starting to hit this is that you've optimized the orm to use executemany() more often, correct? that it does, yes. On Jan 3, 3:09 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 3, 2012, at 1:58 PM, Kent wrote: The statements that are executed as a single statement make no such check (and the database engine correctly translates a string to integer), but cursor.executemany checks type: lib/sqlalchemy/engine/default.py, line 327, in do_executemany cursor.executemany(statement, parameters) TypeError: expecting numeric data You know that inconsistency? cursor is the DBAPI cursor, so any inconsistencies there are on the DBAPI side.I don't know what the numeric data in question would be here. -- 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. -- 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.
[sqlalchemy] column_property and class Mixin problem
Hello. Can't get this to work, I want to get users who is online - users where last_read column = now() - 30 minutes With DBSession.query(User).filter(User.is_online) query But get the following error: File /home/eps/devel/tourclub/pbb/pbb/models/__init__.py, line 147, in module class User(UserMixin, Base): File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py, line 1273, in __init__ _as_declarative(cls, classname, cls.__dict__) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py, line 1078, in _as_declarative column_copies[obj] = getattr(cls, name) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py, line 1480, in __get__ return desc.fget(cls) File /home/eps/devel/tourclub/pbb/pbb/models/__init__.py, line 143, in is_online return column_property(case([(cls.last_read is not None, cls.last_read = func.now() - datetime.timedelta(minutes=30))], else_=False)) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py, line 607, in case return _Case(whens, value=value, else_=else_) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py, line 3016, in __init__ _literal_as_binds(r)) for (c, r) in whens File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py, line 1410, in _no_literals bound value. % element) sqlalchemy.exc.ArgumentError: Ambiguous literal: True. Use the 'text()' function to indicate a SQL expression literal, or 'literal()' to indicate a bound value. I want to get something like this but in SA... SELECT * FROM user u WHERE (CASE WHEN u.last_read IS NULL THEN False ELSE u.last_read = now() - 30 * INTERVAL '1 minute' END) IS True; I use next UserMixin class and User declarative model: class UserMixin(object): id = Column(Integer, primary_key=True) username = Column(String(255), unique=True, nullable=False) ... last_read = Column(DateTime) @declared_attr def is_online(cls): return column_property(case([(cls.last_read is not None, cls.last_read = func.now() - datetime.timedelta(minutes=30))], else_=False)) class User(UserMixin, Base): __tablename__ = 'user' -- 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/-/KUr2Iqu__x0J. 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.
Re: [sqlalchemy] column_property and class Mixin problem
On Jan 3, 2012, at 3:31 PM, sector119 wrote: Hello. Can't get this to work, I want to get users who is online - users where last_read column = now() - 30 minutes With DBSession.query(User).filter(User.is_online) query But get the following error: File /home/eps/devel/tourclub/pbb/pbb/models/__init__.py, line 147, in module class User(UserMixin, Base): File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py, line 1273, in __init__ _as_declarative(cls, classname, cls.__dict__) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py, line 1078, in _as_declarative column_copies[obj] = getattr(cls, name) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py, line 1480, in __get__ return desc.fget(cls) File /home/eps/devel/tourclub/pbb/pbb/models/__init__.py, line 143, in is_online return column_property(case([(cls.last_read is not None, cls.last_read = func.now() - datetime.timedelta(minutes=30))], else_=False)) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py, line 607, in case return _Case(whens, value=value, else_=else_) File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py, line 3016, in __init__ _literal_as_binds(r)) for (c, r) in whens File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py, line 1410, in _no_literals bound value. % element) sqlalchemy.exc.ArgumentError: Ambiguous literal: True. Use the 'text()' function to indicate a SQL expression literal, or 'literal()' to indicate a bound value. I want to get something like this but in SA... SELECT * FROM user u WHERE (CASE WHEN u.last_read IS NULL THEN False ELSE u.last_read = now() - 30 * INTERVAL '1 minute' END) IS True; I use next UserMixin class and User declarative model: class UserMixin(object): id = Column(Integer, primary_key=True) username = Column(String(255), unique=True, nullable=False) ... last_read = Column(DateTime) @declared_attr def is_online(cls): return column_property(case([(cls.last_read is not None, cls.last_read = func.now() - datetime.timedelta(minutes=30))], else_=False)) you'd need to say cls.last_read != None to produce IS NOT NULL. You might need else_=literal(False) too. -- 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.
Re: [sqlalchemy] column_property and class Mixin problem
Now I get
File
/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/visitors.py, line
59, in _compiler_dispatch
return getter(visitor)(self, **kw)
File
/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/compiler.py, line
370, in visit_column
raise exc.CompileError(Cannot compile Column object until
CompileError: Cannot compile Column object until it's 'name' is assigned.
With
case([(cls.last_read != None, cls.last_read = func.now() -
datetime.timedelta(minutes=30))], else_=literal(False))
case([(cls.last_read != None, literal(cls.last_read = func.now() -
datetime.timedelta(minutes=30)))], else_=literal(False))
case([(cls.last_read != None, literal(cls.last_read = func.now() -
datetime.timedelta(minutes=30)).label('foo'))], else_=literal(False))
--
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/-/vRfXbta7DoMJ.
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.
Re: [sqlalchemy] column_property and class Mixin problem
On Jan 3, 2012, at 4:14 PM, sector119 wrote:
Now I get
File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/visitors.py,
line 59, in _compiler_dispatch
return getter(visitor)(self, **kw)
File /home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/compiler.py,
line 370, in visit_column
raise exc.CompileError(Cannot compile Column object until
CompileError: Cannot compile Column object until it's 'name' is assigned.
With
case([(cls.last_read != None, cls.last_read = func.now() -
datetime.timedelta(minutes=30))], else_=literal(False))
case([(cls.last_read != None, literal(cls.last_read = func.now() -
datetime.timedelta(minutes=30)))], else_=literal(False))
case([(cls.last_read != None, literal(cls.last_read = func.now() -
datetime.timedelta(minutes=30)).label('foo'))], else_=literal(False))
please forego the usage of column_property() here in favor of a hybrid. An
example is attached, and the SQLAlchemy documentation is being updated right
now to stress that hybrids should be used unless there's a specific performance
advantage to column_property().
--
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/-/vRfXbta7DoMJ.
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.
--
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/-/vRfXbta7DoMJ.
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.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
import datetime
Base= declarative_base()
class UserMixin(object):
id = Column(Integer, primary_key=True)
username = Column(String(255), unique=True, nullable=False)
last_read = Column(last_read, DateTime)
@hybrid_property
def is_online(self):
if self.last_read is not None:
return self.last_read = \
datetime.datetime.now() - \
datetime.timedelta(minutes=30)
else:
return False
@is_online.expression
def is_online(cls):
return case([(cls.last_read != None,
cls.last_read = func.now() -
datetime.timedelta(minutes=30))], else_=literal(False))
class User(UserMixin, Base):
__tablename__ = 'user'
s = Session()
print s.query(User.is_online)
[sqlalchemy] Re: column_property and class Mixin problem
Thanks a lot, Michael! Works like a charm! -- 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/-/W6s3M_bzrRAJ. 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.
[sqlalchemy] Is there a way to accrue all references to a declarative mapped object?
I have a relatively complex structure to my data. In essence it's something
like:
* A Foo is a small object containing some data.
* A Bar has a fixed number of (say, 3) references to Foos
* A Baz has a single Bar, plus one additional Foo
* A Qux has a single Foo
I know it seems like an odd structure, but there is other data present at each
level, and you're just going to have to take my word for it that it does make
sense! :P
Here are the SQLA declarative classes representing the above:
class Foo(Base):
__tablename__ = 'foos'
id = Column(Integer, primary_key = True)
name = Column(String)
class Bar(Base):
__tablename__ = 'bars'
id = Column(Integer, primary_key = True)
primary_foo_id = Column(Integer, ForeignKey('foos.id'))
secondary_foo_id = Column(Integer, ForeignKey('foos.id'))
tertiary_foo_id = Column(Integer, ForeignKey('foos.id'))
primary_foo = relationship(Foo, primaryjoin = (primary_foo_id ==
Foo.id))
secondary_foo = relationship(Foo, primaryjoin = (secondary_foo_id ==
Foo.id))
tertiary_foo = relationship(Foo, primaryjoin = (tertiary_foo_id ==
Foo.id))
class Baz(Base):
__tablename__ = 'quxs'
id = Column(Integer, primary_key = True)
bar_id = Column(Integer, ForeignKey('bars.id'))
extra_foo_id = Column(Integer, ForeignKey('foos.id'))
bar = relationship(Bar)
extra_foo = relationship(Foo)
class Qux(Base):
__tablename__ = quxs
id = Column(Integer, primary_key = True)
foo_id = Column(Integer, ForeignKey('foos.id'))
foo = relationship(Foo)
I should note that there is no rule that says a Bar cannot use the same Foo
twice, e.g. as both its secondary and tertiary Foos. Likewise, a Baz's extra
Foo could be the same as one of the Foos of its Bar.
Now, to my actual problem. Given a Foo object, I want an easy way to gather all
references to it. So I want a list of every Bar, Baz and Qux that references
the Foo object, and if a Bar references the Foo two or three times, it should
appear two or three times in the list.
So far the only solution I've come up with is to put a uniquely named backref
into each of the above relationships (they would be somethinig like
'bars_as_primary', 'bars_as_secondary', 'bars_as_tertiary', 'bazs', and
'quxs'), and then add a method, Foo.GetUsages(), which chains all of these
arrays together and returns the result.
That seems a bit verbose, but then it's not exactly a textbook problem. There's
no magical function that returns a list of all of the objects that have a
reference to some specific other object is there?
If you've read this far, thanks!
Cheers,
Cam
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.aumailto:cameron.jack...@thalesgroup.com.au |
www.thalesgroup.com.auhttp://www.thalesgroup.com.au
-
DISCLAIMER: This e-mail transmission and any documents, files and
previous e-mail messages attached to it are private and confidential.
They may contain proprietary or copyright material or information that
is subject to legal professional privilege. They are for the use of
the intended recipient only. Any unauthorised viewing, use, disclosure,
copying, alteration, storage or distribution of, or reliance on, this
message is strictly prohibited. No part may be reproduced, adapted or
transmitted without the written permission of the owner. If you have
received this transmission in error, or are not an authorised recipient,
please immediately notify the sender by return email, delete this
message and all copies from your e-mail system, and destroy any printed
copies. Receipt by anyone other than the intended recipient should not
be deemed a waiver of any privilege or protection. Thales Australia
does not warrant or represent that this e-mail or any documents, files
and previous e-mail messages attached are error or virus free.
-
--
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.
[sqlalchemy] RE: Is there a way to accrue all references to a declarative mapped object?
In case anyone was going to attempt to solve this, I have come up with a better
way to approach the problem. The data is still structured the same way, but I
am now going to traverse it in a different way, such that I no longer need a
Foo to be aware of everywhere it is being referenced from.
Still, if anyone is aware of a nice solution to the problem I originally
presented, please, feel free to share it!
Cheers
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.aumailto:cameron.jack...@thalesgroup.com.au |
www.thalesgroup.com.auhttp://www.thalesgroup.com.au
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Jackson, Cameron
Sent: Wednesday, 4 January 2012 10:41 AM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Is there a way to accrue all references to a declarative
mapped object?
I have a relatively complex structure to my data. In essence it's something
like:
* A Foo is a small object containing some data.
* A Bar has a fixed number of (say, 3) references to Foos
* A Baz has a single Bar, plus one additional Foo
* A Qux has a single Foo
I know it seems like an odd structure, but there is other data present at each
level, and you're just going to have to take my word for it that it does make
sense! :P
Here are the SQLA declarative classes representing the above:
class Foo(Base):
__tablename__ = 'foos'
id = Column(Integer, primary_key = True)
name = Column(String)
class Bar(Base):
__tablename__ = 'bars'
id = Column(Integer, primary_key = True)
primary_foo_id = Column(Integer, ForeignKey('foos.id'))
secondary_foo_id = Column(Integer, ForeignKey('foos.id'))
tertiary_foo_id = Column(Integer, ForeignKey('foos.id'))
primary_foo = relationship(Foo, primaryjoin = (primary_foo_id ==
Foo.id))
secondary_foo = relationship(Foo, primaryjoin = (secondary_foo_id ==
Foo.id))
tertiary_foo = relationship(Foo, primaryjoin = (tertiary_foo_id ==
Foo.id))
class Baz(Base):
__tablename__ = 'quxs'
id = Column(Integer, primary_key = True)
bar_id = Column(Integer, ForeignKey('bars.id'))
extra_foo_id = Column(Integer, ForeignKey('foos.id'))
bar = relationship(Bar)
extra_foo = relationship(Foo)
class Qux(Base):
__tablename__ = quxs
id = Column(Integer, primary_key = True)
foo_id = Column(Integer, ForeignKey('foos.id'))
foo = relationship(Foo)
I should note that there is no rule that says a Bar cannot use the same Foo
twice, e.g. as both its secondary and tertiary Foos. Likewise, a Baz's extra
Foo could be the same as one of the Foos of its Bar.
Now, to my actual problem. Given a Foo object, I want an easy way to gather all
references to it. So I want a list of every Bar, Baz and Qux that references
the Foo object, and if a Bar references the Foo two or three times, it should
appear two or three times in the list.
So far the only solution I've come up with is to put a uniquely named backref
into each of the above relationships (they would be somethinig like
'bars_as_primary', 'bars_as_secondary', 'bars_as_tertiary', 'bazs', and
'quxs'), and then add a method, Foo.GetUsages(), which chains all of these
arrays together and returns the result.
That seems a bit verbose, but then it's not exactly a textbook problem. There's
no magical function that returns a list of all of the objects that have a
reference to some specific other object is there?
If you've read this far, thanks!
Cheers,
Cam
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.aumailto:cameron.jack...@thalesgroup.com.au |
www.thalesgroup.com.auhttp://www.thalesgroup.com.au
-
DISCLAIMER: This e-mail transmission and any documents, files and previous
e-mail messages attached to it are private and confidential. They may contain
proprietary or copyright material or information that is subject to legal
professional privilege. They are for the use of the intended recipient only.
Any unauthorised viewing, use, disclosure, copying, alteration, storage or
distribution of, or reliance on, this message is strictly prohibited. No part
may be reproduced, adapted or transmitted without the written permission of the
owner. If you have received this transmission in error, or are not an
authorised recipient, please immediately notify the
