SQLAlchemy OperationalError due to Quoo-invoked autoflush

Question

I have a table in a database that is created and accessible through SQLAlchemy:

I add a record to it using Flask-SQLAlchemy, for example:

... 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 in order. Immediately after this code, I request another table:

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

However, during the request, I get a message:

OperationalError: (raised as a result of a called Query autoflush; consider using the session.no_autoflush block if this flash happens prematurely) (_mysql_exceptions.OperationalError) (1292, "Date value is incorrect: '11 / 20 'for column" date "on row 1 ") [SQL: u'UPDATE messages SET date =% s WHERE posts.id =% s '] [parameters: (('11 / 20', 1L))]

Why is the date of adding a message updated when I already specified it when adding a record to the table? And what could be causing this error? Thanks.

Edit:

Here's what the table model looks 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 

Answer 1

Stefan is right, you are passing the wrong model type, either passing the datetime.date object, or change the model definition. Regarding the first part of the question, I recommend reading something about sessions and cleanup . It is important:

All changes in the objects supported by the session are monitored - before the database is queried again or before the current transaction is committed, it resets all pending changes to the database.

So, by creating a post object and adding it to the session, you only made a pending change, but so far there was no connection to the database. This happens with flush (), which can be called manually or automatically, for example, by calling commit ().

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

Answer 2

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

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

Try either:

• from datetime import date

  ...

  content = request.form ['content']

  date = date.today () -> add a Date object, not a Datetime

or

• class Post (db.Model): → change the message scheme

  ...

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

Answer 3

This error may arise from another request, even if you decide that exceptions will still occur if you do not discard the previous session error.

You can catch the exception and rollback transaction

usually in my jar application, I commit the session at the end of the request

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