Need help with joins in sqlalchemy
- by Steve
I'm new to Python, as well as SQL Alchemy, but not the underlying development and database concepts. I know what I want to do and how I'd do it manually, but I'm trying to learn how an ORM works.
I have two tables, Images and Keywords. The Images table contains an id column that is its primary key, as well as some other metadata. The Keywords table contains only an id column (foreign key to Images) and a keyword column. I'm trying to properly declare this relationship using the declarative syntax, which I think I've done correctly.
Base = declarative_base()
class Keyword(Base):
__tablename__ = 'Keywords'
__table_args__ = {'mysql_engine' : 'InnoDB'}
id = Column(Integer, ForeignKey('Images.id', ondelete='CASCADE'),
primary_key=True)
keyword = Column(String(32), primary_key=True)
class Image(Base):
__tablename__ = 'Images'
__table_args__ = {'mysql_engine' : 'InnoDB'}
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(256), nullable=False)
keywords = relationship(Keyword, backref='image')
This represents a many-to-many relationship. One image can have many keywords, and one keyword can relate back to many images.
I want to do a keyword search of my images. I've tried the following with no luck.
Conceptually this would've been nice, but I understand why it doesn't work.
image = session.query(Image).filter(Image.keywords.contains('boy'))
I keep getting errors about no foreign key relationship, which seems clearly defined to me. I saw something about making sure I get the right 'join', and I'm using 'from sqlalchemy.orm import join', but still no luck.
image = session.query(Image).select_from(join(Image, Keyword)).\
filter(Keyword.keyword == 'boy')
I added the specific join clause to the query to help it along, though as I understand it, I shouldn't have to do this.
image = session.query(Image).select_from(join(Image, Keyword,
Image.id==Keyword.id)).filter(Keyword.keyword == 'boy')
So finally I switched tactics and tried querying the keywords and then using the backreference. However, when I try to use the '.images' iterating over the result, I get an error that the 'image' property doesn't exist, even though I did declare it as a backref.
result = session.query(Keyword).filter(Keyword.keyword == 'boy').all()
I want to be able to query a unique set of image matches on a set of keywords. I just can't guess my way to the syntax, and I've spent days reading the SQL Alchemy documentation trying to piece this out myself.
I would very much appreciate anyone who can point out what I'm missing.
