store a list in a column using sqlalchemy

as_a_blob.py

# -*- coding: utf-8 -*-
import json

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

class Json(TypeDecorator):

    impl = String

    def process_bind_param(self, value, dialect):
        return json.dumps(value)

    def process_result_value(self, value, dialect):
        return json.loads(value)

Base = declarative_base()

class Word(Base):
    __tablename__ = "words"

    eng = Column(String(32), primary_key=True)
    chinese = Column(Json(128))

    def __repr__(self):
       return "Word(%r, %r)" % (self.eng, self.chinese)

# connect to db; create tables, session maker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine,
                                      autocommit=False, autoflush=False))

# add word to db
## from .model import Session, Word
session = Session()
word = Word(eng='art', chinese=[u'艺术',u'美术'])
session.add(word)
session.commit()

# get word from db
session = Session()
word = session.query(Word).filter_by(eng='art').one()
print("\n".join(map(repr, [word, word.eng, word.chinese, word.chinese[0]])))

model.py

# -*- coding: utf-8 -*-
from sqlalchemy import Column, Integer, ForeignKey, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class Word(Base):
    __tablename__ = "words"

    id = Column(Integer, primary_key=True)
    eng = Column(String(32), unique=True)
    chinese = relationship("Chinese", backref="eng")

    def __init__(self, eng, chinese):
        self.eng = eng
        self.chinese = map(Chinese, chinese)

    def __repr__(self):
       return "Word(%r, %r)" % (self.eng, [c.word for c in self.chinese])

class Chinese(Base):
    __tablename__ = "chinese_words"

    word = Column(String(128), primary_key=True)
    eng_id = Column(Integer, ForeignKey('words.id'), primary_key=True)

    def __init__(self, word):
        self.word = word

    def __repr__(self):
       return "Chinese(%r)" % (self.word,)


# connect to db; create tables, session maker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine,
                                      autocommit=False, autoflush=False))

# add word to db
## from .model import Session, Word
session = Session()
word = Word(eng='art', chinese=[u'艺术',u'美术'])
session.add(word)
session.commit()

# get word from db
session = Session()
word = session.query(Word).filter_by(eng='art').one()
print("\n".join(map(str, [word, word.eng, word.chinese, word.chinese[0].eng])))