CODEWITHSUNDEEP
pythonoracle-databaseflasksqlalchemyflask-sqlalchemy
nycynik
nycynik

Reputation: 7541

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

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.

Upvotes: 0

Views: 3742

Answers (1)

nycynik
nycynik

Reputation: 7541

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.

Upvotes: 6

Related Questions