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

user_3664629 0
Viewed 24087

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?

flask-sqlalchemy flask python-2.7 sqlalchemy python
3 Answers

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))
user_32507990

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.

user_104414760
Related