How to join a postgres db and create a table there?

Question

Python

Hey ! For educational purposes, I launched a database on docker + adminer

Docker-compose:

version: '3.1'

services:
tgbot:
container_name: bot
build:
context: .
command: python main.py
restart: always

db:
image: postgres
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
volumes:
- ./pgdata:/var/lib/postgresql/data

adminer:
image: adminer
restart: always
ports:
- 8080:8080

Dockerfile:

FROM python:3.9

RUN mkdir /src
WORKDIR /src
COPY . /src
RUN pip install -r requirements.txt

CODE:

from aiogram import Bot, Dispatcher, executor
from aiogram.types import Message
import logging
import psycopg2
from aiogram.dispatcher.filters.builtin import CommandStart, Text





bot = Bot(token = 'СЕКРЕТ')
dp = Dispatcher(bot)
logging.basicConfig(format=u'%(filename)s [LINE:%(lineno)d] #%(levelname)-8s [%(asctime)s]  %(message)s',
                    level=logging.INFO)




@dp.message_handler(CommandStart())
async def welcome(message: Message):
    user_id = message.chat.id
    conn = psycopg2.connect(dbname = "postgres",
                            user = "postgres",
                            password = "postgres",
                            host = "localhost",
                            port = 5432)

    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS id_list(id_number INTEGER);")
    cursor.execute("INSERT INTO id_list(id_number) VALUES(%s)",(user_id,))
    conn.commit()
    conn.close()

@dp.message_handler(Text(equals=["kuku"]))
async def feedback_press_button(message: Message):
    conn = psycopg2.connect(dbname="postgres",
                            user="postgres",
                            password="postgres",
                            host="localhost",
                            port=5432)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM id_list")
    grab_data = cursor.fetchall()
    for val in grab_data:
        await message.answer(val[0])
    conn.commit()
    conn.close()

    await message.answer("OK")



if __name__ == '__main__':
    executor.start_polling(dp, skip_updates=True)

When I log in via admin (localhost:8080) to the postgres database using this data:

Engine = PostgreSQL
Server = db
Username = postgres
Password = postgres



But when the code is executed:

conn = psycopg2.connect(dbname = "postgres",
                            user = "postgres",
                            password = "postgres",
                            host = "localhost",
                            port = 5432)

    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS id_list(id_number INTEGER);")
    cursor.execute("INSERT INTO id_list(id_number) VALUES(%s)",(user_id,))
    conn.commit()
    conn.close()

nothing is created, nothing happens. And I think the problem is here

conn = psycopg2.connect(dbname = "postgres",
                            user = "postgres",
                            password = "postgres",
                            host = "localhost",
                            port = 5432)

Please help me, what data do I need to enter in psycopg2.connect () to connect to the database and create a table in it.

Answer 1

Your DBMS port is not open outside, add to ports5432:5432 and modify pg_hba to suit your needs .

Answer 2

conn = psycopg2.connect(dbname = "postgres",
user = "postgres",
password = "postgres",
host = "localhost",
port = 5432)

Replace localhost with:
host = " db "
Can be accessed by the container name you specify in docker-compose...