In [37]:

    

import sqlite3


    

In [38]:

    

db_path = "/Users/jjpr/dev/mkgu/mkgu/lookup.db"


    

In [39]:

    

conn = sqlite3.connect(db_path)


    

In [40]:

    

conn


    

    
Out[40]:





<sqlite3.Connection at 0x104be0f10>

In [41]:

    

cursor = conn.cursor()


    

In [42]:

    

cursor


    

    
Out[42]:





<sqlite3.Cursor at 0x104adee30>

In [50]:

    

sql_drop_store = """DROP TABLE if exists store;"""
sql_create_store = """CREATE TABLE store 
    (id integer primary key, type text, location text);"""


    

In [51]:

    

sql_drop_assembly = """DROP TABLE if exists assembly;"""
sql_create_assembly = """CREATE TABLE assembly 
    (id integer primary key, name text unique, class text);"""


    

In [52]:

    

sql_drop_assembly_store = """DROP TABLE if exists assembly_store;"""
sql_create_assembly_store = """CREATE TABLE assembly_store 
    (id integer primary key, role text, 
    store_id integer, assembly_id integer);"""


    

In [59]:

    

cursor.execute(sql_drop_store)
cursor.execute(sql_create_store)


    

    
Out[59]:





<sqlite3.Cursor at 0x104adee30>

In [60]:

    

cursor.execute(sql_drop_assembly)
cursor.execute(sql_create_assembly)


    

    
Out[60]:





<sqlite3.Cursor at 0x104adee30>

In [61]:

    

cursor.execute(sql_drop_assembly_store)
cursor.execute(sql_create_assembly_store)


    

    
Out[61]:





<sqlite3.Cursor at 0x104adee30>

In [62]:

    

sql_insert_store = """INSERT INTO store 
(type, location) 
VALUES 
("S3", 
"https://mkgu-dicarlolab-hvm.s3.amazonaws.com/hvm_neuronal_features.nc")
"""


    

In [63]:

    

cursor.execute(sql_insert_store)
store_id = cursor.lastrowid
store_id


    

    
Out[63]:





1

In [64]:

    

sql_insert_assy = """INSERT INTO assembly 
(name, class) 
VALUES 
("HvM", "NeuronRecordingAssembly")
"""


    

In [65]:

    

cursor.execute(sql_insert_assy)
assy_id = cursor.lastrowid
assy_id


    

    
Out[65]:





1

In [66]:

    

sql_insert_assy_store = """INSERT INTO assembly_store 
(role, store_id, assembly_id) 
VALUES 
("HvM", ?, ?)
"""


    

In [67]:

    

cursor.execute(sql_insert_assy_store, (store_id, assy_id))
assy_store_id = cursor.lastrowid
assy_store_id


    

    
Out[67]:





1

In [68]:

    

conn.commit()


    

In [69]:

    

conn.close()


    

In [70]:

    

conn


    

    
Out[70]:





<sqlite3.Connection at 0x104be0f10>

In [71]:

    

conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()


    

In [72]:

    

sql_lookup_assy = """SELECT 
    a.id as a_id, a.name, a.class 
    FROM
    assembly a
    WHERE
    a.name = ?
    """


    

In [73]:

    

sql_get_assy = """SELECT 
    a.id as a_id, a.name, a.class, a_s.id as a_s_id, a_s.role, 
    s.id as s_id, s.type, s.location 
    FROM
    assembly a
    JOIN assembly_store a_s ON a.id = a_s.assembly_id
    JOIN store s ON a_s.store_id = s.id
    WHERE
    a.name = ?
    """


    

In [74]:

    

cursor.execute(sql_lookup_assy, ("HvM",))
assy_result = cursor.fetchone()
{k: assy_result[k] for k in assy_result.keys()}


    

    
Out[74]:





{'a_id': 1, 'class': 'NeuronRecordingAssembly', 'name': 'HvM'}

In [75]:

    

cursor.execute(sql_get_assy, ("HvM",))
assy_store_result = cursor.fetchall()
{k: r[k] for r in assy_store_result for k in r.keys()}


    

    
Out[75]:





{'a_id': 1,
 'a_s_id': 1,
 'class': 'NeuronRecordingAssembly',
 'location': 'https://mkgu-dicarlolab-hvm.s3.amazonaws.com/hvm_neuronal_features.nc',
 'name': 'HvM',
 'role': 'HvM',
 's_id': 1,
 'type': 'S3'}

In [76]:

    

class AssemblyRecord(object):
    """An AssemblyRecord stores information about the canonical location where the data
    for a DataAssembly is stored.  """
    def __init__(self, db_id, name, cls, stores={}):
        self.db_id = db_id
        self.name = name
        self.cls = cls
        self.stores = stores


class AssemblyStoreMap(object):
    """An AssemblyStoreMap links an AssemblyRecord to a Store.  """
    def __init__(self, db_id, role, store, assembly_record):
        self.db_id = db_id
        self.role = role
        self.store = store
        self.assembly_record = assembly_record


class Store(object):
    """A Store stores the location of a DataAssembly data file.  """
    def __init__(self, db_id, type, location, assemblies=[]):
        self.db_id = db_id
        self.type = type
        self.location = location
        self.assemblies = assemblies


    

In [77]:

    

assy = AssemblyRecord(assy_result["a_id"], assy_result["name"], assy_result["class"])
for r in assy_store_result:
    s = Store(r["s_id"], r["type"], r["location"], [assy])
    role = r["role"]
    a_s = AssemblyStoreMap(r["a_s_id"], role, s, assy)
    assy.stores[role] = a_s
assy


    

    
Out[77]:





<__main__.AssemblyRecord at 0x104bb6e48>

In [79]:

    

(assy.name, assy.cls)


    

    
Out[79]:





('HvM', 'NeuronRecordingAssembly')

In [80]:

    

[(s.role, s.store.type, s.store.location) for s in assy.stores.values()]


    

    
Out[80]:





[('HvM',
  'S3',
  'https://mkgu-dicarlolab-hvm.s3.amazonaws.com/hvm_neuronal_features.nc')]

In [ ]: