CODEWITHSUNDEEP
pythonsqlalchemy
C19
C19

Reputation: 758

python using sqlalchemy store a list into sql database

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

Upvotes: 5

Views: 16614

Answers (5)

plpsanchez
plpsanchez

Reputation: 389

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

Upvotes: 0

jfs
jfs

Reputation: 414835

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.

Upvotes: 10

JosefAssad
JosefAssad

Reputation: 4128

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.

Upvotes: 0

thebjorn
thebjorn

Reputation: 27351

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.

Upvotes: 4

Rostyslav Dzinko
Rostyslav Dzinko

Reputation: 40825

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.

Upvotes: 0

Related Questions