Home > database >  Flask SQLAlchemy - Relationship with two models
Flask SQLAlchemy - Relationship with two models

Time:05-03

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/<int:printer_id>/', 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', <Printer 1>)] (Background on this error at: https://sqlalche.me/e/14/rvf5)

probably the issue is that the "printer_id" Value is <Printer 1> 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!

CodePudding user response:

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'
  • Related