在SQLAlchemy中从字典创建表 [英] Creating Table from dictionary in SQLAlchemy

问题描述

我正在尝试根据SQLAlchemy中的字典值创建一个表.我正在使用Flask，目前我的课程如下:

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

这很好用，但是我的问题是我最终将在此表中有许多列，可能是+100.我宁愿不要用100行这种东西填满我的models.py文件.我想在自己的字典中将其保存在自己的文件中，字典看起来像这样:

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

但是，这给了我一个名为sensor的专栏.我在sqlalchemy中发现了几个相关的问题，但是他们没有使用此结构来创建表.如果可能的话，我会更喜欢继续使用db.Model结构的方法，而不是使用create_engine的结构，这是因为稍后会进行一些JSON序列化，而使用该结构(以及一些应用程序结构的东西)会更容易.有什么办法吗?

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?

推荐答案

不是将所有传感器值都塞入

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.

最后，由于传感器表本质上是键/值存储，因此某些RDBMS支持不同类型的文档类型，例如您可以使用的Postgresql的hstore，json和jsonb列.

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.

这篇关于在SQLAlchemy中从字典创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！