[FIXED] MySQLInterfaceError when commiting in SQLAlchemy

Issue

I'm building a web API with Flask with SQLAlchemy to push data to a MySQL database. Whenever I add an object 'Project' to the session and commit it, I get the following error:

result[key] = self._cmysql.convert_to_mysql(value)[0] _mysql_connector.MySQLInterfaceError: Python type tuple cannot be converted

The class that I'm attempting to commit:

class Project(Base):
__tablename__ = 'project'

id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(255), unique=True, nullable=False)
client = Column(String(255), unique=False, nullable=False)
work_week = Column(Integer, unique=False, nullable=False)
start_date = Column(Date, unique=False, nullable=False)
end_date = Column(Date, unique=False, nullable=True)
description = Column(String(255), unique=False, nullable=True)

def __init__(self, title, client, work_week, start_date, end_date, description):
    self.title = title
    self.client = client
    self.work_week = work_week
    self.start_date = start_date,
    self.end_date = end_date,
    self.description = description

The MarshMallow Schema for (de)serializing the above class:

class ProjectSchema(Schema):
   id = fields.Integer()
   title = fields.Str()
   client = fields.Str()
   work_week = fields.Integer()
   start_date = fields.Date()
   end_date = fields.Date()
   description = fields.Str()

And finally the method that generates the error with values at runtime:

def post_project(project: str): '{"title":"Project X","client":"Klant X","work_week":40,"start_date":"2021-11-08","end_date":"2021-11-15","description":"Dit is een voorbeeld."}'
schema = ProjectSchema() schema: <ProjectSchema(many=False)>
project_data = schema.loads(project) {'end_date': datetime.date(2021, 11, 15), 'start_date': datetime.date(2021, 11, 8), 'description': 'Dit is een voorbeeld.', 'title': 'Project X', 'client': 'Klant X', 'work_week': 40}
result = Project(**project_data) <api.models.project.Project object at 0x0000023271C0B640>

session.add(result)
session.commit() <-- ERROR

return '', 200

I'm confused as to why it complains about a tuple that cannot be converted. Can anyone shed some light on this issue?

Thanks.

Solution

In your Project.__init__, you have

    ...
    self.start_date = start_date,
    self.end_date = end_date,
    ...

Note the commas at the end of the lines, which turns these values into tuples.

Is it even necessary to reimplement __init__ like this? The one in Base should already assign kwargs to the respective members, so maybe you could best solve your problem by removing the method altogether...

Answered By - Jeronimo