How do I access postgresql within Docker with sqlalchemy?

5
votes

I'm trying to interact with a dockerized PostgreSQL server using SQLAlchemy. Something like:

engine = create_engine('postgresql://user:user_password@localhost:5432/database')
df.to_sql('table', engine)

Which gives me this error:

OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

Which suggests the Docker postgresql (which is running) isn't available at that port. I've tried adding -p 5432:5432 to my docker-compose exec without success. Any tips?

3
postgresqldockersqlalchemy

3 Answers

4
votes

As your flask app and Postgres images are not in the same docker container you cannot access the database via localhost !!

in your database URL replace localhost the name of Postgres Service in docker-compose/

engine = create_engine('postgresql://user:user_password@{}:5432/database'.format('service_name_of_postgres'))

kudos to this answer.

3
votes

Connecting SQLAlchemy with PostgreSQL on Docker

Hello, guys! Let me try to help you to connect a Flask Web App to a PostgreSQL database, both running on Docker.

Please read everything before trying to copy and paste code

First of all you should have installed these python modules:

  • SQLAlchemy
  • psycopg2

You should install them using pip.

psycopg2 is one of the PostgreSQL drivers needed to connect SQLAlchemy to your PostgreSQL database.

I'm showing you my docker-compose.yml file.

version: '3'
services:

  web:
     #YourWebServiceConfiguration
     #YourWebServiceConfiguration
     #YourWebServiceConfiguration

  db_postgres:
    image: postgres:latest
    container_name: postgres_db_container
    ports:
        - "5432:5432"
    volumes:
        - postgres_db_vol:/var/lib/postgresql/data

    environment:
        POSTGRES_USER: yourUserDBName
        POSTGRES_PASSWORD: yourUserDBPassword
        POSTGRES_DB: yourDBName

Now let's take a look at my python code! This is a test.py file.

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker


conn_url = 'postgresql+psycopg2://yourUserDBName:yourUserDBPassword@yourDBDockerContainerName/yourDBName'

engine = create_engine(conn_url)

db = scoped_session(sessionmaker(bind=engine))


query_rows = db.execute("SELECT * FROM anyTableName").fetchall()
for register in query_rows:
    print(f"{register.col_1_name}, {register.col_2_name}, ..., {register.col_n_name}")
    # Note that this Python way of printing is available in Python3 or more!!

If you want to learn more you can take a look at the following links.

0
votes

I had the same problem. After a few tries, it occurs that the problem is in the connection URL string given to the sqlalchemy.create_engine()

Firstly I had similarly as you:

db_URL = f"postgresql://{db_login}:{db_password}@localhost:5432/postgres"

Fixed one:

db_URL = f"postgresql+psycopg2://{db_login}:{db_password}@db:5432/postgres"

A bit dumb approach from my side doing two alterations at once, but I've checked it and looks like the problem was localhost. Changing server address to container_name stated in the docker_compose.yml should fix the problem with the connection.

db:
    image: postgres:12.7-alpine
    container_name: db