CODEWITHSUNDEEP
pythonsqlalchemydatabase-migrationalembic
Rafael Korbas
Rafael Korbas

Reputation: 2363

Set column order in alembic when adding a new column

I'm struggling to find a clean way (without raw SQL) to set the column order in alembic. For example, I would like to put a new column called 'name' after the 'id' column, something like this:

from alembic import op
import sqlalchemy as sa

...

op.add_column(
    'people',
    sa.Column(
        'name',
        sa.String(),
        nullable=False
    ),
    after='id'
)

But of course, alembic does not have the 'after' parameter, therefore this code fails and I have not found an equivalent to this 'after' parameter in the docs. I'm only able to append the column at the end of the table.

Can anybody suggest how to achieve in alembic/sqlalchemy what I want? Is it possible without raw SQL?

Upvotes: 19

Views: 7421

Answers (2)

anjaneyulubatta505
anjaneyulubatta505

Reputation: 11665

alembic version 1.4.0 or higher supports this feature.

use the batch operation to achieve it.

from alembic import op
import sqlalchemy as sa

with op.batch_alter_table("people") as batch_op:
  batch_op.add_column(
    Column("name", sa.String(50)),
    insert_after="id"
  )

Reference: https://alembic.sqlalchemy.org/en/latest/ops.html?highlight=insert_after#alembic.operations.BatchOperations.add_column

Upvotes: 4

RasikhJ
RasikhJ

Reputation: 611

Slice them into separate nested lists, ending at the column before the inserted column ( so in this case, create a one nested list that ends at the "id" column.

Let's call that variable id_col

Now, you can either use the "extend" built-in or "append" to add the "name" column right after the id column.

You can also add the nested lists to end up with a final single data structure that has what you're looking for.

The emphasis, here, being, that you do not have to be constrained by exclusively using your SQL table format. You can convert it into a a nested list and then once combined using list methods, reconvert them back into your desired SQL table.

Upvotes: 0

Related Questions