Python has great frameworks and libraries, but the Flask framework is offers ease-of-use for programmers to jump right in and begin writing code. If you're unfamiliar with Flask, you should brush up on the basics of building a Flask app, first. The Flask framework is excellent for beginners because it has several extensions to pick and choose from that can make it easier to understand how to put each of the pieces together in the back-end of a web app. A popular one that you will see often used in the Flask community is Flask-SQLAlchemy.

What is SQLAlchemy?

SQLAlchemy is a toolkit and object relational mapper (ORM) that allows programmers to take hold of the power of SQL without all of the trouble of configuring their Python app to communicate correctly.

Normally, if you were to create an app with a SQL database, you would need to install that database's driver and an API that allows the app to work with the database. There is a lot of configuration involved when taking this route in database modeling because the database is setup a little differently on various operating systems. Not to mention, if you aren't experienced, you're likely to run into a few issues along the way.

For instance, to setup a MySQL database connection to Flask on Windows 10, you must first download the MySQLclient that is compatible with your particular version of Python 3 (32-bit or 64-bit AMD). MySQLclient is an API interface that's a fork of the MySQL-python package. If you have a 32-bit Python 3 installation, you must also download the 32-bit version of MySQLclient as well.

Then open the command prompt, navigate to the MySQLclient installation and run the following commands:

# The mysqlclient version will vary depending on the version downloaded
pip install mysqlclient‑1.4.6‑cp38‑cp38‑win32.whl

pip install flask_mysqldb 

Of course, this doesn't include any of the potential errors that occur when setting up MySQLclient or Flask-mySQLdb on Windows and Linux, which are the operating systems that encounter the most issues with downloads like these. This tends to be a pretty heavy way to get your database connected to your Flask app, especially considering that we haven't even created the cursor connection or models inside of Flask yet. That's the benefit of using an ORM like SQLAlchemy; it only takes one command to get it going.

SQLAlchemy avoids heavy configuration while also allowing programmers to use raw SQL commands within their code. Which can actually be a potential security risk if not handled correctly, but with many safeguards already in place most security threats are avoided. Overall, SQLAlchemy is a robust solution to using raw SQL queries and requires minimal code to get started.

This toolkit has simplified the use of SQL in Python apps incredibly. Thus, leading to its incorporation in some of the popular Python libraries and frameworks, including Flask.

What Makes Flask-SQLAlchemy Different?

You may be wondering: 'If SQLAlchemy is a toolkit made specifically for Python, then why do we need to make something specifically for Flask?' And the answer is: it's not necessary to have an extension of SQLAlchemy specifically for Flask, but it makes programmers' lives much easier having one.

There are several features and benefits built into many extensions that make using it with the framework more convenient, but each of these extensions comes with limitations regarding scalability among other issues. As a developer, it's important to weigh out the benefits versus the drawbacks of an extension before implementing it in code. Flask-SQLAlchemy is often worth it for most small projects, especially for beginners.

Differences Between the Extension and the Package

Let's clarify what exactly makes the SQLAlchemy extension slightly different from the package. The Flask-SQLAlchemy extension is nothing more than a wrapper of the SQLAlchemy package with the addition of built-in functionality specifically made for the Flask framework. In other words, the extension will have all of the same abilities as the package, but you also have access to benefits that are specific to Flask by installing the extension.

The most significant features of the Flask-SQLAlchemy extension revolve around integrating SQLAlchemy into the Flask framework seemlessly. This includes configuring the session, connection, and engine. Without the extension, these configurations would need to be handled by the programmer. Instead, Flask-SQLAlchemy handles this setup for you. Which is why it's a helpful extension for less experienced developers in particular.

Now let's take a look of an example of what our app would look like if we integrated Flask-SQLAlchemy. If we wanted to use the same MySQL database as before but using Flask-SQLAlchemy, we would only need to create a database and a table in MySQL. The rest is handled within your Flask app after installing Flask-SQLAlchemy and setting up your database URI.

pip install flask-sqlalchemy

Database setup within the app.py file:

# app.py
...
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost/db_name'
db = SQLAlchemy(app)

Once you have configured models in your python file, you can use SQL commands to migrate your data to the database.

Otherwise, you can install additional dependencies and Flask-SQLAlchemy can handle the migration as well.

# Additional extensions needed to migrate data from flask
pip install flask-migrate flask-script psycopg2-binary

This approach would require a basic manage.py file which handles the migrations.

# manage.py

from flask_migrate import Migrate, MigrateCommand
from flask_script import Manager
from app import app, db

migrate = Migrate(app, db)
manager = Manager(app)

manager.add_command('db', MigrateCommand)

if __name__ == '__main__':
    manager.run()

Then run these commands to get data moved to MySQL:

python manage.py db init

python manage.py db migrate

python manage.py db upgrade

In most cases, opting for Flask-SQLAlchemy takes some of the overhead off of the developer's shoulders. Flask-SQLAlchemy is preferred over using the standalone SQLAlchemy package because you're not writing a lot of unnecessary code just to get the engine and connection working correctly within Flask.

A Note For New Programmers

With all of that said, it's not always the case that Flask extensions are worth the effort. While some like Flask-SQLAlchemy may offer convenience and ease of setup, others may leave you in more trouble than they're worth. In many cases the extension is no longer being maintained by its author after a period of time. It's important to conduct a significant amount of research and read documentation before you start using any extension in a web application.

If you're new to programming, you can always reach out to someone in the online programming community or ask a mentor what your best options are before using extensions in your app. While your practice projects may not seem like they're very important right now, they will build the foundation of your programming knowledge and habits. Knowing when and when not to use certain extensions is a skill that will come in handy throughout your web development career. It's best to start questioning extensions and common practices early on.

Benefits of Flask-SQLAlchemy

You may be asking yourself what exactly makes Flask-SQLAlchemy worth using in web applications. Once you have the extension setup in your Flask app, using the extension requires minimal code. Programmers can use simple commands such as db.session.delete(id) to perform powerful commands directly on a database. Flask-SQLAlchemy is easy to pick up and start using in a short amount of time, regardless of your programming experience. It's also a reliable extension that prevents many SQL injection attacks. For many, Flask-SQLAlchemy makes SQL queries swift and database migration simple.

With this article at OpenGenus, you must have got the complete of using SQLAlchemy in Flask (Python). Enjoy.