在SqlAlchemy的水瓶中为一对一的关系获取“SqlAlchemy”

Question

我正试图在sqlalchemy中插入一个新行。父表(里程碑)有一个名为Funding的子表。两个表通过一个名为milestone_id的列共享一个关系。这是一对一的关系。

我已经查过了，但我不知道如何在基金表中插入新行时引用milestone_id。父ID是一个自动增量。我用的是酒瓶和SqlAlchemy。

模型：

 class Milestone(db.Model):
    __tablename__ = "**************"

   milestone_id = db.Column(db.Integer, primary_key=True)
   company_id = db.Column(db.Integer, db.ForeignKey('stlines_startups.company_id'))
   milestone_date = db.Column(db.Integer)
   snapshots = db.relationship('Snapshot', uselist=False, primaryjoin='Milestone.milestone_id==Snapshot.milestone_id', backref='milestone')
   fundraising = db.relationship('Funding', uselist=False, primaryjoin='Milestone.milestone_id==Funding.milestone_id', backref='milestone')

   def __init__(self, milestone_id, company_id, milestone_date, snapshots = [], fundraising = []):
    self.milestone_id = milestone_id
    self.company_id = company_id
    self.milestone_date = milestone_date
    self.snapshots = snapshots
    self.fundraising = fundraising

class Funding(db.Model):
    __tablename__ = "**************************"

   funding_id = db.Column(db.Integer, primary_key=True)
   funding_type = db.Column(db.Text)
   funding_message = db.Column(db.Text)
   funding_amount = db.Column(db.Integer)
   milestone_source = db.Column(db.Text)
   company_id = db.Column(db.Integer, db.ForeignKey('stlines_milestones.company_id'))
   milestone_id = db.Column(db.Integer, db.ForeignKey('stlines_milestones.milestone_id'))
   user_id = db.Column(db.Integer)
   funding_timestamp = db.Column(db.Integer)

def __init__(self, funding_id, funding_type, funding_message, funding_amount, milestone_source, milestone_id, company_id, user_id, funding_timestamp):
    self.funding_id = funding_id
    self.funding_type = funding_type
    self.funding_message = funding_message
    self.funding_amount = funding_amount
    self.milestone_source = milestone_source
    self.milestone_id = milestone_id
    self.company_id = company_id
    self.user_id = user_id
    self.funding_timestamp = funding_timestamp

炼金术查询：

@app.route('/_add_funding')
def add_funding():
    funding_type = request.args.get('funding_stage', '', type=str)
    funding_message = request.args.get('funding_message', '', type=str)
    funding_amount = request.args.get('funding_amount', 0, type=int)
    milestone_source = request.args.get('milestone_source', '', type=str)
    milestone_date = request.args.get('milestone_date', '', type=str)
    company_id = request.args.get('company_id', '', type=int)

    milestone_date_final = datetime.datetime.strptime(milestone_date, '%B %d, %Y')

    ''' In this line, I try to reference milestone_id with new_milestone.milestone_id, but nothing shows up in the database '''
    new_funding = Funding('', funding_type=funding_type, funding_message=funding_message, funding_amount=funding_amount, milestone_source=milestone_source, company_id=company_id, milestone_id=new_milestone.milestone_id, user_id='1', funding_timestamp=milestone_date_final)
    new_milestone = Milestone('', company_id=company_id, milestone_date=milestone_date_final, snapshots=None, fundraising=new_funding)
    db.session.add(new_milestone)
    output = new_milestone.milestone_id
    db.session.commit()

    return jsonify(result=output)

当我在资助表中插入资金信息时，我如何告诉SqlAlchemy使用来自里程碑表的自动生成的milestone_id？这应该是两个不同的问题吗？

更新：

我接受了ThiefMaster关于使用刷新函数的建议，但我仍然收到一个错误: UnboundLocalError:赋值前引用的局部变量'new_milestone‘

以下是更新的代码：

@app.route('/_add_funding')
def add_funding():
    funding_type = request.args.get('funding_stage', '', type=str)
    funding_message = request.args.get('funding_message', '', type=str)
    funding_amount = request.args.get('funding_amount', 0, type=int)
    milestone_source = request.args.get('milestone_source', '', type=str)
    milestone_date = request.args.get('milestone_date', '', type=str)
    company_id = request.args.get('company_id', '', type=int)

    milestone_date_final = datetime.datetime.strptime(milestone_date, '%B %d, %Y')
    ''' In this line, I try to reference milestone_id with new_milestone.milestone_id, but nothing shows up in the database '''
    new_funding = Funding('', funding_type=funding_type, funding_message=funding_message, funding_amount=funding_amount, milestone_source=milestone_source, company_id=company_id, milestone_id=new_milestone.milestone_id, user_id='1', funding_timestamp=milestone_date_final)
    new_milestone = Milestone('', company_id=company_id, milestone_date=milestone_date_final, snapshots=None, fundraising=new_funding)
    db.session.add(new_milestone)
    db.session.commit()
    db.session.flush()
    output = new_milestone.milestone_id

    return jsonify(result=output)

有什么想法吗？

Answer 1

我找不到确切的解决办法。如果有人想知道，我最终通过直接执行SQL来解决这个问题。这并不理想，但它能完成目前的工作。最后，我一次插入一行，下面的代码如下：

@app.route('/_add_funding')
def add_funding():
    funding_type = request.args.get('funding_stage', '', type=str)
    funding_message = request.args.get('funding_message', '', type=str)
    funding_amount = request.args.get('funding_amount', 0, type=int)
    milestone_source = request.args.get('milestone_source', '', type=str)
    milestone_date = request.args.get('milestone_date', '', type=str)
    company_id = request.args.get('company_id', '', type=int)

    milestone_date_final =  time.mktime(datetime.datetime.strptime(milestone_date, '%B %d, %Y').timetuple())

    sql = "INSERT INTO ******** (`milestone_id`,`company_id`, `milestone_date`) VALUES ('','{}','{}')".format(company_id, milestone_date_final)
    result = db.engine.execute(sql)

    milestone_id = result.lastrowid 

    sql = "INSERT INTO ****** (`funding_id`,`funding_type`, `funding_message`, `funding_amount`, `milestone_source`, `company_id`, `milestone_id`, `user_id`, `funding_timestamp`) VALUES ('','{}','{}','{}','{}','{}','{}','1', '{}')".format(funding_type, funding_message, funding_amount, milestone_source, company_id, milestone_id, milestone_date_final)
    result = db.engine.execute(sql)

    output = result.lastrowid
    return jsonify(result=output)

Answer 2

在db.session.flush()调用之后添加db.session.add(..)。这将导致将INSERT发送到数据库，然后您将访问ID。