<jupyter_start><jupyter_text>Use a SQL database for clinical data 🧪Demo to quickly load 400k+ drug-disease associations in a MySQL database on the DSRI with Python, and pandas.<jupyter_code># !pip install -r requirements.txt
import mysql.connector as mysql
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 could also add `DRUGBANK:` at the start of the Chemical ID to have valid CURIEs (namespace + identifier). But for some reason the apply lead Pandas to shuffle the rows, and consider the header as row! Even if the `print` shows that the header. This is a major bug in Pandas, be careful as it can easily lead to error in the data.<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 MySQL database using the database Service name (chosen when starting the database), and create the table for drug-disease associations in the default database selected by postgres<jupyter_code>try: 
    conn.close()
except:
    print('No conn to close')
conn = mysql.connect(
    host='mysql-demo',
    # host='localhost',
    database='sampledb',
    user='root',
    password='<PASSWORD>',
    autocommit=True,
    allow_local_infile=True)
cursor = conn.cursor()
cursor.execute("SET GLOBAL local_infile = 1;")
cursor.execute("SET GLOBAL max_allowed_packet=1073741824;")
cursor.execute("DROP TABLE IF EXISTS associations;")
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.It can also be done with Pandas (if the stars are properly aligned in the sky):```data.to_sql('associations', conn, if_exists='replace', index=False)```<jupyter_code># You need to use absolute path (which is ridiculous)
cursor.execute("""LOAD DATA LOCAL INFILE '/home/jovyan/dsri-demo/mined-disease-chemical-associations.csv'
    INTO TABLE associations;
""")
## Or upload row by row
# for i, row in data.iterrows():
#     print(row['Chemical'])
#     print(row['# Disease(MESH)'])
#     cursor.execute ("INSERT INTO associations (disease_id, chemical_id) VALUES ('" + row['# Disease(MESH)'] + "', '" + row['Chemical'] + "');")
#     break<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)
conn.close()<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.11692677070828332, 'fraction_numerical': 0.05714285714285714, 'mean_word_length': 4.926031294452347, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, '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': '228376', 'license': 'MIT', 'n_tokens_mistral': 1568, 'n_tokens_neox': 1374, 'n_words': 387, 'path': 'mysql.ipynb', 'repo_name': 'MaastrichtU-IDS/dsri-demo', 'size': '4164'}