'AttributeError: Could not locate column in row for column '_sa_instance_state' Flask-sqlAlchemy
im developing a new project, im building a small CRM web app using Flask. Im very new using this technology. Im getting this error: AttributeError: Could not locate column in row for column '_sa_instance_state' when i try to create a new quote for a client.
Model: I have three tables: Clientes, Solicitud and Cotizacion (Quotes). I have the issue on crearcotizacion view, but i dont know how resolve this? all help is very appreciated.
class Clientes(db.Model):
__tablename__ = "clientes"
id = db.Column(db.Integer, primary_key=True)
nombres = db.Column(db.String(80), nullable=False)
apellidos = db.Column(db.String(80), nullable=False)
correo = db.Column(db.String(120), nullable=False)
empresa = db.Column(db.String(120), nullable=False)
celular = db.Column(db.String(50), nullable=False)
mensaje = db.Column(db.String(500), nullable=False)
checkbox = db.Column(db.Boolean, nullable=False)
cotizaciones = db.relationship('Cotizacion', backref='clientes_cotizan', lazy=True)
solicitudes = db.relationship('Solicitud', backref='sol_client', lazy=True)
def __init__(self, nombres, apellidos, correo, empresa, celular, mensaje, checkbox, sol_client):
self.nombres = nombres
self.apellidos = apellidos
self.correo = correo
self.empresa = empresa
self.celular = celular
self.mensaje = mensaje
self.checkbox = checkbox
self.sol_client = sol_client
def __repr__(self):
return '<Clientes %r>' % self.id
class Solicitud(db.Model):
__tablename__ = "solicitud"
id = db.Column(db.Integer, primary_key=True)
servicio_campo = db.Column(db.String(120), nullable=False)
asesore = db.Column(db.String(120), nullable=False)
# LLAVE FORANEA
solicitud_cliente = db.Column(db.Integer, db.ForeignKey("clientes.id"), nullable=True)
solicitudes_cliente = db.relationship('Cotizacion', backref='clientes_solicitan', lazy=True)
def __init__(self, servicio_campo, asesore, sol_client):
self.servicio_campo = servicio_campo
self.asesore = asesore
self.sol_client = sol_client
class Cotizacion(db.Model):
__tablename__ = "cotizacion"
id = db.Column(db.Integer, primary_key=True)
numero_horas = db.Column(db.Integer, nullable=False)
descuento = db.Column(db.Integer, nullable=False)
# LLAVE FORANEA
cliente_id = db.Column(db.Integer, db.ForeignKey("clientes.id"), nullable=True)
solicitud_id = db.Column(db.Integer, db.ForeignKey("solicitud.id"), nullable=True)
def __init__(self, numero_horas, descuento, clientes_cotizan, clientes_solicitan):
self.numero_horas = numero_horas
self.descuento = descuento
self.clientes_cotizan = clientes_cotizan
self.clientes_solicitan = clientes_solicitan
def __repr__(self):
return '<Cotizacion %r>' % self.id
My view
@app.route('/cotizaciones/crear/<int:id>', methods=("GET", "POST"))
@login_required
def crearcotizacion(id):
datos_clientes = (db.session.query(Clientes.id, Clientes.nombres, Clientes.apellidos, Clientes.empresa, Clientes.correo, Solicitud.id, Solicitud.servicio_campo, Solicitud.asesore).join(Solicitud).filter_by(id=id).one())
datos_solicitud = (db.session.query(Solicitud).filter_by(id=Solicitud.id).first())
form = creacion_Cotizacion(request.form)
if current_user.role == True:
if request.method == 'POST':
cotizan = Cotizacion(
numero_horas=form.numero_horas.data,
descuento=form.descuento.data,
clientes_cotizan = datos_clientes,
clientes_solicitan= datos_solicitud
)
db.session.add(cotizan)
db.session.commit()
flash('La cotización ha sido creado exitosamente', 'success')
return render_template('crearcotizacion.html', form=form, id=id, datos_solicitud=datos_solicitud, datos_clientes=datos_clientes)
else:
abort(401)
return render_template('crearcotizacion.html', nombres=current_user.nombres, correo=current_user.correo, role=current_user.role, id=id, form=form, datos_solicitud=datos_solicitud, datos_clientes=datos_clientes)
In this view, user can pick the requests for each client
@app.route('/listarcotizacion')
@login_required
def listarcotizacion():
if current_user.role == True:
clientes= db.session.query(Solicitud.id, Solicitud.servicio_campo, Solicitud.asesore, Clientes.nombres, Clientes.apellidos, Clientes.correo, Clientes.empresa, Clientes.mensaje, Clientes.celular).join(Clientes).all()
return render_template('listacotizacion.html', clientes=clientes, nombres=current_user.nombres, correo=current_user.correo)
Error message
AttributeError
AttributeError: Could not locate column in row for column '_sa_instance_state'
Traceback (most recent call last)
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask_login/utils.py", line 277, in decorated_view
return current_app.ensure_sync(func)(*args, **kwargs)
File "/home/georgeboy/sanwise/app.py", line 905, in crearcotizacion
cotizan = Cotizacion(
File "<string>", line 4, in __init__
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/state.py", line 480, in _initialize_instance
manager.dispatch.init_failure(self, args, kwargs)
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/state.py", line 477, in _initialize_instance
return manager.original_init(*mixed[1:], **kwargs)
File "/home/georgeboy/sanwise/app.py", line 159, in __init__
self.clientes_cotizan = clientes_cotizan
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 459, in __set__
self.impl.set(
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1268, in set
value = self.fire_replace_event(state, dict_, value, old, initiator)
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1294, in fire_replace_event
value = fn(
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1729, in emit_backref_from_scalar_set_event
instance_state(child),
During handling of the above exception, another exception occurred:
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask/app.py", line 2095, in __call__
return self.wsgi_app(environ, start_response)
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask/app.py", line 2080, in wsgi_app
response = self.handle_exception(e)
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask/app.py", line 2077, in wsgi_app
response = self.full_dispatch_request()
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask/app.py", line 1525, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask/app.py", line 1523, in full_dispatch_request
rv = self.dispatch_request()
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask/app.py", line 1509, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/home/georgeboy/.local/lib/python3.8/site-packages/flask_login/utils.py", line 279, in decorated_view
return func(*args, **kwargs)
File "/home/georgeboy/sanwise/app.py", line 905, in crearcotizacion
cotizan = Cotizacion(
File "<string>", line 4, in __init__
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/state.py", line 480, in _initialize_instance
manager.dispatch.init_failure(self, args, kwargs)
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/state.py", line 477, in _initialize_instance
return manager.original_init(*mixed[1:], **kwargs)
File "/home/georgeboy/sanwise/app.py", line 159, in __init__
self.clientes_cotizan = clientes_cotizan
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 459, in __set__
self.impl.set(
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1268, in set
value = self.fire_replace_event(state, dict_, value, old, initiator)
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1294, in fire_replace_event
value = fn(
File "/home/georgeboy/.local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1729, in emit_backref_from_scalar_set_event
instance_state(child),
AttributeError: Could not locate column in row for column '_sa_instance_state'
Solution 1:[1]
It should be as simple as utilizing the 'Solicitud's sol_client backref. Try this:
@app.route('/cotizaciones/crear/<int:id>', methods=("GET", "POST"))
@login_required
def crearcotizacion(id):
datos_solicitud = (db.session.query(Solicitud).filter_by(id=Solicitud.id).first())
datos_clientes = datos_solicitud.sol_client
form = creacion_Cotizacion(request.form)
if current_user.role == True:
if request.method == 'POST':
cotizan = Cotizacion(
numero_horas=form.numero_horas.data,
descuento=form.descuento.data,
clientes_cotizan = datos_clientes,
clientes_solicitan= datos_solicitud
)
db.session.add(cotizan)
db.session.commit()
flash('La cotización ha sido creado exitosamente', 'success')
return render_template('crearcotizacion.html', form=form, id=id, datos_solicitud=datos_solicitud, datos_clientes=datos_clientes)
else:
abort(401)
return render_template('crearcotizacion.html', nombres=current_user.nombres, correo=current_user.correo, role=current_user.role, id=id, form=form, datos_solicitud=datos_solicitud, datos_clientes=datos_clientes)
To explain this, we are getting the desired Solicitud object with a simple filtered query using the selected 'id'. We then use the backref sol_client of this 'Solicitud' object to get the parent client. This is much simpler than performing a separate query to get the 'Client' object from the 'Solicitud's id.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Patrick Yoder |