<jupyter_start><jupyter_text>CFChemDb Demo* https://www.nih-cfde.org/In this simple CFDE-IDG demo we pull CFChemDb data from AWS EC2 cloud instance, for display, analysis and integration with related entities and datasets.<jupyter_code>!pip install psycopg2-binary
import sys,os,json,base64,io,requests,urllib.request
import pandas as pd
import psycopg2,psycopg2.extras
from PIL import Image as PIL_Image
from IPython.display import Image as IPython_Image
from IPython.core.display import display, HTML<jupyter_output>Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Collecting psycopg2-binary
  Downloading psycopg2_binary-2.9.3-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.0 MB)
[K     |████████████████████████████████| 3.0 MB 5.8 MB/s 
[?25hInstalling collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.9.3<jupyter_text>Database credentials<jupyter_code>dsn = (f"host='unmtid-dbs.net' port='5442' dbname='cfchemdb' user='commoner' password='<PASSWORD>'")
dbcon = psycopg2.connect(dsn)
dbcon.cursor_factory = psycopg2.extras.DictCursor<jupyter_output><empty_output><jupyter_text>Tables and row-counts<jupyter_code>tables = pd.read_sql(f"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'", dbcon)
rowcounts=None;
for tname in tables.table_name:
  df_this = pd.read_sql(f"SELECT COUNT(*) AS rowcount FROM {tname}", dbcon)
  df_this["table"] = tname
  rowcounts = df_this if rowcounts is None else pd.concat([rowcounts, df_this])
rowcounts = rowcounts[["table", "rowcount"]]
rowcounts<jupyter_output><empty_output><jupyter_text>Columns<jupyter_code>columns=None;
for tname in tables.table_name:
  sql2 = (f"SELECT column_name,data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '{tname}'")
  df_this = pd.read_sql(sql2, dbcon)
  df_this["table"] = tname
  columns = df_this if columns is None else pd.concat([columns, df_this])
columns = columns[["table", "column_name", "data_type"]]
columns<jupyter_output><empty_output><jupyter_text>Drug from DrugCentral<jupyter_code>drug = pd.read_sql("SELECT * FROM drugcentral dc WHERE name = 'ranitidine'", dbcon)
drug.transpose()<jupyter_output><empty_output><jupyter_text>Depict chemical structure via MOL2IMG from UNM.<jupyter_code>url = f"https://datascience.unm.edu/tomcat/biocomp/mol2img?smiles={urllib.request.quote(drug['smiles'][0])}"
IPython_Image(requests.get(url).content)<jupyter_output><empty_output><jupyter_text>Properties from properties table<jupyter_code>drug_props = pd.read_sql(f"SELECT * FROM properties p WHERE mol_id = {drug['mol_id'][0]}", dbcon)
drug_props.transpose()<jupyter_output><empty_output><jupyter_text>Similarity search joining LINCS and DrugCentral<jupyter_code>results = pd.read_sql("""SELECT
	mols.id,
	mols.cansmi AS smiles,
	drugcentral.id AS dc_id,
	drugcentral.name AS drugname,
	lincs.pert_name AS lincs_name,
	lincs.sig_count,
	ROUND(tanimoto_sml(rdkit_fp(mol_from_smiles('NCCc1ccc(O)c(O)c1'::cstring)), mols.fp)::NUMERIC, 2) similarity
FROM
	mols
JOIN
        drugcentral ON (drugcentral.mol_id = mols.id)
JOIN
	lincs ON (lincs.mol_id = mols.id)
WHERE
	rdkit_fp(mol_from_smiles('NCCc1ccc(O)c(O)c1'::cstring))%mols.fp
ORDER BY
	similarity DESC""", dbcon)
results
for i,smi in enumerate(results["smiles"]):
  url = f"https://datascience.unm.edu/tomcat/biocomp/mol2img?smiles={urllib.request.quote(smi)}&w=200&h=160"
  img = PIL_Image.open(io.BytesIO(requests.get(url).content))
  w,h = img.size
  img_buffer = io.BytesIO()
  img.save(img_buffer, format="PNG")
  imgStr = base64.b64encode(img_buffer.getvalue()).decode('utf8')
  results.loc[i,'img'] = f"""<img width="{w}" height="{h}" src="data:image/png;base64,{imgStr}">"""
html_all = results[["id", "drugname", "img", "similarity"]].to_html(escape=False)
display(HTML(html_all))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14336917562724014, 'fraction_numerical': 0.01894521249359959, 'mean_word_length': 7.493478260869566, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '534221', 'license': 'CC0-1.0', 'n_tokens_mistral': 1583, 'n_tokens_neox': 1501, 'n_words': 259, 'path': 'notebooks/CFChemDb_Demo.ipynb', 'repo_name': 'unmtransinfo/CFChemDb', 'size': '3904'}