All geek questions in one place

SQLAlchemy one-to-many without a child table that has a primary key

Is it possible to create a table without a primary key in SQLAlchemy? The relationship I want to define is as follows:

class TPost(Base): __tablename__ = "forum_post" id = Column(Integer, primary_key = True) topic_id = Column(Integer, ForeignKey("forum_topic.id")) index = Column(Integer) page = Column(Integer) user_id = Column(Integer, ForeignKey("forum_user.id")) posted_at = Column(DateTime) post_text = Column(String) has_quotes = Column(Boolean) quotes = relationship("TQuote") class TQuote(Base): __tablename__ = "forum_quotes" id = Column(Integer, ForeignKey("forum_post.id")) is_direct = Column(Boolean) quoted_text = Column(String) quoted_id = Column(Integer)

As you can see, I really do not need a primary key, and I do not intend to extend the Quote relationship in the future.

My problem is specifically represented by this error message:

 sqlalchemy.exc.ArgumentError: Mapper Mapper|TQuote|forum_quotes could not assemble any primary key columns for mapped table 'forum_quotes'

edit: The pair (id,quoted_id) unique and is present for most data, however, when the quote is not direct (and in this case does not have quoted_id), I insert the quoted text directly into the quotation relation. I could use a two-table approach (where the correct quotes have a table with a primary key), but I would prefer to implement this as a one-to-many relationship. I do not want to do more than one connection.

change 2:

I will quote the quotes and use the number generated with the foreign key +, like pkey, still annoying tho. Now to figure out the syntax.

change 3:

The problem stated in edition 2 has been resolved. Sql alchemy is quite annoying, since it has all the information necessary to implement relatioship, even when modeling data at a high level. I understand the reasons why Sql Alchemy wants to have a primary key (simplifies the implementation of orm).

I am starting to doubt why I use Sql Alchemy, without it I could implement one-way asynchronous UPSERT or CREATE_IF_NOT_EXIST operations using psycopg2. ORM really needs to catch up.

+6
source share
2 answers

Add an extra column to quote the index, and then add the composite key of this new column + foreign key.

+1
source

I assume @TokenMacGuy is right, and you really mix the concepts of PrimaryKey and a surrogate key . In this case, the answer to your question:

  • NO , SA does not support tables (and therefore relations to tables) without a primary key
  • and NO , you do not need to create a surrogate key for each table to serve the primary key . You can define PK using any combination of columns with unique ones.

See the code below:

 class TPost(Base): __tablename__ = 'forum_post' id = Column(Integer, primary_key = True) post_text = Column(String) quotes = relationship("TQuote", backref="post") class TQuote(Base): __tablename__ = "forum_quotes" id = Column(Integer, ForeignKey("forum_post.id")) is_direct = Column(Boolean) quoted_text = Column(String) quoted_id = Column(Integer) __table_args__ = (PrimaryKeyConstraint(id, quoted_id),)
+13
source

Source: https://habr.com/ru/post/919172/

More articles:


All Articles