Use dataclass decorator in Flask-SQLAlchemy model class?

Question

I wrote a Flask-SQLAlchemy model class that looks like this (from this reference):

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

class User(db.Model):
    
    __tablename__ = 'user'
    
    user_id = db.Column(db.Integer, primary_key=True)
    user_url = db.Column(db.String(2083), unique=True, nullable=False)
    username = db.Column(db.String(80), unique=True, nullable=False)
    avatar_url = db.Column(db.String(2083), unique=True, nullable=False)
    
    def __init__(self, user_id, user_url, username, avatar_url):
    
        self.user_id = user_id
        self.user_url = user_url
        self.username = username
        self.avatar_url = avatar_url

However, I would like to use the dataclass decorator (reference) to simplify the implementation of this, specifically to avoid overriding the __init__ dunder method, and to make it easier to serialize this class into a Python dict.

However, changing the implementation of this class to:

@dataclass
class User(db.Model):

    __tablename__ = 'user'

    user_id: db.Column(db.Integer, primary_key=True)
    user_url: db.Column(db.String(2083), unique=True, nullable=False)
    username: db.Column(db.String(80), unique=True, nullable=False)
    avatar_url: db.Column(db.String(2083), unique=True, nullable=False)

Does not work, yielding an error similar to this ("Mapper mapped class could not assemble any primary key columns for mapped table").

Any help on how to simplify classes with baseclass db.Model by using the dataclass decorator (or alternative solution)?

user_3264407 · Answer

I know this is over a year old but answering for others struggling with this.

I feel your pain as I was going around in circles with Flask-Marshallow for ages trying to serialise my Flask-SqlAlchemy query.

The answer is that you have to put datatypes at the top of you Model class. So using the OP's example you need to change this:

class User(db.Model):
    __tablename__ = 'user'

    user_id: db.Column(db.Integer, primary_key=True)
    user_url: db.Column(db.String(2083), unique=True, nullable=False)
    username: db.Column(db.String(80), unique=True, nullable=False)
    avatar_url: db.Column(db.String(2083), unique=True, nullable=False)

to this:

@dataclass
class User(db.Model):
    __tablename__ = 'user'
    user_id: int
    user_url: str
    username: str
    avatar_url: str

    user_id: db.Column(db.Integer, primary_key=True)
    user_url: db.Column(db.String(2083), unique=True, nullable=False)
    username: db.Column(db.String(80), unique=True, nullable=False)
    avatar_url: db.Column(db.String(2083), unique=True, nullable=False)

Note that you only need to declare the data type for the fields you need to serialise.

Use dataclass decorator in Flask-SQLAlchemy model class?

1 Answers