How to create only one table with SQLAlchemy?

Question

I am unable to create a single table using SQLAlchemy.

I can create it by calling Base.metadata.create_all(engine) but as the number of table grows, this call takes a long time.

I create table classes on the fly and then populate them.

from sqlalchemy import create_engine, Column, Integer, Sequence, String, Date, Float, BIGINT from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker  Base = declarative_base()  class HistoricDay():      id = Column(Integer, Sequence('id_seq'), primary_key=True)     #  Date, Open, High, Low, Close, Volume, Adj Close     date = Column(Date)     open = Column(Float)     high = Column(Float)     low = Column(Float)     close = Column(Float)     volume = Column(BIGINT)     adjClose = Column(Float)      def __init__(self, date, open, high, low, close, volume, adjClose):         self.date = date         self.open = open         self.high = high         self.low = low         self.close = close         self.volume = volume         self.adjClose = adjClose  def build_daily_history_table_repr(self):         return "<"+self.__tablename__+"('{}','{}','{}','{}','{}','{}','{}','{}')>".format(self.id, self.date, self.open, self.high, self.low, self.close, self.volume, self.adjClose)  def build_daily_history_table(ticket):     classname = ticket+"_HistoricDay"     globals()[classname] = type(classname, (HistoricDay,Base), {'__tablename__' : ticket+"_daily_history"})     setattr(globals()[classname], '__repr__', build_daily_history_table_repr)  # Initialize the database :: Connection & Metadata retrieval engine = create_engine('mysql+cymysql://root@localhost/gwc?charset=utf8&use_unicode=0', pool_recycle=3600) # ,echo = True  # SqlAlchemy :: Session setup Session = sessionmaker(bind=engine)  # Create all tables that do not already exist Base.metadata.create_all(engine)  # SqlAlchemy :: Starts a session session = Session()  ticketList = getTicketList()  for ticket in ticketList:     build_daily_history_table(ticket)     class_name = ticket+"_HistoricDay"      meta_create_all_timer = time.time()     # Create all tables that do not already exist     # globals()[class_name]('2005-07-24',0,0,0,0,0,0).create(engine)  #doesn't work     #(globals()[class_name]).__table__.create(engine) #doesn't work     # session.commit() #doesn't work      #Base.metadata.create_all(engine) # works but gets very slow     print("  meta_create_all_timer {}s".format(time.time()-meta_create_all_timer))      data = getData(ticket)      for m_date, m_open, m_close, m_high, m_low, m_volume, m_adjClose in data:         entry = globals()[class_name](m_date, m_open, m_high, m_low, m_close, m_volume, m_adjClose)         session.add(entry)      session.commit() 

I saw in the documentation that you can do

engine = create_engine('sqlite:///:memory:')  meta = MetaData()  employees = Table('employees', meta,     Column('employee_id', Integer, primary_key=True),     Column('employee_name', String(60), nullable=False, key='name'),     Column('employee_dept', Integer, ForeignKey("departments.department_id")) ) employees.create(engine) 

However, I'm not able to figure out how to do the same thing as Table does, with declarative_base().

How can I do that with classes that inherit from declarative_base()?

Answer 1

Above, the declarative_base() callable returns a new base class from which all mapped classes should inherit. When the class definition is completed, a new Table and mapper() will have been generated.

The resulting table and mapper are accessible via __table__ and __mapper__ attributes

(From here)

Therefore:

def build_daily_history_table(ticket):     classname = ticket + "_HistoricDay"     ticket = type(classname, (Base, HistoricDay), {'__tablename__' : ticket+"_daily_history"})     ticket.__repr__ =  build_daily_history_table_repr     return ticket  build_daily_history_table("test").__table__.create(bind = engine) 

Output:

2013-10-04 22:36:53,263 INFO sqlalchemy.engine.base.Engine  CREATE TABLE test_daily_history (     id INTEGER NOT NULL,      date DATE,      open FLOAT,      high FLOAT,      low FLOAT,      close FLOAT,      volume BIGINT,      "adjClose" FLOAT,      PRIMARY KEY (id) )   2013-10-04 22:36:53,263 INFO sqlalchemy.engine.base.Engine () 2013-10-04 22:36:53,263 INFO sqlalchemy.engine.base.Engine COMMIT 

Credit goes to javex's comment/correction or I might have suggested something akin to:

Base.metadata.tables["ticket_daily_history"].create(bind = engine) 

Advise:

The approach used in build_daily_history_table could be one of the least elegant ways of doing things, primarily for the reason that it is polluting/cluttering the namespace.