SQLite No such Column error while using flask and sqlalchemy

Question

I am a bit new to flask and so I am having trouble executing the code where I actually want to add an item while adding the time it was added so as to determine the latest items added on the webpage. But when I try it throws an error

OperationalError: (sqlite3.OperationalError) no such column: items.date.

This one is my database.py

from sqlalchemy import Column, ForeignKey, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()

class Item(Base):
    __tablename__ = 'items'

    title = Column(String(80), nullable=False)
    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)
    description = Column(String(250))
    category_id = Column(Integer, ForeignKey('category.id'))
    category = relationship(Category)

engine = create_engine('sqlite:///catalogs.db')
Base.metadata.create_all(engine)

project_pc.py:

@app.route('/Category/<int:category_id>/items/new/', methods=['GET', 
'POST'])
def newItem(category_id):
    category = session.query(Category).filter_by(id=category_id).one()
    if request.method == 'POST' :
        newItem = Item(title=request.form['name'], 
                       description = request.form['description'],
                       category_id = category_id,
                       date=datetime.now())
        session.add(newItem)
        session.commit()
        flash('New %s Item Successfully Created' % (newItem.title))
        return redirect(url_for('showItems', category_id=category_id))
    else:
        return render_template('newItem.html', category_id=category_id)



    Traceback (most recent call last):
         File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 
        1701, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1689, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1687, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1360, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1358, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1344, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/vagrant/project_pc.py", line 234, in showItems
    category_id=category_id).all()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2588, in all
    return list(self)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2736, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2751, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
OperationalError: (sqlite3.OperationalError) no such column: items.date [SQL: u'SELECT items.title AS items_title, items.id AS items_id, items.date AS items_date, items.description AS items_description, items.category_id AS items_category_id, items.user_id AS items_user_id \nFROM items \nWHERE items.category_id = ?'] [parameters: (2,)

Answer 1

I guess you've first created the database without this date column in the model Item. The method create_all cannot add this missing column to an existing table (it can add new table, but it's not as powerful as a migration tool).

---

If you can drop all the data inside your database, just delete the file catalogs.db (or better rename it as a backup), then the create_all method will generate a brand new working database.

---

If you already have datas in this database and don't want to drop them, you could manually ALTER the table from a shell with something like below, but this way is very limited because the NOT NULL property needs a default value to be applied to each existing row, and the ALTER command won't accept a CURRENT_TIME or CURRENT_TIMESTAMP , only a constant value. The following command could work only if you don't care about using a default hardcoded date like today or"1900-01-01 00:00:00":

ALTER TABLE items ADD COLUMN date DATETIME NOT NULL DEFAULT '2018-06-14 22:00:00';

---

So you may need to create a new table and copy all the rows. Take a look at this question that match exactly this point : Add not null DateTime column to SQLite without default value?

Answer 2

Terminate your server if it still running . Then navigate to your folder which has catalogs.db and rename it to backups.db or something else ..you can also delete it. Run your server and the create all method will automatically create catalogs.db for you. I think the error occurred because you had made some changes after the creation. I was experiencing the same issue so hopefully, it will help you.