As you begin to build more complex applications using Flask, you will have to use relationships at some point. Using relationships ensures that your database is normalized. One to many relationships will be covered in this post. It is assumed that you are using Flask-SQLAlchemy in your application.
A one to many relationship is best understood using a practical example. Let’s say you have a login system in your web app and that you want to keep track of login details like the IP address, the browser etc. If you had one table where each user had an entry, you would have to overwrite the older IP and browser fields each time a user logs in. A better way to do this would be to add a table to store login details. Each time a user logs in, a record is created in the other table and it can be accessed using the user’s primary key.
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60), unique=False)
user_logins = db.relationship('Userlogins', backref='recordowner',cascade='all,delete')
Above is a class ‘User’ that relates to the table in your database. The field adding the one to many relationship is the user_logins. In the db.relationship(), the first argument is the name of the class of the table to store logins. Cascade defines the action to occur when the user is deleted. The login record is also deleted.
class Userlogins(db.Model):
id = db.Column(db.Integer, primary_key=True)
ip = db.Column(db.String(20),unique=False)
browser = db.Column(db.String(40), unique=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'),nullable=False)
The important field in the Userlogins class is the user_id. This stores the primary key of the user who the record belongs to. This is used when performing joins between the two tables.
#Once this is integrated into your web app, you can access all user logins with
user = User.query.filter_by(id=1).first()
user.user_logins
To add records to your one to many relationship, you create an object using the userlogins class then append it to the user object. Example below.
user = User.query.filter_by(id=1).first()
login_record = Userlogins(id=1,ip=request.remote_addr, browser=request.user_agent.browser)
user.user_logins.append(login_record)
#commit changes
db.session.commit()
As simple as that. You now have the ability to track user logins and track multiple account usage etc.