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 usingroot
(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 instancel
, which will list all your available databases - among the databases outputted, you should see
postgres
and alsotemplate0
andtemplate1
; template databases are actually used when you run aCREATE DATABASE
command, as they will be copied copied to create your new database but, for now, we are going to stick with the defaultpostgres
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 defaultpostgres
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 !
Top comments (0)