python using sqlalchemy store a list into sql database

Question

The brief code is like this:

    class Word(Base):
        __tablename__ = 'word'
        eng                 = Column(String(32),primary_key=True)
        chinese             = Column(String(128))

word = Word(eng='art',chinese=[u'艺术',u'美术'])
session.add(word)
session.commit()

I'm trying to store word.chinese as a string. And in python it's a list... Well, when I write sql myself I could str(word.chinese) and then insert into the database. When need to get it, I could simply eval(result) to get the original python object. But since I'm using the sqlalchemy to store my objects, I wonder where to change to reach my goal...

Answer 1

To store a list in a db you could use a new table:

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)

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

See full example.

Don't use str()/eval() if you want to store chinese as a blob you could use json.dumps()/json.loads(). Using suggested by @thebjorn TypeDecorator:

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)

class Word(Base):
    __tablename__ = "words"

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

See full example.

Answer 2

You'll find the functionality you're asking for in TypeDecorator ( http://docs.sqlalchemy.org/en/rel_0_7/core/types.html#sqlalchemy.types.TypeDecorator -- you'll have to create e.g. a subclass of list to get it to work).

However, what you're trying to do is store two different translations for the English word art (at least that's what google translate is telling me :-). Storing them as a comma-separated list in a text field is not first normal form. You should store two records

('art', u'艺术')
('art', u'美术')

and change your database structure to allow for this.