NonDbi Namespace Reference

Namespaces
namespace	assoc
namespace	dataset
namespace	director
namespace	dj
namespace	meta
namespace	movie
namespace	srmm
namespace	test_dj
Classes
class	MetaDB
Functions
def	cfg_
def	dj_init_
def	engine_
def	session_
Variables
tuple	log = logging.getLogger(__name__)
tuple	Session = sessionmaker()

---

Detailed Description

SQLAlchemy Ecosystem
====================

Requirements, the currently non-standard ``SQLAlchemy`` external, install with::

   ./dybinst trunk external SQLAlchemy 

After installation many examples are available at::

   external/build/LCG/SQLAlchemy-0.6.7/examples/

Reading from DB :dybgaudi:`Database/NonDbi/tests/read.py`::

   from NonDbi import session_, Movie, Director
   session = session_("tmp_offline_db", echo=False) 
   for m in session.query(Movie).all():
       print m


Writing to DB :dybgaudi:`Database/NonDbi/tests/write.py`::

    from NonDbi import session_, Movie, Director
    session = session_("tmp_offline_db", echo=False) 
    m1 = Movie("Star Trek", 2009)
    m1.director = Director("JJ Abrams")
    d2 = Director("George Lucas")
    d2.movies = [Movie("Star Wars", 1977), Movie("THX 1138", 1971)]
    try:
        session.add(m1)
        session.add(d2)
        session.commit()
    except:
        session.rollback()




Deficiencies
~~~~~~~~~~~~~~

Problems with multiple sessions, may need rearrangement

 * http://www.sqlalchemy.org/docs/orm/session.html#session-frequently-asked-questions







Accessing Non Dbi tables with SQLAlchemy
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The ``kls_`` method on the SQLAlchemy ``session`` returns an SQLAlchemy class
mapped to the specified table. Usage::

    from NonDbi import session_
    s = session_("fake_dcs")
    kls = s.kls_("DBNS_SAB_TEMP")   
    n = s.query(kls).count()



Accessing DBI pairs with SQLAlchemy 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The ``dbikls_`` 
method on the SQLAlchemy ``session`` has been shoe-horned 
in using some esoteric python.
It returns an SQLAlchemy class mapped to the join of payload and validity tables. 
Usage::

    from NonDbi import session_
    session = session_("tmp_offline_db")
    YReactor = session.dbikls_("Reactor")   

    # Use dynamic class in standard SQLAlchemy ORM manner
    n = session.query(YReactor).count()
    a = session.query(YReactor).filter(YReactor.SEQNO==1).one()
    print vars(a)        ## instances of the class have all payload and validity attributes 

Esotericness includes : closures, dynamic addition of instance methods and dynamic class generation.
The advantage of this approach is that there are no static ".spec" or declarative table definitions,
everything is dynamically created from the Database schema.
This dynamism is also a disadvantage as the static files can be useful places for 
adding functionality.
 
Reference for SQLAlchemy querying 
 * http://www.sqlalchemy.org/docs/orm/tutorial.html#querying


How to add a class/table 
~~~~~~~~~~~~~~~~~~~~~~~~~~

#. follow patten of examples in ``movie.py`` and ``director.py`` 
#. import the declarative classes into ``__init__`` of **NonDbi** 
#. write tests to check functionality 

References
~~~~~~~~~~~~

Declarative SQLAlchemy

* http://www.sqlalchemy.org/docs/orm/tutorial.html#creating-table-class-and-mapper-all-at-once-declaratively

Hierarchy using self referential one-to-many:

* http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships

For a self-contained script to quickstart model prototyping see :

* http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-step-sqlalchemy-tutorial-part-2-of-2/


SQLite tips
~~~~~~~~~~~~

SQLite is useful for quick tests without need to connect to a remote DB, 
the DB lives inside a file or even in memory::

    sqlite3 tutorial.db
    SQLite version 3.3.6
    Enter ".help" for instructions
    sqlite> .tables
    addresses  users
    sqlite> .help
    .databases             List names and files of attached databases
    .dump ?TABLE? ...      Dump the database in an SQL text format
    .echo ON|OFF           Turn command echo on or off
    .exit                  Exit this program
    ...

    sqlite> .schema addresses
    CREATE TABLE addresses (
       id INTEGER NOT NULL,
       email_address VARCHAR NOT NULL,
       user_id INTEGER,
       PRIMARY KEY (id),
       FOREIGN KEY(user_id) REFERENCES users (id)
    );



Implementation Notes
~~~~~~~~~~~~~~~~~~~~~~~~

Try adopting SA split model layout promulgated at 
 
   * http://docs.pylonsproject.org/projects/pyramid_cookbook/dev/sqla.html
   * http://blogs.symora.com/nmishra/2010/02/28/configure-pylons-with-sqlalchemy-and-separate-files-for-models/

With motivation:

#. keep model classes in separate files   

---

Function Documentation

def NonDbi::cfg_	(		sect,
			path = "~/.my.cnf"
	)

Provide a dict of config paramertes in section `sect`

Definition at line 182 of file __init__.py.

                                   :
    """
    Provide a dict of config paramertes in section `sect`
    """ 
    import os
    from ConfigParser import ConfigParser
    cfp = ConfigParser()
    cfp.read( map(lambda _:os.path.expanduser(_), [path] ))
    cfg = dict(cfp.items(sect))
    return cfg  

def NonDbi::dj_init_	(		dbconf = "tmp_offline_db",
			djapp = "NonDbi.dj.dataset"
	)

Check Django compatibility by trying to use it to talk to the SQLAlchemy generated model

Definition at line 193 of file __init__.py.

                                                                     :
    """
    Check Django compatibility by trying to use it to talk to the SQLAlchemy generated model
    """ 
    from django.conf import settings
    cnf = cfg_(dbconf)
    djc = dict(DATABASE_ENGINE='mysql',DATABASE_HOST=cnf['host'],DATABASE_NAME=cnf['database'],DATABASE_USER=cnf['user'],DATABASE_PASSWORD=cnf['password'])
    djc['INSTALLED_APPS'] = ( djapp, )
    djc['DEBUG'] = True
    settings.configure( **djc)
    from django.core.management import call_command 
    call_command('syncdb')

def NonDbi::engine_	(		dbconf = "tmp_offline_db",
			echo = False
	)

Creates SQLAlchemy engine for `dbconf`, usage::

    from NonDbi import engine_
    engine = engine_("tmp_offline_db")
    print engine.table_names()

Definition at line 210 of file __init__.py.

                                                  :
    """
    Creates SQLAlchemy engine for `dbconf`, usage::

        from NonDbi import engine_
        engine = engine_("tmp_offline_db")
        print engine.table_names()

    """
    from sqlalchemy import create_engine
    url = "mysql://%(user)s:%(password)s@%(host)s/%(database)s" % cfg_(dbconf)
    engine = create_engine( url , echo=echo )
    return engine 

def NonDbi::session_	(		dbconf = "tmp_offline_db",
			echo = False,
			drop_all = False,
			drop_some = [],
			create = False
	)

Creates SQLAlchemy connection to DB and drops and/or creates 
all tables from the active declarative models 
Returns ``session`` through which DB can be queries or updates  

:param dbconf:  section in :file:`~/.my.cnf` with DB connection parameters
:param echo:  emit the SQL commands being performed
:param drop_all:  drop all active NonDbi tables **CAUTION: ALL TABLES**
:param drop_some: drop tables corresponding to listed mapped classes 
:param create:  create all tables if not existing 

SQLAlchemy innards are managed in the ``meta`` module 

Definition at line 224 of file __init__.py.

                                                                                                :
    """
    Creates SQLAlchemy connection to DB and drops and/or creates 
    all tables from the active declarative models 
    Returns ``session`` through which DB can be queries or updates  

    :param dbconf:  section in :file:`~/.my.cnf` with DB connection parameters
    :param echo:  emit the SQL commands being performed
    :param drop_all:  drop all active NonDbi tables **CAUTION: ALL TABLES**
    :param drop_some: drop tables corresponding to listed mapped classes 
    :param create:  create all tables if not existing 

    SQLAlchemy innards are managed in the ``meta`` module 


    """ 
    engine = engine_( dbconf )
    meta.engine = engine
    meta.Session.configure( bind=engine )
    metadata = meta.Base.metadata

    if drop_all:
        metadata.drop_all(engine)   ## drop all tables if they do not exist 
    if drop_some:
        for cls in drop_some:
            cls.__table__.drop(bind=engine,checkfirst=True)
    if create:
        metadata.create_all(engine)        # create tables if they do not exist 

    def Session_dbikls_( self, tn ):        # metadata+engine are bound into this closure 
        pt = Table( tn      , metadata, autoload=True, autoload_with=engine )
        vt = Table( tn+'Vld', metadata, autoload=True, autoload_with=engine )
        pv = join(  pt , vt , pt.c.SEQNO == vt.c.SEQNO )
        Kls = type( 'Y'+tn ,(object,),{})    # dynamic class creation
        mapper( Kls , pv , properties={ 'SEQNO':[pt.c.SEQNO, vt.c.SEQNO] })
        return Kls

    def Session_kls_( self, tn , primary_key=None ):        # metadata+engine are bound into this closure 
        t = Table( tn      , metadata, autoload=True, autoload_with=engine )
        Kls = type( 'Y'+tn ,(object,),{})    # dynamic class creation
        if primary_key:
            log.warn("special handling for delinquent tables with no PK defined")        
            assert type(primary_key) in (tuple,list), "unexpected type for primary_key" 
            pks = map(lambda _:getattr(t.c,_), primary_key )
            mapper( Kls , t , primary_key=pks )
        else:
            mapper( Kls , t )

        return Kls

    session = meta.Session()
    import new
    session.__class__.dbikls_ = new.instancemethod( Session_dbikls_, session , session.__class__ )
    session.__class__.kls_    = new.instancemethod( Session_kls_, session , session.__class__ )
    return session 

---

Variable Documentation

tuple NonDbi::log = logging.getLogger(__name__)

Definition at line 161 of file __init__.py.

tuple NonDbi::Session = sessionmaker()

Definition at line 171 of file __init__.py.