Creating Table from dictionary in SQLAlchemy

Question

I'm trying to create a table from dictionary values in SQLAlchemy. I'm using Flask, and currently my class looks like this:

class Machine(db.Model):
    """Template for the Machine Info table"""
    __tablename__ = 'machine'
    id = db.Column(db.Integer, primary_key=True)
    machine_name = db.Column(db.String(32))
    date = db.Column(db.String(32))
    time = db.Column(db.String(32))
    sensor1 = db.Column(db.String(32))
    sensor2 = db.Column(db.String(32))

This works fine, but my issue is that I will eventually have many columns in this table, possibly +100. I would rather not fill up my models.py file with 100 lines of this kind of stuff. I wanted to have it in its own dictionary in its own file, the dictionary looks like this:

SENSOR_LOOKUP_DICT = {
    "machine_name":"machine_name",
    "date":"date",
    "time":"time",
    "sensor1":"sensor1",
    "sensor2":"sensor2"
}

A list would probably work here too.

I was thinking I could use some kind of loop, like this:

class Machine(db.Model):
    """Template for the Machine Info table"""
    __tablename__ = 'machine'
    id = db.Column(db.Integer, primary_key=True)
    for sensor in SENSOR_LOOKUP_DICT:
        sensor = db.Column(db.String(32))

But this just gives me a column called sensor. I found a couple sort of relevant questions with sqlalchemy but they didn't use this structure for creating tables. I would very much prefer a method if possible that continues to use the db.Model structure, rather than a structure that uses create_engine, due to some JSON serialization later which is easier with this structure (as well as some app structure stuff). Is there any way to do this?

Having to have hundreds of repetitive columns in a table sounds like an antipattern. Could you instead have a separate sensor table, such as *(name, machine_id, value)* etc.? Would be interesting to see what relevant questions you found. For example [this](https://stackoverflow.com/questions/2768607/dynamic-class-creation-in-sqlalchemy) has the building blocks you need, if you continue on this path. — Ilja Everilä, Jul 06 '17 at 05:29
Alternatively you might be interested in using some other type of DB instead of a relational/SQL DB, if you just want to store log/document items as a whole, and perhaps not care so much about transactions etc. — Ilja Everilä, Jul 06 '17 at 06:32

Ilja Everilä · Answer 1 · 2017-07-06T08:58:21.100

Instead of cramming all the sensor values in to a single row of hundred or more columns, you could split your design to machine and sensor tables:

from datetime import datetime

from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.associationproxy import association_proxy

class Machine(db.Model):
    """The Machine Info table"""
    __tablename__ = 'machine'
    id = db.Column(db.Integer, primary_key=True)
    machine_name = db.Column(db.String(32))
    datetime = db.Column(db.DateTime, default=datetime.utcnow)
    sensors = db.relationship(
        'Sensor',
        collection_class=attribute_mapped_collection('name'),
        cascade='all, delete-orphan')
    sensor_values = association_proxy(
        'sensors', 'value',
        creator=lambda k, v: Sensor(name=k, value=v))

class Sensor(db.Model):
    """The Sensor table"""
    __tablename__ = 'sensor'
    machine_id = db.Column(db.Integer, db.ForeignKey('machine.id'),
                           primary_key=True)
    # Note that this could be a numeric ID as well
    name = db.Column(db.String(16), primary_key=True)
    value = db.Column(db.String(32))

The dictionary collection relationship combined with the association proxy allow you to handle the sensor values like so:

In [10]: m = Machine(machine_name='Steam Machine')

In [11]: m.sensor_values['sensor1'] = 'some interesting value'

In [12]: db.session.add(m)

In [13]: db.session.commit()

In [14]: m.sensor_values
Out[14]: {'sensor1': 'some interesting value'}

In [16]: m.sensor_values['sensor1']
Out[16]: 'some interesting value'

An added benefit of having separate tables instead of a fixed schema is that if you add sensors later in life, you don't need to migrate your schema to accommodate that – in other words no need to alter the table to add columns. Just add the new sensor values to the sensor table like before.

Finally, some RDBMS support different kinds of document types, such as Postgresql's hstore, json, and jsonb columns that you could use, since the sensor table is essentially a key/value store.

Creating Table from dictionary in SQLAlchemy

1 Answers1

Linked