Adding in a database
While using dictionaries to store data is fine for small projects, it is not a good idea for larger projects. We normally want our data to be persistent and not lost when the server is restarted. This is where databases come in. Databases are used to store data in a structured way and can be queried to retrieve data.
We will be using SQLite for these resources but any SQL database can be used. SQLite is a lightweight database that is easy to use and is perfect for small projects.
Setting up the database
First, we need to create a database and a table to store our data. We will be storing the data in a table called posts
with the following columns:
id
: The unique identifier for each posttitle
: The title of the postcontent
: The content of the post
We will be using the sqlite3
module to interact with the database. The sqlite3
module is included with Python so you don’t need to install anything.
Create a new file called database.py
and add the following code:
This code creates a new database file called blog.db
and creates a new table called posts
with the required columns. The id
column is the primary key and will auto-increment for each new post.
Run the database.py
file to create the database and table:
Using our database with flask
Now that we have our database set up, we can start using it in our Flask application. We will be using the sqlite3
module to interact with the database.
-
We first define a
get_db
function that returns a connection to the database.We use the
g
object to store the connection so that it can be reused across requests. This function will return the connection if it already exists, otherwise, it will create a new connection to the database. -
We define a
close_connection
function that closes the connection to the database when the request is finished.We use the
teardown_appcontext
decorator to register this function so that when the app closes the connection is closed. -
We define a route called
/
that fetches all the posts from the database and returns them.We use the
execute
method to execute an SQL query and thefetchall
method to fetch all the results.
Using SQLAlchemy
While using the sqlite3
module directly is fine for small projects, it can be cumbersome for larger projects. SQLAlchemy is a popular ORM (Object-Relational Mapping) library that makes it easy to interact with databases.
To use SQLAlchemy, you need to install the flask_sqlalchemy
package:
Configuring SQLAlchemy
You can then use the SQLAlchemy
class to create a new database connection:
Defining a model
You can then define a model for the posts
table using the db.Model
class:
Querying the database
You can then use the query
method to query the database:
This would be equivalent to the following SQL query:
Inputting data into the database
You can then use the add
method to add data to the database:
Instead of having to write raw SQL queries, you can use SQLAlchemy to interact with the database in a more object-oriented way. Using raw SQL queries we would have to convert it out of the dictionary format and into a string format to be able to insert it into the database. With SQLAlchemy, we can just pass the object directly to the add
method.
Extending SQLAlchemy
While the above example is a simple example of using SQLAlchemy, it can be extended to include more advanced features such as relationships, migrations, and more.
Relationships
When we have multiple tables in our database, we can define relationships between them using SQLAlchemy. For example, we can define a User
model and a Post
model and define a relationship between them:
We can then query the database to get all the posts for a user:
Updating and Deleting Data
We can also update and delete data from the database using SQLAlchemy. For example, we can update a post with a specific id:
We can also delete a post with a specific id:
Using SQLAlchemy to setup the database
We can also use SQLAlchemy to create the database and tables for us. We can use the create_all
method to create the tables. If the tables already exist, this method will not do anything.