How to solve "sqlalchemy.exc.OperationalError" when using Alembic

Question

I want to add columns to a table in my database dynamically since I don't want to have to specify all columns when I set it up in the class

In order to solve this I am instead using alembic to add columns to a table but I am having problems.

In test.py script I have defined a class as below.

DATABASE_URL_dev = "postgresql+psycopg2://user:password@localhost:5432/testdb"

engine = create_engine(DATABASE_URL_dev)
Base = declarative_base(engine)


class my_class1(Base):
    __tablename__ = "test1"

    id = Column(Integer, primary_key=True)
    list = Column(String)

def  loadsession():
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    return session

session = loadsession()

Now I want to add a column to table test1 and I am thereby trying to use Alembic.

I followed the instructions at https://alembic.sqlalchemy.org/en/latest/tutorial.html and did the following:

I installed alembic by running pip install alembic
I created an environment using alembic init alembic
In the .ini file I set sqlalchemy.url = "postgresql+psycopg2://user:password@localhost:5432/testdb"
I created a migration script by running alembic revision -m "add a column "
I then edited the script by adding the following "op.add_column('test1', sa.Column('new_column', sa.String))" in upgrade()
I ran the first migration with "alembic upgrade head"
This gave me the error: "sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) (Background on this error at: http://sqlalche.me/e/e3q8)"

Question
Have I set sqlalchemy.url correctly or what is else setting the OperationalError?

Entire traceback

Traceback (most recent call last):
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\runpy.py", line 193, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\Users\user\AppData\Local\Programs\Python\Python38-32\Scripts\alembic.exe\__main__.py", line 9, in <module>
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\alembic\config.py", line 577, in main
    CommandLine(prog=prog).main(argv=argv)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\alembic\config.py", line 571, in main
    self.run_cmd(cfg, options)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\alembic\config.py", line 548, in run_cmd
    fn(
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\alembic\command.py", line 298, in upgrade
    script.run_env()
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\alembic\script\base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\alembic\util\pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\alembic\util\compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "y\env.py", line 77, in <module>
    run_migrations_online()
  File "y\env.py", line 65, in run_migrations_online
    with connectable.connect() as connection:
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 2218, in connect
    return self._connection_cls(self, **kwargs)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 103, in __init__
    else engine.raw_connection()
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 2317, in raw_connection
    return self._wrap_pool_connect(
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 2288, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1554, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 2285, in _wrap_pool_connect
    return fn()
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\base.py", line 773, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\impl.py", line 238, in _do_get
    return self._create_connection()
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\base.py", line 657, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\pool\base.py", line 652, in __connect
    connection = pool._invoke_creator(self)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "c:\users\user\appdata\local\programs\python\python38-32\lib\site-packages\sqlalchemy\engine\default.py", line 488, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "c:\users\c\appdata\local\programs\python\python38-32\lib\site-packages\psycopg2\__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError)
(Background on this error at: http://sqlalche.me/e/e3q8)

Can you check if the solutions here work for you? https://stackoverflow.com/questions/59190010/psycopg2-operationalerror-fatal-unsupported-frontend-protocol-1234-5679-serve — snakecharmerb, May 04 '20 at 16:49
Tried to add both "?gssencmode=disable" and "?sslmode=disable" respectively but the operational error still stands — user12288003, May 04 '20 at 17:10
Ok. I usually just run "runpsql" when accessing the database but when testing your example and running "psql -U postgres -c \conninfo" and the filling in the requested password the result is "You are connected to database "postgres" as user "postgres" on host "localhost" (adress "::1") on port "5432". Have I understood it correctly? — user12288003, May 04 '20 at 18:06
I tried the username for that database too but got the same result unfortunately — user12288003, May 04 '20 at 19:36
I found the logs and when I ran it again I got the following: 2020-05-05 17:25:21.668 CEST [16836] LOGG: couldn't receive data from client: An existing connection was forcibly closed by the remote host. 2020-05-05 17:25:21.670 CEST [16836] LOGG: unexpected EOF from clientconnection with open transaction 2020-05-05 17:25:25.788 CEST [18308] FATAL: Password authentication failed for user "postgres" 2020-05-05 17:25:25.788 CEST [18308] DETAIL: Password doesn't match for user "postgres". Connection matched row 82 in pg_hba.conf: "host all all ::1/128 — user12288003, May 05 '20 at 15:30
Though the password in alembic.ini is the same as in the test.py file so I don't understand the issue — user12288003, May 05 '20 at 15:31

How to solve "sqlalchemy.exc.OperationalError" when using Alembic

0 Answers0