Can't update a record using flask-restx sqlalchemy mariadb v10.5.20 "(mariadb.NotSupportedError) Data type 'tuple'"

Question

This is a simple REST API, have 2 classes Client, Product.

Relationship: A client can have many products, Client 1---* Product.

I can list, create, search by id, with the 2 models, Client and Product. But now working in the PUT(update) verb, the bellow error is showing

appi_model.py

product_model = api.model("ProductModel", {
    "id": fields.Integer,
    "sku": fields.String,
    "description": fields.String,
    "price": fields.Integer,
    "quantity": fields.Integer,
    "created_on": fields.DateTime,
    "updated_on": fields.DateTime,
    #relationship
    "client": fields.Nested(client_model)
})

product_input_model = api.model("ProductInputModel", {
    "sku": fields.String,
    "description": fields.String,
    "price": fields.Integer,
    "quantity": fields.Integer,
    #relationship
    "client_id": fields.Integer,
})

resource.py

@ns.route("/product")
class ProductList(Resource):
    @ns.marshal_list_with(product_model)
    def get(self):
        return Product.query.all()

    @ns.expect(product_input_model)
    @ns.marshal_with(product_model)
    def post(self):
        product = Product(sku=ns.payload["sku"],
                          description=ns.payload["description"],
                          price=ns.payload["price"],
                          quantity= ns.payload["quantity"] or None,
                          client_id=ns.payload["client_id"])
        db.session.add(product)
        db.session.commit()
        return product, 201

@ns.route("/product/<int:id>")
class ProductAPI(Resource):
    @ns.marshal_with(product_model)
    def get(self, id):
        product = Product.query.get(id)
        if product:
            return product    
        else:
            ns.abort(404, f"product with id {id} not found")

    @ns.expect(product_input_model)
    @ns.marshal_with(product_model)
    def put(self, id):
        product = Product.query.get(id)
        if product:
            product.sku = ns.payload["sku"],
            product.description = ns.payload["description"],
            product.price = ns.payload["price"],
            product.quantity = ns.payload["quantity"],
            product.client_id = ns.payload["client_id"]
            db.session.commit()
            return product
        else:
            ns.abort(404, f"product with id {id} not found")

model.py

class Product(db.Model):
    __tablename__= "product"

    id = db.Column(db.Integer, primary_key=True)
    sku = db.Column(db.String(25), index=True, nullable=False, unique=True)
    description = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Numeric(12,4))
    quantity = db.Column(db.Integer)
    created_on = db.Column(db.DateTime, default=datetime.now)
    updated_on = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
    
    client_id = db.Column(db.ForeignKey("client.id"))
    client = db.relationship("Client", back_populates="products")

    def __repr__(self):
        #literal string interpolation '''
        return f'''<Product\n{self.id}\n{self.sku}\n{self.description}\n
            {self.price}\n{self.quantity}\n{self.client_id}>'''
    
    def __init__(self, sku, description, client_id, price=0, quantity=0):
        self.sku = sku
        self.description = description
        self.client_id = client_id
        self.price = price
        self.quantity = quantity

Each time when trying to update a record by the API, for example the product_id=4, with the bellow new information.

{
  "sku": "tech-gra-001",
  "description": "Nvidia RTX 3080",
  "price": 830,
  "quantity": 5,
  "client_id": 1
}

I am getting the following Error.

sqlalchemy.exc.NotSupportedError: (mariadb.NotSupportedError) Data type 'tuple' in column 0 not supported in MariaDB
Connector/Python
[SQL: UPDATE product SET sku=?, description=?, price=?, quantity=?, updated_on=? WHERE product.id = ?]
[parameters: (('tech-gra-001',), ('Nvidia RTX 3080',), (830,), (5,), datetime.datetime(2023, 5, 20, 17, 52, 22, 7187
31), 4)]

The origin error codeline is

File "/home/chris/Documents/web-projects/crud-flask-product/blueprint/resource.py", line 79, in put
   db.session.commit()
 File "/home/chris/Documents/web-projects/crud-flask-product/env/lib64/python3.11/site-packages/sqlalchemy/orm/scop
ing.py", line 553, in commit
   return self._proxied.commit()

Any advice is welcome.

[Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551) — Georg Richter, May 22 '23 at 07:26

score 0 · Answer 1 · answered May 21 '23 at 02:03

The problems was those commas at the end of the values. product.sku = ns.payload["sku"],.

@ns.expect(product_input_model)
    @ns.marshal_with(product_model)
    def put(self, id):
        product = Product.query.get(id)
        if product:
            product.sku = ns.payload["sku"],
            product.description = ns.payload["description"],
            product.price = ns.payload["price"],
            product.quantity = ns.payload["quantity"],
            product.client_id = ns.payload["client_id"]
            db.session.commit()
            return product
        else:
            ns.abort(404, f"product with id {id} not found")

Remove those commas, those commas are becomes the values in tuples.

@ns.expect(product_input_model)
    @ns.marshal_with(product_model)
    def put(self, id):
        product = Product.query.get(id)
        if product:
            product.sku = ns.payload["sku"]
            product.description = ns.payload["description"]
            product.price = ns.payload["price"]
            product.quantity = ns.payload["quantity"]
            product.client_id = ns.payload["client_id"]
            db.session.commit()
            return product
        else:
            ns.abort(404, f"product with id {id} not found")

Can't update a record using flask-restx sqlalchemy mariadb v10.5.20 "(mariadb.NotSupportedError) Data type 'tuple'"

1 Answers1