sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) with PostgreSQL

C

2

7

I searched for this error a lot, but I only find some with more information behind that like "FATAL: ...". Mine has none. It only says

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError)

I have a postgres database inside a docker container that has set his port to the standard 5432. I've created the container with the following command:

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

It is clean so no database created. The API should automatically create them.

I'm using Pycharm IDE, maybe it has something todo with that.

Traceback (most recent call last):
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 373, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\middleware\debug.py", line 96, in __call__
    raise exc from None
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\middleware\debug.py", line 93, in __call__
    await self.app(scope, receive, inner_send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\fastapi\applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__
    raise exc from None
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\exceptions.py", line 82, in __call__
    raise exc from None
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\routing.py", line 580, in __call__
    await route.handle(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\routing.py", line 241, in handle
    await self.app(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\routing.py", line 52, in app
    response = await func(request)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\fastapi\routing.py", line 226, in app
    raw_response = await run_endpoint_function(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\fastapi\routing.py", line 159, in run_endpoint_function
    return await dependant.call(**values)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\app\routers\v1\users.py", line 31, in create_user
    session.commit()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 1428, in commit
    self._transaction.commit(_to_root=self.future)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 829, in commit
    self._prepare_impl()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 808, in _prepare_impl
    self.session.flush()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 3339, in flush
    self._flush(objects)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 3479, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 3439, in _flush
    flush_context.execute()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 456, in execute
    rec.execute(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 209, in save_obj
    for (
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 370, in _organize_states_for_save
    for state, dict_, mapper, connection in _connections_for_states(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 1709, in _connections_for_states
    connection = uowtransaction.transaction.connection(base_mapper)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 626, in connection
    return self._connection_for_bind(bind, execution_options)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 735, in _connection_for_bind
    conn = self._parent._connection_for_bind(bind, execution_options)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 747, in _connection_for_bind
    conn = bind.connect()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\future\engine.py", line 419, in connect
    return super(Engine, self).connect()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 3194, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
    else engine.raw_connection()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 3273, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 3243, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2097, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 3240, in _wrap_pool_connect
    return fn()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
    rec = pool._do_get()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
    return self._create_connection()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
    self.__connect()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) 
(Background on this error at: https://sqlalche.me/e/14/e3q8)

This is my error that i get. My code looks like this:

main.py:

import os
import uvicorn
if __name__ == '__main__':
    port = int(os.getenv("PORT"))
    uvicorn.run("main:app", host='0.0.0.0', port=port, reload=True, debug=True, workers=3)

app/main.py:

import os
from fastapi import FastAPI
from .database import engine
from .routers import v1
engine.init_db()
port = int(os.getenv("PORT")) #Port is 8000
app = FastAPI()

app.include_router(v1.router, prefix="/v1")

app/database/engine.py: (referenced in the file above)

import os

from fastapi.security import HTTPBearer
from sqlmodel import create_engine, SQLModel, Session
DATABASE_URL = "postgresql+psycopg2://postgres:mysecretpassword@localhost:5432"
engine = create_engine(DATABASE_URL, echo=True)
token_auth_scheme = HTTPBearer()


async def init_db():
    async with engine.begin() as conn:
        # await conn.run_sync(SQLModel.metadata.drop_all)
        await conn.run_sync(SQLModel.metadata.create_all)


async def get_session():
    session = Session(engine)
    try:
        yield session
    finally:
        session.close()

The route "users" inside routers/v1/users.py:

from typing import Optional

from fastapi import APIRouter, Depends, HTTPException, Query, Path, Response, status
from pydantic import ValidationError
from sqlalchemy.exc import IntegrityError
from sqlalchemy.sql.functions import concat
from sqlalchemy import func
from sqlmodel import Session, select, col
from starlette import status

from app.database import models
from app.database.authentication import VerifyToken
from app.database.engine import get_session, token_auth_scheme

router = APIRouter()


@router.post("", status_code=status.HTTP_201_CREATED, response_model=models.UserRead,
             response_model_exclude_none=True, name="Create User", tags=["users"])
async def create_user(user_data: models.UserCreate,
                      session: Session = Depends(get_session)):

    try:
        new_user = models.User(**dict(user_data))
        session.add(new_user)
        session.commit()
        session.refresh(new_user)

        return new_user
    except IntegrityError:
        session.rollback()
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT, detail="IntegrityError")
    except ValidationError:
        session.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="ValidationError")

The models.User:

class UserBase(SQLModel):
    id: str
    username: Optional[str]
    country_code: Optional[str]
    phone: Optional[str]

    class Config:
        allow_population_by_field_name = True

class User(UserBase, table=True):
    __tablename__ = 'users'
    id: str = Field(primary_key=True)
    username: Optional[str] = Field(sa_column=Column('username', VARCHAR(length=50), unique=True, default=None))
    phone: Optional[str] = Field(sa_column=Column('phone', VARCHAR(length=20), unique=True, default=None))

I hope that this is everything you guys need to find something. If you need something else contact me.

Best regards Colin

EDIT:

After I changed the link from `postgresql+psycopg2` to `postgresql+asyncpg` I get a new error:

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 373, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\middleware\debug.py", line 96, in __call__
    raise exc from None
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\uvicorn\middleware\debug.py", line 93, in __call__
    await self.app(scope, receive, inner_send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\fastapi\applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__
    raise exc from None
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\exceptions.py", line 82, in __call__
    raise exc from None
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\routing.py", line 580, in __call__
    await route.handle(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\routing.py", line 241, in handle
    await self.app(scope, receive, send)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\starlette\routing.py", line 52, in app
    response = await func(request)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\fastapi\routing.py", line 226, in app
    raw_response = await run_endpoint_function(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\fastapi\routing.py", line 159, in run_endpoint_function
    return await dependant.call(**values)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\app\routers\v1\users.py", line 26, in create_user
    session.commit()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 1428, in commit
    self._transaction.commit(_to_root=self.future)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 829, in commit
    self._prepare_impl()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 808, in _prepare_impl
    self.session.flush()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 3339, in flush
    self._flush(objects)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 3479, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 3439, in _flush
    flush_context.execute()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 456, in execute
    rec.execute(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 209, in save_obj
    for (
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 370, in _organize_states_for_save
    for state, dict_, mapper, connection in _connections_for_states(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 1709, in _connections_for_states
    connection = uowtransaction.transaction.connection(base_mapper)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 626, in connection
    return self._connection_for_bind(bind, execution_options)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 735, in _connection_for_bind
    conn = self._parent._connection_for_bind(bind, execution_options)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\orm\session.py", line 747, in _connection_for_bind
    conn = bind.connect()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\future\engine.py", line 419, in connect
    return super(Engine, self).connect()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 3194, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
    else engine.raw_connection()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 3273, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\base.py", line 3240, in _wrap_pool_connect
    return fn()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
    rec = pool._do_get()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
    return self._create_connection()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
    self.__connect()
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\engine\default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 748, in connect
    await_only(self.asyncpg.connect(*arg, **kw)),
  File "C:\Users\Veril\PycharmProjects\partyapp-python\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 61, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)

EDIT 2: If I use my local postgres database in docker i get this error. If I use my extern database from Heroku it works perfectly fine!

EDIT 3: So apparently it won't work with my local docker postgres database. Now I am using my production database over heroku (so externally) and everything works with psycopg2. I didn't found the error why it won't let me use my local db but whatever.

Concur answered 14/2, 2022 at 18:23 Comment(4)

If you are trying to use async you should be using postgresql+asyncpg://, not postgresql+psycopg2:// – Doings 14/2, 2022 at 18:38

@GordThompson I edited the post and added the new error. – Concur 14/2, 2022 at 18:54

If you installed psycopg2, try instead to install psycopg2-binary. – Heine 15/2, 2022 at 17:57

I too had the same error connecting to pg; and in my case it was due a @ char in the password, due to which the connection string was wrongly parsed; – Carbonate 15/5 at 10:21

L

2

Even though I can see that you are using default port, maybe this can help to somebody with the same problem. For me the problem was in explicit port definition - as I'm running two different postgres DB and (both from containers), one of them I set to listen to 5433 port - and this gives me exact same problem, so instead

SQLALCHEMY_DATABASE_URL = "postgresql://postgres:password@localhost/fastapi"

I just put:

SQLALCHEMY_DATABASE_URL = "postgresql://postgres:password@localhost:5433/fastapi"

Problem solved right away.

Lopes answered 19/3, 2022 at 19:6 Comment(1)

I spent 2 hours with this error, thanks for sharing that – Il 22/7, 2022 at 4:42

B

1

I don't know exactly which step resolved the issue for me, but I attempted the following steps:

I installed both psycopg2-binary==2.9.9 and psycopg2==2.9.9 packages. Initially, I suspected that the problem might be due to using psycopg2==2.9.9, so I also installed psycopg2-binary==2.9.9 to try an alternative approach.
I configured the DATABASE_URL variable with the PostgreSQL connection string: DATABASE_URL = "postgresql+psycopg2://postgres:mysecretpassword@localhost:5432". I made sure to fill in the correct values for the PostgreSQL database connection data. Additionally, I experimented with using the specific driver by specifying postgresql+psycopg2 instead of just postgresql.
Upon checking my local PostgreSQL version using psql --version, I found it to be PostgreSQL 16.1. However, it appeared that psycopg2 didn't work correctly with PostgreSQL 16.1. Consequently, I installed PostgreSQL 13 locally as an alternative.
In my Docker environment, I encountered the error initially when building a new container. However, upon running it for a second time, the error disappeared. Therefore, it seemed that starting the Docker container a second time resolved the issue.

This issue initially occurred with my local PostgreSQL 16 version when attempting to connect and run my FastAPI + SQLAlchemy app. However, by using Docker Compose with a PostgreSQL 13 version image instead of 16, I managed to resolve the problem. I recommend trying to install PostgreSQL 13 version to see if it resolves the issue.

Bellied answered 26/2 at 17:33 Comment(0)

Recommended topics

Hot tags