CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemyflask-sqlalchemy
Pav Sidhu
Pav Sidhu

Reputation: 6944

SQLAlchemy OperationalError due to Query-invoked autoflush

I have a table in a database which is created and accessed through SQLAlchemy:

I add a record to it using Flask-SQLAlchemy like so:

...
content = request.form['content']
date = datetime.today()
post = Post(date, content)
db.session.add(post)
db.session.commit()
...

This record is added to the table fine. Right after that code is executed, I query another table:

userID = session['userID']
posts = db.session.query(Post).filter_by(userID=userID).count()

However I receive an error during the query:

OperationalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (_mysql_exceptions.OperationalError) (1292, "Incorrect date value: '11/20' for column 'date' at row 1") [SQL: u'UPDATE posts SET date=%s WHERE posts.id = %s'] [parameters: (('11/20', 1L))]

Why is the date of the post being updated when I have already specified it when adding the record to the table? Also what could the cause of this error be? Thanks.

Edit:

This is what the table model is like:

class Post(db.Model):

  __tablename__ = 'posts'

  id = db.Column(db.Integer, primary_key=True)
  content = db.Column(db.String(500))
  date = db.Column(db.Date, nullable=False)

  def __init__(self, id, content, date):
      self.id = id
      self.content = content
      self.date = date

Upvotes: 11

Views: 9119

Answers (3)

Stephane Martin
Stephane Martin

Reputation: 1642

date = datetime.today() returns a datetime object (date AND time)

but the date attribute of the Post model is a db.Date (date WITHOUT time)

Try either :

  from datetime import date

  ...

  content = request.form['content']

  date = date.today()    #inject a Date object rather than a Datetime

or:

  class Post(db.Model):     #modify Post schema

  ...

     date = db.Column(db.TIMESTAMP, nullable=False)

Upvotes: 1

SheepPerplexed
SheepPerplexed

Reputation: 1152

Stephane is right, you are passing a wrong type to the model, either pass datetime.date object or change the definition of the model. As to the first part of the question, I recommend reading something about sessions and flushing. This is important:

All changes to objects maintained by a Session are tracked - before the database is queried again or before the current transaction is committed, it flushes all pending changes to the database.

So by creating the post object and adding it to the session, you made just a pending change, but there was no communication with the database at that point yet. That happens with flush(), which you can either call manually or automatically, for example, by calling commit().

(btw. you dont need to create your own init method for the model, see http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#adding-and-updating-objects)

Upvotes: 6

faisal burhanudin
faisal burhanudin

Reputation: 1160

This error can cause from another query, even if you solve it that exceptions will still occured if you not rollback previous session error

You can catch exception and rollback transaction

usually in my flask application, I commit session in end of request

@app_instance.after_request
def after(response):
    try:
        # commit transaction
        db.session.commit()
    except Exception:
        db.session.rollback()
        raise

    return response

Upvotes: -1

Related Questions