flask sqlalchemy cannot create table

Question

I am a newbie of python. I am trying to create tables and practice how to declarative models with Flask-SQLAlchemy. While I to run db.create_all() I got the error below. I am not sure which part is wrong. Hope Those who have experience could help me.

sqlalchemy.exc.ArgumentError: Mapper Mapper|TeacherUser|teacher_user could not assemble any primary key columns for mapped table 'teacher_user'

user -> search_condition is one-to-one
user <-> teacher is many-to-many, teacherUser is the relation table between these two tables.
user -> classes is many-to-one
user -> book is one-to-many

Here is my model schema

from flaskr import db
from sqlalchemy import text
from datetime import datetime
from sqlalchemy.dialects.mysql import ENUM, JSON


class User(db.Model):
    __tablename__ = 'user'
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    search_condition = db.relationship('SearchCondition', uselist=False, backref='user')

    teachers = db.relationship('TeacherUser', backref=db.backref('users', lazy='joined'), lazy='dynamic')

    class_id = db.Column(db.Integer, db.ForeignKey('classes.id'))
    classes = db.relationship('Classes', backref='classes')

    books = db.relationship('Book',backref='book')

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<Demo %r>' % self.username

    def save(self):
        db.session.add(self)
        db.session.commit()


class SearchCondition(db.Model):
    __tablename__ = 'search_condition'
    search_condition_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'))
    condition = db.Column(JSON, server_default=text(r"{}"))


class TeacherUser(db.Model):
    __tablename__ = 'teacher_user'
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'))
    teacher_id = db.Column(db.Integer, db.ForeignKey('teacher.teacher_id'))


class Teacher(db.Model):
    __tablename__ = 'teacher'
    teacher_id = db.Column(db.Integer, primary_key=True)
    participants = db.relationship('TeacherUser', backref=db.backref('teachers', lazy='joined'), lazy='dynamic')


class Classes(db.Model):
    __tablename__ = 'classes'
    class_id = db.Column(db.Integer, primary_key=True)


class Book(db.Model):
    __tablename__ = 'book'
    book_id = db.Column(db.Integer, primary_key=True)
    own_id = db.Colum(db.Integer,db.ForeignKey('user.id'))

You need to add a primary column for table teacher_user

mad_
– mad_

2018-10-22 14:53:16 +00:00
Commented Oct 22, 2018 at 14:53 — mad_
– mad_, Commented Oct 22, 2018 at 14:53

mad_ · Accepted Answer · 2018-10-22 14:54:37Z

1

Add a composite primary key to your model

class TeacherUser(db.Model):
    __tablename__ = 'teacher_user'
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), primary_key=True)
    teacher_id = db.Column(db.Integer, db.ForeignKey('teacher.teacher_id'), primary_key=True)

answered Oct 22, 2018 at 14:54

mad_

8,2832 gold badges32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rukeith Over a year ago

I add primary_key to it. But I got this

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1215, 'Cannot add foreign key constraint') [SQL: '\nCREATE TABLE teacher_user (\n\tuser_id INTEGER NOT NULL, \n\tteacher_id INTEGER NOT NULL, \n\tPRIMARY KEY (user_id, teacher_id), \n\tFOREIGN KEY(user_id) REFERENCES user (user_id), \n\tFOREIGN KEY(teacher_id) REFERENCES teacher (teacher_id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/gkpj)

Collectives™ on Stack Overflow

flask sqlalchemy cannot create table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related