In [6]:

    

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.objects import *
import datetime as dt


    

In [2]:

    

# in-memory SQLite, where echo causes the SQL spam sent to server.
engine = create_engine('sqlite:///:memory:', echo=True)


    

In [3]:

    

# explicit call to create tables
Base.metadata.create_all(engine)


    

    




2017-08-08 13:45:14,131 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-08-08 13:45:14,132 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,133 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-08-08 13:45:14,133 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,135 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("run")
2017-08-08 13:45:14,135 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,136 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("library")
2017-08-08 13:45:14,137 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,137 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("sample")
2017-08-08 13:45:14,138 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,139 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("pool_library")
2017-08-08 13:45:14,139 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,140 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("run_pool")
2017-08-08 13:45:14,140 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,141 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("pool")
2017-08-08 13:45:14,142 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,143 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE run (
	id INTEGER NOT NULL, 
	facility VARCHAR, 
	machine_type VARCHAR, 
	cell_type VARCHAR, 
	run_type VARCHAR, 
	run_date DATE, 
	data_path VARCHAR, 
	PRIMARY KEY (id)
)


2017-08-08 13:45:14,144 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,146 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-08 13:45:14,147 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE sample (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	organism VARCHAR, 
	collection_date DATE, 
	geo_loc_name VARCHAR, 
	strain VARCHAR, 
	isolate VARCHAR, 
	host VARCHAR, 
	isolation_source VARCHAR, 
	sample_type VARCHAR, 
	PRIMARY KEY (id)
)


2017-08-08 13:45:14,147 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,148 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-08 13:45:14,149 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE pool (
	id INTEGER NOT NULL, 
	molarity FLOAT, 
	PRIMARY KEY (id)
)


2017-08-08 13:45:14,150 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,150 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-08 13:45:14,152 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE library (
	id INTEGER NOT NULL, 
	barcode VARCHAR, 
	creation_date DATE, 
	status VARCHAR, 
	tray VARCHAR, 
	well VARCHAR, 
	ba_conc FLOAT, 
	nano_count INTEGER, 
	sample_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(sample_id) REFERENCES sample (id)
)


2017-08-08 13:45:14,153 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,155 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-08 13:45:14,156 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE run_pool (
	run_id INTEGER, 
	pool_id INTEGER, 
	FOREIGN KEY(run_id) REFERENCES run (id), 
	FOREIGN KEY(pool_id) REFERENCES pool (id)
)


2017-08-08 13:45:14,157 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,158 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-08 13:45:14,162 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE pool_library (
	pool_id INTEGER, 
	library_id INTEGER, 
	FOREIGN KEY(pool_id) REFERENCES pool (id), 
	FOREIGN KEY(library_id) REFERENCES library (id)
)


2017-08-08 13:45:14,162 INFO sqlalchemy.engine.base.Engine ()
2017-08-08 13:45:14,164 INFO sqlalchemy.engine.base.Engine COMMIT

In [5]:

    

# create a Microbe sample

mc = Microbe(name='test', organism='E.coli', collection_date=dt.datetime.now(), geo_loc_name='Sydney', \
             strain='k12', isolation_source='someones butt', sample_type='cell culture')


    

In [7]:

    

# create a session with dbrms
Session = sessionmaker(bind=engine)
s = Session()


    

In [8]:

    

# persist microbe
s.add(mc)


    

In [10]:

    

# load from store
mc2 = s.query(Microbe).first()


    

    




2017-08-08 13:53:39,482 INFO sqlalchemy.engine.base.Engine SELECT sample.id AS sample_id, sample.name AS sample_name, sample.organism AS sample_organism, sample.collection_date AS sample_collection_date, sample.geo_loc_name AS sample_geo_loc_name, sample.strain AS sample_strain, sample.isolate AS sample_isolate, sample.host AS sample_host, sample.isolation_source AS sample_isolation_source, sample.sample_type AS sample_sample_type 
FROM sample
 LIMIT ? OFFSET ?
2017-08-08 13:53:39,483 INFO sqlalchemy.engine.base.Engine (1, 0)

In [11]:

    

# compare state
mc2 == mc


    

    
Out[11]:





True

In [12]:

    

# need more objects to make it interesting. In the meantine, checkout querying docs
# http://docs.sqlalchemy.org/en/rel_1_1/orm/tutorial.html#querying


    

In [14]:

    

mc.libraries


    

    




2017-08-08 13:55:37,549 INFO sqlalchemy.engine.base.Engine SELECT library.id AS library_id, library.barcode AS library_barcode, library.creation_date AS library_creation_date, library.status AS library_status, library.tray AS library_tray, library.well AS library_well, library.ba_conc AS library_ba_conc, library.nano_count AS library_nano_count, library.sample_id AS library_sample_id 
FROM library 
WHERE ? = library.sample_id
2017-08-08 13:55:37,550 INFO sqlalchemy.engine.base.Engine (1,)






    
Out[14]:





[]

In [ ]: