Other api/nondbi

Table Of Contents

Previous topic


Next topic


This Page

Daya Bay Links

Content Skeleton



SQLAlchemy Ecosystem

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

./dybinst trunk external SQLAlchemy 

After installation many examples are available at:


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)]


Problems with multiple sessions, may need rearrangement

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

How to add a class/table

  1. follow patten of examples in movie.py and director.py
  2. import the declarative classes into __init__ of NonDbi
  3. write tests to check functionality

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 (
   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

With motivation:

  1. keep model classes in separate files
class NonDbi.MetaDB(dbconf=None)[source]

Bases: object

Create one MetaDB instance per database connection , usage:

off_ = MetaDB("tmp_offline_db")
off  = off_()                           ## call to pull up a session 

daq_ = MetaDB("tmp_daqdb")
daq = daq_()

YCableMap = off_.dbikls_("CableMap")     ## NB now on the MetaDB instance rather than the session 
print off.query(YCableMap).count()

YSTF = daq_.kls_("SFO_TZ_FILE")
print daq.query(YSTF).count()

No need to diddle with the session kls this way, although could if decide to get sugary.

The initial session_ approach has difficulties when dealing with multiple DB/sessions, multiple Session.configure causes warnings

The contortions were caused by:

  1. sharing metadata with declarative base ?
  2. having a single vehicle on which to plant API (the session)

Try again unencumbered by declarative base compatitbility and the meta module


Binding is deferred until the last moment

NonDbi.cfg_(sect, path='~/.my.cnf')[source]

Provide a dict of config paramertes in section sect

NonDbi.dj_init_(dbconf='tmp_offline_db', djapp='NonDbi.dj.dataset')[source]

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

NonDbi.engine_(dbconf='tmp_offline_db', echo=False)[source]

Creates SQLAlchemy engine for dbconf, usage:

from NonDbi import engine_
engine = engine_("tmp_offline_db")
print engine.table_names()
NonDbi.session_(dbconf='tmp_offline_db', echo=False, drop_all=False, drop_some=, []create=False)[source]

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

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

SQLAlchemy innards are managed in the meta module