{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import pymysql\n",
    "\n",
    "def read_data(filename,re_id):\n",
    "    detail = {}\n",
    "    with open(filename) as f:\n",
    "        reader = csv.reader(f)\n",
    "        header_row =next(reader)\n",
    "        for row in reader:\n",
    "            detail.setdefault(row[0],)\n",
    "            detail[row[0]].append((re_id,row[0],row[2]))\n",
    "    return detail\n",
    "\n",
    "per_id = 1\n",
    "item_id = 1\n",
    "re_date = '2020-07-07'\n",
    "db = pymysql.connect(\"localhost\",\"songyi\",\"yylzs\",\"mydata\" )\n",
    "cursor = db.cursor()\n",
    "filename = '户外跑步数据.csv'\n",
    "sql = \"select id from sports_record where re_date=%s and item_id =%s and person_id =%s\"\n",
    "cursor.execute(sql, (re_date,item_id,per_id))\n",
    "result = cursor.fetchone()\n",
    "if result:\n",
    "    print('记录已经存在！')\n",
    "else:\n",
    "    sql = 'insert into sports_record (re_date,item_id,person_id) values(%s,%s,%s)'\n",
    "    cursor.execute(sql,(re_date,item_id,per_id))\n",
    "    db.commit()\n",
    "    re_id = cursor.lastrowid;\n",
    "    print(re_id)\n",
    "    detail = read_data(filename,re_id)\n",
    "    sql = \"insert into sports_detail (rec_id_id,target_id_id,value) values(%s,%s,%s)\"\n",
    "    try:\n",
    "        cursor.executemany(sql,detail)\n",
    "        db.commit()\n",
    "        print(\"ok!\")\n",
    "    except:\n",
    "       # 如果发生错误则回滚\n",
    "       db.rollback()   \n",
    "db.close()\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "choice = input('记录已存在，是否覆盖？（y/n）')\n",
    "if choice.upper() == \"Y\":\n",
    "    print('记录已更新')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import pymysql\n",
    "def read_data(filename,re_id):\n",
    "    detail = []\n",
    "    with open(filename) as f:\n",
    "        reader = csv.reader(f)\n",
    "        header_row =next(reader)\n",
    "        for row in reader:\n",
    "            detail.append((re_id,row[0],row[2]))\n",
    "    return detail\n",
    "    \n",
    "    \n",
    "per_id = 1\n",
    "item_id = 1\n",
    "re_date = '2020-06-28'\n",
    "db = pymysql.connect(\"localhost\",\"songyi\",\"yylzs\",\"mydata\" )\n",
    "cursor = db.cursor()\n",
    "filename = '户外跑步数据.csv'\n",
    "re_id = 19;\n",
    "detail = read_data(filename,re_id)\n",
    "print(detail)\n",
    "       \n",
    "db.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ok!\n"
     ]
    }
   ],
   "source": [
    "import openpyxl\n",
    "import pymysql\n",
    "import json\n",
    "\n",
    "db = pymysql.connect(\"81.68.135.145\",\"colab\",\"songyi\",\"gaokao\" )\n",
    "cursor = db.cursor()\n",
    "sql = 'select code from college';\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchall()\n",
    "college = []\n",
    "for result in results:\n",
    "    college.append(result[0])\n",
    "wb = openpyxl.load_workbook('./data/2017-2019.xlsx')\n",
    "#sheet = wb.active\n",
    "sheets = wb.sheetnames\n",
    "new_col = []\n",
    "dict1 = {}\n",
    "new_code = []\n",
    "for m in sheets:\n",
    "    sheet = wb[m]\n",
    "    \n",
    "    \n",
    "    for n in range(4,sheet.max_row):\n",
    "        col_code = sheet.cell(n,1).value\n",
    "        \n",
    "        if col_code not in college and col_code not in new_code: \n",
    "            m_year = []\n",
    "            dict2 = {}\n",
    "            #dict2.setdefault('nian',[])\n",
    "            new_code.append(col_code)            \n",
    "            dict2['name'] =  sheet.cell(n,2).value\n",
    "            dict2['nian'] =  []\n",
    "            #m_year.append(m[0:4])\n",
    "            #dict2['nian'][] = (m[0:4])\n",
    "            dict1[col_code] = dict2\n",
    "    for m_code in dict1.keys():\n",
    "        if m[0:4] not in dict1[m_code]['nian']:\n",
    "            dict1[m_code]['nian'].append(m[0:4])\n",
    "    \n",
    "filename = './data/2020年未招生学校.json'\n",
    "with open(filename,'w') as fl:\n",
    "    json.dump(dict1, fl,ensure_ascii=False)\n",
    "            \n",
    "            \n",
    "            \n",
    "#            new_col.append((col_code,sheet.cell(n,2).value,m[0:4]))\n",
    "           # print(col_code)\n",
    "\n",
    "print('ok!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 导入2020年投档录取信息\n",
    "import pymysql\n",
    "import json\n",
    "db = pymysql.connect(\"81.68.135.145\",\"colab\",\"songyi\",\"gaokao\" )\n",
    "cursor = db.cursor()\n",
    "sql = \"select * from digao_2020\"\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchall()\n",
    "college = []\n",
    "m_adm = []\n",
    "for result in results:\n",
    "    bm_col = result[1][0:4]\n",
    "    bm_adm = result[2][0:2]\n",
    "    name_adm = result[2][2:]\n",
    "    if bm_col not in college:\n",
    "        college.append(bm_col)        \n",
    "        \n",
    "    m_adm.append((bm_col,bm_adm,result[3],result[4],result[5],result[6],result[7],'2020'))   \n",
    "sql = \"insert into admission (college,speciality,plan,plan_dispense,num_dispense,num_min,rank_min,nian) values(%s,%s,%s,%s,%s,%s,%s,%s)\"\n",
    "try:\n",
    "    cursor.executemany(sql,m_adm)\n",
    "    db.commit()\n",
    "    print(\"ok!\")\n",
    "except:\n",
    "    # 如果发生错误则回滚\n",
    "    print(\"error!\")\n",
    "    db.rollback()   \n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 导入2017-2019年投档录取信息\n",
    "import pymysql\n",
    "import json\n",
    "db = pymysql.connect(\"81.68.135.145\",\"colab\",\"songyi\",\"gaokao\" )\n",
    "cursor = db.cursor()\n",
    "sql = \"select * from digao_1719\"\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchall()\n",
    "college = []\n",
    "m_adm = []\n",
    "for result in results:\n",
    "    bm_col = result[1][0:4]\n",
    "    bm_adm = result[2][0:2]\n",
    "    name_adm = result[2][2:]\n",
    "    if bm_col not in college:\n",
    "        college.append(bm_col)        \n",
    "        \n",
    "    m_adm.append((bm_col,bm_adm,result[3],result[4],result[5],result[6],result[7],'2020'))   \n",
    "sql = \"insert into admission (college,speciality,plan,plan_dispense,num_dispense,num_min,rank_min,nian) values(%s,%s,%s,%s,%s,%s,%s,%s)\"\n",
    "try:\n",
    "    cursor.executemany(sql,m_adm)\n",
    "    db.commit()\n",
    "    print(\"ok!\")\n",
    "except:\n",
    "    # 如果发生错误则回滚\n",
    "    print(\"error!\")\n",
    "    db.rollback()   \n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "import json\n",
    "\n",
    "for i in range(1,23):\n",
    "    fl_name = '识别结果'\n",
    "wb = openpyxl.load_workbook('./data/2017-2019.xlsx')\n",
    "#sheet = wb.active\n",
    "sheets = wb.sheetnames\n",
    "new_col = []\n",
    "dict1 = {}\n",
    "new_code = []\n",
    "for m in sheets:\n",
    "    sheet = wb[m]\n",
    "    \n",
    "    \n",
    "    for n in range(4,sheet.max_row):\n",
    "        col_code = sheet.cell(n,1).value\n",
    "        \n",
    "        if col_code not in college and col_code not in new_code: \n",
    "            m_year = []\n",
    "            dict2 = {}\n",
    "            #dict2.setdefault('nian',[])\n",
    "            new_code.append(col_code)            \n",
    "            dict2['name'] =  sheet.cell(n,2).value\n",
    "            dict2['nian'] =  []\n",
    "            #m_year.append(m[0:4])\n",
    "            #dict2['nian'][] = (m[0:4])\n",
    "            dict1[col_code] = dict2\n",
    "    for m_code in dict1.keys():\n",
    "        if m[0:4] not in dict1[m_code]['nian']:\n",
    "            dict1[m_code]['nian'].append(m[0:4])\n",
    "    \n",
    "filename = './data/2020年未招生学校.json'\n",
    "with open(filename,'w') as fl:\n",
    "    json.dump(dict1, fl,ensure_ascii=False)\n",
    "            \n",
    "            \n",
    "            \n",
    "#            new_col.append((col_code,sheet.cell(n,2).value,m[0:4]))\n",
    "           # print(col_code)\n",
    "\n",
    "print('ok!')"
   ]
  },
  {
   "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.8.5"
  },
  "toc-autonumbering": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}