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...

Community · Accepted Answer · 2017-05-23 11:45:34Z

10

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Aug 11, 2012 at 14:30

jfs

417k210 gold badges1k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thebjorn Over a year ago

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 Over a year ago

@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.

thebjorn · Accepted Answer · 2012-08-11 13:44:07Z

4

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.

answered Aug 11, 2012 at 13:44

thebjorn

27.6k12 gold badges107 silver badges152 bronze badges

Comments

Rostyslav Dzinko · Accepted Answer · 2012-08-11 13:40:44Z

0

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.

answered Aug 11, 2012 at 13:40

Rostyslav Dzinko

41k5 gold badges55 silver badges66 bronze badges

Comments

JosefAssad · Accepted Answer · 2012-08-11 14:01:53Z

0

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.

answered Aug 11, 2012 at 14:01

JosefAssad

4,12830 silver badges37 bronze badges

Comments

plpsanchez · Accepted Answer · 2022-02-09 14:38:32Z

0

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

answered Feb 9, 2022 at 14:38

plpsanchez

3896 silver badges12 bronze badges

Collectives™ on Stack Overflow

python using sqlalchemy store a list into sql database

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related