SQLAlchemy OperationalError due to Query-invoked autoflush

Question

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

Answer 1

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)

Answer 2

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)