from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base, declared_attr

class BaseCFG(object):
    @declared_attr
    def __tablename__(cls):  # @NoSelf
        return cls.__name__.lower()

engine = create_engine('sqlite:///:memory:')
Base = declarative_base(cls=BaseCFG)
Session = sessionmaker(bind=engine)

class Item(Base):
    id = Column(Integer, primary_key=True)
    data = Column(String)
    i18ns = relationship("ItemI18N", backref="item")

class ItemI18N(Base):
    lang_short = Column(String, primary_key=True)
    item_id = Column(Integer, ForeignKey('item.id'), primary_key=True)
    name = Column(String)

Base.metadata.create_all(engine)
session = Session()

# Init data
item = Item(data="item")
item_en = ItemI18N(name="English Name", lang_short="en")
item.i18ns.append(item_en)
item_de = ItemI18N(name="German Name", lang_short="de")
item.i18ns.append(item_de)
session.add(item)

# Use it as a list
item = session.query(Item).first()
print item.data, [i18n.name for i18n in item.i18ns if i18n.lang_short == "en"][0]

# Get the name separately
item, name = session.query(Item, ItemI18N.name).join(ItemI18N).filter(ItemI18N.lang_short == "en").first()
print item.data, name