title: Wrap SQLAlchemy in Classes in Python
body: <p>I am using SQLAlchemy 1.0.6 and Python 2.7.6.  After the great insight I gleaned from my last post (<a href="https://stackoverflow.com/questions/31325860/dynamic-datasets-and-sqlalchemy/31325947#31325947">Dynamic Datasets and SQLAlchemy</a>), I am now looking to modularize my code for easier implementation into my framework.  Going from this full-working code</p>
  
  <pre><code>from time import time
  from sqlalchemy import Column, String, Integer
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  
  Base = declarative_base()
  class Empty_Init():
      def __init__(self, **kwargs):   
          for k,v in kwargs.items():
              #This class and the structure of the table need to be sync'd.
                 if hasattr(self, k):
                  setattr(self, k, v)
                 else:
                  if k not in engine.execute("SELECT * FROM `{tbl}`".format(tbl = self.__tablename__))._metadata.keys:
                      engine.execute("ALTER TABLE `{tbl}` ADD COLUMN {col} {typ}".format(
                                  tbl = self.__tablename__, 
                                  col = k,
                                  typ = "INT" if type(v) is int else ("DOUBLE" if type(v) is float else "VARCHAR")))
                  setattr(self.__class__, k, Column(k, String))
                  setattr(self, k, v)
  
  class Listing(Empty_Init, Base):
      __tablename__ = 'Listings'
      __table_args__ = {'sqlite_autoincrement': True}
      id = Column(Integer, primary_key=True, nullable=False)
      make = Column(String)
      model = Column(String)
      year = Column(Integer)
  
  t = time()
  
  engine = create_engine('sqlite:///')
  Base.metadata.create_all(engine)
  
  session = sessionmaker()
  session.configure(bind=engine)
  s = session()
  
  try:
      data = {'make':'Chevy',
          'model' : 'Corvette',
          'year' : 1964,
          'doors' : 2,
          'price' : 50000}
      record = Listing(**data)
      s.add(record)
  
      data = {'make':'Chevy',
          'model' : 'Camaro',
          'year' : 1967,
          'doors' : 2,
          'HP' : 375,
          "0-60" : 6.1}
      record = Listing(**data)
      s.add(record)
  
      s.commit() #attempt to commit the changes   
  except:
      s.rollback() #rollback the changes on error
  finally:
      s.close() #Close the connection
  print str(time() - t) + " s."
  </code></pre>
  
  <p>(Note:  the purpose of the Empty_Init class is to have it be inherited to other tables, as seen in the Listing class.)</p>
  
  <p>I'd like to </p>
  
  <ul>
  <li>wrap the SQLAlchemy-related things into it's own self-contained class</li>
  <li>Port the SQLAlchemy-related classes into it's own module via <code>import</code></li>
  </ul>
  
  <p>Thinking that the first goal needs to be achieved before the second, here's where I'm stuck</p>
  
  <pre><code>from time import time
  from sqlalchemy import Column, String, Integer
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  
  class DataBase(object):
      def __init__(self):
          self.Base = declarative_base()
          self.engine = create_engine('sqlite:///')
          self.session = sessionmaker()
          self.session.configure(bind=self.engine)
          self.s = self.session()
          self.Base.metadata.create_all(self.engine)
  
      def Add_Record(self, data):
          record = Listing(self.engine, self.Base, **data)        
          self.s.add(record)
  
      def Commit(self):
          self.s.commit()
  
  class Empty_Init():
      def __init__(self, engine, Base, **kwargs): 
          for k,v in kwargs.items():
              #This class and the structure of the table need to be sync'd.
                 if hasattr(self, k):
                  setattr(self, k, v)
                 else:
                  if k not in engine.execute("SELECT * FROM `{tbl}`".format(tbl = self.__tablename__))._metadata.keys:
                      engine.execute("ALTER TABLE `{tbl}` ADD COLUMN {col} {typ}".format(
                                  tbl = self.__tablename__, 
                                  col = k,
                                  typ = "INT" if type(v) is int else ("DOUBLE" if type(v) is float else "VARCHAR")))
                  setattr(self.__class__, k, Column(k, String))
                  setattr(self, k, v)
  
  class Listing(Empty_Init):
      __tablename__ = 'Listings'
      __table_args__ = {'sqlite_autoincrement': True}
      id = Column(Integer, primary_key=True, nullable=False)
      make = Column(String)
      model = Column(String)
      year = Column(Integer)
  
  t = time()
  
  engine = create_engine('sqlite:///')
  
  DBC = DataBase()
  data = {'make':'Chevy',
      'model' : 'Corvette',
      'year' : 1964,
      'price' : 50000}
  DBC.Add_Record(data)
  
  data = {'make':'Chevy',
      'model' : 'Camaro',
      'year' : 1967,
      'HP' : 375,
      "0-60" : 6.1}
  DBC.Add_Record(data)
  DBC.Commit()
  
  print str(time() - t) + " s."
  </code></pre>
  
  <p>Running this gives the following full Traceback</p>
  
  <pre><code>Traceback (most recent call last):
   File "&lt;stdin&gt;", line 1, in &lt;module&gt;
    File "/usr/lib/python2.7/dist-packages/spyderlib/widgets/externalshell/sitecustomize.py", line 540, in runfile
      execfile(filename, namespace)
    File "/home/manny/sqlalchemy_basic_master_class.py", line 65, in &lt;module&gt;
      DBC.Add_Record(data)
    File "/home/manny/sqlalchemy_basic_master_class.py", line 23, in Add_Record
      record = Listing(self.engine, self.Base, **data)        
    File "/home/manny/sqlalchemy_basic_master_class.py", line 40, in __init__
      if k not in engine.execute("SELECT * FROM `{tbl}`".format(tbl = self.__tablename__))._metadata.keys:
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1987, in execute
      return connection.execute(statement, *multiparams, **params)
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 906, in execute
      return self._execute_text(object, multiparams, params)
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
      statement, parameters
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
      context)
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
      exc_info
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb)
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
      context)
    File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
      cursor.execute(statement, parameters)
  sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: Listings [SQL: 'SELECT * FROM `Listings`']
  </code></pre>
  
  <p>I understand what the error is saying (the database was created, but there are no tables), but why?  Why didn't SQLAlchemy create the table?  I executed the same code in the same sequence under DataBase's <code>__init__</code>.  What's even more baffling, is that if I run the first code, then initialize the <code>DataBase</code> class by inheriting the <code>Base</code> and <code>engine</code> SQLAlchemy classes, like so</p>
  
  <pre><code>class DataBase(object):
      def __init__(self, Base, engine):
          self.Base = Base
          self.engine = engine
          self.session = sessionmaker()
          self.session.configure(bind=self.engine)
          self.s = self.session()
          self.Base.metadata.create_all(self.engine)
  Base = declarative_base()
  engine = create_engine('sqlite:///')
  DBC = DataBase(Base, engine)
  </code></pre>
  
  <p>and run its methods, it writes to the database perfectly, so I strongly suspect I'm not inheriting SQLAlchemy's classes correctly (in the 2nd code) or, to a much lesser degree, I'm not letting SQLAlchemy's behind-the-scenes magic work by wrapping it into a class -- I just can't see why.  Am I missing something obvious?</p>