Connect to a Postgres SQL database with a Python Flask app' on Windows 11

Hi 👋, we are going to look in this post at how to connect to a Postgres database with a Python Flask app' on Windows 11.

pre requisites

• having Python3 installed on your Windows machine and set in the PATH instead of Python 2
• to verify your install, just search for Python 3 in your Windows start menu search bar

1. install PostgresSQL on Windows 11

• head to the downloads page of PostgreSQL and grab the Windows installer
• execute the installer using the defaults
• you should be prompted to pick a password for the default super account of your Postgres install, which is postgres in this example I will be using root (which is a very bad password)
• also you should be asked which port to use with Postgres, the default 5432 is fine as well
• when Postgres SQL install is finished, you dont need to run StackBuilder for additional plugins
• you should now be able to search for the psql tool in your Windows start menu search bar, this is a shell CLI that will allow you to interact with your Postgres database
• on launching the shell, you can choose which server, database, port, and user you want to use for the shell connection you just opened; these fields are pre populated with the values from your fresh install, you'll just need to input the password you have selected in the previous steps
• now you should see =>

2. list databases

• the psql offers handy meta commands to run on your database server, for instance l, which will list all your available databases
• among the databases outputted, you should see postgres and also template0 and template1; template databases are actually used when you run a CREATE DATABASE command, as they will be copied copied to create your new database but, for now, we are going to stick with the default postgres one

3. create and run a Flask application

Flask is a micro-framework with only the bare minimum dependencies required to build a web application, this minimum includes:

• controlers
• cookies and sessions management
• routing
• web development server
• a CLI

Create a new project using your favorite IDE, and in the root of this project, an app.py file, let's put some starter code in there for our Flask application:

from flask import Flask

 # creates an application that is named after the name of the file
app = Flask(__name__)

@app.route('/')
def index():
   return "it works"

# if running this module as a standalone program (cf. command in the Python Dockerfile)
if __name__ == "__main__":
   app.run(host="0.0.0.0")

This simply says that the route / of our Flask application on localhost shoud display some text. But for this to work, you need to have Flask installed as a module using pip.

You can do this by opening a CMD shell and hitting pip install Flask.
Now, from the CMD shell you've just opened, you can cd in whatever directory you created for your project.

Your Flask application needs to be aware of how to load the application, e.g. what is its entry point; to specify that, run set FLASK_APP=app.py.
If you want to have a better developer experience, you can also hit set FLASK_DEBUG=True.
You are now ready to run the application with a python -m flask run --host=0.0.0.0

4. wire the Flask app' to your database server

We'll use psycopg2, which is the most popular PostgresSQL adapter in the Python world. First let's install it with a pip install psycopg2 in your CMD.

Now we can update our app.py code like so =>

from flask import Flask
import psycopg2

 # creates an application that is named after the name of the file
app = Flask(__name__)

@app.route('/')
def index():
   conn = psycopg2.connect("postgresql://postgres:root@localhost:5432/postgres")
   return 'it works'

# if running this module as a standalone program (cf. command in the Python Dockerfile)
if __name__ == "__main__":
   app.run(host="0.0.0.0")

The string postgresql://postgres:root@localhost:5432/postgres is the data source name for our database:

• postgresql://, the connection scheme
• postgres:root, user + password (we use the default postgres user here)
• @localhost:5432, the hostname and its port
• /postgres, which database we connect to (again, the default one)

You can check that you connected successfully by going to localhost:5000 in your browser; for instance, put another value as the password in the DSN and you'll see an error message next time you reload the page.

5. wrapping it up + next steps

Now, what we did here is a very basic setup to get you starting fiddling with Flask in a Windows environment. You are now able to run SQL queries from within your web app' with, for instance =>

# Open a cursor to perform database operations
cur = conn.cursor()

# Execute a query
cur.execute("SELECT * FROM my_data")

# Retrieve query results
records = cur.fetchall()

You now have the bare minimum setup to create web applications that are backed by data :)

Still, once you familiarize with this, if you want to go further, I advise you for your next steps to:

• use Windows WSL to be able to run more commonly used Linux commands during your development process
• learn how to Dockerize your Flask app'
• use an ORM like SQLAlchemy to interact with the database from within your web app'

Well, that's it, see you around !