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
self.shipped = shipped
class Itemsbought(Base):
__tablename__ = 'itemsbought'
id = Column(Integer, primary_key=True)
orderid = Column(Integer, ForeignKey('customerorders.id'))
order = relationship('Customerorder', backref=backref("itemsbought"))
productid = Column(Integer, default = 0)
def __init__(self, productid, orderid):
self.productid = productid
self.orderid = orderid
I try to query only Customerorder and Itemsbought without having those
relations to products:
customerorders = sqlsession.query(Customerorder)\
.join(Itemsbought.order)\
.all()
or with joinedload, outerjoins all the same thing. log shows me always
sqlalchemy makes left outer joins.
Trying to use this data in loop ends up in 1700 queries:
for order in customerorders:
print order.name,
for item in order.itemsbought:
print item.productid
I managed to get all this correct data with sizes and products in 2,5 s
with joinedload but this is a way too long for 5 k entries in itemsbought
and 1680 orders.
Can someone point to what I am doin wrong? And whats the best way to get
all orders in an clearly arranged way with products, price, sizes etc.
Cheers
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
