在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，那么外键就不起作用。之前我已经使用了一些变通方法，但这一切看起来都像是一个大麻烦，我觉得肯定有更好的方法来做这件事。我应该如何组织和访问我的数据？
回答 3
Stack Overflow用户
发布于 2014-04-03 08:31:32
Alternative-1: Table Partitioning
当我读到完全相同的表结构时，我立刻想到了Partitioning。我不是数据库管理员，使用它的生产经验也不多(在PostgreSQL上更是如此)，但请阅读PostgreSQL - Partitioning文档。表分区旨在解决您的问题，但超过1000个表/分区听起来很有挑战性；因此，请在论坛/SO上进行更多研究，以了解与可伸缩性相关的问题。
考虑到这两个最常用的搜索标准，datetime组件是非常重要的，因此必须有可靠的索引策略。如果您决定使用partitioning根目录，那么显而易见的分区策略将基于日期范围。与最近的数据相比，这可能允许您将旧数据划分为不同的块，特别是假设旧数据(几乎从不)更新，因此物理布局将是密集和有效的；而您可以对更“新”的数据采用另一种策略。
Alternative-2: trick SQLAlchemy
这基本上是通过欺骗SA来假设所有这些TimeSeries都是使用Concrete Table Inheritance的一个实体的children，从而使您的示例代码工作。下面的代码是自包含的，它创建了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
如果您在使用数据库时遇到性能问题，我可能会尝试：
- 仍然将数据保存在单独的表/数据库/模式中，就像您使用数据库引擎
- 提供的“原生”解决方案来处理
MapReduce-like数据一样使用MapReduce-like分析。在这里，我会继续使用和- ，并实现自己的分布式查询和聚合(或者找到一些已存在的东西)。显然，只有当您不需要直接在database.
上生成这些结果时，这种方法才有效
编辑-1：Alternative-4: TimeSeries databases
我没有大规模使用它们的经验，但绝对是一个值得考虑的选择。
如果你以后能分享你的发现和整个决策过程，那就太好了。
Stack Overflow用户
发布于 2014-04-01 00:51:01
我会避免你上面提到的数据库设计。我对您正在处理的数据知之甚少，但听起来您应该有两个表。一个表用于location，一个子表用于location_data。location表将存储您上面提到的数据，如坐标和高程。location_data表将存储来自location表的location_id以及您想要跟踪的时间序列数据。
这将避免每次添加另一个位置时更改数据库结构和代码更改，并允许执行您正在查看的查询类型。
Stack Overflow用户
发布于 2014-04-01 00:59:13
两部分：
只使用两张表
没有必要有几十个或数百个相同的表。只需为location和location_data创建一个表，其中每个条目都将fkey到位置。还可以在location_id的location_data表上创建一个索引，这样就可以进行高效的搜索。
不要使用sqlalchemy来创建它
我喜欢sqlalchemy。我每天都在用它。它对于管理您的数据库和添加一些行非常有用，但是您不希望将其用于具有数百万行的初始设置。您希望生成一个与postgres的" COPY“语句兼容的文件[ http://www.postgresql.org/docs/9.2/static/sql-copy.html ]COPY可以让您快速获取大量数据；它是在转储/恢复操作期间使用的。
sqlalchemy对于查询和添加进来的行来说将是很棒的。如果有批量操作，则应使用COPY。
https://stackoverflow.com/questions/22704470
复制相似问题
腾讯云开发者
