How to Install and Use SQLAlchemy in Flask

Flask is a popular web framework for Python that allows you to create web applications quickly and easily. However, Flask does not provide any built-in support for working with databases, which are essential for storing and retrieving data for your web applications. This is where SQLAlchemy comes in handy.

SQLAlchemy is a powerful and flexible library that provides an object-relational mapper (ORM) for Python. An ORM is a tool that allows you to interact with databases using Python objects and classes, instead of writing raw SQL queries. SQLAlchemy supports many database engines, such as SQLite, PostgreSQL, MySQL, Oracle, and more.

In this blog post, you will learn how to install and use SQLAlchemy in Flask, and how to perform some common database operations, such as creating tables, inserting data, querying data, updating data, and deleting data. You will also learn how to use Flask-SQLAlchemy, an extension that simplifies using SQLAlchemy with Flask.

Installing SQLAlchemy and Flask-SQLAlchemy
To use SQLAlchemy in Flask, you need to install two packages: SQLAlchemy and Flask-SQLAlchemy. SQLAlchemy is the core library that provides the ORM functionality, and Flask-SQLAlchemy is a wrapper that integrates SQLAlchemy with Flask and adds some useful features and helpers.

You can install both packages using pip, a Python package manager. To install or update the latest version of both packages, run the following command in your terminal:

$ pip install -U SQLAlchemy Flask-SQLAlchemy

Alternatively, you can specify the packages and their versions in a requirements.txt file, and then run pip install -r requirements.txt to install them.

Configuring the Extension
The next step is to configure the Flask-SQLAlchemy extension in your Flask application. The only required configuration is the SQLALCHEMY_DATABASE_URI key, which is a connection string that tells SQLAlchemy what database engine and database name to use. You can also set some optional configuration keys, such as SQLALCHEMY_TRACK_MODIFICATIONS, which controls whether SQLAlchemy should track changes to the objects in the session.

To configure the extension, you need to create a Flask application object, load the configuration, and then initialize the Flask-SQLAlchemy extension object with the application. For example, the following code creates a Flask application, configures a SQLite database in the app’s instance folder, and initializes the extension:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# create the extension
db = SQLAlchemy()

# create the app
app = Flask(__name__)

# configure the SQLite database, relative to the app instance folder
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///project.db"

# initialize the app with the extension
db.init_app(app)

The db object gives you access to the db.Model class to define models, and the db.session to execute queries. You can also access the names in sqlalchemy and sqlalchemy.orm for convenience, such as db.Column, db.Integer, db.String, etc.

Defining Models
To define a model class, you need to subclass db.Model and define the attributes and columns of the model. Each model class corresponds to a table in the database, and each attribute corresponds to a column in the table. You can use the db.Column class to specify the type, constraints, and options of each column. For example, the following code defines a User model class with three columns: id, username, and email:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)
    email = db.Column(db.String)

The model class will automatically generate a table name by converting the CamelCase class name to snake_case. In this case, the table name “user” will be assigned to the User model. You can also specify a custom table name by setting the tablename attribute in the model class.

Creating the Tables
After defining the model classes, you need to create the tables in the database. You can use the db.create_all() method to create all the tables defined by the models. This method requires an application context, which is a Flask object that contains information about the current request and the current app. Since you are not in a request at this point, you need to create an application context manually using the app.app_context() context manager. For example, the following code creates the tables for the User model:

with app.app_context():
    db.create_all()

You can also use the db.drop_all() method to drop all the tables defined by the models, if you need to reset the database.

Performing Database Operations
Once you have created the tables, you can perform various database operations, such as inserting, querying, updating, and deleting data. You can use the db.session object to interact with the database, which represents a collection of objects that are in the scope of a single database transaction. You can use the db.session.add() method to add a new object to the session, the db.session.commit() method to commit the changes to the database, and the db.session.rollback() method to rollback the changes in case of an error.

Inserting Data
To insert a new record into the database, you need to create an instance of the model class, and then add it to the session and commit the changes. For example, the following code creates and inserts a new User object into the database:

# create a new User object
user = User(username="example", email="example@example.com")

# add the object to the session
db.session.add(user)

# commit the changes
db.session.commit()

You can also add multiple objects to the session at once using the db.session.add_all() method, which takes a list of objects as an argument.

Querying Data
To query data from the database, you can use the db.session.query() method, which returns a Query object that allows you to filter, order, group, and slice the results. You can also use the model class itself as a shortcut to query all the records of that model. For example, the following code queries all the User objects from the database:

# query all the User objects
users = db.session.query(User).all()

# or use the model class as a shortcut
users = User.query.all()

You can use various methods and operators on the Query object to refine the results, such as filter(), filter_by(), order_by(), limit(), offset(), count(), first(), get(), etc. For example, the following code queries the User object with the username “example”:

# query the User object with the username "example"
user = db.session.query(User).filter(User.username == "example").first()

# or use the filter_by() method as a shortcut
user = User.query.filter_by(username="example").first()

You can also use the db.session.execute() method to execute raw SQL statements, if you need more control or flexibility over the queries. For example, the following code executes a SQL statement to select all the users from the user table:

# execute a raw SQL statement
result = db.session.execute("SELECT * FROM user")

# iterate over the result
for row in result:
    print(row)

Updating Data
To update an existing record in the database, you need to query the object from the database, modify its attributes, and then commit the changes to the session. For example, the following code updates the email of the User object with the username “example”:

# query the User object with the username "example"
user = User.query.filter_by(username="example").first()

# update the email attribute
user.email = "newemail@example.com"

# commit the changes
db.session.commit()

You can also use the db.session.query().update() method to update multiple records at once, without loading them into memory. This method takes a dictionary of attribute-value pairs as an argument, and returns the number of rows affected by the update. For example, the following code updates the email of all the User objects to “test@example.com”:

# update the email of all the User objects
rows = db.session.query(User).update({"email": "test@example.com"})

# commit the changes
db.session.commit()

# print the number of rows affected
print(rows)

Deleting Data
To delete an existing record from the database, you need to query the object from the database, and then delete it from the session and commit the changes. For example, the following code deletes the User object with the username “example” from the database:

# query the User object with the username "example"
user = User.query.filter_by(username="example").first()

# delete the object from the session
db.session.delete(user)

# commit the changes
db.session.commit()

You can also use the db.session.query().delete() method to delete multiple records at once, without loading them into memory. This method returns the number of rows affected by the delete. For example, the following code deletes all the User objects from the database:

# delete all the User objects
rows = db.session.query(User).delete()

# commit the changes
db.session.commit()

# print the number of rows affected
print(rows)

Conclusion
In this blog post, you learned how to install and use SQLAlchemy in Flask, and how to perform some common database operations, such as creating tables, inserting data, querying data, updating data, and deleting data. You also learned how to use Flask-SQLAlchemy, an extension that simplifies using SQLAlchemy with Flask and adds some useful features and helpers.

SQLAlchemy is a powerful and flexible library that provides an ORM for Python, allowing you to interact with databases using Python objects