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