{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    " # TODO：连接数据库\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "# 打开数据库连接\n",
    "db = pymysql.connect(host='localhost',\n",
    "                     user='root',\n",
    "                     password='root',\n",
    "                     db='test_db',\n",
    "                     charset='utf8mb4',\n",
    "                     cursorclass=pymysql.cursors.DictCursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>applicant</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20000001</td>\n",
       "      <td>REPAID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20000001</td>\n",
       "      <td>REPAID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20000001</td>\n",
       "      <td>REPAID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20000002</td>\n",
       "      <td>RETURNING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20000002</td>\n",
       "      <td>IN_PROGREESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20000002</td>\n",
       "      <td>REPAID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20000003</td>\n",
       "      <td>REPAID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20000003</td>\n",
       "      <td>IN_PROGREESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20000004</td>\n",
       "      <td>IN_PROGREESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20000004</td>\n",
       "      <td>REPAID</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   applicant        status\n",
       "0   20000001        REPAID\n",
       "1   20000001        REPAID\n",
       "2   20000001        REPAID\n",
       "3   20000002     RETURNING\n",
       "4   20000002  IN_PROGREESS\n",
       "5   20000002        REPAID\n",
       "6   20000003        REPAID\n",
       "7   20000003  IN_PROGREESS\n",
       "8   20000004  IN_PROGREESS\n",
       "9   20000004        REPAID"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  # 查询person表\n",
    "# person_sql =\n",
    "# person_detail=pd.read_sql(sql=person_sql,con=db)\n",
    "\n",
    "# 查询apply表\n",
    "apply_sql = 'select applicant, status from apply limit 10'\n",
    "apply_detail = pd.read_sql(sql=apply_sql, con=db)\n",
    "apply_detail\n",
    "\n",
    "\n",
    "\n",
    "# # 查询call表\n",
    "# call_sql =\n",
    "# call_detail=pd.read_sql(sql=call_sql,con=db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " ## 任务1：查询某个用户是否有处于OVERDUE状态的进件\n",
    " 方法1: 通过代码查询 <br> 方法2: 通过sql语句查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndentationError",
     "evalue": "unexpected indent (<ipython-input-21-fe8ee5f1b5a0>, line 6)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-21-fe8ee5f1b5a0>\"\u001b[0;36m, line \u001b[0;32m6\u001b[0m\n\u001b[0;31m    for p in apply_list：\u001b[0m\n\u001b[0m    ^\u001b[0m\n\u001b[0;31mIndentationError\u001b[0m\u001b[0;31m:\u001b[0m unexpected indent\n"
     ]
    }
   ],
   "source": [
    "# 获取当前借款人是否有OVERDUE状态的进件（通过代码实现）\n",
    "def get_od_apply(person_id):\n",
    "    # 通过apply_detail拿到'applicant','status'两列的值\n",
    "   \n",
    "    apply_list = apply_detail\n",
    "        for p in apply_list：\n",
    "            if p == persion_id:\n",
    "                print(app_list[p])\n",
    "    # 遍历apply_list,找到person_id，如果有OVERDUE状态进件则返回True，否则返回False\n",
    "\n",
    "print(get_od_apply(20000001))    # False\n",
    "# print(get_od_apply(20000035))    # True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}