How to save and find a list in SQLAlchemy?

Question

I need to write two classes:

class Item(Base, DBBase): __tablename__ = 'items' id = Column(Integer, primary_key = True) name = Column(String) description = Column(String) price = Column(Float, default = 0) on_sell = Column(Boolean, default = False) img = Column(String) attributes = relationship('ItemAttribute') def __init__(self, name, description): self.name = name self.description = description class ItemAttribute(Base, DBBase): __tablename__ = 'itemattributes' id = Column(Integer, primary_key = True) name = Column(String, nullable = False) value = Column(String, nullable = False) item_id = Column(Integer, ForeignKey('items.id')) item = relationship('Item') def __init__(self, name, value): self.name = name self.value = value 

A single element can have several attributes, and I need: 1. Insert some methods into the Item class to easily execute the CURD attributes (insert, delete, update, and query). I need to find the attribute of an element and return its corresponding value. 2. Have the ability to search for items by attributes. For example, some items have attributes "Feature" = "True." I need to get all the elements that have this attribute.

Thanks for the help.: -)

Answer 1

If you added backref to your ItemAttribute relation:

 item_id = Column(Integer, ForeignKey('items.id', onupdate='CASCADE', ondelete='CASCADE')) item = relationship(Items, backref='attributes') 

This will create an array and an Item.attributes [] element that contains the ItemAttribute. You can also add onupdate and ondelete if you are using mysql.

Then, upon request, you can do this:

 rs = mySession.query(Items) firstItem = rs.first() for attribute in firstItem.attributes: print attribute 

When prompted, you can filter by attaching backref:

 rs = mySession.query(Items).join(Items.attributes).filter(ItemAttribute.name=='somethingSpecial') 

In addition, if this relationship is one to one (but it is not), you can skip the list by specifying uselist = False:

 item = relationship(ITEM, backref='attribute', uselist=False)