Made withFlaskClip

Flask Tutorial - 3. Setting up Flask with SQLAlchemy & PostgreSQL

C

Chris Hawkes

Creator on the Flask channel

Watch on YouTube ↗

123K viewsJan 13, 201611:33 runtime

Learn how to connect your Flask application to a production-ready database by setting up Flask-SQLAlchemy with PostgreSQL. This tutorial explains the role of an Object-Relational Mapper (ORM) and guides you through the entire process, from installing the necessary libraries to…

Chapters

#flask#clip#backend

Why Use an ORM?

An Object-Relational Mapper (ORM) acts as a database abstraction layer, simplifying tasks like creating tables, managing relationships, and querying data.
It's especially helpful for developers who aren't database experts.
Flask-SQLAlchemy is the preferred ORM in the Flask ecosystem, providing a bridge to the powerful SQLAlchemy library.
For this project, PostgreSQL is chosen as the database over SQLite because it's a more robust, production-ready solution.

Core Setup Steps

Installation: Install the Flask-SQLAlchemy library using pip.
```
pip install flask-sqlalchemy
```

Initialization: Import and initialize SQLAlchemy within your Flask app.

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# ... configurations ...
db = SQLAlchemy(app)

Important
The db object must be initialized after the app object is created and before your models are defined.

Database Configuration

The connection to the database is configured via the app.config dictionary.
The key for the connection string is SQLALCHEMY_DATABASE_URI.
The format of the URI depends on the database engine. For PostgreSQL, it is: postgresql://user:password@host:port/dbname

Example Connection String:

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:test123@localhost/flask_movie'

Note
This example uses credentials directly in the code. For production, these should be loaded securely from environment variables.

Defining Models and Creating Schema

Database tables are defined as Python classes that inherit from db.Model.
Class attributes, defined using db.Column, map to table columns.

Example User Model:

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

To create the actual tables in the database, use the db.create_all() method from within a Python shell.
```
from app import db
db.create_all()
```

Troubleshooting: The `psycopg2` Driver

To connect to a PostgreSQL database, Python requires a specific driver. psycopg2 is the most common one.
If you get an error like ModuleNotFoundError: No module named 'psycopg2', you need to install this library.
On Windows, a standard pip install psycopg2 might fail if build tools are missing.
Solution: Download the pre-compiled psycopg2 wheel (.whl) file for your specific Python version and system architecture. Then, install it directly using pip:
```
pip install psycopg2-2.6.1-cp35-none-win32.whl
```

Why Use an ORM with Flask?

Hey guys, what's up? So, one of the things with Flask is you really need a database abstraction layer, something that allows you to manage your database much easier. And those are typically done using something called an object relational mapper, and it's something that Django has built into it, and it's really helpful, especially for noobs that aren't very good when it comes to database manipulation, like creating tables, setting things like to auto increment, being able to query those things in a way that makes sense without slamming your database.

So, for that reason, a lot of people use these ORMs. The preferred one from what I've seen in the Flask community is SQL Alchemy. So, Flask has a Flask SQL Alchemy project that we we're going to be looking to install in this application. I plan on using Postgres with our new application here. So, I have Postgres installed in this myAdmin of MySQL... Um, it's Postgres's GUI to make things a lot easier to deal with. And I have a video already on my channel, and it's titled installing Postgres for Windows, and in there it shows you how to actually install it, so you can have this tool.

But I mean, whenever you go into production, it's probably best just to kind of get that out of the way now. I mean, don't start with SQL because it might be easier to use something like SQLite, which comes with Python, but SQLite isn't really going to be your answer in production. So, it's best to just take that plunge now and try to get involved in a production type of database. Postgres is very popular in the Django community these days. You can also use MySQL. Personally, I'm just going to use Postgres for this. There's been a major push, I think, in the Python community to continue to gravitate towards Postgres. It has a lot of really good JSON support coming down the line and it just seems like a really good option. So, that's why I'm using it. But you don't necessarily have to. But Flask SQL Alchemy is just going to be how we interface to our database whether you choose Postgres or MySQL is going to be up to you.

Installing and Initializing Flask-SQLAlchemy

All right, so that being said, we need to go ahead and install it. So, let's go back to our command line and we're going to run and let's kill the server by pressing control C and we're going to run pip install flask-sqlalchemy.

All right, so that installed okay. That's good. Now, let's go ahead and see if we can import it into the project that we just created—this simple little project. Underneath the flask import statement, let's go ahead and say from flask_sqlalchemy import SQLAlchemy. And now we need to create our database object. We're going to say DB = SQLAlchemy() and then we're going to use SQL Alchemy that we just imported.

And then under the app.run command... what we actually want to do is we want to pass the app as an argument. So, DB needs to be defined after app. And here we're just going to pass in app.

Configuring the PostgreSQL Database Connection

Now, one of the next step that you're going to do is going to be specific to your setup, to your database. But let's go ahead and, um, I'm going to copy in this code so I don't make any mistakes. And what we're going to be doing is setting up the config. So, we have the SQLALCHEMY_DATABASE_URI, which is just a key value that we're setting. And each database has a different config string. So, the Postgres one that I'm going to be using is going to look like this.

Now, this is going to be the name of your database, and then you want to have your actual root user, whoever you decided that to be. So, when you installed Postgres, you actually created a user which you have to log into every time you want to gain access to the server. So, if I maximize this and I double-click, then it's going to ask you for the actual password. Now, if you notice my username is Postgres here, so that's what I decided to make the main user.

I already have a few databases in here. So, we're going to go ahead and create a database by saying New Database, and then we'll name it flask_movie. All right. So, now flask_movie is created. So, if I went in here, we don't have any schemas or anything. It's all just empty. There's no tables inside of it. But at least we have a database now set up in Postgres.

So, I would actually want to change this to the name of my user, which like I said is Postgres, and then this would be my password. And then localhost is fine because that's where it's currently deployed. And then we just give it the name of the database. So for demonstration purposes, since this is just a locally running database that I don't care about, I've actually changed my password to test123, so that way you guys can see everything that I'm doing. And if I type in test123 here, it all works. So, this should work and connect to our Postgres just fine and to the newly created database that we had just created using our pgAdmin tool.

Defining the User Data Model

All right, so now that we've done that, let's go ahead and create our database table or our schema. And we do that by by creating a class, and we call the class whatever we want our table name to be. So I'm going to use the documentation from online so that way I don't mess this up, but we're going to have a user table which, you know, we definitely want a user, so we have users to our site and blah blah blah. So, we definitely want to have that.

And you can see that we create an ID, and we give it a db.Column. It's an integer. We set a primary key, and that should auto increment for every new user that's added. The ID will add one to itself, so that way it's always unique. And a primary key is always considered, you know, it's always going to be unique, so you don't have to specify that it is unique like we do down here. Now, we obviously want usernames to be unique. We don't need multiples of those. And then we would want emails to be unique as well. That way you don't have the same jackass registering to your site 100 times to try to, you know, manipulate your data. So, this is all specific to SQL Alchemy and how this works.

Creating the Database Schema and Encountering an Error

So we're just going to go ahead and run our code here. And in order for this database table to actually be created, what we're going to do is go back to our command line, and let's run the Python interpreter. And what we need to do is say from and then the name of our application, which is called FlaskMovie. We're going to say import db.

Is Flask Movie right? Or no, it's from app. I'm sorry. It's from app. All right. Now, this is just giving us some sort of a overhead warning. We don't care about that for right now. So, just disregard that. But you can see the name of our app is app because that's what we named it under. The folder is just Flask Movie.

So, now we just need to say db.create_all(). Okay, it looks like it's actually missing the Python, the Python library tool that we actually need in order for this to work.

Troubleshooting: Installing psycopg2 for PostgreSQL

So, let me see. So, if you're using Windows like I am, what we need to do is we need to find the psy, whatever the crap that thing's called. And you need to go to this URL right here, which is an EDU site that actually... So, it's psycopg. All right. So, there it is. And I'm using Python 3.5. So, you would actually want whether you're using the 32-bit version or the 64. I'm actually using the 32. So, you can see I've already downloaded it.

What I'm going to do is copy this wheel file. And then we need to put it into the name of our file. So, Flask Movie, go to the virtual environment, go to lib, site-packages, and then we're going to paste it inside of there. And now I want to CD into that drive. So, go into site-packages. And now I should just be able to say pip install and then the long name that is psycopg2-2.6.1-cp35-none-win32.whl for wheel.

In case you've never used a pip wheel file, wheel is just the alternate version and how these packages are contained, but you can still use pip to install them. So, you just need to go wherever you have the wheel file located in your directory and run that.

Verifying the Table Creation

All right, so let's go back and try to do that again. db.create_all(). All right, so that's cool. So, that should work.

And let's go into our pgAdmin here and take a look at our flask_movie database. Go into schemas, go into public, go into tables. You can see before we had zero, now we have this new user table that we just defined. And if we go in there, you can see that was, see, there's three columns. So, we have ID, username, and email. Those are the three columns that we just created right there. If I wanted to add, I could go ahead and just say like, uh, insert. I could do that here, but we're actually not going to do that right now in this video.

So, this video was simply just how we get our database Flask-SQLAlchemy set up with Python 3.5, Flask, and everything else that we're doing in this tutorial. All right, guys, thanks for watching. Bye.