Get "insert_id" for one to one relationship in Flask, SqlAlchemy

Question

I'm trying to insert a new row through sqlalchemy. The parent table (Milestone) has a child table called Funding. Both tables share a relationship through a column called milestone_id. It is a one to one relationship.

I have looked it up, but I can't figure out how to reference the milestone_id when INSERTING a new row in the Funding table. The parent ID is an autoincrement. I am using Flask and SqlAlchemy.

models:

 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

Alchemy Query:

@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)

How can I tell SqlAlchemy to use the auto generated milestone_id from the milestone table when insert the funding information in the funding table? Should these be two separate queries?

Update:

I took up the advice from ThiefMaster about using the flush function, but I'm still getting an error: UnboundLocalError: local variable 'new_milestone' referenced before assignment

here's the updated code:

@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)

any ideas?

Answer 1

I couldn't find an exact solution. If anyone is wondering, I ended up working around it by executing SQL directly. It's not ideal, but it gets the job done for now. I ended up inserting on row at a time, here's the code below:

@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

Add db.session.flush() after your db.session.add(..) call. This will result in the INSERT being sent to the database and afterwards you'll have acess to the ID.