/search.css" rel="stylesheet" type="text/css"/> /search.js">
| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

In This Package:

Namespaces | Classes | Functions | Variables
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.

00183                                    :
00184     """
00185     Provide a dict of config paramertes in section `sect`
00186     """ 
00187     import os
00188     from ConfigParser import ConfigParser
00189     cfp = ConfigParser()
00190     cfp.read( map(lambda _:os.path.expanduser(_), [path] ))
00191     cfg = dict(cfp.items(sect))
00192     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.

00194                                                                      :
00195     """
00196     Check Django compatibility by trying to use it to talk to the SQLAlchemy generated model
00197     """ 
00198     from django.conf import settings
00199     cnf = cfg_(dbconf)
00200     djc = dict(DATABASE_ENGINE='mysql',DATABASE_HOST=cnf['host'],DATABASE_NAME=cnf['database'],DATABASE_USER=cnf['user'],DATABASE_PASSWORD=cnf['password'])
00201     djc['INSTALLED_APPS'] = ( djapp, )
00202     djc['DEBUG'] = True
00203     settings.configure( **djc)
00204     from django.core.management import call_command 
00205     call_command('syncdb')
00206 
00207 
00208 
00209 

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.

00211                                                   :
00212     """
00213     Creates SQLAlchemy engine for `dbconf`, usage::
00214 
00215         from NonDbi import engine_
00216         engine = engine_("tmp_offline_db")
00217         print engine.table_names()
00218 
00219     """
00220     from sqlalchemy import create_engine
00221     url = "mysql://%(user)s:%(password)s@%(host)s/%(database)s" % cfg_(dbconf)
00222     engine = create_engine( url , echo=echo )
00223     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.

00225                                                                                                 :
00226     """
00227     Creates SQLAlchemy connection to DB and drops and/or creates 
00228     all tables from the active declarative models 
00229     Returns ``session`` through which DB can be queries or updates  
00230 
00231     :param dbconf:  section in :file:`~/.my.cnf` with DB connection parameters
00232     :param echo:  emit the SQL commands being performed
00233     :param drop_all:  drop all active NonDbi tables **CAUTION: ALL TABLES**
00234     :param drop_some: drop tables corresponding to listed mapped classes 
00235     :param create:  create all tables if not existing 
00236 
00237     SQLAlchemy innards are managed in the ``meta`` module 
00238 
00239 
00240     """ 
00241     engine = engine_( dbconf )
00242     meta.engine = engine
00243     meta.Session.configure( bind=engine )
00244     metadata = meta.Base.metadata
00245 
00246     if drop_all:
00247         metadata.drop_all(engine)   ## drop all tables if they do not exist 
00248     if drop_some:
00249         for cls in drop_some:
00250             cls.__table__.drop(bind=engine,checkfirst=True)
00251     if create:
00252         metadata.create_all(engine)        # create tables if they do not exist 
00253 
00254     def Session_dbikls_( self, tn ):        # metadata+engine are bound into this closure 
00255         pt = Table( tn      , metadata, autoload=True, autoload_with=engine )
00256         vt = Table( tn+'Vld', metadata, autoload=True, autoload_with=engine )
00257         pv = join(  pt , vt , pt.c.SEQNO == vt.c.SEQNO )
00258         Kls = type( 'Y'+tn ,(object,),{})    # dynamic class creation
00259         mapper( Kls , pv , properties={ 'SEQNO':[pt.c.SEQNO, vt.c.SEQNO] })
00260         return Kls
00261 
00262     def Session_kls_( self, tn , primary_key=None ):        # metadata+engine are bound into this closure 
00263         t = Table( tn      , metadata, autoload=True, autoload_with=engine )
00264         Kls = type( 'Y'+tn ,(object,),{})    # dynamic class creation
00265         if primary_key:
00266             log.warn("special handling for delinquent tables with no PK defined")        
00267             assert type(primary_key) in (tuple,list), "unexpected type for primary_key" 
00268             pks = map(lambda _:getattr(t.c,_), primary_key )
00269             mapper( Kls , t , primary_key=pks )
00270         else:
00271             mapper( Kls , t )
00272 
00273         return Kls
00274 
00275     session = meta.Session()
00276     import new
00277     session.__class__.dbikls_ = new.instancemethod( Session_dbikls_, session , session.__class__ )
00278     session.__class__.kls_    = new.instancemethod( Session_kls_, session , session.__class__ )
00279     return session 
00280 


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.

| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

Generated on Fri May 16 2014 09:50:09 for NonDbi by doxygen 1.7.4