Using SQLAlchemy with multiple classes/modules
Power Button
I am quite new to Python and SQLAlchemy and I have a question about to
setup the project structure.
I have my main python script and then a bunch of classes - each
defined in their own class file and stored in packages (directories)
like so
/root
myApp.py
modules/
class1.py
class2.py
modules/packageA
Amodule1.py
Amodule2.py
modules/packageB
Bmodule1.py
BModule2.py
and so on. This app is for a long running daemon running on linux
which monitors the file system and updates the database depending on
certain things.
From reading through theSQLAlchemy docs, I gather that I am supposed
to instantiate an engine and a session once, globally for the app and
use this throughout the app, opening and closing connections when
needed and configuring sessions etc.
My question is (I am very new to Python to please bear with me), how
do I do this?
What I have tried is putting all the setup instructions into a class
and instantiating this in myApp.py. This doesn't make it global to all
the modules though. Do I need to pass this object around to all
classes as arguments to __init__() in order to reference this class?
(this seems a bit bulky to me)
Here is my Bootstrap class
class Bootstrap(object):
def __init__(self):
self.mDb = MyDb() #my DB abstraction class
self.mEngine = self.mDb.getEngine()
self.mSession = self.mDb.getSession()
self.mMeta = self.mDb.getMeta(self.mEngine)
if self.mTable1 == None:
self. mTable1 = self.mDb.getTable("table1", self.mMeta)
self.mEdoMapper = mapper(Table1, self. mTable1)
if self.mTable2 == None:
self. mTable2 = self.mDb.getTable("table2", self.mMeta)
self.mEdoMapper = mapper(Table2, self. mTable2)
#<snip>
class MyDb(object):
mDbHost = "localhost"
mDbUser = "user"
mDbPass = "pass"
mDbSchema = "schema"
mEngine = None
mSession = None
mConn = None
mMeta = None
def getEngine(self):
self.mEngine = create_engine("mysql://%s:%s@%s/%s" %
(self.mDbUser, self.mDbPass, self.mDbHost, self.mDbSchema))
return self.mEngine
def getConnection(self):
return self.mEngine.connect()
def getMeta(self, engine):
self.mMeta = MetaData()
self.mMeta.bind = engine
return self.mMeta
def getTable(self, table, meta):
t = Table(table, meta, autoload=True)
Column('created', mysql.MSDateTime, PassiveDefault(text
("CURRENT_TIMESTAMP")), nullable=False)
return t
def getSession(self):
if self.mSession == None:
self.mSession = sessionmaker()
self.mSession.configure(bind=self.mEngine)
return self.mSession
#<snip>
I need to talk to the database in various modules but I don't want to
open up new engines/sessions etc. Can anyone point me to how to go
about setting up a project which involves many classes/modules etc
(I'm also using threads but I'll leave that for another day :))
Any pointers on this would be much appreciated. (please excuse my lack
of understanding of Python, I'm only about a week old on it so far)
thanks
Michael Bayer
On Nov 17, 2008, at 7:17 AM, Power Button wrote:
>
> What I have tried is putting all the setup instructions into a class
> and instantiating this in myApp.py. This doesn't make it global to all
> the modules though. Do I need to pass this object around to all
> classes as arguments to __init__() in order to reference this class?
> (this seems a bit bulky to me)
Neither your classes, mappers, or Table objects need any knowledge of
the Session or engine in order to be defined. Table objects only need
a MetaData() object, which you probably want to define once in a
single module, which is imported by all of your other modules. If
using declarative, then your declarative base class takes the place of
the MetaData object.
The Session and engine should also be instantiated once globally.
When you start using the Session against particular classes, that's
when they have some association with the database.
> Here is my Bootstrap class
>
> class Bootstrap(object):
> def __init__(self):
> self.mDb = MyDb() #my DB abstraction class
> self.mEngine = self.mDb.getEngine()
> self.mSession = self.mDb.getSession()
> self.mMeta = self.mDb.getMeta(self.mEngine)
>
> if self.mTable1 == None:
> self. mTable1 = self.mDb.getTable("table1", self.mMeta)
> self.mEdoMapper = mapper(Table1, self. mTable1)
>
> if self.mTable2 == None:
> self. mTable2 = self.mDb.getTable("table2", self.mMeta)
> self.mEdoMapper = mapper(Table2, self. mTable2)
this is overly complex. You dont need any reference to mapper
objects, and the Table objects don't need to be so formally assigned
either. A simple setup looks like:
base.py
----------
# global MetaData object
metadata = MetaData()
model1.py
--------------
import base
class MyClass(object):
.....
t1 = Table('sometable', base.metadata, ...)
mapper(MyClass, t1)
# more classes and tables
model2.py
--------------
import base
class SomeOtherClass(object):
.....
t1 = Table('....', ....)
mapper(SomeOtherClass, t2, ...)
bootstrap.py
----------------
# configure the database connection/session
import base
engine = create_engine('my engine://')
Session = scoped_session(sessionmaker(bind=engine))
def create_tables():
"""create all tables which don't yet exist in the DB."""
base.metadata.create_all(engine)
application.py
-------------------
from bootstrap import Session
frmo model1 import MyClass
print Session.query(MyClass).all()
I would also recommend the usage of declarative, http://www.sqlalchemy.org/docs/05/plugins.html#plugins_declarative
, which simply means that the separate class/Table/mapper() objects
are defined at once, and it also allows dependencies between mapped
classes to be defined using strings which can reduce or remove
circular dependency issues.
Power Button
thanks for this. Has cleared alot of things up.
thanks again for the nice library.