postgresql_by_psycopg2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 一、python操作postgresql介绍"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 导入psycopg2，并创建一个connection连接：connect()<br />\n",
    "database：设置连接数据库的名称<br />\n",
    "user：用户名，可以从cmd命令行中，通过“psql molecules”进入数据库，并输入“select usename from pg_user;”查看用户名<br />\n",
    "password：密码<br />\n",
    "port：端口号，默认5432<br />\n",
    "host：宿主ip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "psycopg2.extensions.connection"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import psycopg2\n",
    "connection = psycopg2.connect(database='molecules',\n",
    "                              user='hxw',\n",
    "                              password='postgresql',\n",
    "                              port='5432',\n",
    "                              host='127.0.0.1')\n",
    "type(connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 创建一个游标cursor，在内存中开辟一块区域，用于存储数据操作的结果："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "psycopg2.extensions.cursor"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur = connection.cursor()\n",
    "type(cur)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 执行一条SQL语句测试一下，查看操作用户"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('hxw',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from current_user;\")\n",
    "reply = cur.fetchall()\n",
    "reply"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 操作失误了可以删除数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = 'drop table info'\n",
    "cur.execute(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 写一条postgresql语句，记为query，创建一个数据表，命名为info，并表添加列名，设置数据格式\n",
    "- 使用游标来执行语句：execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''create table info(id serial primary key,\n",
    "                             database text,\n",
    "                             project text,\n",
    "                             SMILES text,\n",
    "                             HA integer,\n",
    "                             HD integer,\n",
    "                             RB integer,\n",
    "                             MW float,\n",
    "                             LOGP float);'''\n",
    "cur.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 读取1000条左右的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Name</th>\n",
       "      <th>CAS #</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>human VDss (L/kg)</th>\n",
       "      <th>human CL (mL/min/kg)</th>\n",
       "      <th>fraction unbound \\nin plasma (fu)</th>\n",
       "      <th>MRT (h)</th>\n",
       "      <th>terminal  t1/2 (h)</th>\n",
       "      <th>Reference</th>\n",
       "      <th>Comments</th>\n",
       "      <th>Notes</th>\n",
       "      <th>Year of first disclosure</th>\n",
       "      <th>MW</th>\n",
       "      <th>HBA</th>\n",
       "      <th>HBD</th>\n",
       "      <th>TPSA_NO</th>\n",
       "      <th>RotBondCount</th>\n",
       "      <th>moka_ionState7.4</th>\n",
       "      <th>MoKa.LogP</th>\n",
       "      <th>MoKa.LogD7.4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>α-hANP</td>\n",
       "      <td>85637-73-6</td>\n",
       "      <td>N1[C@H](C(=O)N[C@H](C(=O)N[C@H](C(=O)N[C@H](C(...</td>\n",
       "      <td>0.2</td>\n",
       "      <td>25.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.22</td>\n",
       "      <td>Nakao, K.; Sugawara, A.; Morii, N.; Sakamoto, ...</td>\n",
       "      <td>α-Human Atrial Natriuretic Peptide. MRT calcul...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1982</td>\n",
       "      <td>3080.44</td>\n",
       "      <td>84</td>\n",
       "      <td>53</td>\n",
       "      <td>1403.4</td>\n",
       "      <td>75</td>\n",
       "      <td>cationic</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>-9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Name       CAS #                                             SMILES  \\\n",
       "0  α-hANP  85637-73-6  N1[C@H](C(=O)N[C@H](C(=O)N[C@H](C(=O)N[C@H](C(...   \n",
       "\n",
       "   human VDss (L/kg)  human CL (mL/min/kg)  fraction unbound \\nin plasma (fu)  \\\n",
       "0                0.2                  25.4                                NaN   \n",
       "\n",
       "   MRT (h)  terminal  t1/2 (h)  \\\n",
       "0     0.13                0.22   \n",
       "\n",
       "                                           Reference  \\\n",
       "0  Nakao, K.; Sugawara, A.; Morii, N.; Sakamoto, ...   \n",
       "\n",
       "                                           Comments  Notes  \\\n",
       "0  α-Human Atrial Natriuretic Peptide. MRT calcul...   NaN   \n",
       "\n",
       "   Year of first disclosure       MW  HBA  HBD  TPSA_NO  RotBondCount  \\\n",
       "0                      1982  3080.44   84   53   1403.4            75   \n",
       "\n",
       "  moka_ionState7.4  MoKa.LogP  MoKa.LogD7.4  \n",
       "0         cationic       -9.0          -9.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel('ippin.xlsx')\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 获取mol对象列表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1351"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from rdkit import Chem\n",
    "mol_list = [x for x in [Chem.MolFromSmiles(i) for i in df.SMILES] if x]\n",
    "len(mol_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 创建一个描述符计算对象，将类药五规则设置到计算器中，这部分不明白的可以我看我之前的文章"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from rdkit.ML.Descriptors import MoleculeDescriptors\n",
    "des_list = ['MolWt', 'NumHAcceptors', 'NumHDonors', 'MolLogP', 'NumRotatableBonds']\n",
    "calculator = MoleculeDescriptors.MolecularDescriptorCalculator(des_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 使用计算器，对每个分子计算所需描述符"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_list = ['%s'%(str(calculator.CalcDescriptors(mol))) for mol in mol_list]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 在query中写入一条向数据表info插入数据的语句，并执行该语句"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"insert into info (MW, HA, HD, LOGP, RB) values %s\" % (','.join(feat_list))\n",
    "cur.execute(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 通过聚合函数count(*)看一下有多少条结果\n",
    "- 通过游标获取查询结果：fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1351,)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = 'select count(*) from info'\n",
    "cur.execute(query)\n",
    "reply = cur.fetchall()\n",
    "reply"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 最后操作完后，记得提交对数据库的修改：commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 二、自动化导入脚本\n",
    "对dataframe中的分子计算类药五规则，并自动导入到数据库中。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "test for connection: hxw\n",
      "drop the info and create a new one\n",
      "using time: 2.6931540966033936\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import psycopg2\n",
    "import time\n",
    "import pandas as pd\n",
    "from rdkit import Chem\n",
    "from rdkit.ML.Descriptors import MoleculeDescriptors\n",
    "\n",
    "def import_data(df, smi_col, database, project=None, table='info', renew=False):\n",
    "    '''Import smiles and corresponding properties into the postgreSQL automatically.\n",
    "    \n",
    "    df: the DataFrame containing SMILES of the mols.\n",
    "    smi_col: the column name of the SMILES.\n",
    "    database: the PG database that want to modified.\n",
    "    project: the project that the mols belong to.\n",
    "    table: the table in the database that will be modified.\n",
    "    renew: drop the table and create a new one.\n",
    "    '''\n",
    "    # connect to database and test for connection\n",
    "    time1 = time.time()\n",
    "    connection = psycopg2.connect(database=database,\n",
    "                                  user='hxw',\n",
    "                                  password='postgresql',\n",
    "                                  port='5432',\n",
    "                                  host='127.0.0.1')\n",
    "    \n",
    "    cur = connection.cursor()\n",
    "    cur.execute(\"select * from current_user;\")\n",
    "    print('test for connection:', cur.fetchall()[0][0])\n",
    "\n",
    "    # examine whether the table exist\n",
    "    query = \"select count(*) from pg_class where relname = '%s';\"%table\n",
    "    cur.execute(query)\n",
    "    exist = cur.fetchall()[0][0]\n",
    "    \n",
    "    # renew or create a table\n",
    "    if not exist or renew:\n",
    "        if not exist:\n",
    "            print('\"%s\" not found in \"%s\" and will be created'%(table, database))\n",
    "        if renew:\n",
    "            print('drop the %s and create a new one'%table)\n",
    "            query = 'drop table %s'%table\n",
    "            cur.execute(query)\n",
    "        query = '''create table %s(id serial primary key,\n",
    "                                   database text,\n",
    "                                   project text,\n",
    "                                   SMILES text,\n",
    "                                   HA integer,\n",
    "                                   HD integer,\n",
    "                                   RB integer,\n",
    "                                   MW float,\n",
    "                                   LOGP float);'''%table\n",
    "        cur.execute(query)\n",
    "\n",
    "    # calc the descriptors\n",
    "    des_list = ['MolWt', 'NumHAcceptors', 'NumHDonors', 'MolLogP', 'NumRotatableBonds']\n",
    "    calculator = MoleculeDescriptors.MolecularDescriptorCalculator(des_list)\n",
    "    feat_all = []\n",
    "    create_var = locals()\n",
    "    for mol_smiles in df[smi_col]:\n",
    "        mol = Chem.MolFromSmiles(mol_smiles)\n",
    "        if not mol:\n",
    "            continue\n",
    "        feat_mol = []\n",
    "        for item in calculator.CalcDescriptors(mol):\n",
    "            if isinstance(item, float) or isinstance(item, int):\n",
    "                item = str(round(item, 3))\n",
    "            feat_mol.append(item)\n",
    "        feat_mol.append(\"'%s'\"%mol_smiles)\n",
    "        feat_all.append(\"(%s)\"%(','.join(feat_mol)))\n",
    "\n",
    "    # import the data into database and set the project\n",
    "    query = \"insert into %s (MW, HA, HD, LOGP, RB, SMILES) values %s\" % (table, ','.join(feat_all))\n",
    "    cur.execute(query)\n",
    "    if project:\n",
    "        query = \"update %s set project = '%s'\"%(table, project)\n",
    "        cur.execute(query)\n",
    "    connection.commit()\n",
    "    \n",
    "    time2 = time.time()\n",
    "    print('using time:', time2-time1)\n",
    "\n",
    "df = pd.read_excel('ippin.xlsx')\n",
    "import_data(df, 'SMILES', 'molecules', 'ippih', 'info', True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  },
  "toc-autonumbering": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}