Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 13.06.2013, 21:20 Uhr, schrieb Andy : It's the original issue. The relation (that I want the ORM to see) isn't what's literally set in the schema by foreign keys, and the primaryjoin/foreign_keys ORM magic for this IMO sucks. Something like "onetomany" and "manytoone" in the mapper config would solve the problem nicely. Can't you make the join condition explicit? I seem to remember doing something like that recently. I don't like relying on magic ever but I do think that SQLAlchemy does a really excellent job in most situations. In others, I think you can use SQL Expressions as Mike has recently indicated on another thread. My big point is that people using databases have to be prepared to find out how to get the best use of them and that often means writing out a query in SQL first and then writing it in SQLAlchemy. Well, yes, I'd always recommend Postgres over MySQL but I don't see what the choice of backend has to do with this problem, except how well reflection works with Postgres. From a developer's perspective MySQL's biggest problem, apart from MyASM, is that its behaviour can be unpredictable. The MySQL vs PostgreSQL holy war is completely irrelevant to this issue Indeed, but you started it! ;-) Charlie -- Charlie Clark Managing Director Clark Consulting & Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
On Thursday, June 13, 2013 1:59:16 AM UTC-7, Charlie Clark wrote:
>
> Am 13.06.2013, 00:42 Uhr, schrieb Andy >:
>
> >> Ah, okay. I think I understand what I was missing initially. I've had
> >> another go at this and it seems to work as required, even on MySQL.
> Not
> >> sure how this all works with SQLAlchemy but I would expect it to be
> fine
> >> with it.
> >>
> >> insert into favourites (thing_id, group_id) values (2, 2)
> >> Error Code: 1062. Duplicate entry '2' for key 'PRIMARY'0.012
> sec
> >>
> >> insert into favourites (thing_id, group_id) values (1, 3)
> >> Error Code: 1452. Cannot add or update a child row: a foreign key
> >> constraint fails ("favourites"."favourites", CONSTRAINT "checker"
> >> FOREIGN
> >>
> >> KEY ("thing_id", "group_id") REFERENCES "groups_things" ("thing_id",
> >> "group_id"))0.007 sec
> >>
> >
> > It's not, at least with automatically configured relations, hence this
> > thread.
>
> What do you mean with automatically configured relations? Using SQLa to
> define the schema? I always manage the schema directly so I don't know so
>
> much about that. For me, the important thing is that SQLa can work with
> the schema with the least number of contortions and I don't see any
> required here.
>
It's the original issue. The relation (that I want the ORM to see) isn't
what's literally set in the schema by foreign keys, and the
primaryjoin/foreign_keys ORM magic for this IMO sucks. Something like
"onetomany" and "manytoone" in the mapper config would solve the problem
nicely.
>
> >> > Also, SERIAL? You must be using a real database engine.
> >>
> >> Well, er, yes. When it comes to modelling that's where you should
> start.
> >> And Oracle is making progress with MySQL now that InnoDB with separate
> >> files pro table and 5.6 even seems to have some kind of vacuuming
> >> built-in.
> >>
> >
> > I dunno. This may be the end of my (nonexistent) web developer career,
>
> > but
> > I think I will never again recommend using mysql for any purpose
> > whatsoever
> > (except perhaps compatibility). I've learned my lesson. Next time I'll
> > use PostgreSQL.
>
> Well, yes, I'd always recommend Postgres over MySQL but I don't see what
> the choice of backend has to do with this problem, except how well
> reflection works with Postgres. From a developer's perspective MySQL's
> biggest problem, apart from MyASM, is that its behaviour can be
> unpredictable.
>
The MySQL vs PostgreSQL holy war is completely irrelevant to this issue :)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] TypeDecorator and orm refresh
> > HI! > > I implemented a TypeDecorator which allows setting the precision of a > datetime value. > > It does something like: > > *import datetime* > * > * > *class PrecisionDateTime(TypeDecorator):* > *def process_bind_param(self, value, dialect):* > *return datetime.datetime(value.year, value.month,* > * value.hour, value.minute,* > * 0, 0)* > > > This works but the problem is, that I have to call Session.refresh() on > every added (and flushed) > object because otherwise the date-column still has the old value (with > second != 0 and millisecond != 0). > > Is there a way the refresh could be avoided? > > > you'd want to switch/augment by using a @validates rule: > > > http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html?highlight=validates#simple-validators > > Thanks for the fast answer Michael! Your suggestion brought me onto the right track! I solved it like this: *def create_dt_precision_validator(precision):* *def validate_precision_date_time(target, value, oldvalue, initiator):* *return change_date_precision(value, precision)* *return validate_precision_date_time* * * * * *@event.listens_for(orm.mapper, 'mapper_configured')* *def add_precision_dt_validators(mapper, class_):* *for prop in mapper.iterate_properties:* *if isinstance(prop, ColumnProperty):* *for column in prop.columns:* *if isinstance(column.type, PrecisionDateTime):* *event.listen(prop, 'set',* * create_dt_precision_validator(* * column.type.precision),* * retval=True)* Somehow I like it better when I am able to define such rules at the column level. But the @validators features is good to know! Thanks a lot, Florian -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] TypeDecorator and orm refresh
On Jun 13, 2013, at 12:03 PM, Florian Müller wrote: > HI! > > I implemented a TypeDecorator which allows setting the precision of a > datetime value. > > It does something like: > > import datetime > > class PrecisionDateTime(TypeDecorator): > def process_bind_param(self, value, dialect): > return datetime.datetime(value.year, value.month, > value.hour, value.minute, > 0, 0) > > This works but the problem is, that I have to call Session.refresh() on every > added (and flushed) > object because otherwise the date-column still has the old value (with second > != 0 and millisecond != 0). > > Is there a way the refresh could be avoided? you'd want to switch/augment by using a @validates rule: http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html?highlight=validates#simple-validators -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SA0.7.9 - poor query performance (full scan instead of index scan because of joined table inheritance)
On Jun 13, 2013, at 11:03 AM, Ladislav Lenart wrote: > Unfortunately migrating to SA 0.9 is not an option for me at the moment due to > severe time constraints. I was pretty sure you'd say that, though I'm really looking to verify that my fixes are going to hold up under real world usage. The issues you're having are real issues, and they've been fixed. > > Could you please help me write SA query for 0.7.9 that uses index scan and > also > loads all the necessary relations? It must be possible with a proper use of > from_statement(), contains_eager() and/or other SA features. It is just that > toy > examples in the documentation don't help me much with this complex beast. you use the SQL expression language in conjunction with .join()/outerjoin(), pass to query.select_from(), then use contains_eager(): j = Foo.__table__.outerjoin(Bar.__table__.outerjoin(BarA.__table__).outerjoin(Data.__table__)) q = s.query(Foo).\ select_from(j).\ filter(Foo.id.in_([1, 2, 3])).\ options( contains_eager(Foo.bar), contains_eager(Foo.bar.of_type(BarA), BarA.data) ) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] TypeDecorator and orm refresh
HI! I implemented a TypeDecorator which allows setting the precision of a datetime value. It does something like: *import datetime* * * *class PrecisionDateTime(TypeDecorator):* *def process_bind_param(self, value, dialect):* *return datetime.datetime(value.year, value.month,* * value.hour, value.minute,* * 0, 0)* This works but the problem is, that I have to call Session.refresh() on every added (and flushed) object because otherwise the date-column still has the old value (with second != 0 and millisecond != 0). Is there a way the refresh could be avoided? I have SQLAlchemy version 0.7.10 Regards, Florian -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SA0.7.9 - poor query performance (full scan instead of index scan because of joined table inheritance)
Hello. Just a minor correction: The new query can return whatever it pleases as long as it *also* returns Foo instances with properly populated relations. The rest of the e-mail is the same. Please help me, Ladislav Lenart On 13.6.2013 17:03, Ladislav Lenart wrote: > Unfortunately migrating to SA 0.9 is not an option for me at the moment due to > severe time constraints. > > Could you please help me write SA query for 0.7.9 that uses index scan and > also > loads all the necessary relations? It must be possible with a proper use of > from_statement(), contains_eager() and/or other SA features. It is just that > toy > examples in the documentation don't help me much with this complex beast. > > Here is a simplified version of my problem: > > Foo > bar (can be NULL) > > Bar > > BarA (subclass of Bar) > data -> Data (cannot be NULL) > > Data > value (string) > > I need to rewrite this query: > > q = session.query(Foo).filter(Foo.id.in_(...)) > q = q.options( > joinedload_all(Foo.bar, BarA.data) > ) > > in such a way that it does NOT perform any full scan and also populates > Foo.bar.data.value of each returned Foo. The new query can return whatever it > pleases as long as it returns Foo instances with properly populated relations. > > Please help me write it (or tell me that it is not possible in SA 0.7.9, > though > I highly doubt that). > > > Thank you, > > Ladislav Lenart > > > On 13.6.2013 15:51, Michael Bayer wrote: >> Please try out 0.9 from the git master which fixes the issue of the nested >> SELECT on the right side of a join. >> >> Sent from my iPhone >> >> On Jun 13, 2013, at 9:18 AM, Ladislav Lenart wrote: >> >>> Hello. >>> >>> I have a query that does a full scan of an inherited table with more than >>> million rows even though I need only 100 of them (on postgres 9.1). This is >>> a >>> real bummer! Please help me rewrite the SA query or instruct postgres to >>> not do >>> this stupidity. >>> >>> >>> I have the following setup (only the interesting relations): >>> >>>Contact >>>contact_tags -> ContactTag (collection) >>>phones -> Phone (collection) >>>emails -> Email (collection) >>> >>>ContactTag >>>tag -> Tag (cannot be NULL) >>> >>>PersonalContact (Contact subclass) >>>partner -> Partner (can be NULL) >>>client -> PersonalClient (can be NULL) >>> >>>CorporateContact (Contact subclass) >>>client -> CorporateClient (can be NULL) >>> >>>Client >>> >>>PersonalClient (Client subclass) >>>data -> PersonalData (cannot be NULL) >>> >>>CorporateClient (Client subclass) >>>data -> CorporateData (cannot be NULL) >>> >>> >>> I have the following query that loads data of one window: >>> >>># window (input argument) is a list of id values. >>>q = session.query(PersonalContact).filter(PersonalContact.id.in_(window) >>>q = q.options( >>>subqueryload_all(PersonalContact.contact_tags, ContactTag.tag), >>>subqueryload(PersonalContact.phones), >>>subqueryload(PersonalContact.emails), >>>joinedload_all( >>>PersonalContact.partner, >>>Partner.personal_data, >>>PersonalData.address, >>> ), >>>joinedload_all( >>>PersonalContact.client, >>>PersonalClient.data, >>>PersonalData.address >>>), >>>) >>> >>> >>> (Note that I have similar query for CorporateContact. Infact, I generate >>> them >>> both in the same method.) >>> >>> It produces SQL like this (the problematic part is emphasized): >>> >>>SELECT * >>>FROM >>>contact >>>JOIN personal_contact ON contact.id = personal_contact.id >>>-- *** >>>LEFT OUTER JOIN ( >>>SELECT * >>>FROM >>>client >>>JOIN personal_client ON client.id = personal_client.id >>>) AS anon_1 ON personal_contact.client_id = anon_1.personal_client_id >>>-- *** >>>LEFT OUTER JOIN partner AS partner_1 >>>ON personal_contact.partner_id = partner_1.id >>>LEFT OUTER JOIN personal_data AS personal_data_1 >>>ON partner_1.personal_data_id = personal_data_1.id >>>LEFT OUTER JOIN address AS address_1 >>>ON personal_data_1.address_id = address_1.id >>>LEFT OUTER JOIN personal_data AS personal_data_2 >>>ON anon_1.personal_client_data_id = personal_data_2.id >>>LEFT OUTER JOIN address AS address_2 >>>ON personal_data_2.address_id = address_2.id >>>WHERE personal_contact.id IN (...) >>> >>> >>> The inner select directly corresponds to joinedload of >>> PersonalContact.client, a >>> PersonalClient instance (and a Client subclass). >>> >>> The postgres does a full scan of tables Client and PersonalClient even >>> though I >>> will need at mo
Re: [sqlalchemy] SA0.7.9 - poor query performance (full scan instead of index scan because of joined table inheritance)
Unfortunately migrating to SA 0.9 is not an option for me at the moment due to severe time constraints. Could you please help me write SA query for 0.7.9 that uses index scan and also loads all the necessary relations? It must be possible with a proper use of from_statement(), contains_eager() and/or other SA features. It is just that toy examples in the documentation don't help me much with this complex beast. Here is a simplified version of my problem: Foo bar (can be NULL) Bar BarA (subclass of Bar) data -> Data (cannot be NULL) Data value (string) I need to rewrite this query: q = session.query(Foo).filter(Foo.id.in_(...)) q = q.options( joinedload_all(Foo.bar, BarA.data) ) in such a way that it does NOT perform any full scan and also populates Foo.bar.data.value of each returned Foo. The new query can return whatever it pleases as long as it returns Foo instances with properly populated relations. Please help me write it (or tell me that it is not possible in SA 0.7.9, though I highly doubt that). Thank you, Ladislav Lenart On 13.6.2013 15:51, Michael Bayer wrote: > Please try out 0.9 from the git master which fixes the issue of the nested > SELECT on the right side of a join. > > Sent from my iPhone > > On Jun 13, 2013, at 9:18 AM, Ladislav Lenart wrote: > >> Hello. >> >> I have a query that does a full scan of an inherited table with more than >> million rows even though I need only 100 of them (on postgres 9.1). This is a >> real bummer! Please help me rewrite the SA query or instruct postgres to not >> do >> this stupidity. >> >> >> I have the following setup (only the interesting relations): >> >>Contact >>contact_tags -> ContactTag (collection) >>phones -> Phone (collection) >>emails -> Email (collection) >> >>ContactTag >>tag -> Tag (cannot be NULL) >> >>PersonalContact (Contact subclass) >>partner -> Partner (can be NULL) >>client -> PersonalClient (can be NULL) >> >>CorporateContact (Contact subclass) >>client -> CorporateClient (can be NULL) >> >>Client >> >>PersonalClient (Client subclass) >>data -> PersonalData (cannot be NULL) >> >>CorporateClient (Client subclass) >>data -> CorporateData (cannot be NULL) >> >> >> I have the following query that loads data of one window: >> >># window (input argument) is a list of id values. >>q = session.query(PersonalContact).filter(PersonalContact.id.in_(window) >>q = q.options( >>subqueryload_all(PersonalContact.contact_tags, ContactTag.tag), >>subqueryload(PersonalContact.phones), >>subqueryload(PersonalContact.emails), >>joinedload_all( >>PersonalContact.partner, >>Partner.personal_data, >>PersonalData.address, >> ), >>joinedload_all( >>PersonalContact.client, >>PersonalClient.data, >>PersonalData.address >>), >>) >> >> >> (Note that I have similar query for CorporateContact. Infact, I generate them >> both in the same method.) >> >> It produces SQL like this (the problematic part is emphasized): >> >>SELECT * >>FROM >>contact >>JOIN personal_contact ON contact.id = personal_contact.id >>-- *** >>LEFT OUTER JOIN ( >>SELECT * >>FROM >>client >>JOIN personal_client ON client.id = personal_client.id >>) AS anon_1 ON personal_contact.client_id = anon_1.personal_client_id >>-- *** >>LEFT OUTER JOIN partner AS partner_1 >>ON personal_contact.partner_id = partner_1.id >>LEFT OUTER JOIN personal_data AS personal_data_1 >>ON partner_1.personal_data_id = personal_data_1.id >>LEFT OUTER JOIN address AS address_1 >>ON personal_data_1.address_id = address_1.id >>LEFT OUTER JOIN personal_data AS personal_data_2 >>ON anon_1.personal_client_data_id = personal_data_2.id >>LEFT OUTER JOIN address AS address_2 >>ON personal_data_2.address_id = address_2.id >>WHERE personal_contact.id IN (...) >> >> >> The inner select directly corresponds to joinedload of >> PersonalContact.client, a >> PersonalClient instance (and a Client subclass). >> >> The postgres does a full scan of tables Client and PersonalClient even >> though I >> will need at most 100 rows from each one. >> >> However, if I rewrite the problematic part by hand like this: >> >>LEFT OUTER JOIN client >>ON personal_contact.client_id = client.id >> LEFT OUTER JOIN personal_client >>ON client.id = personal_client.id >> >> it works like a charm. >> >> Unfortunately I don't know how to write such a query in SA. I am really >> stuck so >> any help is much appreciated. >> >> >> Thank you, >> >> Ladislav Lenart >> >> >>
Re: [sqlalchemy] Re: SqlAlchemy use in Twisted threads
I'm glad you got a better solution. The key is now test it and see if memory leaks and connections get closed properly. The main problem in Twisted, IMHO, is the usage of "maybeDeferred" in some points, which can go to a thread or stay in the main thread, which is a little hard to predict and then lead to memory leaks. Another problem (that I get sometimes) is applications running on single-core servers. Things get mixed up :) If your application is all structured in thread calls, then the Pylons approach is almost the same, and it might work as expected. But, if you plan to use anything else from Twisted (conch, names, or 3rd parties, like Cyclone or txAmqp), first check if their calls are all deferreds to other threads :) Cyclone, for instance, doesn't create a thread for every request it receives; it's up to you to do thatน. Cheers, Richard. น I may be wrong about this, but at the time I used it, I had to defer all incoming requests to threads to get sessions closed properly. On 06/13/2013 10:30 AM, writes_on wrote: Hi all, Based on some comments here and in the SqlAlchemy IRC chat room, I've updated my decorator to make some changes. Here is the updated version: class ScopedSession(object): SESSIONMAKER = None # this is the single sessionmaker instance def __init__(self, engine, auto_commit=True): assert engine is not None, "Must pass a valid engine parameter" self._auto_commit = auto_commit if ScopedSession.SESSIONMAKER is None: ScopedSession.SESSIONMAKER = scoped_session(sessionmaker(expire_on_commit=True, bind=engine)) def __call__(self, func): @functools.wraps(func) def wrapper(*args, **kwargs): db_session = ScopedSession.SESSIONMAKER() try: results = func(db_session, *args, **kwargs) db_session.commit() # should we rollback for safety? if not self._auto_commit: db_session.rollback() except: db_session.rollback() raise finally: # release the session back to the connection pool db_session.close() return results return wrapper This version changes the code to a class based decorator so that I can create and use a single sessionmaker. It also calls the close() method of the session at the end of the decorator to release the session back to the connection pool. In response to using SqlAlchemy in a thread (in Twisted), I also write Pylons applications which use SqlAlchemy as the database backend. Since every request is a thread in Pylons, SqlAlchemy runs in those threads without a problem, so I'm not sure I see the problem running it in Twisted. The threads.deferToThread(...) call is nothing fancy in Twisted, it gets a thread from a thread pool, runs the passed function in that thread, and returns a deferred that fires in the main Twisted thread when the thread ends. This code looks like this: @defer.inlineCallbacks def update(data): # this function does some db work @ScopedSession(engine=engine) def process(session): # this method will be run in a thread and is passed a session by the decorator results = session.query() return results # call the internal process function in a thread try: results = yield threads.deferToThread(process) except Exception, e: # do something with exceptions defer.returnValue(results) Using @defer.inlineCallbacks makes calling asynchronous code easier to write in Twisted than using the traditional callback model (in my opinion). Defining the process() method internally gives it access to the parameters within the scope it's defined within and allows me to simplify it's parameters down to the session received from the @ScopedSession decorator. process() returns the SqlAlchemy results normally and is received into results when the yield returns from the thread (deferred fires). In the above example the session is commited and closed by the @ScopedSession decorator because I didn't override the default auto_commite=True parameter. Any comments, criticisms or suggestions are welcome, and again, thanks in advance! Doug -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to th
Re: [sqlalchemy] SA0.7.9 - poor query performance (full scan instead of index scan because of joined table inheritance)
Please try out 0.9 from the git master which fixes the issue of the nested SELECT on the right side of a join. Sent from my iPhone On Jun 13, 2013, at 9:18 AM, Ladislav Lenart wrote: > Hello. > > I have a query that does a full scan of an inherited table with more than > million rows even though I need only 100 of them (on postgres 9.1). This is a > real bummer! Please help me rewrite the SA query or instruct postgres to not > do > this stupidity. > > > I have the following setup (only the interesting relations): > >Contact >contact_tags -> ContactTag (collection) >phones -> Phone (collection) >emails -> Email (collection) > >ContactTag >tag -> Tag (cannot be NULL) > >PersonalContact (Contact subclass) >partner -> Partner (can be NULL) >client -> PersonalClient (can be NULL) > >CorporateContact (Contact subclass) >client -> CorporateClient (can be NULL) > >Client > >PersonalClient (Client subclass) >data -> PersonalData (cannot be NULL) > >CorporateClient (Client subclass) >data -> CorporateData (cannot be NULL) > > > I have the following query that loads data of one window: > ># window (input argument) is a list of id values. >q = session.query(PersonalContact).filter(PersonalContact.id.in_(window) >q = q.options( >subqueryload_all(PersonalContact.contact_tags, ContactTag.tag), >subqueryload(PersonalContact.phones), >subqueryload(PersonalContact.emails), >joinedload_all( >PersonalContact.partner, >Partner.personal_data, >PersonalData.address, > ), >joinedload_all( >PersonalContact.client, >PersonalClient.data, >PersonalData.address >), >) > > > (Note that I have similar query for CorporateContact. Infact, I generate them > both in the same method.) > > It produces SQL like this (the problematic part is emphasized): > >SELECT * >FROM >contact >JOIN personal_contact ON contact.id = personal_contact.id >-- *** >LEFT OUTER JOIN ( >SELECT * >FROM >client >JOIN personal_client ON client.id = personal_client.id >) AS anon_1 ON personal_contact.client_id = anon_1.personal_client_id >-- *** >LEFT OUTER JOIN partner AS partner_1 >ON personal_contact.partner_id = partner_1.id >LEFT OUTER JOIN personal_data AS personal_data_1 >ON partner_1.personal_data_id = personal_data_1.id >LEFT OUTER JOIN address AS address_1 >ON personal_data_1.address_id = address_1.id >LEFT OUTER JOIN personal_data AS personal_data_2 >ON anon_1.personal_client_data_id = personal_data_2.id >LEFT OUTER JOIN address AS address_2 >ON personal_data_2.address_id = address_2.id >WHERE personal_contact.id IN (...) > > > The inner select directly corresponds to joinedload of > PersonalContact.client, a > PersonalClient instance (and a Client subclass). > > The postgres does a full scan of tables Client and PersonalClient even though > I > will need at most 100 rows from each one. > > However, if I rewrite the problematic part by hand like this: > >LEFT OUTER JOIN client >ON personal_contact.client_id = client.id > LEFT OUTER JOIN personal_client >ON client.id = personal_client.id > > it works like a charm. > > Unfortunately I don't know how to write such a query in SA. I am really stuck > so > any help is much appreciated. > > > Thank you, > > Ladislav Lenart > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: SqlAlchemy use in Twisted threads
Hi all, Based on some comments here and in the SqlAlchemy IRC chat room, I've updated my decorator to make some changes. Here is the updated version: class ScopedSession(object): SESSIONMAKER = None # this is the single sessionmaker instance def __init__(self, engine, auto_commit=True): assert engine is not None, "Must pass a valid engine parameter" self._auto_commit = auto_commit if ScopedSession.SESSIONMAKER is None: ScopedSession.SESSIONMAKER = scoped_session(sessionmaker(expire_on_commit=True, bind=engine)) def __call__(self, func): @functools.wraps(func) def wrapper(*args, **kwargs): db_session = ScopedSession.SESSIONMAKER() try: results = func(db_session, *args, **kwargs) db_session.commit() # should we rollback for safety? if not self._auto_commit: db_session.rollback() except: db_session.rollback() raise finally: # release the session back to the connection pool db_session.close() return results return wrapper This version changes the code to a class based decorator so that I can create and use a single sessionmaker. It also calls the close() method of the session at the end of the decorator to release the session back to the connection pool. In response to using SqlAlchemy in a thread (in Twisted), I also write Pylons applications which use SqlAlchemy as the database backend. Since every request is a thread in Pylons, SqlAlchemy runs in those threads without a problem, so I'm not sure I see the problem running it in Twisted. The threads.deferToThread(...) call is nothing fancy in Twisted, it gets a thread from a thread pool, runs the passed function in that thread, and returns a deferred that fires in the main Twisted thread when the thread ends. This code looks like this: @defer.inlineCallbacks def update(data): # this function does some db work @ScopedSession(engine=engine) def process(session): # this method will be run in a thread and is passed a session by the decorator results = session.query() return results # call the internal process function in a thread try: results = yield threads.deferToThread(process) except Exception, e: # do something with exceptions defer.returnValue(results) Using @defer.inlineCallbacks makes calling asynchronous code easier to write in Twisted than using the traditional callback model (in my opinion). Defining the process() method internally gives it access to the parameters within the scope it's defined within and allows me to simplify it's parameters down to the session received from the @ScopedSession decorator. process() returns the SqlAlchemy results normally and is received into results when the yield returns from the thread (deferred fires). In the above example the session is commited and closed by the @ScopedSession decorator because I didn't override the default auto_commite=True parameter. Any comments, criticisms or suggestions are welcome, and again, thanks in advance! Doug -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] SA0.7.9 - poor query performance (full scan instead of index scan because of joined table inheritance)
Hello. I have a query that does a full scan of an inherited table with more than million rows even though I need only 100 of them (on postgres 9.1). This is a real bummer! Please help me rewrite the SA query or instruct postgres to not do this stupidity. I have the following setup (only the interesting relations): Contact contact_tags -> ContactTag (collection) phones -> Phone (collection) emails -> Email (collection) ContactTag tag -> Tag (cannot be NULL) PersonalContact (Contact subclass) partner -> Partner (can be NULL) client -> PersonalClient (can be NULL) CorporateContact (Contact subclass) client -> CorporateClient (can be NULL) Client PersonalClient (Client subclass) data -> PersonalData (cannot be NULL) CorporateClient (Client subclass) data -> CorporateData (cannot be NULL) I have the following query that loads data of one window: # window (input argument) is a list of id values. q = session.query(PersonalContact).filter(PersonalContact.id.in_(window) q = q.options( subqueryload_all(PersonalContact.contact_tags, ContactTag.tag), subqueryload(PersonalContact.phones), subqueryload(PersonalContact.emails), joinedload_all( PersonalContact.partner, Partner.personal_data, PersonalData.address, ), joinedload_all( PersonalContact.client, PersonalClient.data, PersonalData.address ), ) (Note that I have similar query for CorporateContact. Infact, I generate them both in the same method.) It produces SQL like this (the problematic part is emphasized): SELECT * FROM contact JOIN personal_contact ON contact.id = personal_contact.id -- *** LEFT OUTER JOIN ( SELECT * FROM client JOIN personal_client ON client.id = personal_client.id ) AS anon_1 ON personal_contact.client_id = anon_1.personal_client_id -- *** LEFT OUTER JOIN partner AS partner_1 ON personal_contact.partner_id = partner_1.id LEFT OUTER JOIN personal_data AS personal_data_1 ON partner_1.personal_data_id = personal_data_1.id LEFT OUTER JOIN address AS address_1 ON personal_data_1.address_id = address_1.id LEFT OUTER JOIN personal_data AS personal_data_2 ON anon_1.personal_client_data_id = personal_data_2.id LEFT OUTER JOIN address AS address_2 ON personal_data_2.address_id = address_2.id WHERE personal_contact.id IN (...) The inner select directly corresponds to joinedload of PersonalContact.client, a PersonalClient instance (and a Client subclass). The postgres does a full scan of tables Client and PersonalClient even though I will need at most 100 rows from each one. However, if I rewrite the problematic part by hand like this: LEFT OUTER JOIN client ON personal_contact.client_id = client.id LEFT OUTER JOIN personal_client ON client.id = personal_client.id it works like a charm. Unfortunately I don't know how to write such a query in SA. I am really stuck so any help is much appreciated. Thank you, Ladislav Lenart -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Related object not updating on flush, only on commit
Hey Michael, thanks for your fast response and especially the recipe. I completely forgot the FAQ. Most of my questions are usually covered by the documentation, and search engines seem to prefer StackOverflow et al. On Mi, 2013-06-12 at 16:15 -0400, Michael Bayer wrote: > This is the FAQ entry we've had in place for many years: > > http://www.sqlalchemy.org/trac/wiki/FAQ#Isetthefoo_idattributeonmyinstanceto7butthefooattributeisstillNone-shouldntithaveloadedFoowithid7 > > But also, I've added a new recipe which shows how to get this behavior, and > also makes apparent some of the difficulties in establishing it completely > (requires a many-to-one be in place, accessing collections on the "reverse" > side can be tricky): > > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpireRelationshipOnFKChange > The FAQ and recipe state that recommended use is to set the relationship property instead of settings its underlying column -- and this what I usually do. When I ran into this, I used the 'onupdate' column parameter with a python function to set a 'last modified by' attribute. Doing an 'onupdate' on the relation does not seem to be easily accessible through a parameter, although I'm certain it's possible hooking into the right event. For now, I'll go with a scoped version of your recipe. Thanks a lot! Robert -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 13.06.2013, 00:42 Uhr, schrieb Andy :
Ah, okay. I think I understand what I was missing initially. I've had
another go at this and it seems to work as required, even on MySQL. Not
sure how this all works with SQLAlchemy but I would expect it to be fine
with it.
insert into favourites (thing_id, group_id) values (2, 2)
Error Code: 1062. Duplicate entry '2' for key 'PRIMARY'0.012 sec
insert into favourites (thing_id, group_id) values (1, 3)
Error Code: 1452. Cannot add or update a child row: a foreign key
constraint fails ("favourites"."favourites", CONSTRAINT "checker"
FOREIGN
KEY ("thing_id", "group_id") REFERENCES "groups_things" ("thing_id",
"group_id"))0.007 sec
It's not, at least with automatically configured relations, hence this
thread.
What do you mean with automatically configured relations? Using SQLa to
define the schema? I always manage the schema directly so I don't know so
much about that. For me, the important thing is that SQLa can work with
the schema with the least number of contortions and I don't see any
required here.
> Also, SERIAL? You must be using a real database engine.
Well, er, yes. When it comes to modelling that's where you should start.
And Oracle is making progress with MySQL now that InnoDB with separate
files pro table and 5.6 even seems to have some kind of vacuuming
built-in.
I dunno. This may be the end of my (nonexistent) web developer career,
but
I think I will never again recommend using mysql for any purpose
whatsoever
(except perhaps compatibility). I've learned my lesson. Next time I'll
use PostgreSQL.
Well, yes, I'd always recommend Postgres over MySQL but I don't see what
the choice of backend has to do with this problem, except how well
reflection works with Postgres. From a developer's perspective MySQL's
biggest problem, apart from MyASM, is that its behaviour can be
unpredictable.
Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
