Flask-WTF表单提交至SQLite数据库时日期类型错误求助

我是Flask新手，开发酒店预订表单并将数据存入SQLite数据库时遇到问题，错误提示为：SQLite Date type only accepts Python date objects as input。以下是相关代码与报错信息：

表单代码 #

class ReservationForm(FlaskForm):
    checkIn = DateField('Start Date', format='%Y-%m-%d', validators=(validators.DataRequired(),))    
    checkOut = DateField('End Date', format='%Y-%m-%d', validators=(validators.DataRequired(),))
    guestNumber = IntegerField('Number of guests', validators=(validators.InputRequired(),))
    numberOfRooms = IntegerField('Number of rooms', validators=(validators.InputRequired(),))
    room_type = SelectField(choices=[(r, r) for r in Room.room_type.property.columns[0].type.enums])
    amount = FloatField('Total', validators=(validators.InputRequired(),))
    paymentType = SelectField(choices=[(p, p) for p in Payment.paymentType.property.columns[0].type.enums])

路由代码 #

@auth.route('/reservation', methods=['GET', 'POST'])
def reservation():
    form = ReservationForm()

    if request.method == 'POST':
        checkIn = form.checkIn
        checkOut = form.checkOut
        guestNumber = form.guestNumber
        amount = form.amount
        
        new_reservation = Reservation(checkIn=checkIn, checkOut=checkOut, guestNumber=guestNumber, amount=amount, status = "New")
        db.session.add(new_reservation)
        db.session.commit()
        flash('Reservation created!', category='success')
        return redirect(url_for('/'))

    return render_template("reservation.html", form=form, user=current_user)

数据库模型 #

class Reservation(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    guestNumber = db.Column(db.Integer, nullable=False)
    checkIn = db.Column(db.Date)
    checkOut = db.Column(db.Date)
    amount = db.Column(db.Integer, nullable=False)
    status = db.Column(db.Enum('New', 'Canceled', 'Passed', name='ReservationStatus'), nullable=False)

HTML模板 #

{% extends "base.html" %}
{% block title %} Reservation {% endblock %}

{% block content %}
<form method="POST">
  <h3 align="center">Make your reservation</h3>
  {{ form.hidden_tag() }}
    <div class="form-group">
    <label for="checkIn">Check-in Date</label>
      {{form.checkIn(class_='form-control', type='date')}}
    </div>
    <div class="form-group">
      <label for="checkOut">Check-out Date</label>
      {{form.checkOut(class_='form-control')}}
    </div>
    <div class="form-group">
      <label for="numberOfRooms">Number of Rooms</label>
      {{form.numberOfRooms(class_='form-control')}}
  </div>
  <div class="form-group">
    <label for="guestNumber">Number of Guests</label>
    {{form.guestNumber(class_='form-control')}}
  </div>
    <div class="form-group">
      <label for="amount">Total</label>
      {{form.amount(class_='form-control')}}
    </div>

  <br />
  <button type="submit" class="btn btn-primary">Submit</button>

</form>
{% endblock %}

报错回溯 #

Error on request:
Traceback (most recent call last):
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1719, in _execute_context        
    context = constructor(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 1070, in _init_compiled       
    param = [
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 1071, in <listcomp>
    processors[key](compiled_params[key])
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\dialects\sqlite\base.py", line 1068, in process        
    raise TypeError(
TypeError: SQLite Date type only accepts Python date objects as input.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\werkzeug\serving.py", line 319, in run_wsgi
    execute(self.server.app)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\werkzeug\serving.py", line 306, in execute
    application_iter = app(environ, start_response)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 2095, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 2080, in wsgi_app
    response = self.handle_exception(e)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 2077, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1525, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1523, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1509, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "C:\Users\mcraciun\Documents\Maria\Disertatie\WebAppForHotelManagement\website\auth.py", line 111, in reservation
    db.session.commit()
  File "<string>", line 2, in commit
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 1431, in commit
    self._transaction.commit(_to_root=self.future)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 829, in commit
    self._prepare_impl()
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 808, in _prepare_impl
    self.session.flush()
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 3363, in flush
    self._flush(objects)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 3502, in _flush
    with util.safe_reraise():
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 3463, in _flush
    flush_context.execute()
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 456, in execute
    rec.execute(self)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\persistence.py", line 245, in save_obj
    _emit_insert_statements(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\persistence.py", line 1238, in _emit_insert_statements
    result = connection._execute_20(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\sql\elements.py", line 326, in _execute_on_connection  
    return connection._execute_clauseelement(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1498, in _execute_clauseelement  
    ret = self._execute_context(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1725, in _execute_context        
    self._handle_dbapi_exception(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 2043, in _handle_dbapi_exception 
    util.raise_(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1719, in _execute_context        
    context = constructor(
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 1070, in _init_compiled       
    param = [
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 1071, in <listcomp>
    processors[key](compiled_params[key])
  File "C:\Users\mcraciun\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\dialects\sqlite\base.py", line 1068, in process        
    raise TypeError(
sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type only accepts Python date objects as input.
[SQL: INSERT INTO reservation ("guestNumber", "checkIn", "checkOut", amount, status, hotel_id) VALUES (?, ?, ?, ?, ?, ?)]
[parameters: [{'status': 'New', 'amount': <wtforms.fields.numeric.FloatField object at 0x0000029F0B3BD510>, 'checkIn': <wtforms.fields.datetime.DateField object at ... (51 characters truncated) ... s.numeric.IntegerField object at 0x0000029F0B3BD2D0>, 'checkOut': <wtforms.fields.datetime.DateField object at 0x0000029F0B3BD210>, 'hotel_id': None}]]

解决方案 #

核心问题 #

从报错参数能看出，你传递给数据库的是WTForms的字段对象（比如<wtforms.fields.datetime.DateField object...>），而不是字段实际的值。SQLite的Date类型只接受Python标准库的date对象，所以必须获取字段的.data属性值。此外还有字段类型不匹配的问题。

具体修复步骤 #

1. 获取表单字段的实际值：所有表单字段都要通过.data属性获取值，而不是直接引用字段对象。
2. 修正amount字段类型不匹配：数据库模型中amount是Integer类型，但表单用的是FloatField，要么把模型改成Float，要么将表单数据转为整数。
3. 使用表单验证：用form.validate_on_submit()替代request.method == 'POST'，触发WTForms的验证逻辑，确保数据符合要求。

修改后的路由代码 #

@auth.route('/reservation', methods=['GET', 'POST'])
def reservation():
    form = ReservationForm()

    if form.validate_on_submit():
        # 获取表单字段的实际值
        checkIn = form.checkIn.data
        checkOut = form.checkOut.data
        guestNumber = form.guestNumber.data
        # 转换amount为整数，或者修改模型为Float
        amount = int(form.amount.data)
        
        new_reservation = Reservation(
            checkIn=checkIn, 
            checkOut=checkOut, 
            guestNumber=guestNumber, 
            amount=amount, 
            status="New"
        )
        db.session.add(new_reservation)
        db.session.commit()
        flash('Reservation created!', category='success')
        # url_for参数是端点名称，不是路径，这里改成对应的首页端点（比如'home'）
        return redirect(url_for('home'))

    return render_template("reservation.html", form=form, user=current_user)

额外说明 #

• 如果金额需要保留小数，把数据库模型的amount字段改成db.Column(db.Float, nullable=False)，这样就不用转成整数。
• url_for('/')是错误用法，应该传入视图函数的名称，比如url_for('home')，对应首页的路由函数名称。

内容的提问来源于stack exchange，提问作者Maria