{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34ca5db2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/root/micromamba/envs/qsar/lib/python3.11/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html\n",
      "  from .autonotebook import tqdm as notebook_tqdm\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ChEMBL 数据库存储路径: /srv/project/LillyMol/test/data/35/data/chembl_35/chembl_35_sqlite/chembl_35.db\n"
     ]
    }
   ],
   "source": [
    "import chembl_downloader\n",
    "# 指定存储目录\n",
    "prefix = '/srv/project/LillyMol/test/data'\n",
    "version = chembl_downloader.latest() # version 35\n",
    "\n",
    "# 下载并提取 ChEMBL 数据库，存放到指定目录\n",
    "path = chembl_downloader.download_extract_sqlite(version=version, prefix=[prefix])\n",
    "# chembl_downloader.download_sqlite(version)\n",
    "\n",
    "print(f\"ChEMBL 数据库存储路径: {path}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dd6f3b25",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, 'molregno', 'BIGINT', 1, None, 0)\n",
      "(1, 'syn_type', 'VARCHAR(50)', 1, None, 0)\n",
      "(2, 'molsyn_id', 'BIGINT', 1, None, 1)\n",
      "(3, 'res_stem_id', 'BIGINT', 0, None, 0)\n",
      "(4, 'synonyms', 'VARCHAR(250)', 0, None, 0)\n"
     ]
    }
   ],
   "source": [
    "import chembl_downloader\n",
    "\n",
    "with chembl_downloader.connect(version='35', prefix=['/srv/project/LillyMol/test/data']) as conn:\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(\"PRAGMA table_info(molecule_synonyms);\")\n",
    "    columns = cursor.fetchall()\n",
    "    for col in columns:\n",
    "        print(col)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c8390d91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>molregno</th>\n",
       "      <th>syn_type</th>\n",
       "      <th>molsyn_id</th>\n",
       "      <th>res_stem_id</th>\n",
       "      <th>synonyms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27340</td>\n",
       "      <td>TRADE_NAME</td>\n",
       "      <td>3001861</td>\n",
       "      <td>None</td>\n",
       "      <td>Brolene antibiotic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27340</td>\n",
       "      <td>TRADE_NAME</td>\n",
       "      <td>3001879</td>\n",
       "      <td>None</td>\n",
       "      <td>Golden eye antibiotic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   molregno    syn_type  molsyn_id res_stem_id               synonyms\n",
       "0     27340  TRADE_NAME    3001861        None     Brolene antibiotic\n",
       "1     27340  TRADE_NAME    3001879        None  Golden eye antibiotic"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import chembl_downloader\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT *\n",
    "FROM molecule_synonyms\n",
    "WHERE syn_type='TRADE_NAME' AND synonyms LIKE '%antibiotic%'\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "# version 一般也建议加上，保证和你本地一致\n",
    "df = chembl_downloader.query(\n",
    "    sql,\n",
    "    version='35',   # 或者 version=version\n",
    "    prefix=['/srv/project/LillyMol/test/data']\n",
    ")\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0c56718",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "qsar",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}