Postgres DB connection to FastAPI

Question

I'm trying to connect a postgres DB with fastapi. My postgres DB is a docker contanier. My docker-compose is:

version: "3.1"
services:
  postgres:
    image: postgres
    container_name: postgres
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
      POSTGRES_HOST_AUTH_METHOD: trust
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgres/data/

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: admin
      PGADMIN_LISTEN_PORT: 5050
    ports:
      - "5050:5050"
    depends_on:
      - postgres


volumes:
  postgres_data:

and in databases.py is:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY = "postgresql://postgres:postgres@localhost:5432/postgres"
engine = create_engine(SQLALCHEMY)
SessionLocal = sessionmaker(autoflush=False, expire_on_commit=False, bind=engine)
Base = declarative_base()

My main.py is:

from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware

from databases.database import Base, engine


def create_tables():
    Base.metadata.create_all(bind=engine)

create_tables()

app = FastAPI()

version = "v0.1"

# Import routers

# Add CORS
app.add_middleware(
    CORSMiddleware,
    allow_origins=['*'],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)


@app.get("/")
async def root():
    return {"detail": "Backend working on"}

If I change localhost to postgres, I have an error that is:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "postgres" to address: Unknown host

but... with localhost I recieve the next problem:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xf3 in position 85: invalid continuation byte

Complete error traceback:

Traceback (most recent call last):
  File "C:\Users\Usuario\AppData\Local\Programs\Python\Python311\Lib\multiprocessing\process.py", line 314, in _bootstrap
    self.run()
  File "C:\Users\Usuario\AppData\Local\Programs\Python\Python311\Lib\multiprocessing\process.py", line 108, in run
    self._target(*self._args, **self._kwargs)
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\uvicorn\_subprocess.py", line 76, in subprocess_started
    target(sockets=sockets)
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\uvicorn\server.py", line 61, in run
    return asyncio.run(self.serve(sockets=sockets))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\AppData\Local\Programs\Python\Python311\Lib\asyncio\runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\AppData\Local\Programs\Python\Python311\Lib\asyncio\runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\AppData\Local\Programs\Python\Python311\Lib\asyncio\base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\uvicorn\server.py", line 68, in serve
    config.load()
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\uvicorn\config.py", line 467, in load
    self.loaded_app = import_from_string(self.app)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\uvicorn\importer.py", line 21, in import_from_string
    module = importlib.import_module(module_str)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\AppData\Local\Programs\Python\Python311\Lib\importlib\__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap>", line 1204, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1176, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1147, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 690, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\main.py", line 10, in <module>
    create_tables()
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\main.py", line 8, in create_tables
    Base.metadata.create_all(bind=engine)
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 5828, in create_all
    bind._run_ddl_visitor(
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 3242, in _run_ddl_visitor
    with self.begin() as conn:
  File "C:\Users\Usuario\AppData\Local\Programs\Python\Python311\Lib\contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 3232, in begin
    with self.connect() as conn:
         ^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 3268, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 3292, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 1269, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 716, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\impl.py", line 169, in _do_get
    with util.safe_reraise():
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\impl.py", line 167, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 678, in __init__
    self.__connect()
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 902, in __connect
    with util.safe_reraise():
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\pool\base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\engine\create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\sqlalchemy\engine\default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Usuario\Documents\UNI\5ºAño\TFG\tfg_backend\venv\Lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xf3 in position 85: invalid continuation byte

Versions:

sys version: '3.11.6 (tags/v3.11.6:8b6ee5b, Oct 2 2023, 14:57:12) [MSC v.1935 64 bit (AMD64)]'
SQLAlchemy==2.0.23
psycopg2==2.9.9

Strange - there seems to be something in the connection parameters that psycopg2 receives, or the response it gets from the server - that is not properly encoded. Can you connect via PgAdmin? Can you view the client and server encoding settings? — snakecharmerb
– snakecharmerb, Commented Dec 3, 2023 at 14:51
It looks like you have pgAdmin_4 available. Can you use it to check the encoding setting for your database? FWIW, mine looks like this. — Gord Thompson
– Gord Thompson, Commented Dec 3, 2023 at 14:53
I have checked the properties and it's all correct. All are in UTF-8 — learning_afo
– learning_afo, Commented Dec 3, 2023 at 15:20
From a Python interactive prompt (>>>) try import psycopg2 followed by conn = psycopg2.connect(database="postgres", user="postgres", password="postgres", host="localhost") - does that throw an error? — Gord Thompson
– Gord Thompson, Commented Dec 3, 2023 at 15:54
Yes, I have tried it and I receive the same error (UnicodeDecodeError: 'utf-8' codec can't decode byte 0xf3 in position 85: invalid continuation byte), it could be the enconding of the project? — learning_afo
– learning_afo, Commented Dec 3, 2023 at 16:17

Collectives™ on Stack Overflow

Postgres DB connection to FastAPI

0

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest

Linked