Could not assemble any primary key columns for mapped table
PythonSqlalchemyFlask SqlalchemyFlask MigratePython 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 Column
s as arguments (e.g. to op.create_table()
or the Table()
constructor) and others that involve declaring a class with Column
s 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 )