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

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

ChEMBL 29 Released

  We are pleased to announce the release of ChEMBL 29. This version of the database, prepared on 01/07/2021 contains: 2,703,543 compound records 2,105,464 compounds (of which 2,084,724 have mol files) 18,635,916 activities 1,383,553 assays 14,554 targets 81,544 documents Data can be downloaded from the ChEMBL FTP site:   https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/releases/chembl_29 .  Please see ChEMBL_29 release notes for full details of all changes in this release: https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/releases/chembl_29/chembl_29_release_notes.txt New Deposited Datasets EUbOPEN Chemogenomic Library (src_id = 55, ChEMBL Document IDs CHEMBL4649982-CHEMBL4649998): 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 synthesiz

Julia meets RDKit

Julia is a young programming language that is getting some traction in the scientific community. It is a dynamically typed, memory safe and high performance JIT compiled language that was designed to replace languages such as Matlab, R and Python. We've been keeping an an eye on it for a while but we were missing something... yes, RDKit! Fortunately, Greg very recently added the MinimalLib CFFI interface to the RDKit repertoire. This is nothing else than a C API that makes it very easy to call RDKit from almost any programming language. More information about the MinimalLib is available directly from the source . The existence of this MinimalLib CFFI interface meant that we no longer had an excuse to not give it a go! First, we added a BinaryBuilder recipe for building RDKit's MinimalLib into Julia's Yggdrasil repository (thanks Mosè for reviewing!). The recipe builds and automatically uploads the library to Julia's general package registry. The build currently targe

Identifying relevant compounds in patents

  As you may know, patents can be inherently noisy documents which can make it challenging to extract drug discovery information from them, such as the key targets or compounds being claimed. There are many reasons for this, ranging from deliberate obfuscation through to the long and detailed nature of the documents. For example, a typical small molecule patent may contain extensive background information relating to the target biology and disease area, chemical synthesis information, biological assay protocols and pharmacological measurements (which may refer to endogenous substances, existing therapies, reaction intermediates, reagents and reference compounds), in addition to description of the claimed compounds themselves.  The SureChEMBL system extracts this chemical information from patent documents through recognition of chemical names, conversion of images and extraction of attached files, and allows patents to be searched for chemical structures of interest. However, the curren

New Drug Warnings Browser

As mentioned in the announcement post of  ChEMBL 29 , a new Drug Warnings Browser has been created. This is an updated version of the entity browsers in ChEMBL ( Compounds , Targets , Activities , etc). It contains new features that will be tried out with the Drug Warnings and will be applied to the other entities gradually. The new features of the Drug Warnings Browser are described below. More visible buttons to link to other entities This functionality is already available in the old entity browsers, but the button to use it is not easily recognised. In the new version, the buttons are more visible. By using those buttons, users can see the related activities, compounds, drugs, mechanisms of action and drug indications to the drug warnings selected. The page will take users to the corresponding entity browser with the items related to the ones selected, or to all the items in the dataset if the user didn’t select any. Additionally, the process of creating the join query is no