1. Introduction
In this tutorial, you will learn how to use databases and SQLAlchemy in Flask, a popular web framework for Python. You will learn how to create a database, perform CRUD operations, and use SQLite, a lightweight and portable database engine.
Databases are essential for any web application that needs to store and manipulate data. They allow you to organize your data in a structured and efficient way, and query it using a standard language called SQL (Structured Query Language).
SQLAlchemy is a powerful ORM (Object Relational Mapper) for Python, which allows you to interact with databases using Python objects and methods, rather than writing raw SQL queries. SQLAlchemy also provides a high-level abstraction layer called the declarative system, which lets you define your database model using Python classes and decorators.
Flask is a web framework that provides a simple and flexible way to create web applications using Python. Flask does not include a built-in database support, but it allows you to use any database library or ORM that you prefer. In this tutorial, you will use SQLAlchemy as your database library, and Flask-SQLAlchemy as an extension that integrates SQLAlchemy with Flask.
By the end of this tutorial, you will have a basic understanding of how to use databases and SQLAlchemy in Flask, and you will be able to create a simple web application that can store and display data using SQLite.
Are you ready to get started? Let’s begin by setting up the project.
2. Setting Up the Project
To use databases and SQLAlchemy in Flask, you need to set up a project folder that contains the following files:
- app.py: This is the main file that runs your Flask application and contains the routes and views.
- models.py: This is the file that defines your database model using SQLAlchemy’s declarative system.
- requirements.txt: This is the file that lists the dependencies that you need to install for your project.
To create these files, you can use any code editor or IDE that you prefer, such as Visual Studio Code, PyCharm, or Sublime Text. You can also use the terminal or command prompt to create the files using the touch command on Linux or Mac, or the type nul > command on Windows.
For example, to create the app.py file on Linux or Mac, you can run the following command in your terminal:
touch app.py
Or, to create the app.py file on Windows, you can run the following command in your command prompt:
type nul > app.py
Similarly, you can create the models.py and requirements.txt files using the same commands, but with different file names.
Once you have created the files, you need to install the dependencies that you need for your project. The main dependencies that you need are Flask, SQLAlchemy, and Flask-SQLAlchemy. You can install them using the pip command, which is a package manager for Python.
To install the dependencies, you need to open your terminal or command prompt and navigate to your project folder. Then, you need to run the following command:
pip install flask sqlalchemy flask-sqlalchemy
This command will install the latest versions of Flask, SQLAlchemy, and Flask-SQLAlchemy in your project folder. You can also specify the exact versions that you want to install by adding the version number after the package name, separated by a double equal sign. For example, to install Flask version 2.0.1, you can run the following command:
pip install flask==2.0.1
After you have installed the dependencies, you need to add them to your requirements.txt file, which is a file that records the packages that you need for your project. This file is useful for sharing your project with others, as they can easily install the same packages that you used by running the pip install -r requirements.txt command.
To add the dependencies to your requirements.txt file, you can use the pip freeze command, which lists the packages that you have installed in your project folder. You can redirect the output of this command to your requirements.txt file using the > operator. For example, to add the dependencies to your requirements.txt file on Linux or Mac, you can run the following command in your terminal:
pip freeze > requirements.txt
Or, to add the dependencies to your requirements.txt file on Windows, you can run the following command in your command prompt:
pip freeze > requirements.txt
This command will overwrite the contents of your requirements.txt file with the list of packages that you have installed. You can also append the list of packages to your requirements.txt file using the >> operator, which will not erase the existing contents of the file.
Now that you have set up the project folder and installed the dependencies, you are ready to create your database model using SQLAlchemy.
3. Creating the Database Model
In this section, you will learn how to create your database model using SQLAlchemy‘s declarative system. A database model is a representation of the structure and relationships of the data that you want to store in your database. It defines the tables, columns, and constraints that make up your database schema.
To create your database model, you need to use the models.py file that you created in the previous section. In this file, you will import the SQLAlchemy and Flask-SQLAlchemy modules, and create a db object that represents the database connection. You will also define a Base class that inherits from db.Model, which is the base class for all your database models.
To import the modules and create the db object, you can write the following code in your models.py file:
from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) db = SQLAlchemy(app)
To define the Base class, you can write the following code below the previous code:
class Base(db.Model):
__abstract__ = True
id = db.Column(db.Integer, primary_key=True)
created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
updated_at = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp())The Base class is an abstract class that defines some common attributes and methods for all your database models. It has an id column that serves as the primary key for each table, and two created_at and updated_at columns that store the timestamps of the creation and update of each record. The __abstract__ attribute is set to True to indicate that this class is not a table itself, but a base class for other tables.
Now that you have defined the Base class, you can create your own database models by inheriting from it. For this tutorial, you will create a simple database model that represents a book. A book has a title, an author, and a genre. To create the Book model, you can write the following code below the Base class:
class Book(Base):
__tablename__ = 'books'
title = db.Column(db.String(255), nullable=False)
author = db.Column(db.String(255), nullable=False)
genre = db.Column(db.String(255), nullable=False)The Book class inherits from the Base class and defines its own attributes and methods. It has a __tablename__ attribute that specifies the name of the table in the database, and three title, author, and genre columns that store the information of each book. The db.Column function creates a column object that takes the data type and some optional parameters as arguments. The nullable parameter is set to False to indicate that the column cannot be empty.
Congratulations! You have created your database model using SQLAlchemy. You can create more models by following the same pattern and adding more columns and relationships as needed. In the next section, you will learn how to configure the database URI that connects your Flask application to your database.
4. Configuring the Database URI
In this section, you will learn how to configure the database URI that connects your Flask application to your database. A database URI is a string that specifies the location and credentials of your database, as well as the database engine that you are using. For example, a database URI for a PostgreSQL database might look like this:
postgresql://username:password@host:port/database
In this tutorial, you will use SQLite as your database engine, which is a lightweight and portable database that stores the data in a single file. SQLite does not require a username, password, host, or port, so the database URI for SQLite is simply the path to the database file. For example, a database URI for SQLite might look like this:
sqlite:///books.db
The sqlite:/// prefix indicates that you are using SQLite as your database engine, and the books.db is the name of the database file that you want to create or use. You can choose any name for your database file, as long as it has the .db extension.
To configure the database URI for your Flask application, you need to use the app.py file that you created in the previous section. In this file, you will set the value of the SQLALCHEMY_DATABASE_URI configuration variable to the database URI that you want to use. You will also set the value of the SQLALCHEMY_TRACK_MODIFICATIONS configuration variable to False, to disable a feature that is not needed for this tutorial and can cause some performance issues.
To set the configuration variables, you can write the following code in your app.py file, below the previous code:
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
This code will tell Flask to use SQLite as the database engine and books.db as the database file. You can change these values if you want to use a different database engine or file name.
Now that you have configured the database URI for your Flask application, you are ready to initialize the database and create the tables for your database model.
5. Initializing the Database
In this section, you will learn how to initialize the database and create the tables for your database model using SQLAlchemy and Flask. Initializing the database means creating the database file and the schema that defines the structure and constraints of the data. Creating the tables means creating the actual objects that store the data in the database.
To initialize the database and create the tables, you need to use the db object that you created in the previous section. The db object has a method called create_all that creates all the tables that are defined in your database model. You can call this method from your app.py file, after you have configured the database URI.
To call the create_all method, you can write the following code in your app.py file, below the previous code:
db.create_all()
This code will create the database file and the tables in your project folder. You can verify that the database file and the tables have been created by using a database browser tool, such as DB Browser for SQLite. You can open the database file using the tool and inspect the tables and their columns.
Alternatively, you can also use the sqlite3 command-line tool that comes with Python to interact with the database. You can open the database file using the tool and execute SQL queries to view and manipulate the data. For example, to open the database file using the sqlite3 tool, you can run the following command in your terminal or command prompt:
sqlite3 books.db
This command will open the database file and prompt you to enter SQL commands. To view the tables that have been created, you can run the following command:
.tables
This command will list the names of the tables in the database. You should see the books table that corresponds to your Book model. To view the columns and their data types of the books table, you can run the following command:
.schema books
This command will show the SQL statement that creates the books table. You should see the id, created_at, updated_at, title, author, and genre columns that correspond to your Book model attributes. To exit the sqlite3 tool, you can run the following command:
.quit
Congratulations! You have initialized the database and created the tables for your database model using SQLAlchemy and Flask. You can now insert, update, delete, and query data using your database model. In the next section, you will learn how to perform CRUD operations using SQLAlchemy.
6. Performing CRUD Operations
In this section, you will learn how to perform CRUD operations using SQLAlchemy and Flask. CRUD stands for Create, Read, Update, and Delete, which are the four basic operations that you can perform on any database. You will use the Book model that you created in the previous section to insert, retrieve, modify, and remove data from the books table in your database.
To perform CRUD operations, you need to use the db object that you created in the previous section. The db object has a method called session that provides a way to interact with the database using your database model. The session object has several methods that allow you to perform CRUD operations, such as add, query, update, delete, and commit. You can call these methods from your app.py file, after you have initialized the database and created the tables.
To perform CRUD operations, you also need to import the Book model from the models.py file, so that you can use it in your app.py file. To import the Book model, you can write the following code in your app.py file, below the previous code:
from models import Book
This code will import the Book class from the models module, which is the name of the models.py file. You can now use the Book class to create and manipulate book objects in your database.
Let’s see how to perform each CRUD operation using SQLAlchemy and Flask.
Create
To create a new record in the database, you need to create a new instance of the Book class and pass the values for the title, author, and genre attributes. Then, you need to add the instance to the session object and commit the changes to the database. For example, to create a new book with the title “The Hitchhiker’s Guide to the Galaxy”, the author “Douglas Adams”, and the genre “Science Fiction”, you can write the following code in your app.py file:
book = Book(title="The Hitchhiker's Guide to the Galaxy", author="Douglas Adams", genre="Science Fiction") db.session.add(book) db.session.commit()
This code will create a new book object with the given values and add it to the session object. The db.session.commit() method will save the changes to the database and assign an id to the book object. You can verify that the book has been inserted into the database by using the database browser tool or the sqlite3 tool that you used in the previous section.
Read
To read a record from the database, you need to use the session object’s query method, which allows you to query the database using your database model. The query method returns a query object, which has several methods that allow you to filter, order, group, and aggregate the results. For example, to query all the books in the database, you can write the following code in your app.py file:
books = db.session.query(Book).all()
This code will return a list of book objects that represent all the records in the books table. You can iterate over the list and access the attributes of each book object. For example, to print the title and author of each book, you can write the following code:
for book in books:
print(book.title, book.author)This code will print the title and author of each book in the database. You can also use the query object’s filter method to query only the books that match a certain condition. For example, to query only the books that have the genre “Science Fiction”, you can write the following code:
books = db.session.query(Book).filter(Book.genre == "Science Fiction").all()
This code will return a list of book objects that have the genre “Science Fiction”. You can use other operators and methods to filter the results, such as != (not equal), like (string matching), in_ (membership), and_ (logical and), or_ (logical or), and not_ (logical not).
Update
To update a record in the database, you need to query the record that you want to update and assign new values to the attributes that you want to change. Then, you need to commit the changes to the database. For example, to update the genre of the book with the title “The Hitchhiker’s Guide to the Galaxy” to “Comedy”, you can write the following code in your app.py file:
book = db.session.query(Book).filter(Book.title == "The Hitchhiker's Guide to the Galaxy").first() book.genre = "Comedy" db.session.commit()
This code will query the book with the given title and assign a new value to the genre attribute. The db.session.commit() method will save the changes to the database. You can verify that the book has been updated in the database by using the database browser tool or the sqlite3 tool that you used in the previous section.
Delete
To delete a record from the database, you need to query the record that you want to delete and pass it to the session object’s delete method. Then, you need to commit the changes to the database. For example, to delete the book with the title “The Hitchhiker’s Guide to the Galaxy”, you can write the following code in your app.py file:
book = db.session.query(Book).filter(Book.title == "The Hitchhiker's Guide to the Galaxy").first() db.session.delete(book) db.session.commit()
This code will query the book with the given title and delete it from the session object. The db.session.commit() method will save the changes to the database and remove the record from the books table. You can verify that the book has been deleted from the database by using the database browser tool or the sqlite3 tool that you used in the previous section.
Congratulations! You have learned how to perform CRUD operations using SQLAlchemy and Flask. You can now insert, retrieve, modify, and remove data from your database using your database model. In the next section, you will learn how to query the database using more advanced features of SQLAlchemy.
7. Querying the Database
Now that you have created your database and performed some CRUD operations, you might want to query the database and retrieve some data. Querying the database means asking it a question and getting an answer in the form of a result set, which is a collection of rows that match your query criteria.
To query the database using SQLAlchemy, you need to use the query method of the session object, which is an instance of the Session class that represents a connection to the database. The query method takes one or more arguments that specify the tables or columns that you want to query, and returns a Query object, which is a representation of your query that can be further modified or executed.
For example, to query all the rows from the Book table, you can use the following code:
from models import Book, session
# query all the books
books = session.query(Book).all()
# print the books
for book in books:
print(book.title, book.author, book.price)The all method of the Query object executes the query and returns a list of objects that correspond to the rows in the table. You can access the attributes of each object using the dot notation, as shown in the print statement.
You can also query specific columns from the table, rather than the whole row. For example, to query only the titles and prices of the books, you can use the following code:
from models import Book, session
# query the titles and prices of the books
books = session.query(Book.title, Book.price).all()
# print the books
for book in books:
print(book.title, book.price)The query method accepts multiple arguments that specify the columns that you want to query, separated by commas. The all method returns a list of tuples that contain the values of the columns that you queried. You can access the values of each tuple using the index notation, as shown in the print statement.
However, if you want to access the values of each tuple using the dot notation, you can use the label method of the column object, which assigns a name to the column that you can use as an attribute. For example, to query the titles and prices of the books and access them using the dot notation, you can use the following code:
from models import Book, session
# query the titles and prices of the books
books = session.query(Book.title.label('title'), Book.price.label('price')).all()
# print the books
for book in books:
print(book.title, book.price)The label method takes a string argument that specifies the name of the column that you want to use as an attribute. The all method returns a list of named tuples that contain the values of the columns that you queried. You can access the values of each named tuple using the dot notation, as shown in the print statement.
These are some basic examples of how to query the database using SQLAlchemy. In the next section, you will learn how to update and delete data from the database using SQLAlchemy.
8. Updating and Deleting Data
In the previous section, you learned how to query the database and retrieve data using SQLAlchemy. In this section, you will learn how to update and delete data from the database using SQLAlchemy.
To update or delete data from the database using SQLAlchemy, you need to use the update or delete methods of the Query object, which are similar to the SQL statements of the same name. The update method takes a dictionary of column names and values that specify the changes that you want to make to the data, and the delete method takes no arguments. Both methods return the number of rows that were affected by the operation.
For example, to update the price of the book with the title “The Catcher in the Rye” to 15.99, you can use the following code:
from models import Book, session
# query the book with the title "The Catcher in the Rye"
book = session.query(Book).filter(Book.title == "The Catcher in the Rye")
# update the price of the book to 15.99
book.update({"price": 15.99})
# commit the changes to the database
session.commit()The filter method of the Query object allows you to specify a condition that the rows must satisfy to be included in the result set. The filter method takes an expression that compares a column to a value, and returns a new Query object that represents the filtered query. You can chain multiple filter methods to apply multiple conditions to the query.
The update method takes a dictionary that maps the column names to the new values that you want to assign to them. The update method modifies the data in the database, but does not commit the changes until you call the commit method of the session object, which is an instance of the Session class that represents a connection to the database. The commit method saves the changes to the database and ends the transaction.
To delete the book with the title “The Catcher in the Rye” from the database, you can use the following code:
from models import Book, session # query the book with the title "The Catcher in the Rye" book = session.query(Book).filter(Book.title == "The Catcher in the Rye") # delete the book from the database book.delete() # commit the changes to the database session.commit()
The delete method deletes the data from the database, but does not commit the changes until you call the commit method of the session object, as explained before.
These are some basic examples of how to update and delete data from the database using SQLAlchemy. In the next and final section, you will learn how to conclude your tutorial and provide some additional resources for the reader.
9. Conclusion
Congratulations! You have successfully completed this tutorial on how to use databases and SQLAlchemy in Flask. You have learned how to:
- Create a project folder and install the dependencies for your web application.
- Create a database model using SQLAlchemy’s declarative system.
- Configure the database URI and initialize the database using Flask-SQLAlchemy.
- Perform CRUD operations on the database using the session object and the query method.
- Query the database and retrieve data using the filter method and the all method.
- Update and delete data from the database using the update method and the delete method.
By following this tutorial, you have gained a basic understanding of how to use databases and SQLAlchemy in Flask, and you have created a simple web application that can store and display data using SQLite.
However, this tutorial only covers the basics of using databases and SQLAlchemy in Flask, and there is much more to learn and explore. If you want to learn more about databases and SQLAlchemy, you can check out the following resources:
- SQLAlchemy Documentation: This is the official documentation of SQLAlchemy, which provides a comprehensive guide on how to use SQLAlchemy and its features.
- SQLite Tutorial: This is a website that provides a series of tutorials on how to use SQLite, which is a lightweight and portable database engine that you can use with SQLAlchemy.
- Flask by Example – Part 2: Postgres, SQLAlchemy, and Alembic: This is a blog post that shows you how to use PostgreSQL, a powerful and open-source database system, with SQLAlchemy and Alembic, a tool that helps you manage database migrations.
We hope you enjoyed this tutorial and found it useful. If you have any questions or feedback, please feel free to leave a comment below. Thank you for reading and happy coding!
