Adding Columns to Database Table with SQLAlchemy-Utils

This function uses the SQLAlchemy-Utils library to add multiple columns to a specific table in the database. It first checks if the table exists, if not it creates the table, then adds the specified columns to the table, and then re-creates the table to apply the changes.

Technology Stack : SQLAlchemy-Utils, SQLAlchemy, MetaData, Table, Column, Integer, String, declarative_base, database_exists, create_database, drop_database, session, autoload_with, reflect

Code Type : The type of code

Code Difficulty : Intermediate

                
                    
def add_columns_to_table(session, table_name, column_names):
    """
    Adds multiple columns to a specific table in the database using SQLAlchemy-Utils.

    Args:
        session (sqlalchemy.orm.Session): The SQLAlchemy session.
        table_name (str): The name of the table to add columns to.
        column_names (list of str): A list of column names to add to the table.
    """
    from sqlalchemy_utils import database_exists, create_database, drop_database
    from sqlalchemy import MetaData, Table, Column, Integer, String
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

    # Check if the database exists
    if not database_exists(session.bind, table_name):
        create_database(session.bind, table_name)

    # Reflect the table from the database
    meta = MetaData()
    table = Table(table_name, meta, autoload_with=session.bind)

    # Add columns to the table
    for column_name in column_names:
        column = Column(column_name, Integer, nullable=False)
        table.append_column(column)

    # Drop the old table and create a new one with the added columns
    meta.reflect()
    drop_database(session.bind, table_name)
    create_database(session.bind, table_name)

    # Re-reflect the new table
    meta.reflect()
    session.commit()