SQLAlchemy: one classes – two tables

SQLAlchemy is a wonderful ORM for Python. While it allows the normal “class per table” semantics, one can do some more interesting stuff with it.

We often start with a table for some stuff.  For e.g., we might start off our app with a User class mapping to a user table, which has columns for an id, name and an address. For illustration, the list of columns is small. In a real application, this table would probably contain a lot more columns.

Here is some boilerplate code that initiates our database connection, etc.:

from sqlalchemy import *
from sqlalchemy import sql
from sqlalchemy.orm import mapper, sessionmaker, create_session

dburl = "mysql://testuser:testpasswd@localhost/test"
engine = create_engine(dburl)
meta = MetaData(bind=engine)

Let’s define a table and a class mapping to the table.

t_users = Table(
        'users', meta,
        Column('id', Integer, primary_key=True),
        Column('name', String(40), nullable=False),
        Column('address', String(200))
        )
class User(object):
    def __init__(self, name, address=None):
        self.name = name
        self.address = address
    def __repr__(self):
        return "<User(%s,%s,%s)>" % (self.id, self.name, self.address)
mapper(User, t_users)

To create the database, we might do something like the below, or roll out own database schema management system:

meta.create_all()

We store and retrieve User objects from the database like this:

# create a session
session = create_session(bind=engine)
# for testing only... ensure table is empty to start with
session.execute(t_users.delete())
# create and storea record
u = User('Beowulf', 'Denmark')
session.save(u)
session.flush()

# retrieve records from the db
session.clear()
u = session.query(User).filter_by(name='Beowulf').one()
print u

The output of the above snippet would be:

<User(2,Beowulf,Denmark)>

It might make sense in the beginning to put everything related to a user in one table. After a while, as the application grows, one starts seeing some patterns. Imagine that the address field above was very large (or imagine that there was a ‘photo’ field containing the user’s photograph). However, the address field is used only in one corner of the application, and only the name is used in the vast majority of the application code. What’s more, the large binary/string field is slowing down other queries. One of the ways to solve this problem is to split the user table into two:

t_users = Table(
    'users', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(40), nullable=False),
    )
t_addresses = Table(
    'addresses', meta,
    Column('user_id', Integer,
           ForeignKey('users.id'), primary_key=True),
    Column('address', String(255)),
    )

meta.create_all()

Now, whereever we are interested in only the user’s name, we’d use a User class. Where-ever we are interested in the address, we’d use an address class.

class User(object):
    pass
class Address(object):
    pass
mapper(User, t_users)
mapper(Address, t_addresses, properties={
        'user': relation(User, backref='address',
                         primaryjoin=t_users.c.id==t_addresses.c.user_id)
        })

session = create_session(bind=engine, transactional=False)
session.execute(t_addresses.delete())
session.execute(t_users.delete())

u1 = User(); u1.name = "Hagar"
session.save(u1)
a1 = Address() ; a1.user = u1 ; a1.address = "Denmark"
session.save(a1)
session.flush()

Now, we are presumably saving a lot of time and memory on our queries. But, what if we want the equivalent of the original User object, one which refers to both the name and address? Well, we can define a class mapped to a join and use it:

usersaddresses = sql.join(t_users, t_addresses,
                           t_users.c.id == t_addresses.c.user_id)
class UserAddress(object):
    def __repr__(self):
        return "<FullUser(%s,%s,%s)" % (self.id, self.name, self.address)
mapper(UserAddress, usersaddresses, properties={
        'id': [t_users.c.id, t_addresses.c.user_id],
        })
f = session.query(UserAddress).filter_by(name='Hagar').one()
print f

Note the “id” column in the “properties”: we’ve told SQLAlchemy that the “id” attribute of our UserAddress class actually is the same as the “users.id” or “addresses.user_id” columns, which are always the same. Thus SQLAlchemy will NOT produce redundant “id” and “user_id” attributes in our class.

We can even use this class to change and save attributes, and the attributes will go to their respective table!

f.name = "Hagar the horrible"
f.address = "Copenhagen"
session.flush()

for (id, name, user_id, address) in session.execute(usersaddresses.select()):
    print id, name, user_id, address

We get:

11 Hagar the horrible 11 Copenhagen