[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()