[PYTHON] How to execute a query from psycopg2 built with SAM to Postgres launched with Docker

What I wanted to do

(1) Read the xml file saved in AWS S3 and acquire the necessary data ② Connect to Postgres in docker container from python program ** ← Stumbled part 1 ** ③ Use psycopg2 to execute a query and acquire data ** ← Stumbled part 2 ** ④ After processing the data, execute the query and save it in Postgres.

Problems with stumbling part 1

Initially, the Python file was written as follows 。

app.py


#DB connection information
    postgre_user_name = "admin"
    postgre_user_password = "password"
    postgre_server_port = 5432
    postgre_database_name = "hugahoge"

    connection = psycopg2.connect(f"host=localhost port={postgre_server_port} dbname={postgre_database_name} user={postgre_user_name} password={postgre_user_password}")

At runtime, I specified the ID of the container in which Postgres is started as an option after sam local invoke. As a result, I got an error saying that such a network could not be found.

% sam local invoke --docker-network 8f1abbbc8138
.
.
.
docker.errors.NotFound: 404 Client Error: Not Found ("network 8f1abbbc8138 not found")

That should be it, as in the option command name, you need to specify the network ID or name to which the container is connected, not the container ID. An ordinary mistake.

Solution for stumbled part 2

Generate and check the Docker network.

% docker network create sam-calc-price-network
c87bcb8cee0e92f57a10de53b2a4930e8e3cf890b5f7448299e45bad3ffed3c4

% docker network inspect sam-calc-price-network
[
    {
        "Name": "sam-calc-price-network",
        "Id": "c87bcb8cee0e92f57a10de53b2a4930e8e3cf890b5f7448299e45bad3ffed3c4",
        "Created": "2020-12-23T05:06:07.9437227Z",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": {},
            "Config": [
                {
                    "Subnet": "172.18.0.0/16",
                    "Gateway": "172.18.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {},
        "Options": {},
        "Labels": {}
    }
]

Connect the Postgres container to the network and check.

% docker network connect sam-calc-price-network dev-postgres

% docker network inspect sam-calc-price-network            
[
    {
        "Name": "sam-calc-price-network",
        "Id": "c87bcb8cee0e92f57a10de53b2a4930e8e3cf890b5f7448299e45bad3ffed3c4",
        "Created": "2020-12-23T05:06:07.9437227Z",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": {},
            "Config": [
                {
                    "Subnet": "172.18.0.0/16",
                    "Gateway": "172.18.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {
            "8f1abbbc813844cb52395b65c711d56cc4701058f2d436bf25ffd553280aa133": {
                "Name": "dev-postgres",
                "EndpointID": "dd5ab005654b3a58b1604014ba1284e1690e5cd009b8d7bf62db655caf3e0657",
                "MacAddress": "02:42:ac:12:00:02",
                "IPv4Address": "172.18.0.2/16",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {}
    }
]

Take a second look and specify your Docker network ID to run!

% sam local invoke --docker-network c87bcb8cee0e92f57a10de53b2a4930e8e3cf890b5f7448299e45bad3ffed3c4
[ERROR] OperationalError: 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?
could not connect to server: Cannot assign requested address
	Is the server running on host "localhost" (::1) and accepting
	TCP/IP connections on port 5432?

… Yes.

As a result of various investigations, it seems that the local host seen from the user side and the local host when looking at the container from the container are different. The local host seen from the container seems to be the container name, so I rewrote the Python file as follows.

#DB connection information
    postgre_user_name = "admin"
    postgre_user_password = "password"
    postgre_server_port = 5432
    postgre_database_name = "fugahoge"

    connection = psycopg2.connect(dbname=f"{postgre_database_name}", 
                                  user=f"{postgre_user_name}", 
                                  password=f"{postgre_user_password}",
                                  host="dev-postgres")

Problems of stumbling part 2

Run again!

{"errorMessage": "relation \"place\" does not exist\nLINE 1: SELECT contract_id FROM place WHERE num...\n                                ^\n", "errorType": "UndefinedTable", "stackTrace": ["  File \"/var/task/app.py\", line 145, in lambda_handler\n    num = get_contract_id(df['num'][i])\n", "  File \"/var/task/app.py\", line 139, in get_contract_id\n    cur.execute(sql_sentence)\n"]}%

… Yes. Different error. I was able to connect to the DB for the time being. But it is said that there is no table that should exist. Data can be obtained without any problem as long as it is executed as a script instead of being executed from SAM. Is that a problem peculiar to executing a query from a container?

Solution for stumbled part 2

I searched a lot, but I didn't know the solution ... For the time being, I should have specified the schema of the table when executing the query.

before

def get_contract_id(num):
        cur = connection.cursor()
        sql_sentence = "SELECT id FROM place WHERE number = %s;"
        cur.execute(sql_sentence, (num,))
        return cur.fetchall()

after

def get_contract_id(num):
        cur = connection.cursor()
        sql_sentence = "SELECT id FROM public.place WHERE number = %s;"
        cur.execute(sql_sentence, (num,))
        return cur.fetchall()

Recommended Posts

How to execute a query from psycopg2 built with SAM to Postgres launched with Docker
How to delete a Docker container
How to create a clone from Github
How to add a package with PyCharm
Execute a script from Jupyter to process
[Ubuntu] How to execute a shell script
How to create a repository from media
How to install python3 with docker centos
How to read a CSV file with Python 2/3
How to open a web browser from python
How to delete log with Docker, not to collect log
How to develop a cart app with Django
How to make a dictionary with a hierarchical structure.
How to generate a Python object from JSON
How to extract coefficients from a fractional formula
How to create a multi-platform app with kivy
How to make a 3D geometric figure with one click [From triangular pyramid to fractal]
How to convert / restore a string with [] in python
[Python] How to draw a line graph with Matplotlib
How to scrape image data from flickr with python
How to create a submenu with the [Blender] plugin
How to get a logged-in user with Django's forms.py
From Kafka to KSQL --Easy environment construction with docker
How to convert a class object to a dictionary with SQLAlchemy
How to make a shooting game with toio (Part 1)
How to share folders with Docker and Windows with tensorflow
How to hold a hands-on seminar using Jupyter using docker
From buying a computer to running a program with python
Execute Python function from Powershell (how to pass arguments)
[Python] How to create a 2D histogram with Matplotlib
How to execute a command using subprocess in Python
How to create a kubernetes pod from python code
[Python] How to draw a scatter plot with Matplotlib
How to connect to Cloud SQL PostgreSQL on Google Cloud Platform from a local environment with Java
How to deploy a web app made with Flask to Heroku
How to make a Cisco Webex Teams BOT with Flask
Convert Select query obtained from Postgre with Go to JSON
How to put a hyperlink to "file: // hogehoge" with sphinx-> pdf
How to install NPI + send a message to line with python
How to kill a process instantly with Python's Process Pool Executor
How to slice a block multiple array from a multiple array in Python
How to convert an array to a dictionary with Python [Application]
How to output a document in pdf format with Sphinx
How to run a Python program from within a shell script
[Linux] Copy data from Linux to Windows with a shell script
How to launch AWS Batch from a python client app
How to create a flow mesh around a cylinder with snappyHexMesh
How to print characters as a table with Python's print function
How to generate a public key from an SSH private key
How to make a simple Flappy Bird game with pygame
From environment construction to deployment for flask + Heroku with Docker
How to generate a query using the IN operator in Django
How to display a list of installable versions with pyenv
How to extract other than a specific index with Numpy
How to run an app built with Python + py2app built with Anaconda
A story about how to deal with the CORS problem
I want to install a package from requirements.txt with poetry
How to get a list of links from a page from wikipedia
I made you to execute a command from a web browser
[Django Learned with the Devil's Blade] How to get a query set for forward / reverse reference
For those of you who don't know how to set a password with Jupyter on Docker