如何使用SQLAlchemy插入复合主键？

Question

当我尝试插入复合主键连接时，返回0行：

CompetitionRound = sa.Table('CompetitionRound', metadata,
                            sa.Column('CompetitionId', sa.Integer, sa.ForeignKey('Competitions.Id'), primary_key=True),
                            sa.Column('RoundId', sa.Integer, sa.ForeignKey('Rounds.Id'), primary_key=True))
...
competition_round_insert = await conn.execute(
    CompetitionRound.insert()
                    .values(CompetitionId=competition_id,
                            RoundId=round_id))
competition_round_row = await competition_round_insert.fetchone()

似乎无法将复合主键作为值插入。。。但在这种情况下如何插入密钥？ 不幸的是，我在SQLAlchemy文档中没有找到一些示例。。。（

Answer 1

根据我对文档的理解，SQLAlchemy仅隐式返回autoincrement主键，而组合PK具体不是：

autoincrement

The autoincrement flag now defaults to "auto" which indicates autoincrement semantics by default for single-column integer primary keys only; for composite (multi-column) primary keys, autoincrement is never implicitly enabled; as always, autoincrement=True will allow for at most one of those columns to be an “autoincrement” column. autoincrement=True may also be set on a Column that has an explicit client-side or server-side default, subject to limitations of the backend database and dialect.

inserted_primary_key

Note that primary key columns which specify a server_default clause, or otherwise do not qualify as “autoincrement” columns (see the notes at Column), and were generated using the database-side default, will appear in this list as None unless the backend supports “returning” and the insert statement executed with the “implicit returning” enabled.

这意味着您可能希望在插入时使用an explicit returning clause。不过，我并没有真正理解这一点，因为插入了相关的值，所以它们显然是相关的