Table of Contents

• Reference
  • Column
  • Declarative API
  • functions
  • Query
  • Session
  • hybrid_property
• How-to
  • Prefix all tables under a declarative_base

Reference

Column

# use a type with arguments
Column('data', String(50))
# use no arguments
Column('level', Integer)

# turn on autoincrement for this column despite
# the ForeignKey()
Column('id', ForeignKey('other.id'),
            primary_key=True, autoincrement='ignore_fk')

• column_property can be used for column level options:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))
    fullname = column_property(firstname + " " + lastname)

• Column can have positional arugments of SchemaItem, such as Constraint, ForeignKey, Sequence, etc.

String, Text

# will assume that input is to be passed as Python unicode objects,
# and results returned as Python unicode objects
# There is an engine configuration which sets this flag to True by default.
String(convert_unicode=True)

Float

• For mysql, set precision to 53 cause to make the column as double

# Use float with double precision
Column(Float(precision=53))

Declarative API

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50))

functions

Query

Session

Session.begin() returns a SessionTransaction object:

class SessionTransaction(object):
    # ...
    def __enter__(self):
        return self

    def __exit__(self, type, value, traceback):
        self._assert_active(deactive_ok=True, prepared_ok=True)
        if self.session.transaction is None:
            return
        if type is None:
            try:
                self.commit()
            except:
                with util.safe_reraise():
                    self.rollback()
        else:
            self.rollback()

So, we can use like this (code from here):

Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
with session.begin():
    item1 = session.query(Item).get(1)
    item2 = session.query(Item).get(2)
    item1.foo = 'bar'
    item2.bar = 'foo'

Session.begin()

• Session.begin
• SessionTransaction.exit
• Returns SessionTransaction. It implements __exit__, which calls session.commit when levaving the block.

Session.commit() vs Session.flush()

• A Session object is basically an ongoing transaction of changes to a database
• session.commit() commits (persists) those changes to the database.
• flush() is always called as part of a call to commit()

s = Session()

# The Foo('A') object has been added to the session.
# It has not been committed to the database yet,
# but is returned as part of a query.
s.add(Foo('A'))
print s.query(Foo).all()  # Output: [<Foo('A')>]
s.commit()


s2 = Session()
s2.autoflush = False

# The Foo('B') object is *not* returned as part of this query
# because it hasn't been flushed yet.
s2.add(Foo('B'))
print s2.query(Foo).all()  # Output: [<Foo('A')>]


# Now, Foo('B') is in the same state as Foo('A') was above.
s2.flush()
print s2.query(Foo).all()  # Output: [<Foo('A')>, <Foo('B')>]


# Foo('B') has not been committed, and rolling
# back the session's transaction removes it from the session.
s2.rollback()
print s2.query(Foo).all()  # Output: [<Foo('A')>]

scoped_session

• scoped_session is used for ensuring one session for a scope
• scoped_session uses sessionmaker
• For scopefunc, threading.local() is used by default.
• Can be used like Session, by dynamic things.

hybrid_property

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))

    @hybrid_property
    def fullname(self):
        return self.firstname + " " + self.lastname

Can use fullname like other columns:

some_user = session.query(User).filter(User.fullname == "John Smith").first()

How-to

Prefix all tables under a declarative_base

• Overriding both cls.__tablename__ along with dict_['__tablename__'] is required because DeclarativeMeta refers the attributes of cls during initialization.

from sqlalchemy.ext.declarative.api import DeclarativeMeta


class PrefixerMeta(DeclarativeMeta):

    def __init__(cls, name, bases, dict_):
        if '__tablename__' in dict_:
            cls.__tablename__ = dict_['__tablename__'] = \
                'someprefix_' + dict_['__tablename__']

        return super().__init__(name, bases, dict_)

Base = declarative_base(metaclass=PrefixerMeta)


class SomeModel(Base):

    __tablename__ = 'sometable'
    ...