Connect Databases to a Flask App With SQLAlchemy ORM
Databases & ORMs
Storing information in databases is a core part of web applications. As a micro-framework, Flask does not come bundled with a solution for databases which offers us the freedom to choose the tool that is best suited for the application we are building.
This is in contrast to larger frameworks like Django where the framework comes with its own built in ORM and it's difficult to bring your own that's best suited for your use case.
Many software as a service applications are best served by a relational database. A relational database allows us to efficiently model mappings, such as a user having many purchases or a store having many products. Commonly used relational databases include MySQL and Postgres.
Using an ORM
ORMs (Object Relational Mapper) provides us with an interface to interact with the database as if the records were Python objects.
For example, a record in the Users table would be an instance of the User class and fields of the record (like name could be accessed like a property of the object user.name)
ORMs also spare us the trouble of having to write SQL queries by hand and provide methods to generate and run queries. Instead of having to write a query like this
select * from users where id=30
we can write something more "Pythonic" using the User class thanks to an ORM.
users = User.query.filter_by(id=30)
This interface also allows the ORM to manage the details of figuring out how to format a query for a specific type of database (which can be useful if you use a different type of database locally and in production)
SQLAlchemy
SQLAlchemy is the ORM of choice for Flask applications that use relational databases. The code base is mature and has a broad range of support for databases. If you are coming from the world of Django or other ORMs, Armin Ronacher (the creator of Flask) has written about how SQLAlchemy takes a different approach, but why ultimately it is the ORM of choice for most Flask developers.
"SQLAlchemy in general just has a much larger featureset and it's the only ORM for Python which allows you to take full advantage of your database and does not stand in your way. It exposes all features of your underlying database if you want and can be heavily fine tuned. ... [The] simple cases [are] not where SQLAlchemy shines. It's the more complex situations which you can't do at all in Django that work nicely in SQLAlchemy.
As you learn SQLAlchemy, you might wonder why it's a little more verbose or require a bit more code than other ORMs you have seen, generally speaking it's usually because of a design choice to require explicitly specifying the configuration or actions you'd like to perform on the database. The Zen of Python, which summarizes the guiding principles of the Python programming language, encourages this with the second line Explicit is better than implicit.
Configuring SQLAlchemy
The most important setting for SQLAlchemy is the one that specifies how find the database and how to connect to it. We can specify the connection details by adding the database connection URI to the Flask configuration as SQLALCHEMY_DATABASE_URI.
If we were connecting to a MySQL database that was listening on our local machine, the connection url would look something like this
mysql://user:password@localhost:5432/dev_db
For our application, we'll be using a simple local database called sqlite in the development environment.
note
What is SQLite?
SQLite offers a zero-configuration, transactional SQL database engine that is backed by a single file. It's something that we can use for testing and development for the simplicity but it is not designed to be used in production for most web applications.
To point SQLAlchemy to use SQLite, we need to give it a reference to a file to use.
In our config.py code, we should point SQLite to the a file for each of the dev and test database. We want to set up a different database for the test environment because when we reset our database to run tests from scratch, we don't want to reset our development data as well.
This lesson preview is part of the Fullstack Flask: Build a Complete SaaS App with Flask course and can be unlocked immediately with a single-time purchase. Already have access to this course? Log in here.
Get unlimited access to Fullstack Flask: Build a Complete SaaS App with Flask with a single-time purchase.
[00:00 - 00:13] So the most important configuration setting when it comes to SQL upcoming is telling it where our database lives. So going into our development configuration, you can go ahead and set a default value here.
[00:14 - 00:33] So this is a connection string that we can specify how to talk to our database. So if we were using something like MySQL, we would have to specify the user, the password, at the host name and the port as well as the database.
[00:34 - 00:41] But we're not going to be using SQL MySQL here. Instead, what we're going to be using is something called SQL Lite.
[00:42 - 01:01] And SQL Lite is a zero configuration, transactional SQL database engine that allows us to work with a single file and doesn't require us to install much. Python by default comes with a way to talk to SQL Lite databases.
[01:02 - 01:16] And it's something that we can use for testing and development because it's simple and easy to be recreated. It's not something that we want to use in production for a variety of reasons, but it's great for our test and local environment.
[01:17 - 01:32] In order to do that, what we're going to do is we're going to specify the connection URL here in config.py. And what we want to tell SQL Lite to do is that we wanted to connect to a database here called dev.db.
[01:33 - 01:42] We're going to tell it to connect within the root folder of the application. So once we run our application, we'll see a new file here called dev.db.
[01:43 - 01:58] Another thing we want to do is configure a variable here called SQL.acme echo. And what that does is it outputs the SQL queries that SQL is doing into our terminal.
[01:59 - 02:07] Okay, so this is a good, good start. What I'm going to go ahead and do is I'm also going to paste this into our test configuration as well.
[02:08 - 02:12] But I'm going to call this one test.db. Great.
[02:13 - 02:20] Now we need to actually install SQL company, which is a Python library. So we're going to go over to requirements.txt and add that in there.
[02:21 - 02:35] What we're going to do is we're going to add flask SQL.acme, which provides us with the easier way to work with SQL.acme in flask. This is something called a flask extension.
[02:36 - 02:44] Flasks extensions usually have their own documentation here with some details and information. And they also usually are open source.
[02:45 - 03:02] And so you can actually look at the code as well and see how it's being used and file issues and look at contributing to the library as well. Flasks SQL is going to provide us with the easier way to integrate SQL.acme into our application.
[03:03 - 03:15] Now going back in our terminal, we're going to have to run pip install dash R inside of our virtual environment and install our libraries. So just install SQL and plastic walk me there.
[03:16 - 03:44] Now going in our code here, we could initialize the flask SQL library by saying something like this from flask SQL.acme import SQL.acme. And then we could do something like our database is equal to SQL.acme and initialize it there.
[03:45 - 04:01] Now we've run into a problem through imports. So for example, if we imported some other application code that depended on importing DB, when Python evaluates our code, it goes top to bottom.
[04:02 - 04:14] So it's going to evaluate this line and then this line and then this line. And as it's evaluating this line, if that file imports DB, database is not actually defined here yet.
[04:15 - 04:26] And so we're going to run into an application import order conflict. So the way we solve that is going to be going to create another file here called extensions.py.
[04:27 - 04:39] And inside of extensions.py, we're actually going to initialize our application extensions here. So from flask SQL.acme import SQL.acme.
[04:40 - 04:48] And all we're going to do inside of extensions.py is instantiate our application extensions. Nothing else.
[04:49 - 05:06] And that makes it so that this is a safe import for any part of our application to do. So instead of doing this, what we're going to do is we're going to say from yum road.extensions import DB.
[05:07 - 05:11] Great. Now that we've imported the extension, we also need to pass in our app.
[05:12 - 05:23] And this is generally true for all flask extensions. Either you're going to pass in your app when you construct it or as part of initialization.
[05:24 - 05:38] So the way we do that is it falls in it app method call. Next up is we're going to have to create some models to tell SQL.acme how to work with our database schema.
[05:39 - 05:48] To do this, we're going to create a file called models.py. Since we only have a few models right now, let's find and put it into one file.
[05:49 - 05:59] You can also organize modules like a module and create one file for every model that you define. Let's go ahead and define a product model.
[06:00 - 06:15] So the way we're going to do that is we're going to start by importing DB from yumroad.extensions. And within DB, that gives us a base class called DB.model.
[06:16 - 06:26] And that's how we're going to extend all of our models as well. So if we're going to call this our model, we can have it import from DB.model.
[06:27 - 06:38] And what we're going to do is we're going to define our columns here as specific fields that SQL.acme is looking for. We're going to set them equal to class level variables.
[06:39 - 06:49] So for example, we can say our ID field is going to be called is going to be DB .column. And then we're going to specify a type here.
[06:50 - 06:57] So we're going to say this is going to be DB.integer. We're also going to say that this is going to be a primary key.
[06:58 - 07:05] All right, let's create the name of our model. So one thing we're going to definitely have to store in yumroad is products.
[07:06 - 07:12] So let's go and create a product model there. Alongside product, we're going to have to create a name.
[07:13 - 07:21] And the name can be DB.column.db.string. And we can specify a specific length.
[07:22 - 07:28] In some databases, you don't want to specify string length by default. But we're going to go and do that here.
[07:29 - 07:40] And we're going to mark that this is a non-level field. So that means every record is going to need to have a name of some kind.
[07:41 - 07:51] And there are some other fields that we can do. First, let's go and check out the types of things that we can use as types in our columns.
[07:52 - 08:14] Here you can see a few of the most commonly used types like integer, boolean, These are all things that we can do for now. Let's go ahead and add another field to our codebase called description.
[08:15 - 08:26] And description is going to be a short description of the product. So we're going to make it 120 characters or so.
[08:27 - 08:37] And we can set this field to a nullable if we want. Next up is we might want to add validations to our model.
[08:38 - 08:50] Validation allows us to ensure that specific things and constraints are held true. SQLAlchemy provides us with a way to do that in pure Python code.
[08:51 - 09:08] So what we can do is we can import a function here called validates and we can specify a function to validate the name. So we're going to pass in key and name to the function.
[09:09 - 09:21] And then we're going to say validates the name. And that means SQLAlchemy is going to check the name field when it saves records.
[09:22 - 09:36] So maybe the length of the name without white spaces is less than three characters. We're going to go and raise some kind of error here.
[09:37 - 09:48] So we're going to say needs to have a real name. Otherwise, we're just going to return the name.
[09:49 - 09:58] This means that anytime we construct a new product with less than three characters, it's going to raise an error. And otherwise, it's just going to continue.
[09:59 - 10:15] The next thing we're going to do is actually create our database and write records to the product model that we just created. In order to do that, we're going to start an interactive flash session where we can type Python code within our application.
[10:16 - 10:31] The way we're going to launch this is by running flash shell. And it's using the environment variables I've already set for flash end as development and flash gap is equal to yumroad, colon create app.
[10:32 - 10:50] Now, now that I've initialized a flask shell, what I can do is I want to run a method that SQLAlchemy provides that allows us to create all of the tables. So the way we're going to do that is first off, we're going to import the SQLAl chemy extension.
[10:51 - 11:07] So we're going to import DB, then we're also going to import the model that we care about. Okay, now that we've imported the model, what we're going to do is run DB.
[11:08 - 11:21] createAll. Okay, and you can see exactly what SQLAlchemy did here. It ran this command called createTableProduct with all of our fields that we specified in our model.
[11:22 - 11:37] So you might be wondering how did SQLAlchemy figure that out? And the answer is that whatever class inherits from the base model that we used in our example, this was DB.model here.
[11:38 - 11:52] SQLAlchemy registers that as a table that might need to be created. And if the class is never evaluated because it was never imported, for example, SQLAlchemy would be unaware of the existence of those models.
[11:53 - 12:06] That's the reason why we ran the import of the product model first. At this point, we can see that a database file gets created in here.
[12:07 - 12:16] So we can see database.db. Now if we look at it, it's a binary file in SQLite format.
[12:17 - 12:25] Going back to our terminal, we can now do things like inserting and editing records, which we're going to cover in the next video.