How To Fix Sqlalchemy Form "db.session.commit()" To The Wrong Parent Object?

My SQLAlchemy form commits db.session.commit() to the .first() parent? I have a 'one-to-many' situation where I can add new a child to a specific parent object, but for some reason

Solution 1:

I agree with the previous speaker (@PGHE). It's not the session. Your query for the "scrapingAccounts" is the reason. The query is based on the id passed in the URL. However, this belongs to the BotList.

<formaction="/update/{{bot.id}}"method="POST">

However, you ask for an account here.

accounts = scrapingAccount.query.get_or_404(id)

The most sensible thing would be if you would not write everything in the same route but would distribute tasks to several. This not only improves the structure of your application, but also makes it clearer. The CRUD paradigm could serve as a guide for a better structure. Using blueprints can make this even easier.

---

The following approach is a hacky, quick fix.

# ParentclassbotList(db.Model):
    __tablename__ = 'botlist'id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(200), nullable=False)
    channel = db.Column(db.String(200), nullable=False)
    bots = db.Column(db.Integer, nullable=False)
    status = db.Column(db.String(200), nullable=False)
    igUsername = db.Column(db.String(200), nullable=True)
    igPassword = db.Column(db.String(200), nullable=True)
    ytUsername = db.Column(db.String(200), nullable=True)
    ytPassword = db.Column(db.String(200), nullable=True)
    scrapingAccounts = db.relationship("scrapingAccount", 
        backref="owner", 
        lazy='dynamic', 
        # Make sure that the referenced child objects are deleted when the # parent object is deleted.
        cascade="all, delete-orphan"
    )

    def__repr__(self):
        return'<Username %r>' % self.id# ChildclassscrapingAccount(db.Model):
    __tablename__ = 'scrapingaccount'id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    username = db.Column(db.String(200), nullable=False)
    # To avoid unassigned accounts, you can forbid empty entries for the foreign key.# An error will be thrown if you try to set it to null / none.
    owner_id = db.Column(db.Integer, db.ForeignKey("botlist.id"), nullable=False)


@app.route('/update/<int:id>', methods=['GET', 'POST'])defupdateBot(id):
    bot_to_update = botList.query.get_or_404(id)
    if request.method == "POST":
        # If it is a POST request.if request.form.get("updateBotButton"):
            bot_to_update.username = request.form['username']
            bot_to_update.channel = request.form['channel']
            bot_to_update.bots = request.form['bots']
            bot_to_update.status = request.form['status']
            bot_to_update.igUsername = request.form['igUsername']
            bot_to_update.igPassword = request.form['igPassword']
            bot_to_update.ytUsername = request.form['ytUsername']
            bot_to_update.ytPassword = request.form['ytPassword']
            try:
                db.session.commit()
                return redirect('#')
            except:
                return"There was a problem updating that bot."elif request.form.get("addAccountButton"):
            name = request.form['addigname']
            username = request.form['addiguser']
            # You want to add an account to the bot here, so you have to set the# reference, otherwise it will be saved, but it does not belong anywhere.
            new_account = scrapingAccount(
                name=name,
                username=username,
                owner_id=bot_to_update.id
            )
            db.session.add(new_account)
            # Instead of assigning the associated bot id to the new account and# adding it to the session, you can also add it to the scrapingAccounts# list. Then you don't need to add it to the session as the parent is # already part of it. The owner_id is assigned automatically.## bot_to_update.scrapingAccounts.add(new_account)try:
                db.session.commit()
                return redirect('#')
            except:
                return"There was a problem adding an account."elif request.form.get("updateAccountButton"):
            # Request the account using its id and owner_id.
            account = scrapingAccount.query.filter_by(
                owner_id=bot_to_update.id,
                id=request.form['accountid']
            ).first_or_404()
            account.name = request.form['igname']
            account.username = request.form['iguser']
            try:
                db.session.commit()
                return redirect('#')
            except:
                return"There was a problem updating an account."else:
        return render_template("update.html", bot=bot_to_update)

{% for account in bot.scrapingAccounts %}
<form action="/update/{{bot.id}}" method="POST">
    <inputtype="hidden" name="accountid" value="{{ account.id }}"/>
    <inputtype="text" name="igname" id="igname" placeholder="eg. Jane Doe" value="{{account.name}}"/>
    <inputtype="text" name="iguser" id="iguser" placeholder="eg. jandoe" value="{{account.username}}"/>
    <inputtype="submit" name="updateAccountButton" value="Update accounts">
</form>
{% endfor %}

Another alternative could be to use optional parameters within the route / URL.

Solution 2:

You're querying your table scrapingaccount by its id column, and returning a potentially unrelated account to your bot list. You want to query scrapingaccount by the owner_id column.

This line:

accounts = scrapingAccount.query.get_or_404(id)

should be:

accounts = db.session.query(scrapingAccount).filter(scrapingAccount.owner_id == id).all()

Edit: I have assumed id in your function is the id for the BotList table.