Testing-postgresql+pg8000

.gitignore

# Local .terraform directories
**/.terraform/*

# .tfstate files
*.tfstate
*.tfstate.*

# Crash log files
crash.log

# Ignore any .tfvars files that are generated automatically for each Terraform run. Most
# .tfvars files are managed as part of configuration and so should be included in
# version control.
#
# example.tfvars

# Ignore override files as they are usually used to override resources locally and so
# are not checked in
override.tf
override.tf.json
*_override.tf
*_override.tf.json

# Include override files you do wish to add to version control using negated pattern
#
# !example_override.tf

# Include tfplan files to ignore the plan output of command: terraform plan -out=tfplan
# example: *tfplan*
notas.txt
backend.tf
.terraform.lock.hcl
tf.plan

# Byte-compiled / optimized / DLL files
__pycache__/
*.py[cod]
*$py.class

# C extensions
*.so

# Distribution / packaging
.Python
build/
develop-eggs/
dist/
downloads/
eggs/
.eggs/
lib/
lib64/
parts/
sdist/
var/
wheels/
share/python-wheels/
*.egg-info/
.installed.cfg
*.egg
MANIFEST

# PyInstaller
#  Usually these files are written by a python script from a template
#  before PyInstaller builds the exe, so as to inject date/other infos into it.
*.manifest
*.spec

# Installer logs
pip-log.txt
pip-delete-this-directory.txt

# Unit test / coverage reports
htmlcov/
.tox/
.nox/
.coverage
.coverage.*
.cache
nosetests.xml
coverage.xml
*.cover
*.py,cover
.hypothesis/
.pytest_cache/
cover/

# Translations
*.mo
*.pot

# Django stuff:
*.log
local_settings.py
db.sqlite3
db.sqlite3-journal

# Flask stuff:
instance/
.webassets-cache

# Scrapy stuff:
.scrapy

# Sphinx documentation
docs/_build/

# PyBuilder
.pybuilder/
target/

# Jupyter Notebook
.ipynb_checkpoints

# IPython
profile_default/
ipython_config.py

# pyenv
#   For a library or package, you might want to ignore these files since the code is
#   intended to run in multiple environments; otherwise, check them in:
# .python-version

# pipenv
#   According to pypa/pipenv#598, it is recommended to include Pipfile.lock in version control.
#   However, in case of collaboration, if having platform-specific dependencies or dependencies
#   having no cross-platform support, pipenv may install dependencies that don't work, or not
#   install all needed dependencies.
#Pipfile.lock

# poetry
#   Similar to Pipfile.lock, it is generally recommended to include poetry.lock in version control.
#   This is especially recommended for binary packages to ensure reproducibility, and is more
#   commonly ignored for libraries.
#   https://python-poetry.org/docs/basic-usage/#commit-your-poetrylock-file-to-version-control
#poetry.lock

# PEP 582; used by e.g. github.com/David-OConnor/pyflow
__pypackages__/

# Celery stuff
celerybeat-schedule
celerybeat.pid

# SageMath parsed files
*.sage.py

# Environments
.env
.env.hmg
.venv
env/
venv/
ENV/
env.bak/
venv.bak/

# Spyder project settings
.spyderproject
.spyproject

# Rope project settings
.ropeproject

# mkdocs documentation
/site

# mypy
.mypy_cache/
.dmypy.json
dmypy.json

# Pyre type checker
.pyre/

# pytype static type analyzer
.pytype/

# Cython debug symbols
cython_debug/

# PyCharm
#  JetBrains specific template is maintained in a separate JetBrains.gitignore that can
#  be found at https://github.com/github/gitignore/blob/main/Global/JetBrains.gitignore
#  and can be added to the global gitignore or merged into this file.  For a more nuclear
#  option (not recommended) you can uncomment the following to ignore the entire idea folder.
#.idea/
builds

process.json
process.sh
.vscode/

docker-compose.yml

version: '3.5'

services:
  postgres:
    container_name: postgres
    image: postgres:13.4-alpine
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres}
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped

  job:
    build: ./
    environment:
      - name=value
      - JOBNAME=job
      - HOST_TARGET=postgres
      - USER_TARGET=postgres
      - PASS_TARGET=postgres
      - PORT_TARGET=5432
      - DB_TARGET=postgres
    networks:
      - postgres
    depends_on:
      - postgres

networks:
  postgres:

Dockerfile

FROM python:3.9.11-slim-buster

ARG JOBNAME
ARG HOST_TARGET
ARG USER_TARGET
ARG PASS_TARGET
ARG PORT_TARGET
ARG DB_TARGET
ARG DB_TABLE_TARGET

ENV JOBNAME=$JOBNAME
ENV HOST_TARGET=$HOST_TARGET
ENV USER_TARGET=$USER_TARGET
ENV PASS_TARGET=$PASS_TARGET
ENV PORT_TARGET=$PORT_TARGET
ENV DB_TARGET=$DB_TARGET
ENV DB_TABLE_TARGET=$DB_TABLE_TARGET

WORKDIR /src

# Create a group and user
RUN useradd -ms /bin/bash job

# Copy the source code to the container
COPY *.py /src/
COPY *.sql /src/
COPY requirements.txt /src/

# Install requirements
RUN pip install --no-cache-dir -r /src/requirements.txt && \
    # Change the owner of the source code to the user job
    chown -R job: /src

# Run the container with user job
USER job

ENTRYPOINT ["python", "main.py"]

main.py

# python3.9 -m venv venv && source venv/bin/activate && pip install -r requirements.txt

import os
import sys

from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine

import logging
logger = logging.getLogger()
# set logging level from environment variable or default to INFO
logger.setLevel(os.environ.get('LOG_LEVEL', logging.INFO))


class Repository():

    def __init__(self, host, user, password, port=5432):
        self.host = host
        self.user = user
        self.password = password
        self.port = port

    def __create_engine(self, database: str) -> Engine:
        try:
            logger.debug("Creating engine")
            str_engine = 'postgresql+pg8000://{0}:{1}@{2}:{3}/{4}'
            return create_engine(str_engine.format(
                                 self.user,
                                 self.password,
                                 self.host,
                                 self.port,
                                 database))
        except Exception as e:
            logger.error("Error in __create_engine: {}".format(e))
            raise e

    def execute(self, query, database) -> None:
        conn = self.__create_engine(database)
        try:
            logger.debug("Executing query: {}".format(query))
            # https://docs.sqlalchemy.org/en/14/core/connections.html#using-transactions
            with conn.begin() as connection:
                result = connection.execute(query)
            logger.info("record(s) affected: %s rows", result.rowcount)
            for row in result:
                logger.info("%s", row)
        except Exception as exception:
            logger.error("Exeception occured:{}".format(exception))
            raise exception


def main():
    logging.info("Starting")
    repository = Repository('postgres','postgres','postgres')
    repository.execute('select * from job','postgres')
    repository.execute('insert into job values (5); select * from job;','postgres')
    logging.info("Finished")


if __name__ == '__main__':
    formatter = logging.Formatter(
        '%(asctime)s - %(name)s - %(levelname)s - %(message)s')
    format_handler = logging.StreamHandler(sys.stdout)
    format_handler.setFormatter(formatter)
    logging.getLogger().addHandler(format_handler)
    main()

requirements.txt

pymysql==1.0.2
SQLAlchemy==1.4.32
pg8000==1.24.1

script.sql