In the realm of web development, building applications that are scalable, secure, and maintainable is crucial. One key aspect of achieving this is through effective database modelling and design. In this article, we'll explore how to design a relational database schema for a Role-Based Access Control (RBAC) system using Python Flask, SQLAlchemy, and Flask-Migrate.


Step 1: Entity Relationship with attributes

Before diving into coding, it's essential to understand the entities and their relationships within the system. Here are the entities identified for our RBAC system along with their attributes:

- Roles: role_id, role_name, created_at, updated_at
- Users: user_id, role_id(fk), username, email_address, password, lastname, firstname, phonenumber, dateofbirth, created_at, updated_at
- Modules: module_id, module_title, module_order, module_icon, created_at, updated_at
- Tasks: task_id, module_id(fk), task_label, task_url, task_route, task_method, task-icon, isdashboard, isnavbar, created_at, updated_at
- Privileges: privilege_id, task_id(fk), role_id(fk), created_at, updated_at

Step 2: SQLAlchemy Models

To interact with the database, we'll use SQLAlchemy, a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. Here's how to set up SQLAlchemy:

```python
# Install SQLAlchemy
pip install Flask SQLAlchemy
```

Set up the database configuration in `flaskenv.py`:

```python
DATABASE_URI='mysql://root:root@localhost/python_rbac_db?unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock'
```

Connect to the MySQL database in `config.py`:

```python
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URI')
```

Create `mydb.py` and import SQLAlchemy:

```python
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
```

Step 3: Create Models

Based on the identified RBAC entities and attributes, we'll create SQLAlchemy models. For instance:

```python
from mydb import db

class RoleModel(db.Model):
__tablename__ = 'roles'
role_id = db.Column(db.Integer, primary_key=True)
role_name = db.Column(db.String(50), nullable=False)
created_at = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
# Define relationships with other tables if needed

class UserModel(db.Model):
__tablename__ = 'users'
user_id = db.Column(db.Integer, primary_key=True)
# Define other columns
```

Update `__init__.py` for Database Migrations:

```python
from mydb import db
from flask_migrate import Migrate

migrate = Migrate()
migrate.init_app(app, db)
```

Step 4: Database Migrations

Database migrations are essential for managing changes to the database schema over time. We'll use Flask-Migrate, which is a Flask wrapper for Alembic.

Install Flask-Migrate and mysqlclient:

```bash
pip install flask-migrate
pip install mysqlclient
```

Create Migration Repository:

```bash
flask db init
```

Generate Migration Files:

```bash
flask db migrate
```

Run Migration:

```bash
flask db upgrade
```

By following these steps, we've successfully designed our database schema for the RBAC system using Python Flask, SQLAlchemy, and Flask-Migrate. This ensures that our application remains flexible and maintainable as it evolves over time.

In the next stages of development, we can focus on implementing business logic, authentication, authorization, and other functionalities to complete our RBAC system.

Building robust applications requires attention to detail, especially in areas like database modelling and design. With the tools and techniques outlined in this article, developers can create scalable and secure web applications with ease.

Source Code on GitHub
Click here to read more and download the source code. https://academy.strideshub.com/blogdetails/day-4-building-a-python-flask-application-with-database-modelling-and-design/Nw==

To build a Python Flask application with database modeling and design, follow these steps:

Set up Flask: Install Flask using pip and create a Flask app. Define routes for different parts of your application.

Create Models: Use a database ORM like SQLAlchemy to define models representing your data. Create classes that inherit from db.Model and define fields like Column(Integer) for integers or Column(String) for strings.

Initialize the Database: Initialize the database in your Flask app by creating a db object and linking it to your app.

Migrate the Database: Use Flask-Migrate to manage database migrations. This allows you to change your database schema without losing data.

Build Views: Create views (HTML templates) to display your data. Use Flask's templating engine to render dynamic content.

Run the Application: Start your Flask app and test it in your browser.

In conclusion, building a Python Flask application with database modeling and design involves setting up Flask, creating models, initializing the database, migrating the database, building views, and running the application. This process is essential for anyone looking to enhance their skills in Python, especially for those pursuing a Python certification course.

Using the Flask micro-framework to create a web application is the first step in building a Python Flask application with database modeling. Through this process includes designing the database structure, defining models using SQLAlchemy, implementing CRUD (Create, Read, Update, Delete) operations, and integrating the application with a database management system like SQLite or PostgreSQL.
Flask is the perfect tool for creating scalable, effective web applications with strong data management features because of its ease of use and adaptability. Get the Python Certification Course to launch your Python career.