Извлеките значения таблиц с помощью перегонного куба и обновите их до другой таблицы.

У меня есть oauth secret и oauth key в таблице client. Теперь я перемещаю их в таблицу oauth credentials, которая будет создана во время миграции. Перегонный куб выпускается по следующей схеме для апгрейда. 

from myapp.models import Client, ClientCredential
from alembic import op
import sqlalchemy as sa


def upgrade():
### commands auto generated by Alembic - please adjust! ###
    op.create_table('client_credential',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('updated_at', sa.DateTime(), nullable=False),
    sa.Column('client_id', sa.Integer(), nullable=False),
    sa.Column('key', sa.String(length=22), nullable=False),
    sa.Column('secret', sa.String(length=44), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.ForeignKeyConstraint(['client_id'], ['client.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('key')
   )
   # Here I need to copy data from table A to newly created Table.
   # Now Client table will not have secret and key attributes
   clients = [{'secret': client.secret, 'key': client.key, 'is_active':True, 'client_id': client.id, 'created_at': sa.func.now(), 'updated_at': sa.func.now()} for client in Client.query.all()]
   op.bulk_insert(ClientCredential, clients)
   #Also replaced above two lines with 
   #connection = op.get_bind()
   #print connection.execute(Client, Client.query.all())
   op.drop_column(u'client', u'secret')
   op.drop_column(u'client', u'key')

Как только скрипт входит в clients или connection.execute перегонный куб зависает. После включения sqlalchemy logs, Base.Engine пусто. Тоже пробовал op.execute, не повезло.

Log

INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade 25e7a9839cd4 -> 176fb70348b9, Added  ClientCredential
2013-09-10 23:59:08,144 INFO sqlalchemy.engine.base.Engine select version()
INFO  [sqlalchemy.engine.base.Engine] select version()
2013-09-10 23:59:08,145 INFO sqlalchemy.engine.base.Engine {}
INFO  [sqlalchemy.engine.base.Engine] {}
2013-09-10 23:59:08,146 INFO sqlalchemy.engine.base.Engine select current_schema()
INFO  [sqlalchemy.engine.base.Engine] select current_schema()
2013-09-10 23:59:08,146 INFO sqlalchemy.engine.base.Engine {}
INFO  [sqlalchemy.engine.base.Engine] {}
2013-09-10 23:59:08,148 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
2013-09-10 23:59:08,150 INFO sqlalchemy.engine.base.Engine SELECT client.id AS client_id,    client.created_at AS client_created_at, client.updated_at AS client_updated_at, client.user_id   AS client_user_id, client.org_id AS client_org_id, client.title AS client_title,   client.description AS client_description, client.website AS client_website, client.redirect_uri AS client_redirect_uri, client.notification_uri AS   client_notification_uri, client.iframe_uri AS client_iframe_uri, client.resource_uri AS client_resource_uri, client.active AS client_active, client.allow_any_login AS client_allow_any_login, client.team_access AS client_team_access, client.trusted AS client_trusted
FROM client
INFO  [sqlalchemy.engine.base.Engine] SELECT client.id AS client_id, client.created_at AS    client_created_at, client.updated_at AS client_updated_at, client.user_id AS client_user_id, client.org_id AS client_org_id, client.title AS client_title, client.description AS client_description, client.website AS client_website, client.redirect_uri AS client_redirect_uri, client.notification_uri AS client_notification_uri, client.iframe_uri AS client_iframe_uri, client.resource_uri AS client_resource_uri, client.active AS client_active, client.allow_any_login AS client_allow_any_login, client.team_access AS client_team_access, client.trusted AS client_trusted
FROM client
2013-09-10 23:59:08,150 INFO sqlalchemy.engine.base.Engine {}
INFO  [sqlalchemy.engine.base.Engine] {}

Как я могу скопировать значения из таблицы client в таблицу client_credential с помощью миграции перегонного куба?

2 15
python sqlalchemy alembic

2 ответа:

Наконец я решил эту проблему. Создал raw sql для извлечения значений и использовал bulk_insert.

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('client_credential',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('updated_at', sa.DateTime(), nullable=False),
    sa.Column('client_id', sa.Integer(), nullable=False),
    sa.Column('key', sa.String(length=22), nullable=False),
    sa.Column('secret', sa.String(length=44), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.ForeignKeyConstraint(['client_id'], ['client.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('key')
    )
    #http://stackoverflow.com/questions/15725859/sqlalchemy-alembic-bulk-insert-fails-str-object-has-no-attribute-autoincre
    client_credential = sa.sql.table('client_credential',
        sa.Column('client_id', sa.Integer, nullable=False),
        sa.Column('is_active', sa.Boolean, nullable=False, default=True),
        sa.Column('key', sa.String(22), nullable=False, default=True),
        sa.Column('secret', sa.String(22), nullable=False, default=True),
        sa.Column('created_at', sa.DateTime, nullable=False, default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime, nullable=False, default=sa.func.now()),
    )
    conn = op.get_bind()
    res = conn.execute("select secret, key, id from client")
    results = res.fetchall()
    clients = [{'secret': r[0], 'key': r[1], 'is_active':True, 'client_id': r[2], 'created_at': datetime.datetime.now(), 'updated_at': datetime.datetime.now()} for r in results]
    op.bulk_insert(client_credential, clients)
    op.drop_column(u'client', u'secret')
    op.drop_column(u'client', u'key')
    ### end Alembic commands ###
23

Вы можете создать DBSession с помощью SQLAlchemy engine bind, тогда вы можете избежать использования SQL-запроса.

from myapp.models import Client, ClientCredential
from alembic import op, context
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    url = context.config.get_main_option("sqlalchemy.url")
    engine = sa.create_engine(url)
    DBSession.configure(bind=engine)

    op.create_table(
        'client_credential',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.Column('client_id', sa.Integer(), nullable=False),
        sa.Column('key', sa.String(length=22), nullable=False),
        sa.Column('secret', sa.String(length=44), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(['client_id'], ['client.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('key'))
    # Here I need to copy data from table A to newly created Table.
    # Now Client table will not have secret and key attributes
    clients = [
        {'secret': client.secret,
         'key': client.key,
         'is_active': True,
         'client_id': client.id,
         'created_at': sa.func.now(),
         'updated_at': sa.func.now()}
        for client in Client.query.all()]
    op.bulk_insert(ClientCredential, clients)
    #Also replaced above two lines with
    #connection = op.get_bind()
    #print connection.execute(Client, Client.query.all())
    op.drop_column(u'client', u'secret')
    op.drop_column(u'client', u'key')
2