Postgresql docker-python playground

.env

POSTGRESQL_VERSION=11.0
PYTHON_VERSION=3.7
PYLIBS=psycopg2-binary sqlalchemy ipython

POSTGRES_DB=debugging
POSTGRES_HOST=docker-postgresql
POSTGRES_ROOT_USER=postgres
POSTGRES_USER=docker
POSTGRES_PASSWORD=verylongpassword
POSTGRES_PORT=5432

README.md

testing postgresql

Using latest ipython with docker to correctly access a postgresql db from another container.

quick start

############
## SHELL 1
docker-compose up

############
## SHELL 2
# wait for pip install to complete
docker-compose exec app ipython
# ... do you python code here ...
# (e.g. see test.ipy)

############
## SHELL 3

## debug db:
# docker-compose exec db psql -U postgres

# verify size increasing
docker-compose exec db ash
du -sh /var/lib/postgresql/data/base

############
## END

docker-compose down -v

docker-compose.yml

version: '3'

networks:
  mynet:

services:
  db:
    image: postgres:${POSTGRESQL_VERSION}-alpine
    environment:
      CUSTOM_PSQL_DB: ${POSTGRES_DB}
      CUSTOM_PSQL_ROOT_USER: ${POSTGRES_ROOT_USER}
      CUSTOM_PSQL_USER: ${POSTGRES_USER}
      CUSTOM_PSQL_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - ./psql_init.sh:/docker-entrypoint-initdb.d/init.sh:ro
    networks:
      mynet:
        aliases:
          - ${POSTGRES_HOST}
  app:
    image: python:${PYTHON_VERSION}
    entrypoint: /bin/bash
    command: -c "pip install --upgrade ${PYLIBS} && echo ready && sleep infinity"
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_HOST: ${POSTGRES_HOST}
      POSTGRES_PORT: ${POSTGRES_PORT}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    networks:
      mynet:
    volumes:
      - ./data:/data

psql_init.sh

psql -v ON_ERROR_STOP=1 --username "$CUSTOM_PSQL_ROOT_USER" << EOSQL
CREATE DATABASE $CUSTOM_PSQL_DB;
CREATE USER $CUSTOM_PSQL_USER WITH PASSWORD '$CUSTOM_PSQL_PASSWORD';
GRANT ALL PRIVILEGES ON DATABASE $CUSTOM_PSQL_DB TO $CUSTOM_PSQL_USER;
EOSQL

test.ipy

import sqlalchemy as db

##########
envs = %env  # noqa
credentials = f"{envs.get('POSTGRES_USER')}:{envs.get('POSTGRES_PASSWORD')}"
server = f"{envs.get('POSTGRES_HOST')}:{envs.get('POSTGRES_PORT')}"
dbname = envs.get('POSTGRES_DB')
url = f"postgresql://{credentials}@{server}/{dbname}"

##########
con = db.create_engine(url, client_encoding='utf8')
meta = db.MetaData(bind=con)
db.MetaData.reflect(meta)

##########
table_name = 'mytable'
mytable = db.Table(
    table_name, meta,
    db.Column('myid', db.Integer, primary_key=True),
    # db.Column('name', db.String, primary_key=True),
    db.Column('text', db.String)
)
meta.create_all(con)
print(mytable)

##########
with open('/data/lorem.txt') as f:
    content = f.read()

for i in range(1, 100):
    sql = mytable.insert().values(text=content)
    con.execute(sql)