Using SQLAlchemy with Oracle and Flask, to create sequences for Primary Keys

Question

When I use SQLAlchemy with Oracle, I have to also add sequences for the primary keys, but the migration is not creating the sequences on it's own. How can I get the sequences to be created?

I have tried multiple times tweaking the code to get SQLAlchemy to create oracle sequences for primary keys, but so far, I have not been able to get Oracle sequences created by SQLAlchemy. I have a very simple user/role setup so far, and the tables exist, but not the sequences. It shows no errors when it runs.

The Model class looks as follows:

class Role(SurrogatePK, Model):
    """A role for a user."""

    __tablename__ = 'roles'
    id = db.Column(db.Integer, db.Sequence(__tablename__ + '_id_seq'), primary_key=True)
    name = Column(db.String(80), unique=True, nullable=False)
    user_id = reference_col('users', nullable=True)
    user = relationship('User', backref='roles')

    def __init__(self, name, **kwargs):
        """Create instance."""
        db.Model.__init__(self, name=name, **kwargs)

I am using Flask, and SQLAlchemy and after I run;

$ python manage.py db init
Creating directory <lots removed here>...done

$ $ python manage.py db migrate
INFO  [alembic.runtime.migration] Context impl OracleImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'users'
INFO  [alembic.autogenerate.compare] Detected added table 'roles'

I see no errors, and everything looks good. However, after I run;

$ python manage.py db upgrade
INFO  [alembic.runtime.migration] Context impl OracleImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 61ca5eb70d06, empty message

The first time I try to create a record, It fails and shows:

sqlalchemy.exc.DatabaseError DatabaseError: (cx_Oracle.DatabaseError) ORA-02289: sequence does not exist

If I manually create the sequences, it works fine.

alembic is not a completely automated solution. You have to manually adjust the created migration files for thing it can't figure out on its own. In this case, it seems that you have to manually add code to create/drop sequences. — univerio
– univerio, Commented Mar 4, 2016 at 0:10

nycynik · Accepted Answer · 2016-03-04 01:08:18Z

Thanks to univerio, I found out that alembic does not handle the creation of the sequences for you. So, based on that I googled around, and came up with this solution:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###

    # not sure of the sequence for creating an object, so just called execute below.
    # op.execute(sa.schema.CreateSequence(sa.Sequence("users_id_seq")))

    op.execute("create sequence roles_id_seq start with 1 increment by 1 nocache nocycle")
    op.execute("create sequence users_id_seq start with 1 increment by 1 nocache nocycle")

and for downgrade:

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.execute(sa.schema.DropSequence(sa.Sequence("roles_id_seq")))
    op.execute(sa.schema.DropSequence(sa.Sequence("users_id_seq")))

As you can see, not sure what the syntax is for creating a sequence with nocache, so I just called the SQL directly. This worked, and created the sequences that were needed.

Collectives™ on Stack Overflow

Using SQLAlchemy with Oracle and Flask, to create sequences for Primary Keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related