Flask SQLAlchemy - Relationship with two models

Question

i am trying to create app which would track all the service incidents.

I have created two models

Printer - Which holds all the information about printer (Inventory number, IP Adress etc)
PrinterService - Which should hold all the data about services.

i am trying to connect those two, so if i fill the form for service - it will join to the Printer table.. but i am failing with this.

This is what i am working with.

Models:

class Printer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200))
    ip = db.Column(db.String(200))


class PrinterServis(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    printer_komponent = db.Column(db.String(140))
    printer_opis_chyby = db.Column(db.String(140))
    printer_id = db.Column(db.Integer, db.ForeignKey('printer.id'))

Forms:

class PrinterForm(FlaskForm):
    printer_name = StringField('Názov Tlačiarne', validators=[InputRequired()])
    printer_ip = StringField('IP Tlačiarne', validators=[InputRequired()])

class PrinterServisForm(FlaskForm):
    printer_komponent = StringField('Vadný komponent', validators=[InputRequired()])
    printer_opis_chyby = StringField('Opis Chyby', validators=[InputRequired()])

Views:

@app.route('/')
def index():
    printers = Printer.query.all()
    return render_template('index.html', printers=printers)

@app.route('/add', methods=['GET', 'POST'])
def add_printer():
    form = PrinterForm()
    if form.validate_on_submit():
        name = form.printer_name.data
        ip = form.printer_ip.data
        new_printer = Printer(name=name, ip=ip)
        db.session.add(new_printer) 
        db.session.commit()
        flash("Pridanie tlačiarne prebehlo úspešne")
        return redirect(url_for('index'))
    return render_template('add_printer.html', form=form)

@app.route('/printer//', methods=['GET', 'POST'])
def view_printer(printer_id):
    form = PrinterServisForm()
    printer = Printer.query.get_or_404(printer_id)
    if form.validate_on_submit():
        printer_komponent = form.printer_komponent.data 
        printer_opis_chyby = form.printer_opis_chyby.data
        printer_servis_id = Printer.query.get_or_404(printer_id)        
        new_servis = PrinterServis(printer_komponent=printer_komponent, printer_opis_chyby=printer_opis_chyby, printer_id=printer_servis_id)
        db.session.add(new_servis)
        db.session.commit()
        flash("Servis bol objednaný")
        return redirect(url_for('index'))
    return render_template('printer.html', printer=printer, form=form)

With this i am getting the error

sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 2 - probably unsupported type. [SQL: INSERT INTO printer_servis (printer_komponent, printer_opis_chyby, printer_id) VALUES (?, ?, ?)] [parameters: ('Valec', 'Valec je poškodený, nefunguje', )] (Background on this error at: https://sqlalche.me/e/14/rvf5)

probably the issue is that the "printer_id" Value is instead of 1, but how can i change it? My head hurts now.. I am only a beginner so sorry for probably stupid question.

Thank you!

user_15368978 · Answer

Your problem is that your relationship between your two models is not quite complete. You need to add something similar to this to the Printer model to complete the relationship:

printer_service = db.relationship('PrinterServis', backref='printer')

This inserts a relationship 'column' which holds the parent printer, into the PrinterServis table. To correctly create a PrinterServis object, fill this 'printer' attribute instead of the foreign key, 'printer_id'.

new_servis = PrinterServis(..., printer=printer_servis_id)

You could then access this parent printer with something similar to the following:

printer_servis_object = PrinterServis.query.first() #get a valid object
printer_object = printer_servis_object.printer      #access its backref of 'printer'

Flask SQLAlchemy - Relationship with two models

1 Answers