DEV Community

Cover image for Python CRUD Rest API using Flask, SQLAlchemy, Postgres, Docker, Docker Compose

Python CRUD Rest API using Flask, SQLAlchemy, Postgres, Docker, Docker Compose

Francesco Ciulla on February 19, 2023

Let's create a CRUD Rest API in Python, using: Flask (Python web framework) SQLAlchemy (ORM) Postgres (database) Docker (containerization) Docker...
Collapse
 
ndevadas profile image
ndevadas

This is a great article.

In my code I am using the connection like the following and using sql statements in api's

conn = psycopg2.connect(host="0.0.0.0",
                        database="lakedistrict",
                        user="root",
                        password="root1!")
return conn
Enter fullscreen mode Exit fullscreen mode

but when I run the app python code I am getting following error: Any suggestion? Thank you.

Error:

psycopg2.OperationalError: connection to server at "0.0.0.0", port 5432 failed: Connection refused

Collapse
 
francescoxx profile image
Francesco Ciulla

are you running this in a container or without Docker?

Collapse
 
ndevadas profile image
ndevadas

Outside container this code work fine. When I combine this in the container with postgress I get the error.

The compose file:

version: '3'

services:
postgres:
image: postgres:13
restart: always
environment:
POSTGRES_USER: root
POSTGRES_PASSWORD: root1!
POSTGRES_DB: lakedistrict

ports:
  - "5432:5432"
volumes:
  - postgres_data:/var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode

pgadmin:
image: dpage/pgadmin4
restart: always
environment:
PGADMIN_DEFAULT_EMAIL: pgadmin4@pgadmin.org
PGADMIN_DEFAULT_PASSWORD: admin
PGADMIN_LISTEN_PORT: 80
ports:
- "9090:80"
depends_on:
- postgres
links:
- postgres

app:
build: .
ports:
- 8100:8200
depends_on:
- postgres
volumes:
- ./static/css:/static/css
- ./static/data:/static/data
- ./static/js:/static/js
- ./static/json:/static/json
- ./static/svg:/static/svg
- ./templates:/templates

volumes:
postgres_data:

THANK YOU

Thread Thread
 
ndevadas profile image
ndevadas

Also, forgot to mention, I downloaded your code and dropped them in to containers and it worked fine. My issue is I am creating some dynamic full text search in the API search function and needed some flexibility. So I avoided using SQLAlchemy.

Thread Thread
 
francescoxx profile image
Francesco Ciulla

I cna't see where you define the user and password for the app. you defined them in the postgres volume so you should either use a DATABASE_URL environment variable or pass them in some ways.

Thread Thread
 
ndevadas profile image
ndevadas

I added that in the app.py code like so:

def db_connection():
conn = None
while not conn:
try:
conn = psycopg2.connect(host="0.0.0.0",
database="lakedistrict",
user="root",
password="root1!")

        print("Database connection successful")

    except BaseException as e:
        print(e)
        time.sleep(5)

return conn
Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
francescoxx profile image
Francesco Ciulla

change host="0.0.0.0" to host="db" and try again

Thread Thread
 
ndevadas profile image
ndevadas

That worked Francesco. I really appreciate. Thank you so much.

Thread Thread
 
francescoxx profile image
Francesco Ciulla

I knew it! I say it clearly here in the video but you had a slighlt different setup (basically hardcoding the values instead of passing them using docker compose)

https://youtu.be/njNXTM6L0wc?si=JijupWLhY57-D6aB&t=812)

Thread Thread
 
ndevadas profile image
ndevadas

I sent a message yesterday not sure you saw that.

Yes, when I changed host="0.0.0.0" to host="postgres" the container system worked as expected.

I really appreciate your help and Thank you very much.

Thread Thread
 
francescoxx profile image
Francesco Ciulla

ye saw it, you are welcome!

Collapse
 
scottlarsen profile image
Scott Larsen

Excellent tutorial so far, a few notes for those trying to follow (or for updating the article). For those getting 500 errors when trying to add a user via Postman, in the Postman POST Request, under Headers, set one with the key as Content-Type and value as application/json. And a tiny note that when testing the database with TablePlus, all of my fields turned green, which I'm guessing means success (I didn't get the text notice shown in the article that the test was successful).

Collapse
 
francescoxx profile image
Francesco Ciulla

This is a great point!

Collapse
 
krishnaa192 profile image
krishnaa192

Is it good method to add all code in one file??Is it not preferred method add models and routes in different file,like Django?

Collapse
 
francescoxx profile image
Francesco Ciulla

it depends on the complexity of the project. for bigger ones, for sure! in this case, I go with the simpliciy of having less files and I focus on the dockerization of the project

Collapse
 
sumitsaurabh927 profile image
Sumit Saurabh

Very well explained Francesco!

Collapse
 
francescoxx profile image
Francesco Ciulla

Thank you Sumit!

Collapse
 
chrisgreening profile image
Chris Greening

Cheers thanks for sharing Francesco - this is a super detailed tutorial I love it!

Gotta love me some Flask 😎 definitely one of my fave Python frameworks

Collapse
 
francescoxx profile image
Francesco Ciulla

you are welcome Chris!

Collapse
 
fortunatus profile image
Fortunatus Adegoke

Nice 👍 step by step explanation on the API CRUD using flask.
Really like it.

Collapse
 
francescoxx profile image
Francesco Ciulla

thank you! more are coming!