SQLAlchemy Tutorial 3: Base Entity Class in SQLAlchemy

SQLAlchemy Tutorial 3: Base Entity Class in SQLAlchemy

How can i use a base entity class in SQLAlchemy?
For example i have 4 columns in all my database tables. I don’t like write again and again these shared fields in all my entities.

created_by_user = Column(VARCHAR(50))
created_at = Column(DATETIME)
updated_by_user = Column(VARCHAR(50))
updated_at = Column(DATETIME)

For before insert before update base extension class

# sqlalchemy declarative base
Base = declarative_base() 

class BaseExtension(MapperExtension):
    """Base entension class for all entity """

    def before_insert(self, mapper, connection, instance):
        """ set the created_at  """
        instance.created_at = datetime.now()

    def before_update(self, mapper, connection, instance):
        """ set the updated_at  """
        instance.updated_at = datetime.now()

You can use as base entity object a mixin class in SQLAlchemy with base extension and table args.

class BaseEntity(object):
    __table_args__ = {
            'mysql_engine': 'InnoDB',
            'mysql_charset': 'utf8'
    }
    # for before insert before update base extension class
    __mapper_args__ = { 'extension': BaseExtension() }  

    # shared fields for all entities
    created_by_user = Column(VARCHAR(50))
    created_at = Column(DATETIME)
    updated_by_user = Column(VARCHAR(50))
    updated_at = Column(DATETIME)

Here Country entity inherits from declarative base class and base entity mixin class.

class Country(Base, BaseEntity):
    """ Country entity class """
    __tablename__ = 'country'

    id = Column('country_id', BIGINT(unsigned=True), primary_key=True)
    code = Column(VARCHAR(10), nullable=False, unique=True)
    name = Column(VARCHAR(100), nullable=False, unique=True)

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

2 Comments

  1. Mengu
    Nov 23, 2011 @ 07:36:14

    hi cem,

    i suggest using Unicode type instead of VARCHAR.

    Reply

  2. Cem Ikta
    Nov 23, 2011 @ 11:39:11

    hi,

    thanks for your reply, i think in MySQL there is table args with UTF-8. It is not enough in SQLAlchemy with __table_args__ = {‘mysql_engine’: ‘InnoDB’, ‘mysql_charset’: ‘utf8′} ?

    Reply

Leave a Reply