Untitled.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[frozenset({1}),\n",
       " frozenset({2}),\n",
       " frozenset({5}),\n",
       " frozenset({4}),\n",
       " frozenset({3})]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataSet = [[1,2,5],[2,4],[2,3],[1,2,4],[1,3],[2,3],[1,3],[1,2,3,5],[1,2,3]]\n",
    "C1 = []\n",
    "for transaction in dataSet:\n",
    "    for item in transaction:\n",
    "        if not [item] in C1:\n",
    "            C1.append([item])\n",
    "list(map(frozenset,C1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**遍历目录下的所有文件**\n",
    "> 注意此处的数据潜在根据名称规定了顺序，在后续存入数据库以及规则提取时，不能改变前后顺序，因为不同学科是有顺序关系的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data15秋所有学生成绩明细（补考后）2016.2.25.xlsx\n",
      "data16春补考后上学期所有课程成绩明细，供班主任使用.xlsx\n",
      "data16秋学年秋季学期补考后学生个人成绩明细2017.2.23.xlsx\n",
      "data17春信息工程学院学生各科成绩.xls\n",
      "data18秋信息18秋成绩（最高）.xlsx\n",
      "data19秋学生成绩-信息按学生排序.xlsx\n",
      "data~$18秋信息18秋成绩（最高）.xlsx\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "def eachFile(filepath):\n",
    "    pathDir =  os.listdir(filepath)\n",
    "    for allDir in pathDir:\n",
    "        child = os.path.join('%s%s' % (filepath, allDir))\n",
    "        print (child)\n",
    "eachFile('data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**对单个文件进行处理**\n",
    "> 需要统计出不同学生各门课程的成绩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\Anaconda3\\lib\\site-packages\\openpyxl\\workbook\\names\\named_range.py:124: UserWarning: Discarded range with reserved name\n",
      "  warnings.warn(\"Discarded range with reserved name\")\n"
     ]
    }
   ],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "workbook = load_workbook(u'data/18秋信息18秋成绩（最高）.xlsx')    #找到需要xlsx文件的位置\n",
    "booksheet = workbook.active                 #获取当前活跃的sheet,默认是第一个sheet\n",
    "#获取sheet页的行数据\n",
    "rows = booksheet.rows\n",
    "#获取sheet页的列数据\n",
    "columns = booksheet.columns\n",
    "\n",
    "i = 0\n",
    "table = []\n",
    "# 迭代所有的行\n",
    "for row in rows:\n",
    "    i = i + 1\n",
    "    line = [col.value for col in row]\n",
    "    table.append(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>学号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "      <th>年级</th>\n",
       "      <th>学生系所</th>\n",
       "      <th>专业</th>\n",
       "      <th>学生校区</th>\n",
       "      <th>考生特征</th>\n",
       "      <th>开课系所</th>\n",
       "      <th>课程号</th>\n",
       "      <th>...</th>\n",
       "      <th>成绩录入人姓名</th>\n",
       "      <th>任课教师号</th>\n",
       "      <th>任课教师姓名</th>\n",
       "      <th>课程班所在校区</th>\n",
       "      <th>考核方式</th>\n",
       "      <th>最近一次异动</th>\n",
       "      <th>最近一次异动时间</th>\n",
       "      <th>是否主修</th>\n",
       "      <th>教学计划学年学期</th>\n",
       "      <th>成绩录入分组类型与权重</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>160974</td>\n",
       "      <td>赵子良</td>\n",
       "      <td>测161</td>\n",
       "      <td>2016</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>测控技术与仪器专业</td>\n",
       "      <td>主校区</td>\n",
       "      <td>无</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>03C1174</td>\n",
       "      <td>...</td>\n",
       "      <td>盛沙</td>\n",
       "      <td>0020040666</td>\n",
       "      <td>盛沙</td>\n",
       "      <td>主校区</td>\n",
       "      <td>考试</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>主修</td>\n",
       "      <td>3秋(2018)</td>\n",
       "      <td>百分制[石油化工字母记分制]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160850</td>\n",
       "      <td>安鑫</td>\n",
       "      <td>通162</td>\n",
       "      <td>2016</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>通信工程</td>\n",
       "      <td>主校区</td>\n",
       "      <td>无</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>COM03101</td>\n",
       "      <td>...</td>\n",
       "      <td>张宁</td>\n",
       "      <td>0020010425</td>\n",
       "      <td>张宁</td>\n",
       "      <td>主校区</td>\n",
       "      <td>未确定</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>主修</td>\n",
       "      <td>3秋(2018)</td>\n",
       "      <td>百分制</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017310882</td>\n",
       "      <td>陈瑶英</td>\n",
       "      <td>通172</td>\n",
       "      <td>2017</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>通信工程</td>\n",
       "      <td>主校区</td>\n",
       "      <td>无</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>EEE03102</td>\n",
       "      <td>...</td>\n",
       "      <td>林建安</td>\n",
       "      <td>0020030635</td>\n",
       "      <td>林建安</td>\n",
       "      <td>主校区</td>\n",
       "      <td>未确定</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>主修</td>\n",
       "      <td>2秋(2018)</td>\n",
       "      <td>百分制[石油化工字母记分制]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>160850</td>\n",
       "      <td>安鑫</td>\n",
       "      <td>通162</td>\n",
       "      <td>2016</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>通信工程</td>\n",
       "      <td>主校区</td>\n",
       "      <td>无</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>COM03102</td>\n",
       "      <td>...</td>\n",
       "      <td>张克</td>\n",
       "      <td>0019930235</td>\n",
       "      <td>张克</td>\n",
       "      <td>主校区</td>\n",
       "      <td>未确定</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>主修</td>\n",
       "      <td>3秋(2018)</td>\n",
       "      <td>百分制</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>150933</td>\n",
       "      <td>郭家伟</td>\n",
       "      <td>测151</td>\n",
       "      <td>2015</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>测控技术与仪器专业</td>\n",
       "      <td>康庄校区</td>\n",
       "      <td>无</td>\n",
       "      <td>信息工程学院</td>\n",
       "      <td>EEE03122</td>\n",
       "      <td>...</td>\n",
       "      <td>纪文刚</td>\n",
       "      <td>0019990387</td>\n",
       "      <td>纪文刚</td>\n",
       "      <td>康庄校区</td>\n",
       "      <td>未确定</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>主修</td>\n",
       "      <td>4秋(2018)</td>\n",
       "      <td>百分制</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           学号   姓名    班级    年级    学生系所         专业  学生校区 考生特征    开课系所  \\\n",
       "0      160974  赵子良  测161  2016  信息工程学院  测控技术与仪器专业   主校区    无  信息工程学院   \n",
       "1      160850   安鑫  通162  2016  信息工程学院       通信工程   主校区    无  信息工程学院   \n",
       "2  2017310882  陈瑶英  通172  2017  信息工程学院       通信工程   主校区    无  信息工程学院   \n",
       "3      160850   安鑫  通162  2016  信息工程学院       通信工程   主校区    无  信息工程学院   \n",
       "4      150933  郭家伟  测151  2015  信息工程学院  测控技术与仪器专业  康庄校区    无  信息工程学院   \n",
       "\n",
       "        课程号       ...       成绩录入人姓名       任课教师号 任课教师姓名 课程班所在校区 考核方式 最近一次异动  \\\n",
       "0   03C1174       ...            盛沙  0020040666     盛沙     主校区   考试   None   \n",
       "1  COM03101       ...            张宁  0020010425     张宁     主校区  未确定   None   \n",
       "2  EEE03102       ...           林建安  0020030635    林建安     主校区  未确定   None   \n",
       "3  COM03102       ...            张克  0019930235     张克     主校区  未确定   None   \n",
       "4  EEE03122       ...           纪文刚  0019990387    纪文刚    康庄校区  未确定   None   \n",
       "\n",
       "  最近一次异动时间 是否主修  教学计划学年学期     成绩录入分组类型与权重  \n",
       "0     None   主修  3秋(2018)  百分制[石油化工字母记分制]  \n",
       "1     None   主修  3秋(2018)             百分制  \n",
       "2     None   主修  2秋(2018)  百分制[石油化工字母记分制]  \n",
       "3     None   主修  3秋(2018)             百分制  \n",
       "4     None   主修  4秋(2018)             百分制  \n",
       "\n",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame(table[1:],columns=table[0])\n",
    "df.head() #查看前5行"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**构建课程字典表**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('EY-MAT005', '物理与人类文明', '2.0', -1, '任选'), ('MATH101', '高等数学A(Ⅰ)', '6.0', -1, '必修'), ('08T5051', '健美', '1.0', -1, '任选'), ('CX0031', '创新学分', '3.0', -1, '任选'), ('EEE14701', '电工电子实习', '2.0', -1, '必修')]\n"
     ]
    }
   ],
   "source": [
    "def get_course_dic(course):\n",
    "    undupNum = list(set(df['课程号'].values.tolist()))\n",
    "    result = []\n",
    "    for num in undupNum:\n",
    "        course_info = df[df['课程号']==num]\n",
    "        course_name = course_info['课程名'].tolist()[0]\n",
    "        hour = -1\n",
    "        credit = course_info['学分'].tolist()[0]\n",
    "        if '学时' in course.columns.tolist():\n",
    "            hour = course_info['学时'].tolist()[0]\n",
    "        attribute = course_info['选课属性'].tolist()[0]\n",
    "        result.append(tuple([num,course_name,credit,hour,attribute]))\n",
    "    return result\n",
    "\n",
    "import pymysql\n",
    "import traceback\n",
    "class MysqlOp():\n",
    "    def __init__(self):\n",
    "        self.host = 'localhost'\n",
    "        self.user = 'root'\n",
    "        self.password = ''\n",
    "        self.conn = pymysql.connect(host=self.host,user=self.user,password=self.password)\n",
    "        self.cursor = self.conn.cursor()\n",
    "        self.conn.select_db('EWS')\n",
    "    \n",
    "    # 采用executemany批量插入极大的提高效率\n",
    "    def insert_course(self,result): \n",
    "        # 注意result为元组列表\n",
    "        sql = \"insert into CourseNum(CNNum,CNName,CNCredit,CNHour,CNAttribute) value(%s,%s,%s,%s,%s)\"\n",
    "        try:\n",
    "            self.cursor.executemany(sql,result)\n",
    "            print(\"数据插入成功\")\n",
    "        except:\n",
    "            self.conn.rollback()\n",
    "            traceback.print_exc()\n",
    "        finally:\n",
    "            self.cursor.close()\n",
    "\n",
    "# list(set(df['课程号'].values.tolist()))\n",
    "# df[df['课程号']=='08T5041']['课程名'].tolist()[0]\n",
    "result = get_course_dic(df)\n",
    "print(result[:5])\n",
    "mysqlop = MysqlOp()\n",
    "# mysqlop.insert_course(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
      "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
      "      dtype='object')\n",
      "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
      "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
      "      dtype='object')\n",
      "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
      "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
      "      dtype='object')\n",
      "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
      "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
      "      dtype='object')\n",
      "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
      "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
      "      dtype='object')\n",
      "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
      "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
      "      dtype='object')\n",
      "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
      "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "mysqlop = MysqlOp()\n",
    "for name in os.listdir(\"data\"):\n",
    "    print(pd.read_excel(\"data/15秋所有学生成绩明细（补考后）2016.2.25.xlsx\",header=0).columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['学号', '姓名', '课程号', '课程名', '课程班别名', '成绩', '学分', '学时', '考核方式', '选课属性',\n",
       "       '课程类别', '学年学期', '考试性质', '院系', '专业', '年级', '学生类别', '班级'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}