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.

• thebjorn: This is not related to the very good answer to the actual question, just a comment that having unique=True on Word.eng will prevent you from storing different translations for homographs (e.g. 'live' -- rhymes with 'give', and 'live' -- rhymes with 'dive'). A similar problem arises when two separate concepts in language A maps to a single concept in language B, e.g. German essen/fressen -> eat (depends on the subject), French savoir/connaître -> know (depends on the object), English roof/ceiling -> tak [Norwegian] (depends on...), etc. Natural language translation is hard ;-)
• jfs: @thebjorn: Word.eng is a primary key in the question that implies uniqueness. Disregarding it, I've used one to many relationship instead of many to many (what you've described) to avoid complicating the example that demonstrates how to store a list in db. Sqlalchemy supports it if you need it.

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.

Answer 3

The problem is that stringifying list doesn't work as you expected (my conclusion from your post). You can store chinese words in database, just instead of str(word.chinese), make u''.join(word.chinese) and store value in database as a string.

Answer 4

I think you probably want to refine your data schema a little more instead of performing unsightly manipulations from and to strings/lists in one of your model's attributes.

I am assuming that each eng can have 1 or more ́chinese ́, in which case you want a 1 to many relationship and two tables/mapped objects instead.

your code will be significantly cleaner.

eval'ing strings is just a really bad idea. Always.

Answer 5

This is an old question that deserves newer answers.

As of the year 2022, you can use MutableList from sqlalchemy, as shown in this answer: https://stackoverflow.com/a/64356997/2719980