Skip to main content
 首页 » 编程设计

python之flask-sqlalchemy 中的奇怪过滤器行为

2025年02月15日9webabcd

我在 flask-sqlalchemy 中有一个查询,filter 表现得很奇怪:

q.filter(Transaction.transaction_id == ReconciledTransaction.safe_withdraw_id).all() 

它工作正常,但是:

q.filter(Transaction.transaction_id != ReconciledTransaction.safe_withdraw_id).all() 

无法正常工作!似乎是什么问题?
UPD
我的模型:
协调交易模型:

class ReconciledTransactionModel(db.Model): 
    """Reconciled Transaction model""" 
 
    __tablename__ = 'ReconciledTransaction' 
 
    id = db.Column('id', db.Integer, primary_key=True, nullable=False) 
    balance_entry_id = db.Column('BalanceEntry_id', db.Integer, db.ForeignKey("BalanceEntry.id"), nullable=False) 
    safe_withdraw_id = db.Column('Transaction_id', db.String, nullable=False) 
    datetime = db.Column('datetime', db.Date(), nullable=False) 
    balance_entry_amount = db.Column('BalanceEntry_amount', db.Float) 
    reconciled_amount = db.Column('ReconciledAmount', db.Float) 
    currency = db.Column('currency', db.String) 
    reconciliation_status = db.Column('reconciliation_status', db.String, nullable=False) 
    status_code = db.Column('status_code', db.Integer, nullable=False) 

交易模型:

class TransactionModel(db.Model): 
    """Transaction SA model.""" 
 
    __tablename__ = 'Transaction' 
 
    id = db.Column('id', db.Integer, primary_key=True) 
    till_id = db.Column('Till_id', db.Integer, db.ForeignKey("Till.id"), 
                        nullable=False) 
    till = relationship("Till", foreign_keys=[till_id], backref="transactions", enable_typechecks=False) 
    establishment_id = db.Column('Establishment_id', db.Integer, 
                                 db.ForeignKey("Establishment.id"), 
                                 nullable=False) 
    establishment = relationship("Establishment", 
                                 foreign_keys=[establishment_id], 
                                 backref="transactions", 
                                 enable_typechecks=False) 
    employee_id = db.Column('Employee_id', db.Integer, 
                            db.ForeignKey("Employee.id"), 
                            nullable=False) 
    employee = relationship("Employee", 
                            foreign_keys=[employee_id], 
                            backref="transactions", 
                            enable_typechecks=False) 
    local_time = db.Column('local_time', db.DateTime, nullable=False) 
    create_time = db.Column('create_time', db.TIMESTAMP(timezone=True), 
                            nullable=False) 
    send_time = db.Column('send_time', db.TIMESTAMP(timezone=True), 
                          nullable=False) 
    receive_time = db.Column('receive_time', db.TIMESTAMP(timezone=True), 
                             nullable=False) 
    total_value = db.Column('total_value', db.Integer, nullable=False) 
    amount = db.Column('amount', db.Float, nullable=False) 
    discrepancy = db.Column('discrepancy', db.Float, nullable=False) 
    type = db.Column('type', db.Enum('shift', 
                                     'payment', 
                                     'skimming', 
                                     'withdraw', 
                                     'refund', 
                                     'till', 
                                     'till_deposit', 
                                     'safe_deposit', 
                                     'safe_withdraw', 
                                     'till_reset', 
                                     name='transaction_type'), 
                     nullable=False) 
    status = db.Column('status', 
                       db.Enum('start', 'end', name='transaction_status'), 
                       nullable=False) 
    receipt_id = db.Column('receipt_id', db.String(32), server_default=None) 
    transaction_id = db.Column('transaction_id', db.String(32), 
                               server_default=None) 
    parent_transaction = db.Column('parent_transaction', db.String(32), 
                                   server_default=None) 
    discrepancy_reason = db.Column('discrepancy_reason', db.String(1024)) 
    resolve_discrepancy_reason = db.Column('resolve_discrepancy_reason', 
                                           db.String(1024)) 
    accounted = db.Column('accounted', db.Boolean, default=False) 

这是我的查询:

_transactions = db.session.query(Transaction, 
                                 status_sq.c.count, 
                                 end_transaction_sq.c.discrepancy, 
                                 end_transaction_sq.c.discrepancy_reason, 
                                 end_transaction_sq.c.resolve_discrepancy_reason, 
                                 end_transaction_sq.c.amount, 
                                 ). \ 
    filter(Transaction.establishment_id.in_(store_ids)). \ 
    filter(Transaction.amount != 0). \ 
    filter_by(status='start') 
 
transactions = _transactions. \ 
    filter(Transaction.type.in_(transaction_types)). \ 
    outerjoin(status_sq, 
              Transaction.transaction_id == status_sq.c.transaction_id). \ 
    outerjoin(end_transaction_sq, 
              Transaction.transaction_id == end_transaction_sq.c.transaction_id) 
 
# check possible values for sorting and pages 
if sort_field not in allowed_sort_fields: 
    sort_field = Transaction.default_sort_field 
if sort_dir not in (ASCENDING, DESCENDING): 
    sort_dir = Transaction.default_sort_dir 
if per_page > 100:  # hard limit 
    per_page = Transaction.default_per_page 
 
if sort_dir == ASCENDING: 
    order = allowed_sort_fields[sort_field].desc() 
else: 
    order = allowed_sort_fields[sort_field].desc() 
 
q = transactions.\ 
    join(Establishment).\ 
    join(Employee, Transaction.employee_id == Employee.id). \ 
    outerjoin(Currency). \ 
    group_by(Transaction, 
             status_sq.c.count, 
             end_transaction_sq.c.discrepancy, 
             end_transaction_sq.c.discrepancy_reason, 
             end_transaction_sq.c.resolve_discrepancy_reason, 
             end_transaction_sq.c.amount, 
             allowed_sort_fields[sort_field]).\ 
    order_by(order) 
items = q.filter(Transaction.transaction_id == ReconciledTransaction.safe_withdraw_id).limit(per_page).offset((page - 1) * per_page).all() 

“无法正常工作”意味着在第二种情况下(当我放置 != 时,并且只想进行不在 ReconciledTransaction 表中的交易)过滤器被忽略,但是当过滤器包含==,一切正常(我只有匹配的交易)。

请您参考如下方法:

当您使用这样的查询时:

q = db.session.query(Transaction). \ 
    filter(Transaction.transaction_id != ReconciledTransaction.safe_withdraw_id) 

它转换成 SQL 查询:

SELECT Transaction.* FROM Transaction, ReconciledTransaction 
WHERE Transaction.transaction_id != ReconciledTransaction.safe_withdraw_id 

这意味着您将获得所有 Transaction 行以及所有 ReconciledTransaction 行,但具有匹配 ID 的行除外。

如果您需要获取不在 ReconciledTransaction 表中的所有 Transaction 对象,您可以先获取所有 ReconciledTransaction id:

r_query = db.session.query(ReconciledTransaction.safe_withdraw_id). \ 
    group_by(ReconciledTransaction.safe_withdraw_id) 
r_ids = [x[0] for x in r_query] 

然后在您的交易查询中使用 NOT IN 过滤器:

q = q.filter(Transaction.transaction_id.notin_(r_ids)) 

或者您可以使用子查询:

q = q.filter(Transaction.transaction_id.notin_( 
    db.session.query(ReconciledTransaction.safe_withdraw_id) 
)) 

编辑:正如 Ilja Everilä 所说,不存在运算符性能 might be better比不在。 SQLAlchemy 查询将如下所示:

q = q.filter(~session.query(ReconciledTransaction). \ 
    filter(ReconciledTransaction.safe_withdraw_id == Transaction.id).exists())