How to add a column after another column in sqlalchemy for mysql?

Question

I have a table where I need to add column say a_1 after column a and the table structure is like column a,b,c,d? How can i add this?

Below query adds column after d but I want to add column after a?

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('sample_table', sa.Column('a_1', mysql.JSON(), nullable=True, ))
# ### end Alembic commands ###


def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('sample_table', 'a_1')
# ### end Alembic commands ###

Class alembic.operations.BatchOperations has a method add_column(column, insert_before=None, insert_after=None). alembic.sqlalchemy.org/en/latest/ops.html — Akina
– Akina, Commented Mar 19, 2021 at 7:59

servidey · Accepted Answer · 2023-09-21 14:58:55Z

Based on answer for similar question, alembic batch operation:

from alembic import op
import sqlalchemy as sa


def upgrade():
  with op.batch_alter_table('sample_table') as batch_op:
    batch_op.add_column(
      sa.Column('a_1', ...),
      insert_after='a',
    )


def downgrade():
  op.drop_column('sample_table', 'a_1')

The feature introduced with alembic 1.4.0. Reference in docs.

= = =

The docs describe "recreate" argument.

In my case [postgres 15, alembic 1.7.4] the above doesn't work without explicit recreate="always". It means a new table is created with all the data copied from existing one. The default recreate="auto" may perform the same actions.

def upgrade():
  with op.batch_alter_table('sample_table', recreate='always') as batch_op:
    ...

= = =

Some other ways:

sql dialect may allow .. ADD COLUMN .. AFTER <existing column> like MySql
recreate table with raw sql
db management tools (I'm not familiar with)

Alembic allows raw sql execution with op.execute(): more docs.

Biobo · Accepted Answer · 2021-03-19 12:16:48Z

add_column(table_name, column, schema=None)

Issue an “add column” instruction using the current migration context.

e.g.:

from alembic import op
from sqlalchemy import Column, String

op.add_column('organization',
    Column('name', String())
)

The provided Column object can also specify a ForeignKey, referencing a remote table name. Alembic will automatically generate a stub “referenced” table and emit a second ALTER statement in order to add the constraint separately:

from alembic import op
from sqlalchemy import Column, INTEGER, ForeignKey

op.add_column('organization',
    Column('account_id', INTEGER, ForeignKey('accounts.id'))
)

Note that this statement uses the Column construct as is from the SQLAlchemy library. In particular, default values to be created on the database side are specified using the server_default parameter, and not default which only specifies Python-side defaults:

from alembic import op
from sqlalchemy import Column, TIMESTAMP, func

# specify "DEFAULT NOW" along with the column add
op.add_column('account',
    Column('timestamp', TIMESTAMP, server_default=func.now())
)

Parameters table_name – String name of the parent table.

column – a sqlalchemy.schema.Column object representing the new column.

schema – Optional schema name to operate within. To control quoting of the schema outside of the default behavior, use the SQLAlchemy construct quoted_name.

Source https://alembic.sqlalchemy.org/en/latest/ops.html

Collectives™ on Stack Overflow

How to add a column after another column in sqlalchemy for mysql?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related