python - SQLite 在使用 flask 和 sqlalchemy 时没有这样的列错误

Question

我对 Flask 有点陌生，所以我在执行代码时遇到了问题，我实际上想添加一个项目，同时添加添加时间以确定网页上添加的最新项目。但是当我尝试时它会抛出一个错误

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

这是我的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

我想您首先创建的数据库没有模型 Item 中的此 date 列。 create_all 方法无法将这个缺失的列添加到现有表中(它可以添加新表，但不如 migration tool 强大)。

---

如果您可以删除数据库中的所有数据，只需删除文件 catalogs.db(或者最好将其重命名为备份)，然后 create_all 方法将生成一个全新的工作数据库。

---

如果你已经在这个数据库中有数据并且不想删除它们，你可以手动 ALTER shell 中的表，如下所示，但这种方式非常有限，因为 NOT NULL 属性需要将默认值应用于每个现有行，ALTER 命令不会接受 CURRENT_TIME 或 CURRENT_TIMESTAMP ，只有一个常量值。以下命令仅在您不关心使用默认硬编码日期(如今天或"1900-01-01 00:00:00")时才有效:

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

---

因此您可能需要创建一个新表并复制所有行。看看这个与这一点完全匹配的问题:Add not null DateTime column to SQLite without default value?