DEV Community

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...

Read full post

ndevadas • Jan 3

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

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

Francesco Ciulla • Jan 4

are you running this in a container or without Docker?

ndevadas • Jan 4

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

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

ndevadas • Jan 4

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.

Francesco Ciulla • Jan 5

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.

ndevadas • Jan 5

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

Francesco Ciulla • Jan 5

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

ndevadas • Jan 5

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

Francesco Ciulla • Jan 5

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)

ndevadas • Jan 6

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.

Francesco Ciulla • Jan 7

ye saw it, you are welcome!

Scott Larsen • Mar 5 '23

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).

Francesco Ciulla • Mar 27 '23

This is a great point!

krishnaa192 • Feb 21

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?

Francesco Ciulla • Feb 21

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