alembic and getting the last inserted value

Question

I'm using alembic to manage my database structure.

After adding a table using id as Integer and primary key the id column will be an autoincrement-column. How do I query the data in the upgrade script so I'm sure that I get the correct id (I know it's 1 in this specific case)?

I know how to

#creating the table
op.create_table(
    'srv_feed_return_type',
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('name', sa.String(50), nullable=False),
    sa.Column('created', sa.DateTime, server_default=func.now(), nullable=False),
    sa.Column('created_by', sa.String(50), nullable=False),
    sa.Column('last_updated', sa.DateTime, nullable=False),
    sa.Column('last_updated_by', sa.String(50), nullable=False)
)

#table for operations
srv_feed_return_type = table('srv_feed_return_type',
                             column('name'),
                             column('created'),
                             column('created_by'),
                             column('last_updated'),
                             column('last_updated_by'))

#bulk insert
op.bulk_insert(srv_feed_return_type,
               [
                   {'name': 'dataset',
                    'created': datetime.now(), 'created_by': 'Asken',
                    'last_updated': datetime.now(), 'last_updated_by': 'Asken'}
               ])

I know I can do update but how do I do a select using something similar like below?

op.execute(
    srv_feed_return_type.update().\
        where(srv_feed_return_type.c.name==op.inline_literal('dataset')).\
        values({'name':op.inline_literal('somethingelse')})
        )

Is it safe to assume that there won't be any other processes modifying your newly created table until your migration is finished? — vvladymyrov, Mar 21 '13 at 13:59
for this example yes but I know that it'll be one here. in another migration it will not be 1 anymore. i need to know the id of a specific return type. — Asken, Mar 21 '13 at 14:11
What database are you using for your app and is there possibility to more to another db - is there requirement to have application be database agnostic? — vvladymyrov, Mar 21 '13 at 15:24

score 5 · Accepted Answer · answered Mar 21 '13 at 18:37

First to have auto-increment column you'll need to modify table schema definition to have Sequence passed for primary key column: sa.Column('id', sa.Integer, Sequence('srv_feed_r_t_seq'),primary_key=True),

#creating the table
op.create_table(
    'srv_feed_return_type',
    sa.Column('id', sa.Integer, Sequence('srv_feed_r_t_seq'),primary_key=True),
    sa.Column('name', sa.String(50), nullable=False),
    sa.Column('created', sa.DateTime, server_default=func.now(), nullable=False),
    sa.Column('created_by', sa.String(50), nullable=False),
    sa.Column('last_updated', sa.DateTime, nullable=False),
    sa.Column('last_updated_by', sa.String(50), nullable=False)
)

Now about how to get PK id:

op.execute and op.bulk_insert doesn't return you any results. But you can get same connection used for these operations.

After doing bulk_insert or execute(table.update ...) you can run select query in the same context and retrieve PK id for the record of interest:

connection = op.get_bind()
r = connection.execute(srv_feed_return_type.select().where(...))
for row in r:
    pk_id = r['id']
    """or something more sophisticated"""

You'll need to specify proper filter in where clause to make sure that you identified record you recently changed in unique way.

Here is some example of similar functionality, but it has hardcoded select queries

Will try. Thanks! Btw, by defining Integer and primary_key=True it will automatically be an auto-increment. — Asken, Mar 21 '13 at 19:21
Thank you for hint about automatic autoincrement. It didn't work for me when I was using Oracle - so I had to use sequence explicitly — vvladymyrov, Mar 21 '13 at 20:12
Maybe it's different for Oracle... maybe I should add it to my code for good measure to be safe. — Asken, Mar 22 '13 at 07:12

alembic and getting the last inserted value

1 Answers1

Linked