GET TID and PREF_NAME from CHEMBL

I want to retrieve relationship between TID and PREF_NAME in specific case from ChEMBL DB.
SQL query is following.

COPY (
  2         SELECT  DISTINCT TID, PREF_NAME FROM ACTIVITIES
  3                       JOIN ASSAYS USING (ASSAY_ID)
  4                       JOIN TARGET_DICTIONARY USING (TID)
  5                       WHERE STANDARD_TYPE = 'Ki'
  6                       AND STANDARD_VALUE IS NOT NULL
  7                       AND STANDARD_RELATION = '='
  8                        )
  9         TO '/path/td.csv'
 10         ( FORMAT CSV )

I ran the sql and got result like ….

1,Maltase-glucoamylase
3,Phosphodiesterase 5A
6,Dihydrofolate reductase
7,Dihydrofolate reductase
8,Tyrosine-protein kinase ABL
9,Epidermal growth factor receptor erbB1
11,Thrombin
12,Plasminogen
13,Beta-lactamase TEM
14,Adenosine deaminase
15,Carbonic anhydrase II
19,Estrogen receptor alpha
21,Neuraminidase
23,Plasma kallikrein
24,HMG-CoA reductase
25,Glucocorticoid receptor
28,Thymidylate synthase
30,Aldehyde dehydrogenase
35,Insulin receptor
36,Progesterone receptor
41,Alcohol dehydrogenase alpha c

🙂

 

Advertisements

SQL query for CHEMBL21

It was just memorandum for my self.
PostgreSql can save sql result as csv using COPY( sql statement ) TO ‘filelocation/filename’
And ‘\i file.sql’ command can execute SQL from file.
https://www.postgresql.org/docs/9.1/static/app-psql.html

COPY( SELECT
             AC.DOC_ID,
             AC.MOLREGNO,
             ST.MOLREGNO,
             ST.CANONICAL_SMILES,
             CP.QED_WEIGHTED,
             CP.ALOGP,
             CP.MOLECULAR_SPECIES,
             AC.STANDARD_TYPE,
             AC.STANDARD_RELATION,
             AC.STANDARD_VALUE,
             AC.STANDARD_UNITS,
             TD.PREF_NAME,
             BS.SITE_NAME,
             PCS.PROTEIN_CLASS_SYNONYM,
             PCS.SYN_TYPE
      FROM ASSAYS AS A,
           ACTIVITIES AS AC,
           COMPOUND_STRUCTURES AS ST,
           COMPOUND_PROPERTIES AS CP,
           TARGET_DICTIONARY AS TD,
           BINDING_SITES AS BS,
           PROTEIN_CLASS_SYNONYMS AS PCS,
           COMPONENT_CLASS AS CC,
           COMPONENT_SEQUENCES AS CS,
           TARGET_COMPONENTS AS TC
      WHERE A.ASSAY_ID=AC.ASSAY_ID
            AND ST.MOLREGNO=AC.MOLREGNO
            AND TD.TID = A.TID
            AND TD.TID = BS.TID
            AND TD.TID = TC.TID
            AND ST.MOLREGNO = CP.MOLREGNO
            AND TC.COMPONENT_ID = CS.COMPONENT_ID
            AND CC.COMPONENT_ID = CS.COMPONENT_ID
            AND CC.PROTEIN_CLASS_ID = PCS.PROTEIN_CLASS_ID
            AND CONFIDENCE_SCORE >= 9
            AND ASSAY_TYPE='B'
            AND AC.STANDARD_RELATION='='
            AND AC.STANDARD_TYPE = 'IC50'
            AND AC.STANDARD_UNITS='nM'
            AND TD.TARGET_TYPE='SINGLE PROTEIN'
            AND CP.MW_FREEBASE <= 600 )
  TO '/Users/iwatobipen/chembldata/chembl.csv' DELIMITER ',';