On 07/21/2016 07:06 PM, John Robson wrote:
Hi everyone, I want to store the minute price of all 500 stocks from S&P 500. This means more than 500 million of rows + almost 200K updates per day + (sometimes) past values must be updated (to adjust ex-dividend, splits). Instead of creating a BIG table, I would like to create 500 tables (one for each stock). They have the same structure/columns (open, high, low, close, volume). There is some way of creating only one Class to map all those 500 tables, or I need to create 500 classes?
well the simplest way is not to "create 500 classes" but just build a factory that spits out a subclass as needed. This can be built on the second example at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName:
from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Stock(Base): __abstract__ = True id = Column(Integer, primary_key=True) price = Column(Float) def __repr__(self): return "Stock(symbol=%r, price=%r)" % (self.symbol, self.price) _symbols = {} @classmethod def for_symbol(cls, symbol): if symbol in cls._symbols: return cls._symbols[symbol] cls._symbols[symbol] = stock_cls = type( "%s_Stock" % symbol, (cls, ), { "__tablename__": "stock_%s" % symbol, "symbol": symbol } ) return stock_cls symbols = ["AAA", "BCA", "GOOGL", "RHAT", "AAPL"] engine = create_engine('sqlite://', echo=True) for symbol in symbols: Stock.for_symbol(symbol) Base.metadata.create_all(engine) sess = sessionmaker(engine)() sess.add_all([ Stock.for_symbol("BCA")(price=45.20), Stock.for_symbol("AAA")(price=50.00), Stock.for_symbol("GOOGL")(price=95.00), Stock.for_symbol("RHAT")(price=72.20), Stock.for_symbol("AAPL")(price=92.20), ]) sess.commit() print sess.query(Stock.for_symbol("AAPL")).first().price
* I found this code of a Dynamically table: https://gist.github.com/munro/3686e3b060c2cd7959350ea8bf77ff2c Is this the best approach for CRUDing this 500 tables and still using all the SQLAlchemy features? * I also found about "Single Table Inheritance": http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#single-table-inheritance So, I wonder what is the best approach to create and manage this DB with 500 tables. Thank you, John
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.