Django model describing ChEMBL database.

---

TL;DR: We have just open sourced our Django ORM Model, which describes the ChEMBL relational database schema. This means you no longer need to write another line of SQL code to interact with ChEMBL database. We think it is pretty cool and we are using it in the ChEMBL group to make our lives easier. Read on to find out more....

---

It is never a good idea to use SQL code directly in python. Let's see some basic examples explaining why:

	import psycopg2
	conn=psycopg2.connect("dbname='template1' user='dbuser' password='mypass'")
	cur = conn.cursor()
	cur.execute("""SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
	FROM Orders
	INNER JION Customers
	ON Orders.CustomerID=Customers.CustomerID;""")

view raw bad_sql.py hosted with ❤ by GitHub

Can you see what is wrong with the code above? SQL keyword `JOIN` was misspelled as 'JION'. But it's hard to find it quickly because most of code highlighters will apply Python syntax rules and ignore contents of strings. In our case the string is very important as it contains SQL statement.

The problem above can be easily solved using some simple Python SQL wrapper, such as edendb. This wrapper will provide set of functions to perform database operations for example 'select', 'insert', 'delete':

	import psycopg2
	import edendb
	conn=psycopg2.connect("dbname='template1' user='dbuser' password='mypass'")
	cur = conn.cursor()
	cols = ['name','age','pet']
	#insert
	db.insert('people', cols, ['paul',102,'cat'])
	#delete
	db.delete('people', 'age = 10')
	#select
	rows = db.select(['name','age'], 'people', 'age > 30', iter=True)

view raw edendb.py hosted with ❤ by GitHub

Now it's harder to make a typo in any of SQL keywords because they are exposed to python so IDE should warn you about mistake.

OK, time for something harder, can you find what's wrong here, assuming that this query is executed against chembl_19 schema:

	import psycopg2
	conn=psycopg2.connect("dbname='template1' user='dbuser' password='mypass'")
	cur = conn.cursor()
	cur.execute("""SELECT DISTINCT molecule_dictionary.chembl_id,
	molecule_synonyms.synonims
	FROM molecule_dictionary
	LEFT OUTER JOIN molecule_synonyms ON (molecule_dictionary.molregno = molecule_synonims.molregno)
	WHERE molecule_dictionary.chembl_id = 'CHEMBL1';""")

view raw schema.py hosted with ❤ by GitHub

Well, there are two errors: first of all `molecule_synonyms` table does not have a `synonims` column. The proper name is `synonyms`. Secondly, there is table name typo  `molecule_synonyms`.

This kind of error is even harder to find because we are dealing with python and SQL code that is syntactically correct. The problem is semantic and in order to find it we need to have a good understanding of the underlying data model, in this case the chembl_19 schema. But the ChEMBL database schema is fairly complicated (341 columns spread over 52 tables), are we really supposed to know it all by heart? Let's leave this rhetorical question and proceed to third example: how to query for compounds containing the substructure represented by 'O=C(Oc1ccccc1C(=O)O)C' SMILES:

For Oracle this would be:

	import cx_oracle
	conn=cx_oracle.connect("dbname='template1' user='dbuser' password='mypass'")
	cur = conn.cursor()
	cur.execute("""SELECT *
	FROM compound_mols
	INNER JOIN molecule_dictionary ON (compound_mols.molregno = molecule_dictionary.molregno)
	LEFT OUTER JOIN compound_structures ON (molecule_dictionary.molregno = compound_structures.molregno)
	INNER JOIN chembl_id_lookup ON (molecule_dictionary.chembl_id = chembl_id_lookup.chembl_id)
	LEFT OUTER JOIN compound_properties ON (molecule_dictionary.molregno = compound_properties.molregno)
	WHERE (((sss(ctab,'O=C(Oc1ccccc1C(=O)O)C','ignore=all')=1))
	AND compound_structures.molregno IS NOT NULL
	AND chembl_id_lookup.entity_type = 'COMPOUND'
	AND compound_properties.molregno IS NOT NULL);""")

view raw substructure_ora.py hosted with ❤ by GitHub

And for Postgres:

	import psycopg2
	conn=psycopg2.connect("dbname='template1' user='dbuser' password='mypass'")
	cur = conn.cursor()
	cur.execute("""SELECT *
	FROM rdk.mols
	INNER JOIN molecule_dictionary ON (rdk.mols.molregno = molecule_dictionary.molregno)
	LEFT OUTER JOIN compound_structures ON (molecule_dictionary.molregno = compound_structures.molregno)
	INNER JOIN chembl_id_lookup ON (molecule_dictionary.chembl_id = chembl_id_lookup.chembl_id)
	LEFT OUTER JOIN compound_properties ON (molecule_dictionary.molregno = compound_properties.molregno)
	WHERE (where m @> 'O=C1OC2=C(C=C1)C1=C(C=CCO1)C=C2'
	AND compound_structures.molregno IS NOT NULL
	AND chembl_id_lookup.entity_type = 'COMPOUND'
	AND compound_properties.molregno IS NOT NULL);""")

view raw substructure_pg.py hosted with ❤ by GitHub

As you can see both queries are different, reasons for these differences are:

1. Differences in Oracle and Postgres dialects
2. Different chemical cartridges (Accelrys Direct and RDKit)
3. Different names of auxiliary tables containing binary molecule objects

These queries are also more complicated than the previous examples as they require more table joins and they make calls to the chemical cartridge-specific functions.

The example substructure search queries described above are similar to those used by the ChEMBL web services, which are available on EBI servers (Oracle backend) and in the myChEMBL VM (PostgreSQL backend). Still, the web services work without any change to their code. How?

All of the problems highlighted in this blogpost can be solved by the use of a technique known as Object Relational Mapping (ORM). ORM converts every table from database (for example 'molecule_dictionary') into Python class (MoleculeDictionary). Now it's easy to create a list of all available classes in Python module (by using 'dir' function) and check all available fields in class which corresponds to columns from SQL tables. This makes database programming easier and less error prone. The ORM also allows the code to work in a database agnostic manner and explains how we use the same codebase with Oracle and PostgreSQL backends.

If this blogpost has convinced you to give the ORM approach a try, please take a look at our ChEMBL example also included in myChEMBL: