sqlalchemy map object which contains dicts

Question

I'm trying to map an object that looks like this:

    self.user = {lots of stuff in here}
    self.timeStamp = i am a date object
    self.coordinates  = {lots of stuff in here}
    self.tweet = {lots of stuff in here}

    self.favourite = 0
    self.reTweet = 0

the non dictionaries seem simple to map

__tablename__ = 'Tweet'
id = Column(Integer, primary_key=True)
timeStamp = Column(DateTime)
favourite = Column(Integer)
reTweet = Column(Integer)

however I have no idea how I can map the dictionary objects. Idealy those objects should proberly go into there own tables so we obey 3rd normal form. However I have no idea where to begin. Can someone point me in the right direction? should I turn these dictionaries into there own objects and map them as well?

many thanks

FMcC · Answer 1 · 2013-08-07T20:13:14.287

The user and co-ordinates entries can be stored as separate tables, which the tweet table will link to as foreign keys. Something like:

class Tweet(Base):
    __tablename__ = 'tweet'
    id = Column(Integer, Sequence('tweet_id_seq'), primary_key=True)
    user = Column(Integer, ForeignKey('user.id'))
    coords = Column(Integer, ForeignKey('coordinates.id'))
    timeStamp = Column(DateTime)
    favourite = Column(Integer)
    reTweet = Column(Integer)

class Coordinates(Base):
    __tablename__ = 'coordinates'
    id = Column(Integer, Sequence('coordinates_id_seq'), primary_key=True)
    lat = ...
    long = ...

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = ...

score 3 · Accepted Answer · edited May 23 '17 at 12:28

For storing dictionary objects, you have several options:

use a Text field, write your dict to it via json.dumps(value), read from it using json.loads(db_value)

create your own json type, like it was suggested in this thread: SQLAlchemy JSON as blob/text

import jsonpickle
import sqlalchemy.types as types

class JsonType(types.MutableType, types.TypeDecorator):    
    impl = types.Unicode

    def process_bind_param(self, value, engine):
        return unicode(jsonpickle.encode(value))

    def process_result_value(self, value, engine):
        if value:
            return jsonpickle.decode(value)
        else:
            # default can also be a list
            return {}

And, FYI, it would be pretty hard for you to follow 3rd normal form, because tweet objects don't have a strict and defined schema - storing it in the database field is just ok.

By the way, I've found that using mongodb for storing tweets is pretty convinient, because it's schemaless and stores json objects.

Hope that helps.

sqlalchemy map object which contains dicts

2 Answers2