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
– user12288003, Commented Apr 21, 2020 at 20:18
Added the entire traceback error with code formatting. Thanks — user12288003
– user12288003, Commented Apr 21, 2020 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ä
– Ilja Everilä, Commented Apr 21, 2020 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
– user12288003, Commented Apr 21, 2020 at 20:47

Danilo Moura · Accepted Answer · 2020-04-21 23:50:05Z

7

Try this in step 5

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

answered Apr 21, 2020 at 23:50

Danilo Moura

3211 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user12288003 Over a year ago

Thanks for the tip but I still get the same error unfortunately. I am not sure if the problem is in step 3 perhaps

Daniel Santana · Accepted Answer · 2023-09-08 19:56:07Z

Operational Error

The error reported by @user12288003 is a standard connectivity issue and Alembic Error Documentation says:

The OperationalError is the most common (but not the only) error class used by drivers in the context of the database connection being dropped, or not being able to connect to the database. For tips on how to deal with this, see the section Dealing with Disconnects.

This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

For this error, check if your database connection string is correct or if for some reason you lost access to your db provider.

How to Add a New Column to a DB Table using Alembic

For those who, like me, end up here wanting to know how to Programmatically Add a Column to your database, using SQL Alchemy, Python, and Alembic, the steps I used are the following, complementing Danilo's answer, and following Alembic Documentation:

Create a new revision. This will create a new version file in the folder [your_app]/alembic/versions

  alembic revision -m "Database v1.0"

In the recently created file, scroll until the def upgrade() -> None: part, and add the information about the column to be created. In the following example, a new column "name" is being added to the table "users":

    def upgrade() -> None:
      with op.batch_alter_table("users") as alter_table_users_op:
        alter_table_users_op.add_column(
          sa.Column("name", sa.String(255)), nullable=False, insert_after="id")

Run the migration

  alembic upgrade head

Versions:

python 3.11.5
pip 23.2.1
alembic 1.11.1

Collectives™ on Stack Overflow

Adding column dynamically to an existing table using sqlalchemy, alembic

2 Answers 2

1 Comment

Operational Error

How to Add a New Column to a DB Table using Alembic

Versions:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Operational Error

How to Add a New Column to a DB Table using Alembic

Versions:

Comments

Your Answer

Sign up or log in

Post as a guest

Related