CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
jakubmatyszewski
jakubmatyszewski

Reputation: 69

Struggling to come up with SQLAlchemy model relation

SQL newbie here, bear with me.

I am trying to store data with headlines from different news sites. Trying to figure out proper DB structure and SQLAlchemy models, but for now it seems like I have trouble grasping how to create proper relationship.

For now I've ended up with following models.py file:

class Headline(Base):
    __tablename__ = 'headlines'

    id = Column(Integer, primary_key=True, index=True)
    headline = Column(DateTime)
    time_stamp = Column(String)


class NewYorkTimes(Base):
    __tablename__ = 'newyorktimes'
    id = Column(Integer, primary_key=True)
    headline_id = Column(ForeignKey(Headline.id))

    headlines = relationship(
        'Headline',
        primaryjoin="and_(NewYorkTimes.headline_id==Headline.id)"
    )


class Times(Base):
    __tablename__ = 'times'
    id = Column(Integer, primary_key=True)
    headline_id = Column(ForeignKey(Headline.id))

    headlines = relationship(
        'Headline',
        primaryjoin="and_(Times.headline_id==Headline.id)"
    )

I am really not sure whether this kind of relationship is even correct... I'd wish to store all the headlines in headlines table, with timestamp and text content and while appending this table with new headline, I'd like to specify what site it was gathered from, so THIS headline id is added to site-specific table.

I used to store data in site-specific tables, but I believe this is just plain wrong solution - there was for example table Times with columns id, headline, time_stamp and tables for other sites had exactly same structure, but creating SQLAlchemy models for this denies probably everything what DRY is about.

Upvotes: 0

Views: 55

Answers (1)

desired login
desired login

Reputation: 1190

I think you could try a different approach, where you have one table (Publications, say) for all of your news sources, rather than a separate table for each source. In this approach, Times, NewYorkTimes and whatever else you have will all be entries in the Publications table.

Also, you seem to have mixed up the column type for your headline and time_stamp columns above.

 class Headline(Base):
    __tablename__ = 'headlines'

    id = Column(Integer, primary_key=True, index=True)
    headline = Column(String)
    time_stamp = Column(Datetime)
    publication_id = Column(Integer, ForeignKey('publications.id'))

class Publication(Base):
    __tablename__ = 'publications'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    headlines = relationship('Headline', backref="publication")

Using backref on the headlines relationship will automatically put a relationship on the Headline table called publication. See sqlalchemy docs on one to many relationships and backref.

Upvotes: 1

Related Questions