Periodically execute Python Script with Docker Compose + Daemon (cron)

Periodically execute Python Script with Docker Compose + Daemon (cron)

Introduction

`Although it is an article on Mac environment, the procedure is the same for Windows environment. Please read and try the environment-dependent part. ``

Purpose

Launch Daemon (cron) and Database (PostgreSQL) with Docker Compose and execute Python Script regularly.

After reading this article to the end, you will be able to:

Execution environment

Source code

I think that understanding will deepen if you read while actually following the implementation contents and source code. Please use it by all means.

GitHub

Related articles

• Docker Compose
• SQLAlchemy
• Alembic -Periodic execution of Python on Heroku Scheduler

System configuration

scenario

Save the cumulative number of daily Qiita accesses.

1. Launch Daemon (cron) and Database (PostgreSQL) with Docker Compose
2. Set the Daemon (cron) startup time to 23:00
3. Execute Python Script triggered by the startup of Daemon (cron)
4. Get Qiita information with Python Script and register it in PostgreSQL

Execution result

Docker

docker.sh

qiita=# SELECT * from qiita ORDER BY date DESC, page_views_count DESC;
 id |    date    |                                   title                                   | page_views_count | likes_count |                         url                          |         created_at
----+------------+---------------------------------------------------------------------------+------------------+-------------+------------------------------------------------------+----------------------------
  1 | 2020-09-10 |Get Salesforce data using REST API|            11571 |          23 | https://qiita.com/nsuhara/items/19cf8ec89b88fb3deb39 | 2020-09-10 12:48:23.512842
  2 | 2020-09-10 |Alexa skills in Python/Implement with Lambda|             4888 |          11 | https://qiita.com/nsuhara/items/5b19cfb5ffb897bd4cfd | 2020-09-10 12:48:23.513017
  3 | 2020-09-10 |Limit Salesforce attachments|             4611 |           6 | https://qiita.com/nsuhara/items/bd41c9ad946b8b832207 | 2020-09-10 12:48:23.51307
  4 | 2020-09-10 |Unit tests for Swift(Unit Test)how to make|             4214 |          12 | https://qiita.com/nsuhara/items/bc06c07ff30a5b78696d | 2020-09-10 12:48:23.51313
  5 | 2020-09-10 |IOS with Kony App Platform/Create an Android app|             3612 |           3 | https://qiita.com/nsuhara/items/c28d838492512850520c | 2020-09-10 12:48:23.513182
  6 | 2020-09-10 | Flask-SQLAlchemy +Create a web service in PostgreSQL|             2842 |           7 | https://qiita.com/nsuhara/items/fa5998c0b2f4fcefbed4 | 2020-09-10 12:48:23.513237
  7 | 2020-09-10 | Heroku + Selenium +Automate WEB process with Chrome|             2719 |          15 | https://qiita.com/nsuhara/items/76ae132734b7e2b352dd | 2020-09-10 12:48:23.513292
  8 | 2020-09-10 | Docker +Create a web service in Flask|             2368 |           7 | https://qiita.com/nsuhara/items/c7eff7fae3801f85b5cd | 2020-09-10 12:48:23.513343
  9 | 2020-09-10 |Periodically run Python on Heroku Scheduler|             2232 |           4 | https://qiita.com/nsuhara/items/fac20adb6b0a122a3709 | 2020-09-10 12:48:23.513393
 10 | 2020-09-10 | Messaging API + LIFF + Heroku + Flask +Create LINE BOT with Framework extension|             1913 |           8 | https://qiita.com/nsuhara/items/0c431913165e4af0f8f5 | 2020-09-10 12:48:23.513449
 11 | 2020-09-10 |Salesforce Lightning Data Service(LDS)learn about|             1636 |           4 | https://qiita.com/nsuhara/items/ecd77def7aa1f985efcc | 2020-09-10 12:48:23.513503
 12 | 2020-09-10 |Create a RESTful web service in Flask|             1571 |           5 | https://qiita.com/nsuhara/items/449835bc94f0fb3bbcbd | 2020-09-10 12:48:23.513553
 13 | 2020-09-10 | AWS-Lambda +AWS CSV data in Python-Write to S3|             1565 |           2 | https://qiita.com/nsuhara/items/b2bd1d2623bca0f767f8 | 2020-09-10 12:48:23.513604
 14 | 2020-09-10 |IOS created with Kony App Platform/Learn about coding Android apps|             1536 |           1 | https://qiita.com/nsuhara/items/bf0e8884a7efc3c55176 | 2020-09-10 12:48:23.513654
 15 | 2020-09-10 |From JSON to Word using Python(docx)To create|             1511 |           2 | https://qiita.com/nsuhara/items/3ba2fa7db38acd04f448 | 2020-09-10 12:48:23.513708
 16 | 2020-09-10 | AWS-Lambda +AWS with Python-RDS/Read PostgreSQL table|             1408 |           3 | https://qiita.com/nsuhara/items/dd780c2622258d10f961 | 2020-09-10 12:48:23.51376
 17 | 2020-09-10 | AWS-Lambda + Python +Perform web scraping regularly with Cron|             1342 |           5 | https://qiita.com/nsuhara/items/0d36600511fc162827f6 | 2020-09-10 12:48:23.513819
 18 | 2020-09-10 | Django + SQLAlchemy + SQLite3 /Create a web app in PostgreSQL|             1287 |           3 | https://qiita.com/nsuhara/items/4ab5366273082ee0aa73 | 2020-09-10 12:48:23.513874
 19 | 2020-09-10 |IOS created with Kony App Platform/Data link between Android app and Salesforce|             1088 |           1 | https://qiita.com/nsuhara/items/756120f1bddc6f8fe78b | 2020-09-10 12:48:23.513928
 20 | 2020-09-10 |IOS created with Kony App Platform/Learn about Auto Layout for Android apps|              898 |           0 | https://qiita.com/nsuhara/items/a52abd9861c51823ecec | 2020-09-10 12:48:23.513984
 21 | 2020-09-10 |AWS with Python-S3 signed(With a time limit)Generate URL|              723 |           2 | https://qiita.com/nsuhara/items/20160b080c2b30d57729 | 2020-09-10 12:48:23.514131
 22 | 2020-09-10 |Kong API Gateway GUI/Build Konga|              665 |           3 | https://qiita.com/nsuhara/items/a0de75e6767f98cc8fec | 2020-09-10 12:48:23.51419
 23 | 2020-09-10 |Create a remote control car with Raspberry Pi and Python|              637 |           2 | https://qiita.com/nsuhara/items/7970b5dfe95ea76c93d6 | 2020-09-10 12:48:23.514248
 24 | 2020-09-10 |Build Kong API Gateway|              535 |           1 | https://qiita.com/nsuhara/items/ad1d8fa1faad7940b5c1 | 2020-09-10 12:48:23.514297
 25 | 2020-09-10 |From setting up Raspberry Pi to installing Python environment|              529 |           0 | https://qiita.com/nsuhara/items/05a2b41d94ced1f54316 | 2020-09-10 12:48:23.514365
 26 | 2020-09-10 |LCD with Raspberry Pi and Python(16x2)Create a game|              424 |           0 | https://qiita.com/nsuhara/items/57105fd232feffbcd05c | 2020-09-10 12:48:23.514416
 27 | 2020-09-10 |Create a web surveillance camera with Raspberry Pi and OpenCV|              392 |           0 | https://qiita.com/nsuhara/items/37fcbc9d0e8209080032 | 2020-09-10 12:48:23.514466
 28 | 2020-09-10 | Flask-How to implement UPSERT in SQLAlchemy|              389 |           0 | https://qiita.com/nsuhara/items/86570f789093222252b1 | 2020-09-10 12:48:23.514518
 29 | 2020-09-10 | Qiita API +Get article visits and likes in Python|              332 |           0 | https://qiita.com/nsuhara/items/b27b84f0150c3f6534ec | 2020-09-10 12:48:23.514569
 30 | 2020-09-10 |How to insert CSV data into PostgreSQL at once|              145 |           0 | https://qiita.com/nsuhara/items/a1b75e0557ed134c5302 | 2020-09-10 12:48:23.514635
(30 rows)

Heroku

App configuration

tree.sh

/
├── Dockerfiles
│   ├── app
│   │   ├── Dockerfile
│   │   ├── crontab
│   │   ├── docker-compose.yml
│   │   ├── entrypoint.sh
│   │   └── qiita.sh
│   └── docker_compose_up.sh
└── app
     ├── __init__.py
     ├── alembic.ini
     ├── config
     │   └── qiita_access_token
     ├── main.py
     ├── migration
     │   ├── README
     │   ├── env.py
     │   ├── script.py.mako
     │   └── versions
     │       └── 62fb1bcf0a8a_create_table.py
     ├── model
     │   ├── __init__.py
     │   └── qiita.py
     └── requirements.txt

Hands-on

download

command.sh

~% git clone https://github.com/nsuhara/python-docker-cron.git -b master

Service start

command.sh

~% cd python-docker-cron/
~% sh Dockerfiles/docker_compose_up.sh

End of service

command.sh

~% Control Key + C

Python Script

tree.sh

/
└── app
     ├── main.py
     └── model
          ├── __init__.py
          └── qiita.py

Model creation

qiita.py

"""app/model/qiita.py
"""
from datetime import datetime, timezone

from sqlalchemy import Column
from sqlalchemy.dialects import postgresql as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Qiita(Base):
    """Qiita
    """
    __tablename__ = 'qiita'

    id = Column(db.INTEGER, primary_key=True, autoincrement=True)
    date = Column(db.DATE, nullable=False)
    title = Column(db.VARCHAR(255), nullable=False)
    page_views_count = Column(db.INTEGER, nullable=False)
    likes_count = Column(db.INTEGER, nullable=False)
    url = Column(db.VARCHAR(255), nullable=False)
    created_at = Column(db.TIMESTAMP, nullable=False)

    def __init__(self, date, title, page_views_count, likes_count, url):
        self.date = date
        self.title = title
        self.page_views_count = page_views_count
        self.likes_count = likes_count
        self.url = url
        self.created_at = datetime.now(timezone.utc)

    def to_dict(self):
        """to_dict
        """
        return {
            'id': self.id,
            'date': self.date,
            'title': self.title,
            'page_views_count': self.page_views_count,
            'likes_count': self.likes_count,
            'url': self.url,
            'created_at': self.created_at
        }

Main creation

main.py

"""app/main.py
"""
import os
import sys
from datetime import datetime

import requests
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from model.qiita import Qiita


def get(url):
    """get
    """
    qiita_access_token = os.getenv('QIITA_ACCESS_TOKEN', '')
    if qiita_access_token == '':
        print('Error: please set \'QIITA_ACCESS_TOKEN\' to environment variable.')
        sys.exit()

    headers = {
        'Content-Type': 'application/json',
        'Charset': 'utf-8',
        'Authorization': 'Bearer {}'.format(qiita_access_token)
    }
    res = requests.get(url=url, headers=headers)
    print('{}, {}'.format(res.status_code, res.url))
    return res


def get_id_list():
    """get_list
    """
    res = get(url='https://{url}?page={page}&per_page={per_page}'.format(**{
        'url': os.getenv('QIITA_URL_LIST'),
        'page': os.getenv('QIITA_PAGE'),
        'per_page': os.getenv('QIITA_PER_PAGE')
    }))
    return [item.get('id') for item in res.json()]


def get_item(qiita_id):
    """get_item
    """
    res = get(url='https://{url}/{id}'.format(**{
        'url': os.getenv('QIITA_URL_ITEM'),
        'id': qiita_id
    }))
    item = res.json()
    return {
        'page_views_count': item.get('page_views_count'),
        'likes_count': item.get('likes_count'),
        'title': item.get('title'),
        'url': item.get('url')
    }


def output_db(items):
    """output_db
    """
    engine = create_engine(
        os.getenv('SQLALCHEMY_DATABASE_URI', ''),
        echo=bool(int(os.getenv('SQLALCHEMY_ECHO', '0'))),
        pool_size=int(os.getenv("SQLALCHEMY_POOL_SIZE", '5')),
        max_overflow=int(os.getenv("SQLALCHEMY_MAX_OVERFLOW", '10')),
        pool_timeout=int(os.getenv("SQLALCHEMY_POOL_TIMEOUT", '30'))
    )
    create_session = sessionmaker(bind=engine)
    session = create_session()

    insert = list()
    date = datetime.now().date()

    session.query(Qiita).filter(Qiita.date == date).delete()

    for item in items:
        insert.append(Qiita(
            date=date,
            title=item.get('title'),
            page_views_count=item.get('page_views_count'),
            likes_count=item.get('likes_count'),
            url=item.get('url')
        ))

    session.add_all(insert)
    session.commit()


def output_log(items):
    """output_log
    """
    total_page_views_count = 0
    total_likes_count = 0

    print('-'*100)

    for item in items:
        total_page_views_count = total_page_views_count + \
            item.get('page_views_count')
        total_likes_count = total_likes_count+item.get('likes_count')

        print('page_views_count={}, likes_count={}, title={}, url={}'.format(
            str(item.get('page_views_count')).zfill(5),
            str(item.get('likes_count')).zfill(2),
            item.get('title'),
            item.get('url'))
        )

    print('\nitems_count={}, total_page_views_count={}, total_likes_count={}'.format(
        len(items), total_page_views_count, total_likes_count))

    print('-'*100)


def main():
    """main
    """
    items = list()

    for qiita_id in get_id_list():
        items.append(get_item(qiita_id=qiita_id))

    sorted_items = sorted(
        items, key=lambda x: x['page_views_count'], reverse=True)

    if os.getenv('OUTPUT', '') == 'output_db':
        output_db(items=sorted_items)
    else:
        output_log(items=sorted_items)


if __name__ == '__main__':
    main()

Qiita access token setting

command.sh

~% echo "export QIITA_ACCESS_TOKEN={qiita access token}" > app/config/qiita_access_token

--Note: [Access Token](https://qiita.com/nsuhara/items/b27b84f0150c3f6534ec#%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83 % 88% E3% 83% BC% E3% 82% AF% E3% 83% B3)

Database Migration

tree.sh

/
└── app
     ├── alembic.ini
     └── migration
          ├── README
          ├── env.py
          ├── script.py.mako
          └── versions
              └── 62fb1bcf0a8a_create_table.py

Alembic installation

command.sh

~% pip install alembic

Alembic initialization

command.sh

~% alembic init {directory}

Alembic settings

command.sh

~% vim app/alembic.ini

command.sh

- sqlalchemy.url = driver://user:pass@localhost/dbname
+ sqlalchemy.url = postgresql+psycopg2://postgres:postgres@docker_db:5432/qiita

Script creation

command.sh

~% alembic revision -m 'Create Table'

--Note: app / migration / versions / {revision id} _create_table.py is generated

Script editing

create_table.py

"""Create Table
"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql as db

# revision identifiers, used by Alembic.
revision = '62fb1bcf0a8a'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'qiita',
        sa.Column('id', db.INTEGER, primary_key=True),
        sa.Column('date', db.DATE, nullable=False),
        sa.Column('title', db.VARCHAR(255), nullable=False),
        sa.Column('page_views_count', db.INTEGER, nullable=False),
        sa.Column('likes_count', db.INTEGER, nullable=False),
        sa.Column('url', db.VARCHAR(255), nullable=False),
        sa.Column('created_at', db.TIMESTAMP, nullable=False),
    )


def downgrade():
    pass

Manual table generation

command.sh

~% python app/migration/versions/{revision id}_create_table.py

Docker Compose

tree.sh

/
├── Dockerfiles
│   ├── app
│   │   ├── Dockerfile
│   │   ├── crontab
│   │   ├── docker-compose.yml
│   │   ├── entrypoint.sh
│   │   └── qiita.sh
│   └── docker_compose_up.sh
└── app

docker_compose_up.sh

docker_compose_up.sh

#!/bin/sh

docker stop $(docker ps -q)
docker rm $(docker ps -q -a)
# docker rmi $(docker images -q) -f

rsync -av --exclude=app/tests* app Dockerfiles/app
docker-compose -f Dockerfiles/app/docker-compose.yml up --build

docker-compose.yml

docker-compose.yml

version: '3'
services:
  docker_db:
    image: postgres:11.5
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'postgres'
      POSTGRES_DB: 'qiita'
      POSTGRES_INITDB_ARGS: '--encoding=UTF-8'

  app:
    build: .
    depends_on:
      - docker_db
    volumes:
      - ./crontab:/var/spool/cron/crontabs/root
    environment:
      PYTHONPATH: '/code/'
      TZ: 'Asia/Tokyo'
      QIITA_PAGE: '1'
      QIITA_PER_PAGE: '100'
      QIITA_URL_ITEM: 'qiita.com/api/v2/items'
      QIITA_URL_LIST: 'qiita.com/api/v2/authenticated_user/items'
      OUTPUT: 'output_db'
      SQLALCHEMY_DATABASE_URI: 'postgresql+psycopg2://postgres:postgres@docker_db:5432/qiita'
      SQLALCHEMY_ECHO: '1'
      SQLALCHEMY_POOL_SIZE: '5'
      SQLALCHEMY_MAX_OVERFLOW: '10'
      SQLALCHEMY_POOL_TIMEOUT: '30'

Dockerfile

FROM python:3.7

RUN mkdir /code
WORKDIR /code

ADD entrypoint.sh /code/entrypoint.sh
ADD qiita.sh /code/qiita.sh
ADD app/ /code

RUN export -p
RUN chmod +x /code/entrypoint.sh
RUN chmod +x /code/qiita.sh

RUN apt-get update && apt-get -y install busybox-static

RUN pip install --upgrade pip --no-cache-dir
RUN pip install -r requirements.txt --no-cache-dir

RUN touch /var/log/cron.log

# EXPOSE 0000

CMD ["/code/entrypoint.sh"]

entrypoint.sh

entrypoint.sh

#!/bin/bash

sleep 5
# set qiita access token to environment variable
source /code/config/qiita_access_token
# migrate database
alembic upgrade head
# run script at 23:00, 0 23 * * * /code/qiita.sh  >> /var/log/cron.log 2>&1
busybox crond -l 8 -L /dev/stderr -f

crontab

0 23 * * * /code/qiita.sh  >> /var/log/cron.log 2>&1

qiita.sh

qiita.sh

#!/bin/sh

python /code/main.py

Start various services

command.sh

~% sh Dockerfiles/docker_compose_up.sh

Log in to the Docker container locally

command.sh

docker ps
# CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
# 0af68012c7c9        app_app             "/code/entrypoint.sh"    38 seconds ago      Up 37 seconds       0.0.0.0:8000->8000/tcp   app_app_1
# 9901a105e8d5        postgres:11.5       "docker-entrypoint.s…"   39 seconds ago      Up 37 seconds       0.0.0.0:5432->5432/tcp   app_postgres_1

docker exec -i -t {CONTAINER ID} /bin/bash

Log in to Docker-PostgreSQL locally

command.sh

~% psql -h localhost -p 5432 -d qiita -U postgres

Bonus: Heroku

Create runtime.txt

Supported runtimes

command.sh

~% cd python-docker-cron/
~% echo python-3.7.9 > app/runtime.txt

Heroku Postgres Registration

1. {app-name}> Resources tab> Enter and select Heroku Postgres in the search for Add-ons> Provision button

Environment variable settings

1. {app-name}> Settings tab> Reveal Config Vars button> Register environment variables

--Note 1: / config / output_db --Note 2: [Access Token](https://qiita.com/nsuhara/items/b27b84f0150c3f6534ec#%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3% 83% 88% E3% 83% BC% E3% 82% AF% E3% 83% B3) --Note 3: Match SQLALCHEMY_DATABASE_URI to Heroku Postgres --Note 4: Don't forget to register QIITA_ACCESS_TOKEN

Edit alembic.ini

command.sh

~% cd python-docker-cron/
~% vim app/alembic.ini

command.sh

- sqlalchemy.url = postgresql+psycopg2://postgres:postgres@docker_db:5432/qiita
+ sqlalchemy.url = {SQLALCHEMY_DATABASE_Same URL as URI}

Deploy

command.sh

~% cd python-docker-cron/app/
~% git init
~% git commit -am "make it better"
~% heroku login
~% heroku git:remote -a {app-name}
~% git push heroku master

Heroku Postgres Migration

command.sh

~% cd python-docker-cron/
~% heroku run python migration/versions/62fb1bcf0a8a_create_table.py

or

command.sh

~% heroku pg:psql {database} --app {app-name}

command.sh

{app-name}::DATABASE=> CREATE TABLE qiita (
    id SERIAL NOT NULL,
    date date not null,
    title VARCHAR(255) NOT NULL,
    page_views_count Integer NOT NULL,
    likes_count Integer NOT NULL,
    url VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
);

Heroku Scheduler registration

1. {app-name}> Resources tab> Search for Add-ons and select Heroku Scheduler> Provision button
2. Heroku Scheduler> Add Job button
3. Set the execution cycle to Schedule
4. Set python main.py to Run Command

--Note 1: Periodic execution of Python on Heroku Scheduler --Note 2: Credit card registration required