{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": 23,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-23-51515430e9dc>, line 2)",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-23-51515430e9dc>\"\u001b[0;36m, line \u001b[0;32m2\u001b[0m\n\u001b[0;31m    person_sql ='(select count(MALE)  from person where sex = 'MALE' union all select count(FEMALE)  from person where sex = 'FEMALEMALE')'\u001b[0m\n\u001b[0m                                                                  ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ],
     "output_type": "error"
    }
   ],
   "source": [
    "#  # 查询person表\n",
    "person_sql ='(select count(MALE)  from person where sex = 'MALE' union all select count(FEMALE)  from person where sex = 'FEMALEMALE')'\n",
    "person_detail=pd.read_sql(sql=person_sql,con=db)\n",
    "\n",
    "# 查询apply表\n",
    "# apply_sql = 'select applicant, status from apply'\n",
    "# apply_detail = pd.read_sql(sql=apply_sql, con=db)\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": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# 获取当前借款人是否有OVERDUE状态的进件（通过代码实现）\n",
    "def get_od_apply(person_id):\n",
    "    # 通过apply_detail拿到'applicant','status'两列的值\n",
    "    apply_list_1 = apply_detail.values\n",
    "    apply_list = apply_list_1.tolist()\n",
    "    # 遍历apply_list,找到person_id，如果有OVERDUE状态进件则返回True，否则返回False\n",
    "    for i in apply_list:\n",
    "        if i[0] == person_id:\n",
    "            if i[1] == 'OVERDUE':\n",
    "                return True\n",
    "            else:\n",
    "                continue\n",
    "        if i[0] > person_id:\n",
    "            return False\n",
    "    \n",
    "print(get_od_apply(20000001))    # False\n",
    "print(get_od_apply(20000035))    # True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODU：获取当前借款人是否有OVERDUE状态的进件（通过sql语句实现）\n",
    "\n",
    "# sql查询用户id为20000035的用户是否有OVERDUE状态的进件\n",
    "#sql1 = select applicant,status from apply where applicant =20000035 and status = 'OVERDUE'   \n",
    "# status1 = \n",
    "# print(stauts1) \n",
    "\n",
    "\n",
    "# # sql查询用户id为20000001的用户是否有OVERDUE状态的进件\n",
    "# sql2 = select applicant,status from apply where applicant =20000001 and status = 'OVERDUE'\n",
    "# stauts2 = \n",
    "# print(stauts2)    # 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务2：\n",
    "1.根据给定金融借款数据，分析出在已知借款人中，男女的具体比例。根据查询结果，画出饼图进行展示。<br>\n",
    "2.查询各种状态进件所占百分比，画出饼图进行展示。<br>\n",
    "3.根据进件状态，分析出男人逾期的概率大还是女人逾期的概率大。<br>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODU:查询男、女各占比例,并绘制成饼图\n",
    "# 从person_info中获取'FEMALE','MALE'的人数\n",
    "value = \n",
    "# 绘制饼图\n",
    "plt.pie()\n",
    "plt.show()"
   ]
  }
 ],
 "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
}