Error on db.session.commit() With sqlachemy and Oracle

Question

I'm following the Flask Mega Tutorial by Miguel here http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-iv-database with just a small modification as using Oracle instead of sqlite as my db. I'm also using flask migrate instead of SQLAlchemy-migrate.

This is my models.py

from app import db

class User(db.Model):

    id = db.Column(db.Integer,db.Sequence('u_id'), primary_key=True)

    nickname = db.Column(db.String(64), index=True, unique=True)

    email = db.Column(db.String(120), index=True, unique=True)

    posts = db.relationship('Post', backref='author', lazy='dynamic')

    def __repr__(self):
        return '<User %r>' % (self.nickname)

class Post(db.Model):

    id = db.Column(db.Integer, primary_key = True)

    body = db.Column(db.String(140))

    timestamp = db.Column(db.DateTime)

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    def __repr__(self):
        return '<Post %r>' % (self.body)

According the the sqlachemy docs here http://docs.sqlalchemy.org/en/latest/dialects/oracle.html, since oracle doesn't support autoincrement, I have to specify a sequence. I have done that in the id column definition.

On the python prompt, I'm able to do:

from app import db, models

u = models.User(nickname='john', email='[email protected]')

db.session.add(u)

When I want to commit as

db.session.commit()

I get this error:

cursor.execute(statement, parameters) sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02289: sequence does not exist [SQL: b'INSERT INTO "user" (id, nickname, email) VALUES (u_id.nextval, :nicknam e, :email) RETURNING "user".id INTO :ret_0'] [parameters: {b'email': 'john@email .com', b'ret_0': None, b'nickname': 'john', 'ret_0': }]

So basically it is saying that the sequence does not exist. I thought that sqlalchemy would actually create sequence and use the autoincrementing value as the unique id. Looks like that doesn't happen. So I start afresh and this time, I create the u_id sequence on the database and then issued the

db.session.commit()

again. When I do that I get this error message:

sqlalchemy.orm.exc.FlushError: Instance has a NULL iden tity key. If this is an auto-generated value, check that the database table all ows generation of new primary key values, and that the mapped Column object is c onfigured to expect these generated values. Ensure also that this flush() is no t occurring at an inappropriate time, such aswithin a load() event.

I tried did this tutorial with sqlite and I didn't have to deal with autoincrement. I'm using Oracle now because that is the db I'll be developing against. Please help if you can. Thanks.

Not that i think it matters, but there is an extra , at the end of the arg-list in id = db.Column(... — hiro protagonist
@hiroprotagonist I removed that ',' . It is not the cause of my problem though. — okyere
As it turned out, cx_oracle was the culprit. When I had this issue I had installed version 5.2 (latest). I decided to drop to a previous version (5.1.3) and it worked fine. Later in the project, I decided to switch to python 2.7.10 and that worked well even with cx_oracle 5.2. So something about python 3.4.3 doesn't play nice with cx_oracle 5.2. — okyere

okyere okyere · Accepted Answer · 2015-09-12T03:49:24

This was a bug with cx_oracle 5.2 working with python 3.4.3. When I dropped to version 5.1.3, everything worked fine.

Error on db.session.commit() With sqlachemy and Oracle

1 Answers