Flask/SQLAlchemy - error creating models with relationships

Question

I created a table users with this mysql statement in the shell:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL,
  `email` varchar(120) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB;

and a table posts with this statement:

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(140) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_id` (`user_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

I want to use these tables in my Flask app. I created a foreign key connecting posts to users. It's supposed to be a one-to-many relationship with the user having many posts.

In my python code I have the following models:

class Users(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(40), unique=True)
    email = db.Column(db.String(120), unique=True)
    posts = db.relationship('Posts', backref='user', lazy='dynamic')

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

    def __repr__(self):
        return '<User {}>'.format(self.username)


class Posts(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String(140))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

When I try running my Flask app however, I keep getting the error:

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not determine join condition between parent/child tables on relationship Users.posts - there are no foreign keys linking these tables.

But I created the foreign key connecting posts to users in the mysql statement. When I take out the line posts = db.relationship('Posts', backref='user', lazy='dynamic'), everything works just fine, so the issue lies in that piece of code. What part of my setup is wrong?

Community · Accepted Answer · 2017-05-23 10:28:18Z

2

Not sure what's wrong with the initial creation of the tables, but i would use sqlalchemy's create_all. See here and in the docs. This way you know any issue is not a matter of different definitions of the initial SQL creation query vs the models' definitions. Stick with SQLAlchemy all the way.

Your model definitions seem OK, except for the backref. I compared it with my models definitions, and my backrefs are always the table names. You have 'user' as a backref, which doesn't really appear anywhere else in the code, so i'm assuming that's where things go wrong.

To forcibly define table names add tablename to the class definition; e.g.

class User(db.model):
    __tablename__ = 'Users'
    id = db.Column(..
    .. more fields ..
    posts = db.relationship('Post', backref='Users', lazy='dynamic')

I use the convention of the classes in singular, and table names in plural, which makes it easier to see how things are defined, what a backref points to, etc. For example, note that the above relationship points to the class Post, while the backref to the table Users. Now there's no ambivalence in the definitions.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Jan 11, 2016 at 5:17

GG_Python

3,5415 gold badges37 silver badges50 bronze badges

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

1 Comment

GG_Python Over a year ago

awesome, just verify your relationships work as they should .tables can be created with 'strange' relationship definitions without error, but wind up not working as expected.

Collectives™ on Stack Overflow

Flask/SQLAlchemy - error creating models with relationships

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related