sqlalchemy无法更新我的mod

Question

我正在更新数据库模型。所以我试了下面的代码

if not sell_requests:
   request.r_quantity = request.quantity
   request.status = StatusEnum.opened

   print request
   print request.quantity
   print request.r_quantity
   con = session.commit()
   print con

看，我做了一个提交，但是在打印所有db行时原始行没有得到更新。request是一个sqlalchemy模型实例。上面代码的输出是

<models.RequestModel object at 0x323...>
12
12
None

这是我的RequestModel的样子

class RequestModel(Base):
    __tablename__ = 'request'
    # Here we define columns for the table address.
    # Notice that each column is also a normal Python instance attribute.
    id = Column(Integer, primary_key=True)
    type = Column(Integer)
    company = Column(Integer, ForeignKey('company.id'))
    quantity = Column(Integer)
    r_quantity = Column(Integer, default=0)
    status = Column(Integer, default=0)

我的整个档案都是这样的

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

# model base class
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from models import CompanyModel, RequestModel


Base = declarative_base()
engine = create_engine('sqlite:///sqlalchemy_test.db')
# Bind the engine to the metadata of the Base class so that the
# declaratives can be accessed through a DBSession instance
Base.metadata.bind = engine

DBSession = sessionmaker(bind=engine)
session = DBSession()

class Side:
    """
    ENUM class forstoring the request type
    """

    BUY = 1
    SELL = 2

    @classmethod
    def get_type_by_value(cls, value):
        return  next(k for k, v in cls.__dict__.items() if v == value)


class StatusEnum:
    opened = 1
    closed = 0

    @classmethod
    def get_type_by_value(cls, value):
        return  next(k for k, v in cls.__dict__.items() if v == value)


class RequestHelper:
    @classmethod
    def get_previous_buy_requests(cls, company):
        return session.query(RequestModel).filter(RequestModel.company == company.id, RequestModel.type == Side.BUY).all()

    @classmethod
    def get_previous_sell_requests(cls, company):
        return session.query(RequestModel).filter(RequestModel.company == company.id, RequestModel.type == Side.SELL).all()

    @classmethod
    def calc_r_quantity_and_status(cls, request):
        company = Company.get_by_id(request.company)

        if request.type == Side.BUY:

            # add buy quantity to the company's buy quantity
            company.buy_quantity += request.quantity

            sell_requests = cls.get_previous_sell_requests(company)

            # if not previous sell requests then r_quantity is same as quantity
            if not sell_requests:
                request.r_quantity = request.quantity
                request.status = StatusEnum.opened

                # print request
                # print request.quantity
                # print request.r_quantity
                session.commit()


                # close the previous buy request if opened
                prev_buy_requests = cls.get_previous_buy_requests(company)
                for req in prev_buy_requests:
                    if req.status == StatusEnum.opened:
                        req.r_quantity = 0
                        req.status = StatusEnum.closed
                        # session.add(req)

                session.commit()

            # any sell requests
            else:
                for sell in sell_requests:
                    if sell.status == StatusEnum.opened:

                        # check whether the sell quan is greater than current buy request quantity
                        # if greater then add the current buy req quantity with the existsing buy quantity else 
                        if sell.r_quantity < request.quantity:
                            company.buy_quantity += request.quantity
                            request.r_quantity = request.quantity - sell.r_quantity
                            request.status = StatusEnum.opened
                            sell.r_quantity = 0
                            sell.status = StatusEnum.closed
                        else:
                            sell.r_quantity -= request.quantity
                            company.sell_quantity = sell.r_quantity
                            request.r_quantity = 0
                            request.status = StatusEnum.closed
                        # session.add(request)
                        # session.add(sell)

                session.commit()

        elif request.type == Side.SELL:
            # add sell quantity to the company's sell quantity
            company.sell_quantity += request.quantity

            buy_requests = cls.get_previous_buy_requests(company)

            if not buy_requests:
                request.r_quantity = request.quantity
                request.status = StatusEnum.opened
                # session.add(request)

                prev_sell_requests = cls.get_previous_sell_requests(company)
                for req in prev_sell_requests:
                    if req.status == StatusEnum.opened:
                        req.r_quantity = 0
                        req.status = StatusEnum.closed
                        # session.add(req)

                session.commit()

            else:
                for buy in buy_requests:
                    if buy.status == StatusEnum.opened:
                        if buy.r_quantity > request.quantity:
                            company.buy_quantity = buy.r_quantity - request.quantity
                            request.r_quantity = 0
                            buy.r_quantity = company.buy_quantity

                            request.status = StatusEnum.closed

                        else:
                            request.r_quantity = request.quantity - buy.r_quantity
                            buy.r_quantity = 0
                            buy.status = StatusEnum.closed
                            request.status = StatusEnum.opened
                        # session.add(request)
                        # session.add(buy)
                session.commit()



class Company(object):
    @classmethod
    def get_or_create_company(cls, name):

        company = session.query(CompanyModel).filter(CompanyModel.name == name).all()
        if not company:
            company = CompanyModel(name=name, buy_quantity=0, sell_quantity=0)
            session.add(company)
            session.commit()
            return company

        return company[0]

    @classmethod
    def get_by_id(cls, id):
        com = session.query(CompanyModel).filter(CompanyModel.id == id)
        if com:
            return com.one()
        return None





class Requests(object):
    template_lines = []
    template_header =  'Side    |     Company   |  Quantity   |  RemaingQty |  Status'
    template_line_format = '{}    |    {}    |    {}     |    {}    |    {}'



    @classmethod
    def do_validation(cls, request_type, company, quantity):
        if request_type.upper() not in dir(Side):
            raise ValueError('Request type must be either buy or sell')

        if not company:
            raise ValueError('You must enter the company name')

        if not quantity or not quantity.isdigit():
            raise ValueError('You should enter integer value for quantity field')

    @classmethod
    def create_request(cls, request_type, company, quantity):
        new_request = RequestModel(type=request_type, company=company.id, quantity=quantity, r_quantity=0, status=0)
        session.add(new_request)
        session.commit()
        new_request.r_quantity = 2
        session.commit()

        print new_request.r_quantity

        return new_request



    @classmethod
    def generate_output(cls):
        requests = session.query(RequestModel).all()
        for ins in requests:
            print ins.r_quantity
            Requests.template_lines.append(Requests.template_line_format.format(Side.get_type_by_value(ins.type), Company.get_by_id(ins.company).name, ins.quantity, ins.r_quantity, StatusEnum.get_type_by_value(ins.status)))

        print Requests.template_header
        print '\r\n'.join(Requests.template_lines)


def main():
    # truncating models
    session.query(CompanyModel).delete()
    session.query(RequestModel).delete()


    while True:
        side = raw_input('Enter the side (buy/sell):\n')
        company = raw_input('Enter the company:\n')
        quantity = raw_input('Enter the quantity:\n')

        com = Company.get_or_create_company(company)

        # store the order request in db
        try:
            Requests.do_validation(side, com, quantity)
        except ValueError:
            continue

        new_request = Requests.create_request(getattr(Side, side.upper()), com, quantity)
        RequestHelper.calc_r_quantity_and_status(new_request)


        out = raw_input('Do you want to continue (Y/n)?\n')
        if out.lower() == 'n':
            Requests.generate_output()

            # truncating models
            session.query(CompanyModel).delete()
            session.query(RequestModel).delete()

            break

        continue



if __name__ == '__main__':
    main()

Answer 1

似乎程序逻辑中有一个错误：在函数calc_r_quantity_and_status中，首先处理当前请求，然后处理所有“以前”的请求并包含当前请求。例如，给定某个公司和某个数量的单个购买请求的会话，数据库中有0个现有请求，将采用以下路径：

@classmethod
def calc_r_quantity_and_status(cls, request):
    company = Company.get_by_id(request.company)

    if request.type == Side.BUY:

        # add buy quantity to the company's buy quantity
        company.buy_quantity += request.quantity

        sell_requests = cls.get_previous_sell_requests(company)

        # if not previous sell requests then r_quantity is same as quantity
        if not sell_requests:
            request.r_quantity = request.quantity
            request.status = StatusEnum.opened

            # print request
            # print request.quantity
            # print request.r_quantity
            session.commit()


            # close the previous buy request if opened
            prev_buy_requests = cls.get_previous_buy_requests(company)
            for req in prev_buy_requests:
                if req.status == StatusEnum.opened:
                    req.r_quantity = 0
                    req.status = StatusEnum.closed
                    # session.add(req)

            session.commit()

由于没有以前的sell_requests，因此更新当前的request：

        if not sell_requests:
            request.r_quantity = request.quantity

但稍后会获取所有购买请求，因为当前请求已持久化到数据库中，所以也会包含它：

            # close the previous buy request if opened
            prev_buy_requests = cls.get_previous_buy_requests(company)
            for req in prev_buy_requests:
                if req.status == StatusEnum.opened:
                    req.r_quantity = 0
                    req.status = StatusEnum.closed
                    # session.add(req)

这将清零r_quantity并关闭所有购买请求，包括当前请求。解决方法很简单：要么延迟当前请求直到所有处理完成，要么在获取以前的购买请求时将其过滤掉。你知道吗