title: How to perform a data migration with Alembic and two versions of my Table?
body: <p>I'm trying to refactor a database model; separate one column out of a table into another new one. I'd like to do this using existing SQLAlchemy Core models &amp; Alembic. I'd also like to use server-side <code>INSERT ... FROM SELECT ...</code>-style query to migrate data (<a href="https://docs.sqlalchemy.org/en/14/tutorial/data_insert.html#insert-from-select" rel="nofollow noreferrer">docs</a>). By avoiding having to copy all the gazillion of rows to Python-world I hope to have maximum scalability, maximum performance and minimum downtime.</p>
  <p>My problem is the programmatic use of SQLAlchemy running on two versions of the same table name in a single Metadata context. Should I resort to using an textual SQL instead? </p>
  <p><code>schema.py</code> before:</p>
  <pre class="lang-py prettyprint-override"><code>class User(Base):
      __tablename__ = &quot;users&quot;
  
      id = Column(BigInteger, primary_key=True, autoincrement=False, nullable=False)
      [...]
      profile_picture_url = Column(String, nullable=True)
  </code></pre>
  <p><code>schema.py</code> after:</p>
  <pre class="lang-py prettyprint-override"><code>class User(Base):
      __tablename__ = &quot;users&quot;
  
      id = Column(BigInteger, primary_key=True, autoincrement=False, nullable=False)
      [...]
  
  class UserProfileExtras(Base):
      __tablename__ = &quot;user_profile_extras&quot;
      
      user_id = Column(BigInteger, ForeignKey(&quot;users.id&quot;), index=True, nullable=False)
      profile_picture_url = Column(String, nullable=False)
  </code></pre>
  <p>So here's my attempt to create an Alembic upgrade script:</p>
  <pre class="lang-py prettyprint-override"><code># Import the new/current-in-code models.
  from ... import User, UserProfileExtras
  
  # Define the previous User model in order to operate on the current/old schema.
  class UserBeforeUpgrade(Base):
      __tablename__ = &quot;users&quot;
  
      id = Column(BigInteger, primary_key=True, autoincrement=False, nullable=False)
      [...]
      profile_picture_url = Column(String, nullable=True)
  
  
  table_before_upgrade: Table = UserBeforeUpgrade.__table__
  
  new_target_table = UserProfileExtras.__table__
  
  [...]
  
  def upgrade() -&gt; None:
      op.create_table(
          &quot;user_profile_extras&quot;,
          sa.Column(&quot;user_id&quot;, sa.BigInteger(), autoincrement=False, nullable=False),
          sa.Column(&quot;profile_picture_url&quot;, sa.VARCHAR(), nullable=False),
          [...]
      )
  
      from_user_table = (select([table_before_upgrade.c.id, table_before_upgrade.c.profile_picture_url])
                         .where(table_before_upgrade.c.profile_picture_url != None))
      insert_from = (
          new_target_table.insert().from_select(
              [new_target_table.c.user_id, new_target_table.c.profile_picture_url],
              from_user_table)
      )
      op.execute(insert_from))
  
      [...]
  
  [...]
  </code></pre>
  <p>Error:</p>
  <pre><code>sqlalchemy.exc.InvalidRequestError: Table 'users' is already defined for this MetaData instance.
  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.
  </code></pre>