Could not assemble any primary key columns for mapped table

PythonSqlalchemyFlask SqlalchemyFlask Migrate

Python Problem Overview


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

Python Solutions


Solution 1 - Python

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 2 - Python

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 3 - Python

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.

Solution 4 - Python

I got this error because of a syntax mistake. I.v misspell 'primary_key' in my declaration

Solution 5 - Python

In relational database tables, it must require the candidate key. You can refer this article https://en.wikipedia.org/wiki/Candidate_key.

You just need to add primary key or composite primary key. For composite primary key, you can use below line in Flask APP. No need to import any thing. All will be take care by db variable in Flask.

In your example,

db.PrimaryKeyConstraint(application_essay_id , application_essay_id )

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMichael SamoylovView Question on Stackoverflow
Solution 1 - PythonMark AmeryView Answer on Stackoverflow
Solution 2 - PythonMinh PhamView Answer on Stackoverflow
Solution 3 - Pythonapi_nestView Answer on Stackoverflow
Solution 4 - PythonValerian ArdeleanView Answer on Stackoverflow
Solution 5 - Pythonkepy97View Answer on Stackoverflow