CODEWITHSUNDEEP
pythonsqlitepython-2.7sqlalchemy
Manuel J. Diaz
Manuel J. Diaz

Reputation: 1270

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

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?

Upvotes: 2

Views: 3682

Answers (1)

Manuel J. Diaz
Manuel J. Diaz

Reputation: 1270

Update: I think I was correct in thinking that the way I was trying to structure my classes wasn't letting SQLAlchemy's behind-the-scenes magic work. Thus, after playing around with the class inheritance, this full-working code works perfectly fine:

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 DataBase(object):
    def __init__(self, Base):
        self.Base = 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, **data)
        self.s.add(record)

    def Commit(self):
        self.s.commit()

    def Close(self):
        self.s.close()


class Empty_Init():
    def __init__(self, engine, **kwargs):
        #This class and the structure of the table need to be sync'd.
        for k,v in kwargs.items():
               if hasattr(self, k): #if the class already has the attribute initialized,
                setattr(self, k, v) # then assign it the new value
               else: # if it doesn't...
                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()

DBC = DataBase(Base)

data = {'make':'Chevy',
    'model' : 'Corvette',
    'year' : 1964,
    'price' : 50000}
DBC.Add_Record(data)

data = {'make':'Chevy',
    'model' : 'Camaro',
    'year' : 1967,
    'doors' : 2,
    'wheels_rwd' : 4.1,
    "test" : "bladads"}
DBC.Add_Record(data)
DBC.Commit()
DBC.Close()

print str(time() - t) + " s."

Note the slight variation in the class inheritance.

Now that the first goal is complete, I would like to move onto the second of placing DataBase, Empty_Init, and Listing into an external module. This is part was a bit tricky, but here's how it all works together

main.py

from time import time
from datetime import datetime
import sqla_lib

t = time()

DBC = sqla_lib.DataBase(sqla_lib.Base)

data = {'make':'Chevy',
    'model' : 'Corvette',
    'year' : 1964,
    'price' : 50000}
DBC.Add_Record(data)

data = {'make':'Chevy',
    'model' : 'Camaro',
    'year' : 1967,
    'doors' : 2,
    'wheels_rwd' : 4.1,
    "test" : "bladads"}
DBC.Add_Record(data)
DBC.Commit()
DBC.Close()

print str(time() - t) + " s."

sqla_lib.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
class DataBase(object):
    def __init__(self, Base):
        self.Base = 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 = self.Listing(self.engine, **data)
        self.s.add(record)

    def Commit(self):
        self.s.commit()

    def Close(self):
        self.s.close()


    class Empty_Init():
        def __init__(self, engine, **kwargs):
            #This class and the structure of the table need to be sync'd.
            for k,v in kwargs.items():
                   if hasattr(self, k): #if the class already has the attribute initialized,
                    setattr(self, k, v) # then assign it the new value
                   else: # if it doesn't...
                    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)

Since I'm still new at SQLAlchemy, I welcome any feedback you may have. But since this solves my two goals, I will mark this question as solved. I hope that it may help someone in the future.

Upvotes: 2

Related Questions