lundi 13 juillet 2015

Wrap SQLAlchemy in Classes in Python

I am using SQLAlchemy 1.0.6 and Python 2.7.6. After the great insight I gleaned from my last post (Dynamic Datasets and SQLAlchemy), I am now looking to modularize my code for easier implementation into my framework. Going from this full-working 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."

(Note: the purpose of the Empty_Init class is to have it be inherited to other tables, as seen in the Listing class.)

I'd like to

  • wrap the SQLAlchemy-related things into it's own self-contained class
  • Port the SQLAlchemy-related classes into it's own module via import

Thinking that the first goal needs to be achieved before the second, here's where I'm stuck

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."

Running this gives the following full Traceback

Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
  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 <module>
    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`']

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 __init__. What's even more baffling, is that if I run the first code, then initialize the DataBase class by inheriting the Base and engine SQLAlchemy classes, like so

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)

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?

Aucun commentaire:

Enregistrer un commentaire