ORM Tutorial

This tutorial guides you through the process of creating Python objects which are directly linked to a database table

Dependencies

SQLAlchemy



In [1]:

    
#Change to base project directory and load main ORM package
import os
os.chdir("..")
from ORM.Mappers import orm, Cancer, CancerSynonyms

Create a cancer and synonym objects

See Cancer.py in ORM.Mappers for object definitions



In [2]:

    
c1 = Cancer(name="melanoma")
s1 = CancerSynonyms(synonym="skin cancer")

Next, link the synonym to cancer



In [3]:

    
c1.synonyms.append(s1)

#Alternatively, could also link from synonym to cancer: s1.cancer = c1

Note that s1 now has its cancer field populated



In [4]:

    
#Cancer object
s1.cancer

#Can be used as any ordinary Python object
print "Cancer for synonym 1: %s" % s1.cancer.name









    



Cancer for synonym 1: melanoma

Create another synonym for melanoma



In [5]:

    
s2 = CancerSynonyms(synonym="another synonym", cancer=c1)

Note that c1 now has an additional synonym



In [6]:

    
for synonym in c1.synonyms:
    print synonym.synonym









    



skin cancer
another synonym

Automagically create Cancer and Cancer Synonym object-relations



In [7]:

    
orm.Base.metadata.create_all()









    



2014-08-27 20:26:57,775 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    



INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    



2014-08-27 20:26:57,776 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-27 20:26:57,777 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    



INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    



2014-08-27 20:26:57,777 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-27 20:26:57,779 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("drug")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("drug")






    



2014-08-27 20:26:57,779 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-27 20:26:57,780 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("cancer")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("cancer")






    



2014-08-27 20:26:57,781 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-27 20:26:57,781 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("drugsynonyms")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("drugsynonyms")






    



2014-08-27 20:26:57,782 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-27 20:26:57,782 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("drugbank")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("drugbank")






    



2014-08-27 20:26:57,783 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-27 20:26:57,783 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("cancersynonyms")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("cancersynonyms")






    



2014-08-27 20:26:57,784 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()

Create a SQLAlchemy Session (a connection to a database)

We interact with the database through a session. We can add rows, query, and perform all the normal database operations through a session.



In [8]:

    
session = orm.loadSession()

Adding instances (rows) to a table requires two steps: adding to a session and committing



In [9]:

    
session.add(c1)
session.commit()









    



2014-08-26 14:39:29,612 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)






    



INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)






    



2014-08-26 14:39:29,613 INFO sqlalchemy.engine.base.Engine INSERT INTO cancer (created_at, name) VALUES (CURRENT_TIMESTAMP, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancer (created_at, name) VALUES (CURRENT_TIMESTAMP, ?)






    



2014-08-26 14:39:29,614 INFO sqlalchemy.engine.base.Engine ('melanoma',)






    



INFO:sqlalchemy.engine.base.Engine:('melanoma',)






    



2014-08-26 14:39:29,616 INFO sqlalchemy.engine.base.Engine INSERT INTO cancersynonyms (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancersynonyms (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



2014-08-26 14:39:29,617 INFO sqlalchemy.engine.base.Engine ('skin cancer', 1)






    



INFO:sqlalchemy.engine.base.Engine:('skin cancer', 1)






    



2014-08-26 14:39:29,618 INFO sqlalchemy.engine.base.Engine INSERT INTO cancersynonyms (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancersynonyms (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



2014-08-26 14:39:29,619 INFO sqlalchemy.engine.base.Engine ('another synonym', 1)






    



INFO:sqlalchemy.engine.base.Engine:('another synonym', 1)






    



2014-08-26 14:39:29,620 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT

Note that we only need to add and commit the Cancer object c1 -- the synonym objects it is linked to will automatically be stored in the Cancer Synonyms table

Query the cancer table



In [10]:

    
cancers = session.query(Cancer).all()









    



2014-08-26 14:40:14,088 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)






    



INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)






    



2014-08-26 14:40:14,089 INFO sqlalchemy.engine.base.Engine SELECT cancer.created_at AS cancer_created_at, cancer.id AS cancer_id, cancer.name AS cancer_name 
FROM cancer






    



INFO:sqlalchemy.engine.base.Engine:SELECT cancer.created_at AS cancer_created_at, cancer.id AS cancer_id, cancer.name AS cancer_name 
FROM cancer






    



2014-08-26 14:40:14,090 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()



In [11]:

    
for cancer in cancers:
    print "Cancer: %s" % cancer.name
    print "Synonyms: %s" % ','.join([syn.synonym for syn in cancer.synonyms])









    



Cancer: melanoma
2014-08-26 14:40:22,096 INFO sqlalchemy.engine.base.Engine SELECT cancersynonyms.created_at AS cancersynonyms_created_at, cancersynonyms.id AS cancersynonyms_id, cancersynonyms.synonym AS cancersynonyms_synonym, cancersynonyms.cancer_id AS cancersynonyms_cancer_id 
FROM cancersynonyms 
WHERE ? = cancersynonyms.cancer_id






    



INFO:sqlalchemy.engine.base.Engine:SELECT cancersynonyms.created_at AS cancersynonyms_created_at, cancersynonyms.id AS cancersynonyms_id, cancersynonyms.synonym AS cancersynonyms_synonym, cancersynonyms.cancer_id AS cancersynonyms_cancer_id 
FROM cancersynonyms 
WHERE ? = cancersynonyms.cancer_id






    



2014-08-26 14:40:22,096 INFO sqlalchemy.engine.base.Engine (1,)






    



INFO:sqlalchemy.engine.base.Engine:(1,)






    



Synonyms: skin cancer,another synonym

Query the CancerSynonyms table



In [12]:

    
synonyms = session.query(CancerSynonyms).all()

for synonym in synonyms:
    print synonym.cancer.name, synonym.synonym









    



2014-08-26 14:40:39,721 INFO sqlalchemy.engine.base.Engine SELECT cancersynonyms.created_at AS cancersynonyms_created_at, cancersynonyms.id AS cancersynonyms_id, cancersynonyms.synonym AS cancersynonyms_synonym, cancersynonyms.cancer_id AS cancersynonyms_cancer_id 
FROM cancersynonyms






    



INFO:sqlalchemy.engine.base.Engine:SELECT cancersynonyms.created_at AS cancersynonyms_created_at, cancersynonyms.id AS cancersynonyms_id, cancersynonyms.synonym AS cancersynonyms_synonym, cancersynonyms.cancer_id AS cancersynonyms_cancer_id 
FROM cancersynonyms






    



2014-08-26 14:40:39,722 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



melanoma skin cancer
melanoma another synonym



In [ ]: