Could not assemble any primary key columns for mapped table

Question

When I'm trying to create a database schema migration, I'm getting this weird error. Can you please help me to figure out what's wrong?

$ python app.py db upgrade
[skipped]
sqlalchemy.exc.ArgumentError: Mapper Mapper|EssayStateAssociations|essay_associations could not assemble any primary key columns for mapped table 'essay_associations'

My model:

class EssayStateAssociations(db.Model):
    __tablename__ = 'essay_associations'

    application_essay_id = db.Column(
        db.Integer,
        db.ForeignKey("application_essay.id"),
        primary_key=True),
    theme_essay_id = db.Column(
        db.Integer,
        db.ForeignKey("theme_essay.id"),
        primary_key=True),
    state = db.Column(db.String, default="pending")

Solution 1

You cannot have two primary keys in a table. Instead, you must use a compound primary key. This can be done by adding a PrimaryKeyConstraint in your model as below (remember to add a comma before closing the bracket in __table_args__:

from db import PrimaryKeyConstraint

class EssayStateAssociations(db.Model):
    __tablename__ = 'essay_associations'
    __table_args__ = (
        PrimaryKeyConstraint('application_essay_id', 'theme_essay_id'),
    )

    application_essay_id = db.Column(
        db.Integer,
        db.ForeignKey("application_essay.id"))
    theme_essay_id = db.Column(
        db.Integer,
        db.ForeignKey("theme_essay.id"))
    state = db.Column(db.String, default="pending")

Solution 2

You get this error because you have trailing commas after your Column() definitions, which cause application_essay_id and theme_essay_id to each be parsed as a one-element tuple containing a Column instead of just a Column. This stops SQLAlchemy from "seeing" that the columns are present, and consequently causes your model not to contain any primary key column.

If you simply replace

application_essay_id = db.Column(
    db.Integer,
    db.ForeignKey("application_essay.id"),
    primary_key=True),
theme_essay_id = db.Column(
    db.Integer,
    db.ForeignKey("theme_essay.id"),
    primary_key=True),

with

application_essay_id = db.Column(
    db.Integer,
    db.ForeignKey("application_essay.id"),
    primary_key=True)
theme_essay_id = db.Column(
    db.Integer,
    db.ForeignKey("theme_essay.id"),
    primary_key=True)

then your error will be fixed.

Aside: since SQLAlchemy (and Alembic and Flask-SQLAlchemy) contain some syntaxes for declaring models/tables that involve passing a comma-separated sequence of Columns as arguments (e.g. to op.create_table() or the Table() constructor) and others that involve declaring a class with Columns as class properties, it's really easy to run into this error by cutting and pasting Column declarations from the first syntax to the second and forgetting to remove some of the commas. I suspect that this easy-to-make mistake is the reason this question has such a huge number of views - over 16000 at the time that I post this answer.

Solution 3

Though good answers have been given above, One trivial mistake that one could make is to create a table without any primary key. Though it may seem unnecessary, a primary key needs to be created for every table. Else the error above gets thrown.