How to insert composite primary key using SQLAlchemy?

Question

When I try to insert the composite primary key connection returns 0 rows:

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()

Seems like it is not possible to insert the composite primary key as values ... but how to insert key in such case ? Unfortunately I have not found some example in SQLAlchemy documentation ... (

score 1 · Accepted Answer · answered Jun 10 '20 at 14:11

From what I understand of the documentation, SQLAlchemy only implicitly returns autoincrement primary keys, which composite PKs specifically are not:

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.

Meaning you probably want to use an explicit returning clause on your insert. Not that I really see the point though, you obviously have the relevant values since you inserted them.

How to insert composite primary key using SQLAlchemy?

1 Answers1