在将数据添加到DB之前，烧瓶Sqlalchemy查询多个条件

Question

编辑： 更好地解释： 在将数据添加到DB之前，我需要检查是否满足多个条件以避免重复。 写的elif的方式仅检查是否满足了任何条件，而不是所有的条件。

我 数据使用SQLite写入SQL DB。

在添加新的预订时，我正在努力写作条件，这将阻止双重预订。我能够做一个独特的条件，但是如果我编写多个条件，它们似乎是单独工作的，而它们是同时存在的。我说的是《代码米》中的两个Elif条件。不查询数据库数据的条件正常工作。 我的错误在哪里？

from flask import Flask, render_template, url_for, request, redirect, flash
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import select, and_ 
from datetime import datetime, timedelta



app = Flask(__name__)
app.config['SESSION_TYPE'] = 'memcached'
app.config['SECRET_KEY'] = 'super secret key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///parking_booking.db'
db = SQLAlchemy(app)

time_now = datetime.now()

class Parking(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    spot_number = db.Column(db.String(2))
    license_plate = db.Column(db.String(10))
    start = db.Column(db.String(20))
    end = db.Column(db.String(20)) 
    date_created = db.Column(db.DateTime, default=datetime.utcnow)
    
 
    def __repr__(self):
        return '<Parking %r>' % self.id



@app.route('/', methods=['POST', 'GET'])
def index():
 

    if request.method == 'POST':
        booking_spot_number = str(request.form['spot_number'])
        booking_license_plate = str(request.form['license_plate'].replace(' ', '').upper())          
        booking_start = datetime(*[int(v) for v in request.form['start'].replace('T', '-').replace(':', '-').split('-')])
        booking_end = datetime(*[int(v) for v in request.form['end'].replace('T', '-').replace(':', '-').split('-')])    
     
        if booking_end < booking_start:
            flash('End time has to be later than start time', category='error')
        elif booking_start < datetime.now():
            flash('Start time can''t be in the past.', category='error') 
          
        elif (booking_end - booking_start) < timedelta(minutes=10):
            flash('Minimum booking time is 10 minutes.', category='error') 

        elif  db.session.query(db.exists().where(Parking.spot_number == booking_spot_number)).scalar() and ((db.session.query(db.exists().where(Parking.start <= booking_start)).scalar() and db.session.query(db.exists().where(Parking.start <= booking_end)).scalar()) or (db.session.query(db.exists().where(Parking.end >= booking_start)).scalar() and db.session.query(db.exists().where(Parking.end <= booking_end)).scalar())):
            flash(f'Spot {booking_spot_number} has already been booked for the selected time. Choose another parking spot or different time.', category='error' )   
)   

        elif db.session.query(db.exists().where(Parking.license_plate == booking_license_plate)).scalar() and ((db.session.query(db.exists().where(Parking.start <= booking_start)).scalar() and db.session.query(db.exists().where(Parking.start <= booking_end)).scalar()) or (db.session.query(db.exists().where(Parking.end >= booking_start)).scalar() and db.session.query(db.exists().where(Parking.end <= booking_end)).scalar())):
            flash(f'A car with license plate {booking_license_plate} has booked another spot for selected time.', category='error' )      

    
        else:
            new_booking = Parking(spot_number = booking_spot_number, license_plate = booking_license_plate, start = booking_start, end = booking_end)
            db.session.add(new_booking)
            db.session.commit()
            flash('New booking has been added', category='success')
    
    bookings = Parking.query.order_by(Parking.start.asc()).all()
    return render_template('index.html', bookings = bookings)

if __name__ == '__main__':
    app.run(debug=True)

Answer 1

我能够通过将SQL DB转换为PANDAS DataFrame来解决问题。
迭代DF很容易，并找到同时满足多个条件的行。

@app.route('/', methods=['POST', 'GET'])
def index():
df = pd.read_sql('parking', 'sqlite:///parking_booking.db')
    
df['start'] = pd.to_datetime(df['start'])
df['end'] = pd.to_datetime(df['end'])

if request.method == 'POST':
    booking_spot_number = str(request.form['spot_number'])
    booking_license_plate = str(request.form['license_plate'].replace(' ', '').upper())          
    booking_start = datetime(*[int(v) for v in request.form['start'].replace('T', '-').replace(':', '-').split('-')])
    booking_end = datetime(*[int(v) for v in request.form['end'].replace('T', '-').replace(':', '-').split('-')])

    license_plate_check = np.where((df['license_plate'] == booking_license_plate) & (((df['start'] <= booking_start) & (df['end'] >= booking_start) | ((df['end'] >= booking_end) & (df['start'] <= booking_end))) | ((booking_start <= df['start']) & (booking_end >= df['end']))))
    spot_reserve_check = np.where((df['spot_number'] == booking_spot_number) & (((df['start'] <= booking_start) & (df['end'] >= booking_start) | ((df['end'] >= booking_end) & (df['start'] <= booking_end))) | ((booking_start <= df['start']) & (booking_end >= df['end']))))
    print(license_plate_check)
    print(spot_reserve_check)

    if booking_end < booking_start:
        flash('End time has to be later than start time', category='error')
    elif booking_start < datetime.now():
        flash('Start time can''t be in the past.', category='error') 
      
    elif (booking_end - booking_start) < timedelta(minutes=10):
        flash('Minimum booking time is 10 minutes.', category='error') 
    
    elif np.size(license_plate_check) != 0:
        flash(f'A car with license plate {booking_license_plate} has already booked a spot during selected time.', category='error' )

    elif np.size(spot_reserve_check) != 0:
        flash(f'The spot {booking_spot_number} has been booked during selected period.', category='error' )

    else:
        new_booking = Parking(spot_number = booking_spot_number, license_plate = booking_license_plate, start = booking_start, end = booking_end)
        db.session.add(new_booking)
        db.session.commit()
        print(df)
        flash('New booking has been added', category='success')
         

bookings = Parking.query.order_by(Parking.start.asc()).all()
time_now = str(datetime.now().strftime('%a %d %b %Y - %H:%M'))
free_spots = 10 - np.size(np.where((time_now >= df['start']) & (time_now <= df['end'])))

return render_template('index.html', bookings = bookings, time_now = time_now , free_spots = free_spots)