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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
