python - 在SQLAlchemy中映射许多相似的表

我有很多(〜2000个)位置数据。每个时间序列都有数百万行。我想将它们存储在Postgres数据库中。我当前的方法是为每个位置时间序列创建一个表，并为一个元数据表存储有关每个位置的信息(坐标，高程等)。我正在使用Python/SQLAlchemy创建和填充表。我想在元表和每个时间序列表之间建立一种关系，以执行诸如“选择日期A和日期B之间具有数据的所有位置”和“选择日期A的所有数据并导出具有坐标的csv”之类的查询。创建具有相同结构(只有名称不同)并与元表有关系的许多表的最佳方法是什么？还是应该使用其他数据库设计？

目前，我正在使用这种类型的方法来生成许多类似的映射:

from sqlalchemy import create_engine, MetaData
from sqlalchemy.types import Float, String, DateTime, Integer
from sqlalchemy import Column, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref

Base = declarative_base()


def make_timeseries(name):
    class TimeSeries(Base):

        __tablename__ = name
        table_name = Column(String(50), ForeignKey('locations.table_name'))
        datetime = Column(DateTime, primary_key=True)
        value = Column(Float)

        location = relationship('Location', backref=backref('timeseries',
                                lazy='dynamic'))

        def __init__(self, table_name, datetime, value):
            self.table_name = table_name
            self.datetime = datetime
            self.value = value

        def __repr__(self):
            return "{}: {}".format(self.datetime, self.value)

    return TimeSeries


class Location(Base):

    __tablename__ = 'locations'
    id = Column(Integer, primary_key=True)
    table_name = Column(String(50), unique=True)
    lon = Column(Float)
    lat = Column(Float)

if __name__ == '__main__':
    connection_string = 'postgresql://user:pw@localhost/location_test'
    engine = create_engine(connection_string)
    metadata = MetaData(bind=engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    TS1 = make_timeseries('ts1')
    # TS2 = make_timeseries('ts2')   # this breaks because of the foreign key
    Base.metadata.create_all(engine)
    session.add(TS1("ts1", "2001-01-01", 999))
    session.add(TS1("ts1", "2001-01-02", -555))

    qs = session.query(Location).first()
    print qs.timeseries.all()

这种方法存在一些问题，最显着的是，如果我创建了多个TimeSeries，则外键不起作用。以前，我已经使用了一些变通方法，但是这似乎是一个很大的漏洞，而且我认为必须有一种更好的方法来做到这一点。我应该如何组织和访问我的数据？

最佳答案

Alternative-1: Table Partitioning
一旦我阅读了与表结构完全相同的，就立即想到了Partitioning。我不是DBA，也没有太多使用它的生产经验(在PostgreSQL上甚至没有更多的经验)，但是
请阅读 PostgreSQL - Partitioning 文档。表分区旨在精确解决您遇到的问题，但是超过1K的表/分区听起来很有挑战性。因此，请在论坛/SO上进行更多研究，以解决与此主题相关的可扩展性问题。

考虑到您两个最常用的搜索条件，datetime组件非常重要，因此必须在其上具有可靠的索引策略。如果您决定使用 partitioning 根，则明显的分区策略将基于日期范围。与最新数据相比，这可能使您可以将旧数据划分为不同的块，尤其是假设(几乎从未)更新旧数据，因此物理布局将是密集且高效的。而您可以采用另一种策略来获取更多“最新”数据。
Alternative-2: trick SQLAlchemy
这基本上可以通过诱骗SA假定所有那些TimeSeries是使用 children 的一个实体的Concrete Table Inheritance来使您的示例代码正常工作。下面的代码是独立的，并使用最少的数据创建了50个表。但是，如果您已经有一个数据库，它应该允许您相当快地检查性能，以便您可以决定是否有可能。

from datetime import date, datetime

from sqlalchemy import create_engine, Column, String, Integer, DateTime, Float, ForeignKey, func
from sqlalchemy.orm import sessionmaker, relationship, configure_mappers, joinedload
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.ext.declarative import AbstractConcreteBase, ConcreteBase


engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(engine)


# MODEL
class Location(Base):
    __tablename__ = 'locations'
    id = Column(Integer, primary_key=True)
    table_name = Column(String(50), unique=True)
    lon = Column(Float)
    lat = Column(Float)


class TSBase(AbstractConcreteBase, Base):
    @declared_attr
    def table_name(cls):
        return Column(String(50), ForeignKey('locations.table_name'))


def make_timeseries(name):
    class TimeSeries(TSBase):
        __tablename__ = name
        __mapper_args__ = { 'polymorphic_identity': name, 'concrete':True}

        datetime = Column(DateTime, primary_key=True)
        value = Column(Float)

        def __init__(self, datetime, value, table_name=name ):
            self.table_name = table_name
            self.datetime = datetime
            self.value = value

    return TimeSeries


def _test_model():
    _NUM = 50
    # 0. generate classes for all tables
    TS_list = [make_timeseries('ts{}'.format(1+i)) for i in range(_NUM)]
    TS1, TS2, TS3 = TS_list[:3] # just to have some named ones
    Base.metadata.create_all()
    print('-'*80)

    # 1. configure mappers
    configure_mappers()

    # 2. define relationship
    Location.timeseries = relationship(TSBase, lazy="dynamic")
    print('-'*80)

    # 3. add some test data
    session.add_all([Location(table_name='ts{}'.format(1+i), lat=5+i, lon=1+i*2)
        for i in range(_NUM)])
    session.commit()
    print('-'*80)

    session.add(TS1(datetime(2001,1,1,3), 999))
    session.add(TS1(datetime(2001,1,2,2), 1))
    session.add(TS2(datetime(2001,1,2,8), 33))
    session.add(TS2(datetime(2002,1,2,18,50), -555))
    session.add(TS3(datetime(2005,1,3,3,33), 8))
    session.commit()


    # Query-1: get all timeseries of one Location
    #qs = session.query(Location).first()
    qs = session.query(Location).filter(Location.table_name == "ts1").first()
    print(qs)
    print(qs.timeseries.all())
    assert 2 == len(qs.timeseries.all())
    print('-'*80)


    # Query-2: select all location with data between date-A and date-B
    dateA, dateB = date(2001,1,1), date(2003,12,31)
    qs = (session.query(Location)
            .join(TSBase, Location.timeseries)
            .filter(TSBase.datetime >= dateA)
            .filter(TSBase.datetime <= dateB)
            ).all()
    print(qs)
    assert 2 == len(qs)
    print('-'*80)


    # Query-3: select all data (including coordinates) for date A
    dateA = date(2001,1,1)
    qs = (session.query(Location.lat, Location.lon, TSBase.datetime, TSBase.value)
            .join(TSBase, Location.timeseries)
            .filter(func.date(TSBase.datetime) == dateA)
            ).all()
    print(qs)
    # @note: qs is list of tuples; easy export to CSV
    assert 1 == len(qs)
    print('-'*80)


if __name__ == '__main__':
    _test_model()

Alternative-3: a-la BigData
如果您确实遇到使用数据库的性能问题，我可能会尝试:

仍然像现在一样将数据保存在单独的表/数据库/方案中

使用数据库引擎

提供的“ native ”解决方案批量导入数据

使用 MapReduce -like分析。

在这里，我将停留在python和sqlalchemy上，并实现自己的分布式查询和聚合(或查找现有的东西)。显然，这仅在不需要直接在数据库上产生这些结果的情况下才有效。

编辑1:Alternative-4: TimeSeries databases
我没有大规模使用它们的经验，但是绝对是值得考虑的选择。

如果您以后可以分享您的发现和整个决策过程，那就太好了。