[sqlalchemy] Deferring a many-to-many mapping
Hi all, I am having trouble deferring a many-to-many mapping. I just had a couple of questions related there-to: (1) The most visible implementations of vanilla many-to-many mappings seem to use sqlalchemy.Table(...). Is that, indeed, the accepted practice? One alternative would be to use a mapped class, but that seems less than parsimonious. (2) There don't appear to be any particularly visible examples of deferred many-to-many mappings. The best I was able to come up with was along the lines of class DeferredManyToMany(object): @declared_attr def __table__(cls): return Table(cls.__tablename__, cls.metadata, ) Is this also accepted practice? (3) Upon execution, the __table__(...) method gets called twice and throws an InvalidRequestError saying the table is already defined. Giving extend_existing=True in the Table constructor fixes things. Can somebody provide some background on that? (4) Now I'm trying to add a PrimaryKeyConstraint and things are going to crap again. See http://pastebin.com/1tQEWh71 . Is there some additional magic that I'm missing? I'm on sqlalchemy 0.9.7. Thanks! ~br -- 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/d/optout.
Re: [sqlalchemy] SQLAlchemy connection errors
On Sep 12, 2014, at 12:26 PM, Matthijs Blaas wrote: > > set the search path within the connection pool using the "connect" event; > > all connections used by the pool will then have this search path setting > > the moment they connect. Don't ever put multiple SQL expressions in a DBAPI > > execute (for this very reason - driver changes, hilarity ensues. We are > > having a crisis in Openstack right now because they made the same mistake). > Thanks, i'll update our code to use the NullPool's connect event. > > > no part of your code should be changing search_path - I'd grep for it. > > I did and made sure there is only one place where it's set. But i have good > hopes the connect event takes care of it, i haven't been able to reproduce > the error anymore. > > I'm curious though, i was under the impression that when you start every > request in a transaction (pyramid_tm in this case), and set the search_path > on every transaction (using the sessions after_begin event), is it even > possible that a new connection is triggered somewhere that isn't included in > the main transaction? SQLAlchemy never does that but I don't know what you'd be doing in your code, it's certainly a pattern that is supported and possible, just wouldn't happen implicitly. -- 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/d/optout.
Re: [sqlalchemy] simple relationship join ends up in 1 s
I think you need to profile the application like Mike said. Just to be thorough, I'd suggest you time the Query execution and the ORM population. Do you have an example of the faster raw SQL that you're trying to generate ? When you do a query on the commandline it is often faster because the database is only displaying the initial data on the screen. When you're using something like SqlAlchemy, the app needs to get all the data and then create objects. This is usually lightweight, but if you have some columns with a lot of data, there will be a hit. When you profile, you'll have a better idea if the query, the loader, of the connection is to blame. -- 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/d/optout.
Re: [sqlalchemy] SQLAlchemy connection errors
> set the search path within the connection pool using the “connect” event; all connections used by the pool will then have this search path setting the moment they connect. Don’t ever put multiple SQL expressions in a DBAPI execute (for this very reason - driver changes, hilarity ensues. We are having a crisis in Openstack right now because they made the same mistake). Thanks, i'll update our code to use the NullPool's connect event. > no part of your code should be changing search_path - I’d grep for it. I did and made sure there is only one place where it's set. But i have good hopes the connect event takes care of it, i haven't been able to reproduce the error anymore. I'm curious though, i was under the impression that when you start every request in a transaction (pyramid_tm in this case), and set the search_path on every transaction (using the sessions after_begin event), is it even possible that a new connection is triggered somewhere that isn't included in the main transaction? Thanks! Matthijs -- 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/d/optout.
Re: [sqlalchemy] SQLAlchemy connection errors
i have no input on the issues michael identified. but i'm curious about a sqlalchemy event somehow not firing. which transaction event were you using? after_begin or after_transaction_create ? -- 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/d/optout.
Re: [sqlalchemy] SQLAlchemy connection errors
On Sep 12, 2014, at 6:45 AM, Matthijs Blaas wrote: > Ok, i found the issue! > > When I tried to use pg8000 to connect to postgres i got an error: "cannot > insert multiple commands into a prepared statement". > Apparently multiple queries is fine with psycopg2, but not using pg8000. > > We prefix all queries because of a workaround. We handle multi-tenancy using > the postgres search_path, but setting the search_path once on an new > transaction event resulted in another issue; the search_path is sometimes > "lost" during requests! I can't see why exactly, but it appears sometimes > during a request another connection is used which doesn't fire a new > transaction event.. To workaround this issue, we prefix every query fired > with a "set search_path to ; " in an > before_cursor_execute callback. set the search path within the connection pool using the "connect" event; all connections used by the pool will then have this search path setting the moment they connect. Don't ever put multiple SQL expressions in a DBAPI execute (for this very reason - driver changes, hilarity ensues. We are having a crisis in Openstack right now because they made the same mistake). > > But this still leaves the strange issue where the search_path is sometimes > suddenly lost during requests.. no part of your code should be changing search_path - I'd grep for it. -- 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/d/optout.
Re: [sqlalchemy] simple relationship join ends up in 1 s
OK, keep going with the profiling, whats the total number of rows you are
seeing with the joined load, 5000 total ? sometimes a join is actually
scrolling through many more rows than that, duplicate rows in the case of a
cartesian product.echo='debug' would show that. Or there could be very
large textual fields in rows that are being fetched repeatedly (in which case
subqueryload() might be better). There's a lot of reasons this could happen.
On Sep 12, 2014, at 3:16 AM, tatütata Okay wrote:
> Thats what I did before, activating echo=True shows me correct query too.
> This joinedload takes 1.3 seconds for this 5000 entries.
> And only 4 queries in general for complete side load. Adding one more
> joinedload ends up in 2 seconds, thats way too much for 1700 customers and 5
> k bought items
>
> On Thu, Sep 11, 2014 at 10:12 PM, Simon King wrote:
> The query that you are doing:
>
>customerorders = sqlsession.query(Customerorder)\
> .join(Itemsbought.order)\
> .all()
>
> probably doesn't do what you intend. In particular, it doesn't populate the
> Customerorder.itemsbought collection. So when you iterate over customerorders
> and access the "itemsbought" property, it issues a new query for each order.
>
> Try starting with something like this:
>
>customerorders = (sqlsession.query(Customerorder)
> .options(saorm.joinedload("itemsbought"))
> .all())
>
> Hope that helps,
>
> Simon
>
> On 11 Sep 2014, at 20:54, Michael Bayer wrote:
>
> > then it sounds like you have lazy loading happening, check echo=True
> >
> >
> > On Sep 11, 2014, at 3:12 PM, tatütata Okay
> > wrote:
> >
> >> Okay will try it. Maybe you got it wrong. I have only 5 k rows
> >>
> >> On Thu, Sep 11, 2014 at 8:55 PM, Michael Bayer
> >> wrote:
> >> three joins over 100K rows is going to be very slow, two seconds seems in
> >> the ballpark. EXPLAIN will show if there are any table scans taking place.
> >>
> >> as far as why the relationship is faster, it might be doing something
> >> different, check the echo=True output.
> >>
> >> On Sep 11, 2014, at 2:35 PM, tatütata Okay
> >> wrote:
> >>
> >>> Yeah that would have been my next steps. I hoped more for some hints if
> >>> the setup is correct more if there are issues with the relationship
> >>> configuration? I think speed related things come up when there are over
> >>> 10 entries and more? We speak here about 5000, thats why I think I
> >>> did a mistake on the configuration of the relation patterns?
> >>>
> >>>
> >>> On Thu, Sep 11, 2014 at 8:12 PM, Michael Bayer
> >>> wrote:
> >>> for some tips on isolating where the speed issue is, follow the steps at:
> >>> http://docs.sqlalchemy.org/en/latest/faq.html#how-can-i-profile-a-sqlalchemy-powered-application
> >>>
> >>> for too many joins, you also want to look into EXPLAIN PLAN.
> >>>
> >>>
> >>>
> >>> On Sep 11, 2014, at 12:57 PM, tatütata Okay
> >>> wrote:
> >>>
> Hey,
> I got some shop tables like:
> customerorders, itemsbought, products whole query with three joins takes
> nothing on command line sql. When I trying to achieve same thing with
> ORM and my relationship it takes 1 second, joining all three 3 tables 2
> seconds?
>
> May I come first up with my table and mapper definitions:
>
> class Products(Base):
> __tablename__ = 'products'
> id = Column(Integer, primary_key=True)
> productname = Column(Unicode(255))
> productname_en =Column(Unicode(255), default = u'nice product')
> path = Column(Unicode(300))
> pic = Column(Unicode(300), unique=True)
> bigpic = Column(Unicode(300), unique=True)
> sold = Column(Boolean)
> price = Column(Integer)
> locked = Column(Boolean)
> sort = Column(Integer)
> catid = Column(Integer)
> sizeid = Column(Integer, ForeignKey('productsizes.sizeid'))
> productsizes = relationship("ProductSizes", backref="products",
> lazy='joined')
> itemsbought = relationship("Itemsbought", backref="products")
>
> def __init__(self, productname, path, pic, bigpic, sold, price,
> sizeid, locked, sort, catid):
> self.productname = productname
> self.path = path
> self.pic = pic
> self.bigpic = bigpic
> self.sold = sold
> self.price = price
> self.sizeid = sizeid
> self.locked = locked
> self.sort = sort
> self.catid = catid
>
> class Customerorder(Base):
> __tablename__ = 'customerorders'
> id = Column(Integer, primary_key=True)
> ip = Column(Unicode(40))
> comment = Column(Unicode(1000))
> plz = Column(Unicode(30))
> name = Column(Unicode(100))
> street = Column(Unicode(200))
> ort = Column(Un
[sqlalchemy] Re: SQLAlchemy connection errors
Ok, i found the issue! When I tried to use pg8000 to connect to postgres i got an error: "cannot insert multiple commands into a prepared statement". Apparently multiple queries is fine with psycopg2, but not using pg8000. We prefix all queries because of a workaround. We handle multi-tenancy using the postgres search_path, but setting the search_path once on an new transaction event resulted in another issue; the search_path is sometimes "lost" during requests! I can't see why exactly, but it appears sometimes during a request another connection is used which doesn't fire a new transaction event.. To workaround this issue, we prefix every query fired with a "set search_path to ; " in an before_cursor_execute callback. So if i leave out this search_path prefix query, and monkey patch_all (when using gevent) everything runs without trouble. But this still leaves the strange issue where the search_path is sometimes suddenly lost during requests.. feeding strategy="threadlocal" might seem relevant here (as it should bind 1 connection to each thread handling a request)? Furthermore i need this behaviour because in some requests i also rely on a for_update lock during the request.. -- 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/d/optout.
Re: [sqlalchemy] simple relationship join ends up in 1 s
Thats what I did before, activating echo=True shows me correct query too.
This joinedload takes 1.3 seconds for this 5000 entries.
And only 4 queries in general for complete side load. Adding one more
joinedload ends up in 2 seconds, thats way too much for 1700 customers and
5 k bought items
On Thu, Sep 11, 2014 at 10:12 PM, Simon King wrote:
> The query that you are doing:
>
>customerorders = sqlsession.query(Customerorder)\
> .join(Itemsbought.order)\
> .all()
>
> probably doesn't do what you intend. In particular, it doesn't populate
> the Customerorder.itemsbought collection. So when you iterate over
> customerorders and access the "itemsbought" property, it issues a new query
> for each order.
>
> Try starting with something like this:
>
>customerorders = (sqlsession.query(Customerorder)
> .options(saorm.joinedload("itemsbought"))
> .all())
>
> Hope that helps,
>
> Simon
>
> On 11 Sep 2014, at 20:54, Michael Bayer wrote:
>
> > then it sounds like you have lazy loading happening, check echo=True
> >
> >
> > On Sep 11, 2014, at 3:12 PM, tatütata Okay
> wrote:
> >
> >> Okay will try it. Maybe you got it wrong. I have only 5 k rows
> >>
> >> On Thu, Sep 11, 2014 at 8:55 PM, Michael Bayer <
> mike...@zzzcomputing.com> wrote:
> >> three joins over 100K rows is going to be very slow, two seconds seems
> in the ballpark. EXPLAIN will show if there are any table scans taking
> place.
> >>
> >> as far as why the relationship is faster, it might be doing something
> different, check the echo=True output.
> >>
> >> On Sep 11, 2014, at 2:35 PM, tatütata Okay
> wrote:
> >>
> >>> Yeah that would have been my next steps. I hoped more for some hints
> if the setup is correct more if there are issues with the relationship
> configuration? I think speed related things come up when there are over
> 10 entries and more? We speak here about 5000, thats why I think I did
> a mistake on the configuration of the relation patterns?
> >>>
> >>>
> >>> On Thu, Sep 11, 2014 at 8:12 PM, Michael Bayer <
> mike...@zzzcomputing.com> wrote:
> >>> for some tips on isolating where the speed issue is, follow the steps
> at:
> http://docs.sqlalchemy.org/en/latest/faq.html#how-can-i-profile-a-sqlalchemy-powered-application
> >>>
> >>> for too many joins, you also want to look into EXPLAIN PLAN.
> >>>
> >>>
> >>>
> >>> On Sep 11, 2014, at 12:57 PM, tatütata Okay <
> rogersher...@googlemail.com> wrote:
> >>>
> Hey,
> I got some shop tables like:
> customerorders, itemsbought, products whole query with three joins
> takes nothing on command line sql. When I trying to achieve same thing with
> ORM and my relationship it takes 1 second, joining all three 3 tables 2
> seconds?
>
> May I come first up with my table and mapper definitions:
>
> class Products(Base):
> __tablename__ = 'products'
> id = Column(Integer, primary_key=True)
> productname = Column(Unicode(255))
> productname_en =Column(Unicode(255), default = u'nice product')
> path = Column(Unicode(300))
> pic = Column(Unicode(300), unique=True)
> bigpic = Column(Unicode(300), unique=True)
> sold = Column(Boolean)
> price = Column(Integer)
> locked = Column(Boolean)
> sort = Column(Integer)
> catid = Column(Integer)
> sizeid = Column(Integer, ForeignKey('productsizes.sizeid'))
> productsizes = relationship("ProductSizes", backref="products",
> lazy='joined')
> itemsbought = relationship("Itemsbought", backref="products")
>
> def __init__(self, productname, path, pic, bigpic, sold, price,
> sizeid, locked, sort, catid):
> self.productname = productname
> self.path = path
> self.pic = pic
> self.bigpic = bigpic
> self.sold = sold
> self.price = price
> self.sizeid = sizeid
> self.locked = locked
> self.sort = sort
> self.catid = catid
>
> class Customerorder(Base):
> __tablename__ = 'customerorders'
> id = Column(Integer, primary_key=True)
> ip = Column(Unicode(40))
> comment = Column(Unicode(1000))
> plz = Column(Unicode(30))
> name = Column(Unicode(100))
> street = Column(Unicode(200))
> ort = Column(Unicode(100))
> date = Column(DateTime, default = func.now())
> money_received = Column(Boolean)
> shipped = Column(Boolean)
>
> def __init__(self, comment, ip, street, plz, ort, name,
> money_received):
> self.ip = ip
> self.customerid = customerid
> self.comment = comment
> self.street = street
> self.plz = plz
> self.ort = ort
> self.name = name
> self.money_received = money_received
> >>>
