Bases de datos: SQLite y SQLAlchemy

Database integration in Flask applications represents a fundamental step in developing robust and scalable web solutions. SQL Alchemy is positioned as a powerful tool that allows Python developers to interact with relational databases without the need to fully master the SQL language, thanks to its ORM (Object-Relational Mapping) implementation. This approach not only simplifies development, but also improves code maintainability by working with Python objects instead of direct SQL queries.

How to integrate a SQLite database into a Flask application?

To integrate a database into our Flask application, we will use Flask-SQLAlchemy, an extension that facilitates the use of SQLAlchemy with Flask. This library allows us to relate database tables with Python models or classes, implementing the ORM (Object-Relational Mapping) pattern.

The first step is to install the necessary library. We open the terminal, activate our virtual environment and run:

pip install flask-sqlalchemy

Once the library is installed, it is recommended to create a requirements.txt file to document the project dependencies:

Flask==2.x.xFlask-SQLAlchemy==3.x.x

To verify that the installation was successful, we can use the flask shell command which provides us with an interactive console with our application loaded:

from flask_sqlalchemy import SQLAlchemy

If no error appears, it means that the library is correctly installed.

Configuring the database in Flask

To configure our SQLite database, we need to modify our main application file(app.py):

import osfrom flask import Flaskfrom flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
 # Database file path configurationdb_filepath = os.path.join(os.path.dirname(__file__), 'notes.sqlite')
 # SQLAlchemy configurationapp.config['SQLALCHEMY_DATABASE_URI'] = f'sqlite:///{db_filepath}'app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
 # SQLAlchemy instancedb = SQLAlchemy(app)

In this code:

1. We import the os library to handle file paths.
2. Define the path where the SQLite file will be created
3. Set the database URI to the format required by SQLAlchemy
4. Disable modification tracking to reduce log verbosity
5. We create an instance of SQLAlchemy linked to our application.

It is important to note that SQLAlchemy is compatible with multiple database engines such as MySQL or PostgreSQL, not only with SQLite.

How to create models and tables with SQLAlchemy?

Models in SQLAlchemy are Python classes that represent tables in the database. Each attribute of the class corresponds to a column in the table.

For our example, we will create a Note model to store notes with title and content:

class Note(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(100), nullable=False) content = db.Column(db.String(200), nullable=False)    
 def __repr__(self): return f'Note {self.id}: {self.title}'

In this model:

• id: Is an integer that acts as a primary key.
• title: Is a text string with a maximum length of 100 characters and cannot be null
• content: Is a text string with a maximum length of 200 characters and can not be null either
• The __repr__ method defines how the object will be displayed when printed.

Creating the tables in the database

Once the model is defined, we need to create the corresponding tables in the database. For this, we use the create_all() method of SQLAlchemy:

flask shell

And inside the interactive console:

from app import dbdb.create_all()

This command will create the notes.sqlite file with the note table according to the structure defined in our model.

Verifying the database structure

To verify that the table has been created correctly, we can use the SQLite command line tool:

sqlite3 notes.sqlite

And inside the SQLite console:

.schema

This command will show us the structure of the note table with its id, title and content columns, confirming that it has been created correctly according to our model.

What are the advantages of using ORM in Flask applications?

The use of ORM (Object-Relational Mapping) such as SQLAlchemy in Flask applications offers numerous advantages:

• Database abstraction: Allows working with Python objects instead of direct SQL queries.
• Portability: Facilitates switching between different database engines without modifying the code
• Security: Helps prevent SQL injection attacks by automatically handling character escaping
• Productivity: Reduces the amount of code needed to interact with the database
• Maintainability: Code is more readable and easier to maintain by working with objects and methods.

The use of models in SQLAlchemy also facilitates the evolution of the database schema as the application grows, allowing new fields or relationships to be added easily.

The integration of databases in Flask applications using SQLAlchemy represents a fundamental step in the development of robust web applications. This approach allows us to focus on the business logic while the ORM takes care of the communication with the database, resulting in a cleaner, more maintainable and secure code. Have you used ORM in your projects? Share your experience in the comments.