[sqlalchemy] Re: Using SQLAlchemy with multiple classes/modules
hi there, thanks for this. Has cleared alot of things up. thanks again for the nice library. On Nov 17, 3:34 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Nov 17, 2008, at 7:17 AM, Power Button wrote: > > > > > What I have tried is putting all the setup instructions into a class > > and instantiating this in myApp.py. This doesn't make it global to all > > the modules though. Do I need to pass this object around to all > > classes as arguments to __init__() in order to reference this class? > > (this seems a bit bulky to me) > > Neither your classes, mappers, or Table objects need any knowledge of > the Session or engine in order to be defined. Table objects only need > a MetaData() object, which you probably want to define once in a > single module, which is imported by all of your other modules. If > using declarative, then your declarative base class takes the place of > the MetaData object. > > The Session and engine should also be instantiated once globally. > When you start using the Session against particular classes, that's > when they have some association with the database. > > > > > Here is my Bootstrap class > > > class Bootstrap(object): > > def __init__(self): > > self.mDb = MyDb() #my DB abstraction class > > self.mEngine = self.mDb.getEngine() > > self.mSession = self.mDb.getSession() > > self.mMeta = self.mDb.getMeta(self.mEngine) > > > if self.mTable1 == None: > > self. mTable1 = self.mDb.getTable("table1", self.mMeta) > > self.mEdoMapper = mapper(Table1, self. mTable1) > > > if self.mTable2 == None: > > self. mTable2 = self.mDb.getTable("table2", self.mMeta) > > self.mEdoMapper = mapper(Table2, self. mTable2) > > this is overly complex. You dont need any reference to mapper > objects, and the Table objects don't need to be so formally assigned > either. A simple setup looks like: > > base.py > -- > # global MetaData object > metadata = MetaData() > > model1.py > -- > import base > class MyClass(object): > . > > t1 = Table('sometable', base.metadata, ...) > > mapper(MyClass, t1) > > # more classes and tables > > model2.py > -- > import base > > class SomeOtherClass(object): > . > t1 = Table('', ) > mapper(SomeOtherClass, t2, ...) > > bootstrap.py > > # configure the database connection/session > > import base > engine = create_engine('my engine://') > Session = scoped_session(sessionmaker(bind=engine)) > > def create_tables(): > """create all tables which don't yet exist in the DB.""" > > base.metadata.create_all(engine) > > application.py > --- > from bootstrap import Session > frmo model1 import MyClass > > print Session.query(MyClass).all() > > I would also recommend the usage of > declarative,http://www.sqlalchemy.org/docs/05/plugins.html#plugins_declarative > , which simply means that the separate class/Table/mapper() objects > are defined at once, and it also allows dependencies between mapped > classes to be defined using strings which can reduce or remove > circular dependency issues. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
Apparently, nothing changed specifically with the values() functionality. It was added as _values() in 0.4.5 . I guess 0.4.6 was the release of this feature, then. Cool. Thanks for your help On Nov 20, 12:47 am, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > if 0.4.6 works you're great. take a look at the changelog to see what > bugs have been fixed between 0.4.6 and 0.4.8. > > Moshe C. wrote: > > > I have tried it out on 0.4.6 and it is working nicely. > > You mentioned 0.4.7 . > > Is there any bug I should be aware of in 0.4.6? > > I cannot upgrade in the near future. > > > On Nov 20, 12:19 am, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > >> query.distinct().values() or > >> query.values(func.distinct(func.count(table.c.column))) > > >> Moshe C. wrote: > > >> > How would that work with distinct() ? > >> > I see it returns an iterator and not a Query. > > >> > On Nov 19, 11:39 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > >> >> 0.4.7 and above supports query.values(col1, col2, ...) . use that. > > >> >> Bobby Impollonia wrote: > > >> >> > Yeah, with .4 there isn't really a way have an ORM query that > >> doesn't > >> >> > select at least one ORM object (possibly with additional columns/ > >> >> > objects added via add_column/ add_entity). You can use the select() > >> >> > construct instead if pulling all the columns of the mapped class is > >> >> > unacceptable. > > >> >> > On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > >> >> >> 0.4.6 > > >> >> >> On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: > >> >> >>> What version of SQLA are you using? In .5 , you can pass > >> individual > >> >> >>> columns instead of a mapped class to session.query. > > >> >> >>> On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> > >> wrote: > > >> >> >>> > For Query there is an add_column() method, but I do not see a > >> >> remove > >> >> >>> > column method. > >> >> >>> > Initializing a Query requires a full mapped class, so how can I > >> >> >>> select > >> >> >>> > on only a subset of the columns. > > >> >> >>> > I want to do this for ding a DISTINCT query on only a couple of > >> >> >>> > columns. > > >> >> >>> > TIA > >> >> >>> > Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: JOIN to subquery in mapper
its impossible to know what you want without seeing literal SQL but this is the general idea from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class USCity(Base): __tablename__ = 'cities' id = Column(Integer, primary_key=True) name = Column(String(50)) @property def zipcode_meta(self): return object_session(self).query(USZipCode.city_id, USZipCode.population, USZipCode.avg_house_value).filter(USZipCode.city_id==self.city_id) class USZipCode(Base): __tablename__ = 'zipcodes' id = Column(Integer, primary_key=True) city_id = Column(Integer, ForeignKey('cities.id')) zip = Column(String(12)) population = Column('population', Integer) avg_house_value = Column('avg_house_value', Integer) indigophone wrote: > > I gave it a shot but I am no closer to knowing how to do this. > > On Nov 19, 12:00 am, Michael Bayer <[EMAIL PROTECTED]> wrote: >> joining to a subquery is better accomplished outside of relation() >> using query, such as query(USZipCode).join((subquery, >> subquery.c.col==USZipCode.somecol)). >> >> Now you want it as an attribute on your class. Do it like this: >> >> class USCity(object): >> ... >> >> [EMAIL PROTECTED] >> def zipcode_meta(self): >> return object_session(self).query(USZipCode).join(...join >> criterion...).params(..whatever...) >> >> The advantage to this is that you can formulate the query and its >> relation to the parent in exactly the way you need. >> >> On Nov 18, 2008, at 9:34 PM, indigophone wrote: >> >> >> >> > zipcode_meta_join_subquery = session.query(us_zipcode_table.c.city_id, >> > us_zipcode_table.c.zipcode_population, >> > us_zipcode_table.c.average_house_value).group_by(us_zipc >> > ode_table.c.city_id).subquery() >> >> > mapper(USCity, us_city_table, properties={ >> > 'state':relation(USState, backref=backref('cities')), >> > 'zipcode_meta':relation(USZipCode, primaryjoin= >> > (zipcode_meta_join_subquery, >> > zipcode_meta_join_subquery.c.city_id==us_city_table.c.city_id)) >> > }) >> >> > The above code obviously doesn't work. How do I add a join to the >> > above subquery in my mapper? > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
if 0.4.6 works you're great. take a look at the changelog to see what bugs have been fixed between 0.4.6 and 0.4.8. Moshe C. wrote: > > I have tried it out on 0.4.6 and it is working nicely. > You mentioned 0.4.7 . > Is there any bug I should be aware of in 0.4.6? > I cannot upgrade in the near future. > > On Nov 20, 12:19 am, "Michael Bayer" <[EMAIL PROTECTED]> wrote: >> query.distinct().values() or >> query.values(func.distinct(func.count(table.c.column))) >> >> Moshe C. wrote: >> >> > How would that work with distinct() ? >> > I see it returns an iterator and not a Query. >> >> > On Nov 19, 11:39 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: >> >> 0.4.7 and above supports query.values(col1, col2, ...) . use that. >> >> >> Bobby Impollonia wrote: >> >> >> > Yeah, with .4 there isn't really a way have an ORM query that >> doesn't >> >> > select at least one ORM object (possibly with additional columns/ >> >> > objects added via add_column/ add_entity). You can use the select() >> >> > construct instead if pulling all the columns of the mapped class is >> >> > unacceptable. >> >> >> > On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. <[EMAIL PROTECTED]> wrote: >> >> >> >> 0.4.6 >> >> >> >> On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: >> >> >>> What version of SQLA are you using? In .5 , you can pass >> individual >> >> >>> columns instead of a mapped class to session.query. >> >> >> >>> On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> >> wrote: >> >> >> >>> > For Query there is an add_column() method, but I do not see a >> >> remove >> >> >>> > column method. >> >> >>> > Initializing a Query requires a full mapped class, so how can I >> >> >>> select >> >> >>> > on only a subset of the columns. >> >> >> >>> > I want to do this for ding a DISTINCT query on only a couple of >> >> >>> > columns. >> >> >> >>> > TIA >> >> >>> > Moshe > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
I have tried it out on 0.4.6 and it is working nicely. You mentioned 0.4.7 . Is there any bug I should be aware of in 0.4.6? I cannot upgrade in the near future. On Nov 20, 12:19 am, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > query.distinct().values() or > query.values(func.distinct(func.count(table.c.column))) > > Moshe C. wrote: > > > How would that work with distinct() ? > > I see it returns an iterator and not a Query. > > > On Nov 19, 11:39 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > >> 0.4.7 and above supports query.values(col1, col2, ...) . use that. > > >> Bobby Impollonia wrote: > > >> > Yeah, with .4 there isn't really a way have an ORM query that doesn't > >> > select at least one ORM object (possibly with additional columns/ > >> > objects added via add_column/ add_entity). You can use the select() > >> > construct instead if pulling all the columns of the mapped class is > >> > unacceptable. > > >> > On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > >> >> 0.4.6 > > >> >> On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: > >> >>> What version of SQLA are you using? In .5 , you can pass individual > >> >>> columns instead of a mapped class to session.query. > > >> >>> On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > >> >>> > For Query there is an add_column() method, but I do not see a > >> remove > >> >>> > column method. > >> >>> > Initializing a Query requires a full mapped class, so how can I > >> >>> select > >> >>> > on only a subset of the columns. > > >> >>> > I want to do this for ding a DISTINCT query on only a couple of > >> >>> > columns. > > >> >>> > TIA > >> >>> > Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
query.distinct().values() or query.values(func.distinct(func.count(table.c.column))) Moshe C. wrote: > > How would that work with distinct() ? > I see it returns an iterator and not a Query. > > On Nov 19, 11:39 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: >> 0.4.7 and above supports query.values(col1, col2, ...) . use that. >> >> Bobby Impollonia wrote: >> >> > Yeah, with .4 there isn't really a way have an ORM query that doesn't >> > select at least one ORM object (possibly with additional columns/ >> > objects added via add_column/ add_entity). You can use the select() >> > construct instead if pulling all the columns of the mapped class is >> > unacceptable. >> >> > On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. <[EMAIL PROTECTED]> wrote: >> >> >> 0.4.6 >> >> >> On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: >> >>> What version of SQLA are you using? In .5 , you can pass individual >> >>> columns instead of a mapped class to session.query. >> >> >>> On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> wrote: >> >> >>> > For Query there is an add_column() method, but I do not see a >> remove >> >>> > column method. >> >>> > Initializing a Query requires a full mapped class, so how can I >> >>> select >> >>> > on only a subset of the columns. >> >> >>> > I want to do this for ding a DISTINCT query on only a couple of >> >>> > columns. >> >> >>> > TIA >> >>> > Moshe > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: JOIN to subquery in mapper
I gave it a shot but I am no closer to knowing how to do this. On Nov 19, 12:00 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > joining to a subquery is better accomplished outside of relation() > using query, such as query(USZipCode).join((subquery, > subquery.c.col==USZipCode.somecol)). > > Now you want it as an attribute on your class. Do it like this: > > class USCity(object): > ... > > [EMAIL PROTECTED] > def zipcode_meta(self): > return object_session(self).query(USZipCode).join(...join > criterion...).params(..whatever...) > > The advantage to this is that you can formulate the query and its > relation to the parent in exactly the way you need. > > On Nov 18, 2008, at 9:34 PM, indigophone wrote: > > > > > zipcode_meta_join_subquery = session.query(us_zipcode_table.c.city_id, > > us_zipcode_table.c.zipcode_population, > > us_zipcode_table.c.average_house_value).group_by(us_zipc > > ode_table.c.city_id).subquery() > > > mapper(USCity, us_city_table, properties={ > > 'state':relation(USState, backref=backref('cities')), > > 'zipcode_meta':relation(USZipCode, primaryjoin= > > (zipcode_meta_join_subquery, > > zipcode_meta_join_subquery.c.city_id==us_city_table.c.city_id)) > > }) > > > The above code obviously doesn't work. How do I add a join to the > > above subquery in my mapper? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
How would that work with distinct() ? I see it returns an iterator and not a Query. On Nov 19, 11:39 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > 0.4.7 and above supports query.values(col1, col2, ...) . use that. > > Bobby Impollonia wrote: > > > Yeah, with .4 there isn't really a way have an ORM query that doesn't > > select at least one ORM object (possibly with additional columns/ > > objects added via add_column/ add_entity). You can use the select() > > construct instead if pulling all the columns of the mapped class is > > unacceptable. > > > On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > >> 0.4.6 > > >> On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: > >>> What version of SQLA are you using? In .5 , you can pass individual > >>> columns instead of a mapped class to session.query. > > >>> On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > >>> > For Query there is an add_column() method, but I do not see a remove > >>> > column method. > >>> > Initializing a Query requires a full mapped class, so how can I > >>> select > >>> > on only a subset of the columns. > > >>> > I want to do this for ding a DISTINCT query on only a couple of > >>> > columns. > > >>> > TIA > >>> > Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
0.4.7 and above supports query.values(col1, col2, ...) . use that. Bobby Impollonia wrote: > > Yeah, with .4 there isn't really a way have an ORM query that doesn't > select at least one ORM object (possibly with additional columns/ > objects added via add_column/ add_entity). You can use the select() > construct instead if pulling all the columns of the mapped class is > unacceptable. > > On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. <[EMAIL PROTECTED]> wrote: >> >> 0.4.6 >> >> On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: >>> What version of SQLA are you using? In .5 , you can pass individual >>> columns instead of a mapped class to session.query. >>> >>> On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> wrote: >>> >>> > For Query there is an add_column() method, but I do not see a remove >>> > column method. >>> > Initializing a Query requires a full mapped class, so how can I >>> select >>> > on only a subset of the columns. >>> >>> > I want to do this for ding a DISTINCT query on only a couple of >>> > columns. >>> >>> > TIA >>> > Moshe >> > >> > > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
Yeah, with .4 there isn't really a way have an ORM query that doesn't select at least one ORM object (possibly with additional columns/ objects added via add_column/ add_entity). You can use the select() construct instead if pulling all the columns of the mapped class is unacceptable. On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > 0.4.6 > > On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: >> What version of SQLA are you using? In .5 , you can pass individual >> columns instead of a mapped class to session.query. >> >> On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> wrote: >> >> > For Query there is an add_column() method, but I do not see a remove >> > column method. >> > Initializing a Query requires a full mapped class, so how can I select >> > on only a subset of the columns. >> >> > I want to do this for ding a DISTINCT query on only a couple of >> > columns. >> >> > TIA >> > Moshe > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
0.4.6 On Nov 19, 11:12 pm, "Bobby Impollonia" <[EMAIL PROTECTED]> wrote: > What version of SQLA are you using? In .5 , you can pass individual > columns instead of a mapped class to session.query. > > On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > > For Query there is an add_column() method, but I do not see a remove > > column method. > > Initializing a Query requires a full mapped class, so how can I select > > on only a subset of the columns. > > > I want to do this for ding a DISTINCT query on only a couple of > > columns. > > > TIA > > Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
What version of SQLA are you using? In .5 , you can pass individual columns instead of a mapped class to session.query. On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. <[EMAIL PROTECTED]> wrote: > > For Query there is an add_column() method, but I do not see a remove > column method. > Initializing a Query requires a full mapped class, so how can I select > on only a subset of the columns. > > I want to do this for ding a DISTINCT query on only a couple of > columns. > > TIA > Moshe > > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] ORM Query that selects only a subset of the columns
For Query there is an add_column() method, but I do not see a remove column method. Initializing a Query requires a full mapped class, so how can I select on only a subset of the columns. I want to do this for ding a DISTINCT query on only a couple of columns. TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Data logging and many-to-many's
i have similar problem for which i made a proper .copy() that does (recursively) the "right" job when copying items/references and implicit many2manys, namely copy the whole relation anew.rel = aold.rel[:] (or equivalent of). it also does special treatment on explicit many2manys (assoc.objects), which need relinking to the new object. and it also disallows copying of one2many relations, so one has to decide what that means and how to do it. a generic semi-working sceleton is here: http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/usage/copydeep.py have fun svil On Wednesday 19 November 2008 16:53:32 Joril wrote: > Hi everyone! > I'm still working on implementing data logging with SQLAlchemy > (=whenever someone updates a persisted entity, generate a new > record and mark the old one as such) and I'm looking for help again > :) > > Everything works fine, aside from many-to-many's.. For example, if > I save a parent object with 2 children, and then update it, every > object gets correctly replicated but the junction table doesn't get > updated, so the new parent record never gets linked to its > children.. So, to visualize: > > Having > > p1.children = [ c1, c2 ] > > after updating p1 I end up with > > p1.children = [ c1, c2 ] > p2.children = [] > with c3 and c4 "on the loose" :/ > > I've been debugging this for 6 hours *_*; But I can't figure out > how to trick SQLA into INSERTing the new relation inside the > junction table.. Is there a way? > I posted a simple testcase at http://pastebin.com/m16f5ef0a > > Many thanks for your attention! > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Data logging and many-to-many's
Joril wrote: > > I see.. But - sorry if I insist - are you implying that it would be > too difficult to do? I know it'd be a kind of ugly hack, but since I > spent almost the whole day looking for it, now I'm wondering if I was > looking for something that doesn't exist at all X-) you'd need to use a mechanism similar to the cascade which occurs within the merge() method of Session. This approach also involves the events being fired off normally within the attribute system so there's no hacking into that. This would be the way to "automate" the process and the "cascade" iterators are only semi-private. Manipulating attribute history entries directly would be more tedious. For that route, of course there's ways to create any kind of state you want in Python, including the exact same state which is created when creating and manipulating mapped objects. It's just that the structure of this state is very specific to SQLA's internal implementation details and can change at any time. This is why the boundary of abstraction provided by the attributes package should be honored. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Data logging and many-to-many's
On 19 Nov, 17:22, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > > Out of curiosity, is there a solution that wouldn't require creating > > new instances of the objects? > > (Some magic incantation like the "del instance_state(entity).key" you > > suggested me some time ago, I guess..?) > > not really since you'd have to manually recreate the history attributes > for the "children" list in order for SQLA to see those items as newly > added, thus resulting in the rules that establish the association to be > invoked. I see.. But - sorry if I insist - are you implying that it would be too difficult to do? I know it'd be a kind of ugly hack, but since I spent almost the whole day looking for it, now I'm wondering if I was looking for something that doesn't exist at all X-) > since the path of "create and associate objects like normal > python objects" is the path of usage the ORM was designed for, that's the > one that will work without danger of being impacted by implementation > details. Yes of course.. Thanks again! --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Data logging and many-to-many's
Joril wrote: > > On 19 Nov, 16:28, "Michael Bayer" <[EMAIL PROTECTED]> wrote: >> a guaranteed stable way that doesn't rely on SQLAlchemy implementation >> details and is easy to understand is here. this is how I would do it: >> >> http://pastebin.com/f6670eebe > > I see, many thanks for yor time :) > Out of curiosity, is there a solution that wouldn't require creating > new instances of the objects? > (Some magic incantation like the "del instance_state(entity).key" you > suggested me some time ago, I guess..?) not really since you'd have to manually recreate the history attributes for the "children" list in order for SQLA to see those items as newly added, thus resulting in the rules that establish the association to be invoked. since the path of "create and associate objects like normal python objects" is the path of usage the ORM was designed for, that's the one that will work without danger of being impacted by implementation details. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Data logging and many-to-many's
On 19 Nov, 16:28, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > a guaranteed stable way that doesn't rely on SQLAlchemy implementation > details and is easy to understand is here. this is how I would do it: > > http://pastebin.com/f6670eebe I see, many thanks for yor time :) Out of curiosity, is there a solution that wouldn't require creating new instances of the objects? (Some magic incantation like the "del instance_state(entity).key" you suggested me some time ago, I guess..?) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Data logging and many-to-many's
a guaranteed stable way that doesn't rely on SQLAlchemy implementation details and is easy to understand is here. this is how I would do it: http://pastebin.com/f6670eebe Joril wrote: > > Hi everyone! > I'm still working on implementing data logging with SQLAlchemy > (=whenever someone updates a persisted entity, generate a new record > and mark the old one as such) and I'm looking for help again :) > > Everything works fine, aside from many-to-many's.. For example, if I > save a parent object with 2 children, and then update it, every object > gets correctly replicated but the junction table doesn't get updated, > so the new parent record never gets linked to its children.. So, to > visualize: > > Having > > p1.children = [ c1, c2 ] > > after updating p1 I end up with > > p1.children = [ c1, c2 ] > p2.children = [] > with c3 and c4 "on the loose" :/ > > I've been debugging this for 6 hours *_*; But I can't figure out how > to trick SQLA into INSERTing the new relation inside the junction > table.. Is there a way? > I posted a simple testcase at http://pastebin.com/m16f5ef0a > > Many thanks for your attention! > > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Data logging and many-to-many's
Hi everyone! I'm still working on implementing data logging with SQLAlchemy (=whenever someone updates a persisted entity, generate a new record and mark the old one as such) and I'm looking for help again :) Everything works fine, aside from many-to-many's.. For example, if I save a parent object with 2 children, and then update it, every object gets correctly replicated but the junction table doesn't get updated, so the new parent record never gets linked to its children.. So, to visualize: Having p1.children = [ c1, c2 ] after updating p1 I end up with p1.children = [ c1, c2 ] p2.children = [] with c3 and c4 "on the loose" :/ I've been debugging this for 6 hours *_*; But I can't figure out how to trick SQLA into INSERTing the new relation inside the junction table.. Is there a way? I posted a simple testcase at http://pastebin.com/m16f5ef0a Many thanks for your attention! --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: adding objects to table only once
I second Eric's opinion, the EntitySingleton is an useful recipy and I used it in SQLAlchemy 5. I think it can be used in your situation well On Nov 18, 6:51 pm, Eric Ongerth <[EMAIL PROTECTED]> wrote: > Faheem, > > On a closely related note, you may find the following 'usage recipe' > interesting, from the SA wiki. It's from the 0.4 era and I think it's > distinct from your problem (it's addressing attributes as opposed to > primary keys) but still useful and related. > > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject > > On Nov 18, 8:53 am, Faheem Mitha <[EMAIL PROTECTED]> wrote: > > > On Mon, 17 Nov 2008, Faheem Mitha wrote: > > > Hi, > > > > I've written a session transcript to init db tables and add objects > > > (well, rows) to the tables. The issue I'm currently facing is how to > > > make the "creating and populating the tables" section of the script a > > > no-op when the objects exist. If the tables already exist sqlalchemy > > > does nothing, which is fine. However, this script currently does try to > > > add the objects that are already there, and so throws an exception. I > > > suppose the thing to do would be to check for each object whether it > > > already exists in the db, and do nothing if so. What would be the > > > simplest/cleanest way to do so? I've been fiddling with this for a while > > > without finding an obviously good solution. Is it possible to check > > > whether an "object" is already in a specific table? > > > [following up to my own message] > > > The following approach works, but is kinda kludgy. In particular, I'd like > > to genericise it. The main obstacle in doing so is finding a generic > > expression for the primary key. There is always a primary key, and by > > definition it is unique, right? So, I think it makes sense to use that for > > comparison, but the actual name of the primary key can differ and is can > > also be composite. So, is there a way to access it in a generic way? > > Alternatively, is there a better approach to this? > > > Thanks, Faheem. > > > def add_patient_obj(session, patient_obj): > > """ Check if object primary key exists in db. If so,exit, else add.""" > > pid = patient_obj.id > > if session.query(Patient).filter_by(id=pid).count() > 0: > > print "Patient object with id %s is already in db."%pid > > exit > > else: > > session.save(patient_obj) > > session.commit() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---