[sqlalchemy] Performance Difference
Are there performance difference between the following line of codes. a. for instance in Query: print instance.id b. for instance in Query.all(): print instance.id -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Query Object
Are there performance difference between the following line of codes. a. for instance in Query: -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Self Join
On Tue, Sep 15, 2009 at 5:53 AM, Michael Bayer wrote: > > Paulo Aquino wrote: > > I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid > > products, a product is valid if it has both a valid 'Selling' and > 'Buying' > > ProductPrice type. A ProductPrice is valid if the valid_from date <= > > date.today() and valid_to >= date.today(). > > > > Product Table: > > > > id | sku | principal > > +---+--- > > 1 | sku_1 | kraft > > 2 | sku_2 | kraft > > 3 | sku_3 | kraft > > 4 | sku_4 | kraft > > > > ProdutPrice Table: > > > > id | type| sku| principal | price | valid_from | valid_to > > > +---+-+-++-+ > > 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 > > 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 > > 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 > > 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 > > 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 > > 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 > > 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 > > 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 > > > > Using Raw SQL here is how I did it: > > > > 1. SELECT product.id, > > type, > > product.sku, > > product.principal, > > price, > > valid_from, > > valid_to INTO TEMP > > FROM product > > INNER JOIN product_price on > > product.principal = product_price.principal AND > > product.sku = product_price.sku > > WHERE valid_from <= current_date AND valid_to >= current_date ; > > > > 2. SELECT DISTINCT * from TEMP a , TEMP b > > WHERE a.type='Selling' AND b.type='Buying' > > AND a.principal = b.principal > > AND a.sku = b.sku; > > > > From this two queries I now have distinct products that have a valid pair > > of > > both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' > > or > > 'Selling price only and not have them both are dropped) > > > > > > Using SQLAlchemy here is how I did it: > > > > 1. valid_price = and_(ProductPrice.sku == Product.sku, > > ProductPrice.principal==Product.principal, > >ProductPrice.valid_from <= date.today(), > >ProductPrice.valid_to >= date.today()) > > > >valid_products = > > session.query(Product).join(ProductPrice).filter(valid_price) > > > > 2. Now I want to self join valid_products, same thing I did in my Raw SQL > > solution no. 2 I've been trying but getting weird results. > > > > > > If someone can please help me, here's my test case > > http://pastebin.com/m3f8a95c8 > > you can say: > > valid_products = session.query(Product, > ProductPrice.type).join(ProductPrice).filter(valid_price) > > a = valid_products.subquery() > b = valid_products.subquery() > > PA = aliased(Product, a) > PB = aliased(Product, b) > > q = session.query(PA, PB).\ >distinct().\ >filter(a.c.type=='Selling').\ >filter(b.c.type=='Buying').\ >filter(a.c.principal==b.c.principal).\ >filter(a.c.sku==b.c.sku) > This works perfectly fine, but count() is wrong, seems to return a count which is more than 1 from what is expected. > print q.all() > > if you just want the columns back you can do away with PA and PB and just > query(a, b). > > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Self Join
On Tue, Sep 15, 2009 at 5:53 AM, Michael Bayer wrote: > > Paulo Aquino wrote: > > I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid > > products, a product is valid if it has both a valid 'Selling' and > 'Buying' > > ProductPrice type. A ProductPrice is valid if the valid_from date <= > > date.today() and valid_to >= date.today(). > > > > Product Table: > > > > id | sku | principal > > +---+--- > > 1 | sku_1 | kraft > > 2 | sku_2 | kraft > > 3 | sku_3 | kraft > > 4 | sku_4 | kraft > > > > ProdutPrice Table: > > > > id | type| sku| principal | price | valid_from | valid_to > > > +---+-+-++-+ > > 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 > > 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 > > 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 > > 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 > > 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 > > 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 > > 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 > > 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 > > > > Using Raw SQL here is how I did it: > > > > 1. SELECT product.id, > > type, > > product.sku, > > product.principal, > > price, > > valid_from, > > valid_to INTO TEMP > > FROM product > > INNER JOIN product_price on > > product.principal = product_price.principal AND > > product.sku = product_price.sku > > WHERE valid_from <= current_date AND valid_to >= current_date ; > > > > 2. SELECT DISTINCT * from TEMP a , TEMP b > > WHERE a.type='Selling' AND b.type='Buying' > > AND a.principal = b.principal > > AND a.sku = b.sku; > > > > From this two queries I now have distinct products that have a valid pair > > of > > both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' > > or > > 'Selling price only and not have them both are dropped) > > > > > > Using SQLAlchemy here is how I did it: > > > > 1. valid_price = and_(ProductPrice.sku == Product.sku, > > ProductPrice.principal==Product.principal, > >ProductPrice.valid_from <= date.today(), > >ProductPrice.valid_to >= date.today()) > > > >valid_products = > > session.query(Product).join(ProductPrice).filter(valid_price) > > > > 2. Now I want to self join valid_products, same thing I did in my Raw SQL > > solution no. 2 I've been trying but getting weird results. > > > > > > If someone can please help me, here's my test case > > http://pastebin.com/m3f8a95c8 > > you can say: > > valid_products = session.query(Product, > ProductPrice.type).join(ProductPrice).filter(valid_price) > > a = valid_products.subquery() > b = valid_products.subquery() > > PA = aliased(Product, a) > PB = aliased(Product, b) > > q = session.query(PA, PB).\ >distinct().\ >filter(a.c.type=='Selling').\ >filter(b.c.type=='Buying').\ >filter(a.c.principal==b.c.principal).\ > filter(a.c.sku==b.c.sku) > > > print q.all() > > if you just want the columns back you can do away with PA and PB and just > query(a, b). > > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Self Join
I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid products, a product is valid if it has both a valid 'Selling' and 'Buying' ProductPrice type. A ProductPrice is valid if the valid_from date <= date.today() and valid_to >= date.today(). Product Table: id | sku | principal +---+--- 1 | sku_1 | kraft 2 | sku_2 | kraft 3 | sku_3 | kraft 4 | sku_4 | kraft ProdutPrice Table: id | type| sku| principal | price | valid_from | valid_to +---+-+-++-+ 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 Using Raw SQL here is how I did it: 1. SELECT product.id, type, product.sku, product.principal, price, valid_from, valid_to INTO TEMP FROM product INNER JOIN product_price on product.principal = product_price.principal AND product.sku = product_price.sku WHERE valid_from <= current_date AND valid_to >= current_date ; 2. SELECT DISTINCT * from TEMP a , TEMP b WHERE a.type='Selling' AND b.type='Buying' AND a.principal = b.principal AND a.sku = b.sku; >From this two queries I now have distinct products that have a valid pair of both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' or 'Selling price only and not have them both are dropped) Using SQLAlchemy here is how I did it: 1. valid_price = and_(ProductPrice.sku == Product.sku, ProductPrice.principal==Product.principal, ProductPrice.valid_from <= date.today(), ProductPrice.valid_to >= date.today()) valid_products = session.query(Product).join(ProductPrice).filter(valid_price) 2. Now I want to self join valid_products, same thing I did in my Raw SQL solution no. 2 I've been trying but getting weird results. If someone can please help me, here's my test case http://pastebin.com/m3f8a95c8 rgds, Paulo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Self Join
I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid products, a product is valid if it has both a valid 'Selling' and 'Buying' ProductPrice type. A ProductPrice is valid if the valid_from date <= date.today() and valid_to >= date.today(). Product Table: id | sku | principal +---+--- 1 | sku_1 | kraft 2 | sku_2 | kraft 3 | sku_3 | kraft 4 | sku_4 | kraft ProdutPrice Table: id | type| sku| principal | price | valid_from | valid_to +---+-+-++-+ 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 Using Raw SQL here is how I did it: 1. SELECT product.id, type, product.sku, product.principal, price, valid_from, valid_to INTO TEMP FROM product INNER JOIN product_price on product.principal = product_price.principal AND product.sku = product_price.sku WHERE valid_from <= current_date AND valid_to >= current_date ; 2. SELECT DISTINCT * from TEMP a , TEMP b WHERE a.type='Selling' AND b.type='Buying' AND a.principal = b.principal AND a.sku = b.sku; >From this two queries I now have distinct products that have a valid pair of both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' or 'Selling price only and not have them both are dropped) Using SQLAlchemy here is how I did it: 1. valid_price = and_(ProductPrice.sku == Product.sku, ProductPrice.principal==Product.principal, ProductPrice.valid_from <= date.today(), ProductPrice.valid_to >= date.today()) valid_products = session.query(Product).join(ProductPrice).filter(valid_price) 2. Now I want to self join valid_products, same thing I did in my Raw SQL solution no. 2 I've been trying but getting weird results. If someone can please help me, here's is my test case http://pastebin.com/m3f8a95c8 rgds, Paulo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] @propert
I add a python property to my class, can i make this query able? Anyone knows of a good sample on how to do this, I would really appreciate. rgds, Paulo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: http://www.sqlalchemy.org/docs/05/ormtutorial.html
I guess it's ipython cause when I tried saving the tutorial source code in a file and run python .py everything is working fine. Yeah you're right seems ridiculous. :) On Thu, Sep 3, 2009 at 11:10 PM, Mike Conley wrote: > Any chance this is the second iteration of declaring the User class in this > session, and the first time was missing the primary_key? > > I run into this in interactive sessions and need to call clear_mappers() > before redoing the class. It seems that the old mapper is still hanging > around and causes the compilation error. > > -- > Mike Conley > > > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
