Skip to main content

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:


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':


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:


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:


And for Postgres:


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:

Comments

Popular posts from this blog

UniChem 2.0

UniChem new beta interface and web services We are excited to announce that our UniChem beta site will become the default one on the 11th of May. The new system will allow us to better maintain UniChem and to bring new functionality in a more sustainable way. The current interface and web services will still be reachable for a period of time at https://www.ebi.ac.uk/unichem/legacy . In addition to it, the most popular legacy REST endpoints will also remain implemented in the new web services: https://www.ebi.ac.uk/unichem/api/docs#/Legacy Some downtime is expected during the swap.  What's new? UniChem’s current API and web application is implemented with a framework version that’s not maintained and the cost of updating it surpasses the cost of rebuilding it. In order to improve stability, security, and support the implementation and fast delivery of new features, we have decided to revamp our user-facing systems using the latest version of widely used and maintained frameworks, i

A python client for accessing ChEMBL web services

Motivation The CheMBL Web Services provide simple reliable programmatic access to the data stored in ChEMBL database. RESTful API approaches are quite easy to master in most languages but still require writing a few lines of code. Additionally, it can be a challenging task to write a nontrivial application using REST without any examples. These factors were the motivation for us to write a small client library for accessing web services from Python. Why Python? We choose this language because Python has become extremely popular (and still growing in use) in scientific applications; there are several Open Source chemical toolkits available in this language, and so the wealth of ChEMBL resources and functionality of those toolkits can be easily combined. Moreover, Python is a very web-friendly language and we wanted to show how easy complex resource acquisition can be expressed in Python. Reinventing the wheel? There are already some libraries providing access to ChEMBL d

LSH-based similarity search in MongoDB is faster than postgres cartridge.

TL;DR: In his excellent blog post , Matt Swain described the implementation of compound similarity searches in MongoDB . Unfortunately, Matt's approach had suboptimal ( polynomial ) time complexity with respect to decreasing similarity thresholds, which renders unsuitable for production environments. In this article, we improve on the method by enhancing it with Locality Sensitive Hashing algorithm, which significantly reduces query time and outperforms RDKit PostgreSQL cartridge . myChEMBL 21 - NoSQL edition    Given that NoSQL technologies applied to computational chemistry and cheminformatics are gaining traction and popularity, we decided to include a taster in future myChEMBL releases. Two especially appealing technologies are Neo4j and MongoDB . The former is a graph database and the latter is a BSON document storage. We would like to provide IPython notebook -based tutorials explaining how to use this software to deal with common cheminformatics p

ChEMBL 30 released

  We are pleased to announce the release of ChEMBL 30. This version of the database, prepared on 22/02/2022 contains: 2,786,911 compound records 2,157,379 compounds (of which 2,136,187 have mol files) 19,286,751 activities 1,458,215 assays 14,855 targets 84,092 documents Data can be downloaded from the ChEMBL FTP site: https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/releases/chembl_30/ Please see ChEMBL_30 release notes for full details of all changes in this release:  https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/releases/chembl_30/chembl_30_release_notes.txt New Deposited Datasets EUbOPEN Chemogenomic Library (src_id = 55, ChEMBL Document ID CHEMBL4689842):   The EUbOPEN consortium is an Innovative Medicines Initiative (IMI) funded project to enable and unlock biology in the open. The aims of the project are to assemble an open access chemogenomic library comprising about 5,000 well annotated compounds covering roughly 1,000 different proteins, to synthesize at least

Multi-task neural network on ChEMBL with PyTorch 1.0 and RDKit

  Update: KNIME protocol with the model available thanks to Greg Landrum. Update: New code to train the model and ONNX exported trained models available in github . The use and application of multi-task neural networks is growing rapidly in cheminformatics and drug discovery. Examples can be found in the following publications: - Deep Learning as an Opportunity in VirtualScreening - Massively Multitask Networks for Drug Discovery - Beyond the hype: deep neural networks outperform established methods using a ChEMBL bioactivity benchmark set But what is a multi-task neural network? In short, it's a kind of neural network architecture that can optimise multiple classification/regression problems at the same time while taking advantage of their shared description. This blogpost gives a great overview of their architecture. All networks in references above implement the hard parameter sharing approach. So, having a set of activities relating targets and molecules we can tra