Adding column dynamically to an existing table using sqlalchemy, 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 up the table in the SQLalchemy class

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

In my python 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 new column ("new_column") to the table test1 in the database.

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

Installed alembic by running pip install alembic
Created an environment using alembic init alembic
Edited the .ini file by inputting "postgresql+psycopg2://user:password@localhost:5432/testdb" at sqlalchemy.url
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()
Running first migration with "alembic upgrade head" This gave me the following error:

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)

Questions

Is this the right way to proceed in order to add columns dynamically or should it be done in another way?
How do I resolve the sqlalchemy operationalerror?

Thanks

Added the rest of the error but the entire error was very long so this is only the last part. Thanks — user12288003, Apr 21 '20 at 20:18
Added the entire traceback error with code formatting. Thanks — user12288003, Apr 21 '20 at 20:26
Stating the obvious, but it seems like connecting to PostgreSQL out right fails when you are running the Alembic command. — Ilja Everilä, Apr 21 '20 at 20:43
OK. Do you have any idea on what I could be doing wrong or what I should change to fix it? — user12288003, Apr 21 '20 at 20:47

score 5 · Answer 1 · answered Apr 21 '20 at 23:50

5

Try this in step 5

op.add_column('test1', sa.Column('new_column', sa.String()))

answered Apr 21 '20 at 23:50

Danilo Moura

191
4

Thanks for the tip but I still get the same error unfortunately. I am not sure if the problem is in step 3 perhaps – user12288003 Apr 22 '20 at 07:02

Adding column dynamically to an existing table using sqlalchemy, alembic

1 Answers1