Conveniently Storing Lists in Database

posted

Storing sequential data in database shouldn't require much effort. And storing lists of sequential data should be just as easy. Except.

Dude?! If I want to store a list structure, won't I have to extend the Column Type?

Well, if you're going to be inserting and removing data from the list then yes. Do that. But if you're honestly just storing a list of sequential data where order does not matter, and you will not, I repeat, NOT be inserting or removing anything, well then no. There's an easier way.

Some examples might be a list of temperatures, points, user ratings. Things that are by nature sequential and require no sorting or filtering. Let me show you a clever way to achieve this. My examples assume you're familiar with SQLALchemy.

Take this class:

class User(db.Model):

    __tablename__ = 'users'

    id = Column(db.Integer, primary_key=True)
    name = Column(db.String, nullable=False, unique=True)
    email = Column(db.String, nullable=False, unique=True)
    _ratings = Column(db.String, default='0.0')

The column _ratings is the "private" member where we are going to store the database's representation of our list of user ratings.
We're going to do nothing more than store a semi-colon (;) delimited list of numbers (floats to be exact) in this example.
(eg: 3.5;1.5;5.0)

Now to get a real list we just need add some class properties to serve as official setters and getters.

    _ratings = Column(db.String, default='0.0')
    @property
    def ratings(self):
        return [float(x) for x in self._ratings.split(';')]
    @ratings.setter
    def ratings(self, value):
        self._ratings += ';%s' % value


The first function (the getter) splits the value in the _ratings column, casts each element to a float and returns a list of those floats.
The second function (the setter) takes a float as a parameter (I'm not doing any type checking here but you should) and appends it as a string to the value in _ratings. So adding values to your ratings list would look like this:

>>> user.ratings = 3.5
>>> user.ratings = 1.5
>>> user.ratings = 5.0


And to retrieve the list:

>>> print user.ratings
[3.5, 1.5, 5.0]


And note the value stored at _ratings:

>>> print user._ratings
'3.5;1.5;5.0'


You can also do things like this now!

>>> average = sum(user.ratings) / float(len(user.ratings))
>>> print average
3.33333333333

Lists, SQLAlchemy, Python

Latest Posts

The Martian.

This book was absolutely riveting. It kept me up two nights in a row and had me imagining amber Martian landscapes around the clock. The author, Andy Weir, was previously a software engineer,...

Pragmatic MVP, References

The Pragmatic MVP is a talk I gave at TalTech Expo 2015 on building effective early stage prototypes. Below is a list of websites, articles, and books I used in preparation...

Introducing wk

A while back I wrote about some handy aliases for virtualenv and serializing frequently and commonly chained commands that one uses to setup project environments. This is a first step at......