SQLAlchemy - Remove column from table definition

user_2690677 0
Viewed 6286

Let's say I want to dynamically set a primary key. I initialize the table with a dummy key because SQLAlchemy seems to require that. I wrote a function that I am using to specify the primary key based on a primary index that behaves like a primary key for all intents and purposes. My problem arises when I try to do away with the dummy key.

I would like to be able to remove my dummy key after table declaration so that I can replace it with my custom key. However, I can't just delattr(my_table,'dummy_key') because there will be other references to the dummy key for instance in my_table.__table__.columns and possibly elsewhere.

My question is, is there a good way to remove a column definition from a table that also removes all these other references?

Currently I define the table like so:

Base = declarative_base(cls=DeferredReflection)

class my_table(Base, TableExtension):
    __tablename__ = "my_table"
    __table_args__ = {'schema': 'my_schema'}
    dummy_key = Column(String(), primary_key=True) #false key, just a placeholder, sqlalchemy requires this

TableExtension adds a method:

def set_primary_key(self, column_name, column_type):
    self[column_name] = Column(column_type, primary_key=True)

Need help writing this function:

def remove_column(table, col_name):
    # what should I do here?

I already have a function get the new keys and it works:

column_names, column_types = get_primary_index(table)

I added a method to the table via inheritance to assign the new keys.

table.set_new_keys(column_names, column_types)

Then reflect the tables in the usual way:

Base.prepare(engine)

The big ? here is how to remove the column reference without doing much surgery on the rest of the table attributes. Any advice would be appreciated.

sqlalchemy python
3 Answers

There is no official documented way for this problem by SQLAlchemy. I dug into the source code and here's what I learnt:

  1. my_table._columns is a ColumnCollection
  2. my_table.columns is a ImmutableColumnCollection generated from my_table._columns
  3. my_table.c is a shortcut to my_table.columns

ColumnCollection has remove method defined which accepts Column object as an argument. So my solution to the problem is like:

# get the column instance from table object
my_col_instance = [col for col in my_table._columns if col.name == my_column_name][0]
# remove from the ColumnCollection, note that we're removing it from ._columns not .columns
my_table._columns.remove(my_col_instance)
# execute actual ALTER TABLE SQL query to drop the column
user_49321650

After deep in source code of SQLAlchemy, there might be no official way to support your needs.

After trying I have found a approach that seems valid(no Guarantee in stable)

simply get metadata from your table class by table_meta = my_table.metadata.tables[my_table.__tablename__] and modify table_meta._cloumns by table_meta._cloumns.remove(table_meta._cloumns['dummy_key'])

This worked for me in query select, hope helpful.

user_36159630

The other solutions seem to be no longer working.

My current workaround is the following

del Table.__mapper__._props["column_name"]
user_85204790
Related