Pyramid framework tips : MySQL Database settings

Installing MySQL DB-API driver on Kubuntu 11.10 in Console:

  sudo apt-get install libmysqlclient-dev
  sudo apt-get install python-dev

In virtual env of your Pyramid installation:

  $ easy_install mysql-python

For MS Windows installation details see please online book: The Definitive Guide to Pylons – James Gardner.

For SQLAlchemy in Pyramid framework in development.ini and production.ini:

  # mysql
  sqlalchemy.url = mysql://DB_USER:PASSWORD@localhost:3306/DATABASE_NAME
  sqlalchemy.pool_recycle = 3600

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