sqlite - Wrap SQLAlchemy in Classes in Python -
i using sqlalchemy 1.0.6 , python 2.7.6. after great insight gleaned last post (dynamic datasets , sqlalchemy), looking modularize code easier implementation framework. going full-working code
from time import time sqlalchemy import column, string, integer sqlalchemy.ext.declarative import declarative_base sqlalchemy import create_engine sqlalchemy.orm import sessionmaker base = declarative_base() class empty_init(): def __init__(self, **kwargs): k,v in kwargs.items(): #this class , structure of table need sync'd. if hasattr(self, k): setattr(self, k, v) else: if k not in engine.execute("select * `{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) int else ("double" if type(v) 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 commit changes except: s.rollback() #rollback changes on error finally: s.close() #close connection print str(time() - t) + " s." (note: purpose of empty_init class have inherited other tables, seen in listing class.)
i'd
- wrap sqlalchemy-related things it's own self-contained class
- port sqlalchemy-related classes it's own module via
import
thinking first goal needs achieved before second, here's i'm stuck
from time import time sqlalchemy import column, string, integer sqlalchemy.ext.declarative import declarative_base sqlalchemy import create_engine 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): k,v in kwargs.items(): #this class , structure of table need sync'd. if hasattr(self, k): setattr(self, k, v) else: if k not in engine.execute("select * `{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) int else ("double" if type(v) 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 gives 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 * `{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 * `listings`'] i understand error saying (the database created, there no tables), why? why didn't sqlalchemy create table? executed same code in same sequence under database's __init__. what's more baffling, if run first code, initialize database class inheriting base , engine sqlalchemy classes, 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 methods, writes database perfectly, suspect i'm not inheriting sqlalchemy's classes correctly (in 2nd code) or, lesser degree, i'm not letting sqlalchemy's behind-the-scenes magic work wrapping class -- can't see why. missing obvious?
update: think correct in thinking way trying structure classes wasn't letting sqlalchemy's behind-the-scenes magic work. thus, after playing around class inheritance, full-working code works fine:
from time import time sqlalchemy import column, string, integer sqlalchemy.ext.declarative import declarative_base sqlalchemy import create_engine 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 , structure of table need sync'd. k,v in kwargs.items(): if hasattr(self, k): #if class has attribute initialized, setattr(self, k, v) # assign new value else: # if doesn't... if k not in engine.execute("select * `{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) int else ("double" if type(v) 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 slight variation in class inheritance.
now first goal complete, move onto second of placing database, empty_init, , listing external module. part bit tricky, here's how works together
main.py
from time import time 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 sqlalchemy import column, string, integer sqlalchemy import create_engine 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 , structure of table need sync'd. k,v in kwargs.items(): if hasattr(self, k): #if class has attribute initialized, setattr(self, k, v) # assign new value else: # if doesn't... if k not in engine.execute("select * `{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) int else ("double" if type(v) 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 @ sqlalchemy, welcome feedback may have. since solves 2 goals, mark question solved. hope may in future.
Comments
Post a Comment