<jupyter_start><jupyter_text>Use a SQL database for clinical data 🧪Demo to quickly load 400k+ drug-disease associations in a PostgreSQL database on the DSRI with Python, and pandas.<jupyter_code>import psycopg2
import pandas as pd<jupyter_output>/home/vemonet/.local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)<jupyter_text>Download the dataFrom http://snap.stanford.edu/biodata/datasets/10004/10004-DCh-Miner.html using Pandas. We also add `DRUGBANK:` at the start of the Chemical ID to have valid CURIEs (namespace + identifier)<jupyter_code>data = pd.read_csv('https://snap.stanford.edu/biodata/datasets/10004/files/DCh-Miner_miner-disease-chemical.tsv.gz', sep='\t', header=0)
data["Chemical"] = data["Chemical"].apply (lambda row: 'DRUGBANK:' + row)
print(data)
data.to_csv('mined-disease-chemical-associations.csv', index=False, header=False)<jupyter_output># Disease(MESH)          Chemical
0         MESH:D005923  DRUGBANK:DB00564
1         MESH:D009503  DRUGBANK:DB01072
2         MESH:D016115  DRUGBANK:DB01759
3         MESH:D018476  DRUGBANK:DB00451
4         MESH:C567059  DRUGBANK:DB00641
...                ...               ...
466652    MESH:C565545  DRUGBANK:DB00482
466653    MESH:D009164  DRUGBANK:DB00977
466654    MESH:D010518  DRUGBANK:DB04216
466655    MESH:D002653  DRUGBANK:DB02701
466656    MESH:D013119  DRUGBANK:DB04465

[466657 rows x 2 columns]<jupyter_text>Load the data in the databaseConnect to the PostgreSQL database, and create the table for drug-disease associations in the default database selected by postgresPostgreSQL client docs: https://www.psycopg.org/docs/usage.htmlYou can try it locally with docker (you will need to use `host='localhost',`)```docker run -it --rm -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=<PASSWORD> -e POSTGRES_DB=sampledb postgres```<jupyter_code>conn = psycopg2.connect(
   host='postgresql-demo',
   # host='localhost',
   # dbname='sampledb',
   user='postgres',
   password='<PASSWORD>')
cursor = conn.cursor()
cursor.execute("""CREATE TABLE associations(
   disease_id VARCHAR(255),
   drug_id VARCHAR(255),
   PRIMARY KEY (disease_id, drug_id)
);""")<jupyter_output><empty_output><jupyter_text>Load the CSV file in the database `associations` table:<jupyter_code>f = open(r'mined-disease-chemical-associations.csv', 'r')
cursor.copy_from(f, 'associations', sep=',')
f.close()
## Example to load a single row:
# cursor.execute("""INSERT INTO associations(disease_id, drug_id)
#     VALUES('MESH:D001523', 'DB00235')
# ;""")<jupyter_output><empty_output><jupyter_text>Query the databaseYou can now run a `SELECT` query:<jupyter_code>print('Number of associations in the database:')
cursor.execute('SELECT COUNT(*) FROM associations;')
records = cursor.fetchall()
for i in records:
    print(i)
print('\nSample of associations in the database:')
cursor.execute('SELECT disease_id, drug_id FROM associations LIMIT 3;')
records = cursor.fetchall()
for i in records:
    print(i)<jupyter_output>Number of associations in the database:
(466657,)

Sample of associations in the database:
('MESH:D005923', 'DRUGBANK:DB00564')
('MESH:D009503', 'DRUGBANK:DB01072')
('MESH:D016115', 'DRUGBANK:DB01759')
{'fraction_non_alphanumeric': 0.12115942028985507, 'fraction_numerical': 0.07188405797101449, 'mean_word_length': 5.2405063291139244, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 2, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '489599', 'license': 'MIT', 'n_tokens_mistral': 1400, 'n_tokens_neox': 1200, 'n_words': 290, 'path': 'postgresql.ipynb', 'repo_name': 'MaastrichtU-IDS/dsri-demo', 'size': '3448'}