Python and PostgreSQL Docker Container (Part 2)
Introduction
This is the second of a two-part tutorial series explaining how to build and run a Python and PostgreSQL docker container. The container is being designed to uses the psycopg2 database adapter for PostgreSQL and Python programming language to interface with the database. Part two will also explain how to use Docker Compose and Dockerfile to customize a PostgreSQL-Python image and use the PythonPIP package manager to install the psycopg2 Postgres adapter.
Prerequisites for Docker
- Docker must be properly installed on the machine or server running the programs. Elevate privileges must be enabled with access to a command prompt or UNIX terminal on the same machine or server.
Open a terminal or command-prompt window on the host machine and execute the following command to confirm Docker is installed and running:
1 | docker --version |
Note that the Docker engine version number must be compatible with the Docker Compose version number to avoid errors.
PostgreSQL table records for testing
Part one of this series created a Docker container with docker run and then entered into the container using the docker exec -it command. This was done in order to create a PostgreSQL table and sample records in a bind-mounted volume for persistent data.
If the steps in part one of this series have not yet been completed, a table must first be inserted and records added into the Docker container’s bind-mounted volume.
IDE for Python and YAML
Confirm access to a text editor or IDE that has both YAML and Python syntax as well as indentation support. While the examples in this tutorial use the Sublime IDE, other editors like Atom IDE or Visual Studio Code that uses the code command will work as well.
Docker-Compose file for Postgres
A Docker Compose file will be used to set up the container and specify such things as the bind-mounted volumes and port mapping.
Create a Docker-Compose file for the Postgres container
Change into root of the PostgreSQL-Docker project directory and create a new Docker compose file.
Following is an example of how to create a new file in a UNIX-based terminal using the touch command:
1 | touch docker-compose.yml |
The following command can be used to edit the new file with the Sublime IDE:
1 | subl docker-compose.yml |
Note that the .yaml file extension can also be used in above command.
Docker-Compose version number
The Docker Compose version must be specified at the top of the YAML file. As shown in the following command, version "3.2" should work for the most recent stable releases of Docker:
1 | version: "3.2" |
NOTE: Typically, releases of Docker Engine 17 or newer will use a version string of "3.x" or greater in the Docker Compose file.
Postgres container and environment variables
At the same indentation level as the version number, the services: keys must be declared and the container name must be specified at one indentation level below the version number. This is shown in the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | services: # container name postgres: # build the image from Dockerfile build: context: ${PWD} # bind mount volume for Postgres data volumes: - $PWD/pg-data/:/var/lib/postgresql/data - $PWD/python-app:/var/www/html # open port so host machine can access ports: - "5432:5432" # configure Postgres environment variables environment: - POSTGRES_USER=objectrocket - POSTGRES_DB=some_db - POSTGRES_PASSWORD=1234 |
The above YAML markup will instruct Docker to map the host port to the default Postgres port of 27017 and set the PostgreSQL environment variables. Note that the relative location for the Dockerfile must be specified under the context: key.
Networking in Docker-Compose
The last part of the YAML mark sets up the network so the container will run on an IP address of 172.28.1.4:
1 2 3 4 5 6 7 8 9 10 | networks: node_net: ipv4_address: 172.28.1.4 networks: node_net: ipam: driver: default config: - subnet: 172.28.0.0/16 |
NOTE: Make certain to save the changes made here before moving on to the Dockerfile.
Dockerfile for Postgres
The Dockerfile for the container used to execute bash commands inside the container must be created.
Execute the following command to open an instance of Dockerfile in the Sublime text editor:
1 | subl Dockerfile |
NOTE: The Dockerfile name cannot have a file extension after it.
Pull the Postgres image
The following commands will pull the latest image for postgres, if needed, from the Docker hub repository, including a “bare bones” distro of Debian Linux.
Execute the following code to install Python 3 and its PIP package manager:
1 2 3 4 5 6 | FROM postgres:latest # install Python 3 RUN apt-get update && apt-get install -y python3 python3-pip RUN apt-get -y install python3.7-dev RUN apt-get install postgresql-server-dev-10 gcc python3-dev musl-dev |
Install the psycopg2 adapter
Now use pip3 to install the psycopg2 Python adapter for PostgreSQL. This will allow for the retrieval and modification PostgreSQL table data stored in the container’s volume.
Execute the following command to install the psycopg2 Python adapter:
1 2 | # install psycopg2 library with PIP RUN pip3 install psycopg2 |
Setup the PostgreSQL environment
In the final part of the Dockerfile, the following command is executed to set up the Postgres volumes, add the postgres admin role and expose the port for the Postgres service:
1 2 3 4 5 6 7 8 | # add the 'postgres' admin role USER postgres # expose Postgres port EXPOSE 5432 # bind mount Postgres volumes for persistent data VOLUME ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"] |
Now save all of the changes made to the file and then open a terminal window at the project’s root directory where the docker-compose.yml file is located.
Build the Postgres container
Execute the following command to spin up and build the Docker container using the specifications laid out in the Docker files:
1 | docker-compose up --build |
NOTE: If a bind mount error is received because the specified port for Postgres is not accessible, use the sudo lsof -i -P -n | grep 5432 command to search for the cause of the error and then pass the PID (Process ID) to the sudo kill -9 command to fix the error.
Create a Python script
While waiting for the container to build from Docker Hub’s postgres image, begin working on the Python script for the RESTful API calls to the container’s bind-mounted Postgres database.
Python script in the app folder
Execute the following code to create a new Python script in the python-app directory on the host machine that is bind-mounted to the container’s volume:
1 | subl python-app/test-postgres.py |
Now paste the following Python code into the .py script, making certain to save it in the directory specified under volumes: in the Docker Compose file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | #!/usr/bin/python3 # -*- coding: utf-8 -*- # import the connect library from psycopg2 from psycopg2 import connect table_name = "some_table" # declare connection instance conn = connect( dbname = "some_db", user = "objectrocket", host = "172.28.1.4", password = "1234" ) # declare a cursor object from the connection cursor = conn.cursor() # execute an SQL statement using the psycopg2 cursor object cursor.execute(f"SELECT * FROM {table_name};") # enumerate() over the PostgreSQL records for i, record in enumerate(cursor): print ("\n", type(record)) print ( record ) # close the cursor object to avoid memory leaks cursor.close() # close the connection as well conn.close() |
The results should resemble the following:
Enter the Postgres Container
The container should run as a foreground process in the terminal window after it has finished building and installing Python. Open a new terminal window, or a new tab in the terminal, and use the below docker exec command to connect to the container interactively as explained in part one of this tutorial series. However, the container ID must first be obtained by using docker ps. Once the ID is obtained, use the first three digits of the alpha-numeric ID to execute the following interactive TTY session with the running container:
1 | docker exec -it 602 bin/bash |
Once inside of the container, input python3 -V and psql -V to verify that both Python 3 and Postgres are working properly, as shown here:
Execute a pycopg2 Python script in the container
Navigate to the /var/www/html directory inside the container where the Python script should be visible. Now use the python3 command to execute the script from the terminal.
The Python code should return the PostgreSQL records stored in the container’s volume. Assuming Python and the psycopg2 adapter are working properly on the host machine, those records should be accessible outside of the container by changing the script’s host to localhostas shown in the following screenshot:
Now type exit into the terminal to leave the container and then input docker-compose down from the root directory of the project to shut down the container.
Conclusion
This was part two of a tutorial series explaining how to build and run a Python and PostgreSQL docker container. This second part of the series covered how to use Docker Compose and Dockerfile to customize a PostgreSQL-Python image and use the PythonPIP package manager to install the psycopg2 Postgres adapter to make API calls to a Postgres database in Python. It is critical to remember that the Dockerfile name cannot have a file extension after it when executing the command to open an instance of Dockerfile in the Sublime text editor.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started