How to add a custom function/method in sqlalchemy model to do CRUD operations?

Question

Below I have a Flask-SQLAlchemy model for the table User.

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

    user_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))
    updated_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))

    def __init__(self):
        #self.name = name
        self.name = None

    def add_user(self, name):
        self.name = name

Here add_user is a custom method. So if I call the add_user method it should add the name to the User table.

Likewise how do I write custom methods for CRUD operations in that model itself?

Answer 1

You'll probably want to use a classmethod to accomplish this.

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

    user_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))
    updated_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))

    def __init__(self, name):
        self.name = name

    @classmethod
    def create(cls, **kw):
        obj = cls(**kw)
        db.session.add(obj)
        db.session.commit()

This way you can use User.create(name="kumaran") to create a new user that will be committed to the database.

Better yet, it is a great idea to create a mixin for this method and others like it so that the functionality can be easily reused in your other models:

class BaseMixin(object):
    @classmethod
    def create(cls, **kw):
        obj = cls(**kw)
        db.session.add(obj)
        db.session.commit()

You can then reuse this functionality in your models by using multiple inheritance, like so:

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

    user_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))
    updated_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))

    def __init__(self, name):
        self.name = name

Answer 2

Not sure this is relevant to Flask-SQLAlchemy, but basic SQLAlchemy has examples of creating Mixin classes or augmenting the Base class.

https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html

e.g.

from sqlalchemy.ext.declarative import declared_attr

class MyMixin(object):

    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    __table_args__ = {'mysql_engine': 'InnoDB'}
    __mapper_args__= {'always_refresh': True}

    id =  Column(Integer, primary_key=True)

class MyModel(MyMixin, Base):
    name = Column(String(1000))

Answer 3

I would accomplish what you're after like this:

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

    user_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))
    updated_at = db.Column(db.DateTime, nullable=False, server_default=db.text("CURRENT_TIMESTAMP"))

    def __init__(self):
        #self.name = name
        self.name = None
    
    @classmethod
    def add_user(cls, session, name):
        user = User(name)
        session.add(user)
        return User

Then in whatever context you're using it in, create a session, call your method, and commit it.

from .user import User
session = Session()
# or if using Flask SQLAlchemy
# session = db.session
User.add_user(session, 'Foo')
session.commit()

From the sqlalchemy docs:

Keep the lifecycle of the session (and usually the transaction) separate and external.

In contrast to one of the other answers, which assumes you are using this model in a Flask app with FlaskSQLAlchemy's global db.session object, parametrizing the session object like this keeps your model code separate from your session management code. This allows it to be used flexibly in many different contexts.