crawl_andriod.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "'''\n",
    "手机抓包\n",
    "'''\n",
    "\n",
    "import re , urllib , http.cookiejar , base64 , binascii , rsa, json, time, math, os\n",
    "import sqlite3\n",
    "from log import log\n",
    "import codecs  \n",
    "from datetime import date\n",
    "\n",
    "\n",
    "\n",
    "__version__ = '0.0.1'\n",
    "__author__ = 'Pidong Li (hope-dream@163.com)'\n",
    "cj = http.cookiejar.LWPCookieJar()\n",
    "cookie_support = urllib.request.HTTPCookieProcessor(cj)\n",
    "opener = urllib.request.build_opener(cookie_support , urllib.request.HTTPHandler)\n",
    "urllib.request.install_opener(opener)\n",
    "con = sqlite3.connect('cuiyuan.db')\n",
    "cu = con.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "headers = {\n",
    "\"User-Agent\":\" Dalvik/1.6.0 (Linux; U; Android 4.1.1; MI 2A MIUI/4.12.5)\",\n",
    "\"Host\":\" cy.zhubaotown.com\",\n",
    "\"Connection\":\" Keep-Alive\",\n",
    "\"Accept-Encoding\":\" gzip\",\n",
    "\"Content-Type\":\" application/x-www-form-urlencoded\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# login\n",
    "def get_token():\n",
    "    '''\n",
    "    发送账号和密码（加密过的），获得token\n",
    "    '''\n",
    "    para_login = 'bundleId=com.zbxz.cuiyuan&onlyCode=91078cb040f2e105465919fa0e08facd&passWord=MTIzNDU2&sysType=android&userName=13679206003&uId=16891&bundleId=com.zbxz.cuiyuan&product_type=cyretail&sysType=1'\n",
    "    url_login = 'http://cy.zhubaotown.com/index.php/user/pwdLogin/'\n",
    "    data = para_login.encode('utf-8')\n",
    "    request = urllib.request.Request(url_login , data, headers)\n",
    "    response = urllib.request.urlopen(request)\n",
    "    text = response.read().decode('utf-8')\n",
    "    text\n",
    "    token = json.loads(text)['userInfo']['token']\n",
    "    return token\n",
    "\n",
    "token = get_token()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_shop_list(page, size):\n",
    "\n",
    "    '''\n",
    "        获得商家列表，以json导出， 城市选择四会，screenRegionId=65\n",
    "    '''\n",
    "\n",
    "    para_shop_list = 'orderKeyword=desc&orderValue=sort_order&page=%d&screenRegionId=65&size=%d&uId=16891&token=%s\\\n",
    "    &bundleId=com.zbxz.cuiyuan&product_type=cyretail&sysType=1' % (page, size, token)\n",
    "    url_shop_list = 'http://cy.zhubaotown.com/index.php/shop/allShopList/'\n",
    "    data = para_shop_list.encode('utf-8')\n",
    "\n",
    "    request = urllib.request.Request(url_shop_list , data, headers)\n",
    "    try:\n",
    "        response = urllib.request.urlopen(request)\n",
    "        text = response.read().decode('utf-8')\n",
    "        \n",
    "        code = json.loads(text)['code']\n",
    "        if code == 409:\n",
    "            print('token错误，请重新检查')\n",
    "            raise SystemExit\n",
    "        if code == 414: # token出现错误需要重新检查\n",
    "            print('No matching data')\n",
    "            return 1\n",
    "        if not code == 200:\n",
    "            print('code:' + str(code) + json.loads(text)['prompt'])\n",
    "            return 0\n",
    "        out = json.loads(text)['datas']\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        out = 0;\n",
    "    return out\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "token = get_token()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 商家列表 shop_list 写入数据库，不清楚具体的商铺数目，先试10000个\n",
    "def db_shop_list(update = True):\n",
    "    table_name = 'shop_list'\n",
    "    if update:\n",
    "        table_name = 'shop_list_new'\n",
    "    cu.execute('create table if not exists %s(shopId int, shopLogo varchar(255), shopMaster varchar(100), \\\n",
    "    shopName varchar(255), shopSn varchar(255), shopTotalGoods int)' %table_name)\n",
    "    con.commit()\n",
    "    shops = []\n",
    "    print('正在更新商铺列表')\n",
    "    for ii in range(100):\n",
    "        try:\n",
    "            print('商铺列表第%d页/每页100家……' % (ii+1), end = '\\r')\n",
    "            shops = get_shop_list(ii+1, 100)\n",
    "            if shops == 1:\n",
    "                print('商铺列表加载完成……')\n",
    "                break\n",
    "            if not shops == 0:\n",
    "                for shop in shops:\n",
    "                    cu.execute(r\"insert into %s values(?, ?, ?, ?, ?, ?)\" % table_name, (shop['shopId'], shop['shopLogo'], shop['shopMaster'], shop['shopName'], shop['shopSn'], shop['shopTotalGoods']))\n",
    "                con.commit()\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "            con.rollback()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_shop_list_num(update = True):\n",
    "    table_name = 'shop_list'\n",
    "    if update:\n",
    "        table_name = 'shop_list_new'\n",
    "    cu.execute('select distinct shopId, shopTotalGoods from %s' % table_name)\n",
    "    shop_ids_nums = cu.fetchall()\n",
    "    return shop_ids_nums # 包含 商铺的id和拥有物品的数目"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 根据商铺数据，得到物品的id，这是一页的\n",
    "def shop2goods_id_page(page, shop_id, size):\n",
    "    para_goods_id = 'goodsNew=0&isRecommended=0&page=%d&shopId=%d&size=%s&uId=16891&token=%s\\\n",
    "    &bundleId=com.zbxz.cuiyuan&product_type=cyretail&sysType=1' % (page ,shop_id,  size, token)\n",
    "    url_goods_id = 'http://cy.zhubaotown.com/index.php/shop/shopGoodsList_new/'\n",
    "    data = para_goods_id.encode('utf-8')\n",
    "    try:\n",
    "        request = urllib.request.Request(url_goods_id , data, headers)\n",
    "        response = urllib.request.urlopen(request)\n",
    "        text = response.read().decode('utf-8')\n",
    "        \n",
    "        code = json.loads(text)['code']\n",
    "        if code == 409:\n",
    "            print('token错误，请重新检查')\n",
    "            raise SystemExit\n",
    "\n",
    "        if not code == 200:\n",
    "            print('code:' + str(code) + json.loads(text)['prompt'])\n",
    "            return 0\n",
    "        xx = json.loads(text)['datas']\n",
    "    except Exception as e:\n",
    "        xx = 0\n",
    "    return(xx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 根据商铺数据，得到全部物品的id，\n",
    "def shop2goods_id(shop_id, shopTotalGoods, size = 100):\n",
    "# 获得商家的物品\n",
    "    goods = []\n",
    "    if shopTotalGoods>100 :\n",
    "        for page in range(math.floor(shopTotalGoods/size)+1):\n",
    "            \n",
    "            xx = shop2goods_id_page(page + 1, shop_id, size)\n",
    "            goods = goods + xx\n",
    "    elif 0< shopTotalGoods <101:\n",
    "        xx = shop2goods_id_page(1, shop_id, shopTotalGoods)\n",
    "        goods = goods + xx\n",
    "    else:\n",
    "        print('chucuole')\n",
    "    return goods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "shop2goods_id(1160, 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# shop_ids2goods_ids 写入数据库\n",
    "#新建数据库 shop2goods\n",
    "def db_shop2goods(update = True):\n",
    "    table_name = 'shop2goods'\n",
    "    if update:\n",
    "        table_name = 'shop2goods_new'\n",
    "\n",
    "    cu.execute('create table if not exists %s (shop_id int , goods_id int, date varchar(100))' % table_name)\n",
    "    con.commit()\n",
    "    index = 0\n",
    "    shop_ids_nums = get_shop_list_num(update)\n",
    "#     print(len(shop_ids_nums))\n",
    "    print('正在更新商家-物品的联系')\n",
    "    for shop_ids_num in shop_ids_nums:\n",
    "        index = index+1\n",
    "        \n",
    "        print('已完成' + str(round(index/len(shop_ids_nums)*100, 3)) + '%,请耐心等待……', end = '\\r')\n",
    "        try:\n",
    "        \n",
    "            goods_list = shop2goods_id(shop_ids_num[0], shop_ids_num[1])\n",
    "            for goods in goods_list:\n",
    "                cu.execute(\"insert into %s values(%d, %d, '%s')\" %(table_name, shop_ids_num[0], goods['goodsId'], goods['add_time']))\n",
    "            con.commit()\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "            con.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 获取 goods_info\n",
    "def get_goods_info(goods_id):\n",
    "    '''\n",
    "    通过goods_id获取内容\n",
    "    '''\n",
    "    goods_id = str(goods_id)\n",
    "    para_goods_info = 'goodsId=%s&uId=16891&token=%s&bundleId=com.zbxz.cuiyuan&product_type=cyretail&sysType=1' % (goods_id, token)\n",
    "    url_goods_info = 'http://cy.zhubaotown.com/index.php/goods/getGoodsInfo/'\n",
    "    data = para_goods_info.encode('utf-8')\n",
    "    try:\n",
    "        request = urllib.request.Request(url_goods_info , data, headers)\n",
    "        response = urllib.request.urlopen(request)\n",
    "        text = response.read().decode('utf-8')\n",
    "        code = json.loads(text)['code']\n",
    "        if code == 409:\n",
    "            print('token错误，请重新检查')\n",
    "            raise SystemExit\n",
    "\n",
    "        if not code == 200:\n",
    "#             print('code:' + str(code) + json.loads(text)['prompt'])\n",
    "            return 0\n",
    "        xx = json.loads(text)['goodsInfo']\n",
    "        \n",
    "    except Exception as e:\n",
    "#         print(e)\n",
    "        xx = 0\n",
    "    return(xx)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 删除物品数据,重新建表\n",
    "def db_manipulate():\n",
    "#     cu.execute('drop table if exists goods_info')\n",
    "#     cu.execute('drop table if exists goods_img_url')\n",
    "    # 物品信息写入数据库,21列\n",
    "    # 创建数据库\n",
    "    cu.execute('create table if not exists goods_info (goodsId varchar(20), appointmentedStatus varchar(20), \\\n",
    "                goodsInfo varchar(200), shopId varchar(20), title varchar(255),\\\n",
    "                 goodsMaterialId varchar(20), goodsSn varchar(20), goodsTypeId varchar(20),\\\n",
    "                has_cert varchar(20),isAppointmented varchar(20), isConcern varchar(20), isRecommended varchar(20),isimplant varchar(20), orderStatus varchar(20),\\\n",
    "                price real, priceRangeName varchar(255), quantity varchar(20),\\\n",
    "                goodPattern varchar(100), goodPatternId varchar(20), goodsCateId int, goodsCateName varchar(100),\\\n",
    "                img_str text, img_str_1 text)')\n",
    "    cu.execute('create table if not exists goods_info_add (goodsId varchar(20), appointmentedStatus varchar(20), \\\n",
    "                goodsInfo varchar(200), shopId varchar(20), title varchar(255),\\\n",
    "                 goodsMaterialId varchar(20), goodsSn varchar(20), goodsTypeId varchar(20),\\\n",
    "                has_cert varchar(20),isAppointmented varchar(20), isConcern varchar(20), isRecommended varchar(20),isimplant varchar(20), orderStatus varchar(20),\\\n",
    "                price real, priceRangeName varchar(255), quantity varchar(20),\\\n",
    "                goodPattern varchar(100), goodPatternId varchar(20), goodsCateId int, goodsCateName varchar(100),\\\n",
    "                img_str text, img_str_1 text)')\n",
    "    cu.execute('create table if not exists goods_info_remove (goodsId varchar(20), appointmentedStatus varchar(20), \\\n",
    "                goodsInfo varchar(200), shopId varchar(20), title varchar(255),\\\n",
    "                 goodsMaterialId varchar(20), goodsSn varchar(20), goodsTypeId varchar(20),\\\n",
    "                has_cert varchar(20),isAppointmented varchar(20), isConcern varchar(20), isRecommended varchar(20),isimplant varchar(20), orderStatus varchar(20),\\\n",
    "                price real, priceRangeName varchar(255), quantity varchar(20),\\\n",
    "                goodPattern varchar(100), goodPatternId varchar(20), goodsCateId int, goodsCateName varchar(100))')\n",
    "    cu.execute('create table if not exists goods_img_url (goods_id int, img_url text)')\n",
    "\n",
    "    con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db_manipulate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 图片url写入数据库\n",
    "\n",
    "def get_img_url(goods_id, img_arr):\n",
    "    con.commit()\n",
    "    img_str = ''\n",
    "    img_str_1 = ''\n",
    "    index = 0\n",
    "    # 54bcde343ac35:1:0:|;55f13ae611bf1:1:1:|;55f13f9b3254c:1:2:|;\n",
    "    for img in img_arr:\n",
    "        \n",
    "        path, file_name = os.path.split(img['imgUrl'])\n",
    "        cu.execute('insert into goods_img_url values(?, ?)', (goods_id, img['imgUrl']) )\n",
    "        \n",
    "        img_str = img_str + r'<P><IMG src=\"file:///C:/图片/%s\"></P>' % (file_name)\n",
    "        img_str_1 = img_str_1 + os.path.splitext(file_name)[0] + ':1:' + str(index) + ':|;'\n",
    "        index += 1\n",
    "        \n",
    "    return img_str, img_str_1\n",
    "        \n",
    "\n",
    "\n",
    "\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "logger = log('cuiyuan.log', 'goods_info')\n",
    "def db_goods_info(goods_ids, status = None, append = False):\n",
    "    index = 0\n",
    "    goods_ids_len = len(goods_ids)\n",
    "    if append:\n",
    "        cu.execute('select distinct goodsId from goods_info')\n",
    "        goods_id_remain = cu.fetchall()\n",
    "        goods_id_remain = tuple2list(goods_id_remain)\n",
    "        #取差集\n",
    "        goods_ids = list(set(goods_ids).difference(set(goods_id_remain)))\n",
    "        index = len(goods_id_remain)\n",
    "        \n",
    "    print('正在更新物品信息…………')\n",
    "    for goods_id in goods_ids:\n",
    "        index = index +1;\n",
    "        print(r'已完成 '+ str( round(index/goods_ids_len*100,3)) + '%, 请耐心等待', end = '\\r')\n",
    "        try:\n",
    "            goods_info = get_goods_info(goods_id)\n",
    "            if not goods_info == 0:\n",
    "                if status is None or status == 'add':\n",
    "#                     pass\n",
    "                    img_str, img_str_1 = get_img_url(goods_id, goods_info['goodsImgArr'])\n",
    "                    cu.execute(\"insert into goods_info values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?, ?)\" , \\\n",
    "                               (goods_info['goodsId'], goods_info['appointmentedStatus'], goods_info['goodsInfo'], goods_info['shopId'], \\\n",
    "                                goods_info['title'], goods_info['goodsMaterialId']\\\n",
    "                               , goods_info['goodsSn'], goods_info['goodsTypeId'], goods_info['has_cert'], goods_info['isAppointmented'], goods_info['isConcern']\\\n",
    "                               , goods_info['isRecommended'], goods_info['isimplant'], goods_info['orderStatus'], goods_info['price'], goods_info['priceRangeName'], goods_info['quantity'], goods_info['goodPattern']\\\n",
    "                               , goods_info['goodPatternId'], goods_info['goodsCateId'], goods_info['goodsCateName'], img_str, img_str_1))\n",
    "\n",
    "                    con.commit()\n",
    "                if status == 'add':\n",
    "                    cu.execute(\"insert into goods_info_add values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?, ?)\" , \\\n",
    "                               (goods_info['goodsId'], goods_info['appointmentedStatus'], goods_info['goodsInfo'], goods_info['shopId'], \\\n",
    "                                goods_info['title'], goods_info['goodsMaterialId']\\\n",
    "                               , goods_info['goodsSn'], goods_info['goodsTypeId'], goods_info['has_cert'], goods_info['isAppointmented'], goods_info['isConcern']\\\n",
    "                               , goods_info['isRecommended'], goods_info['isimplant'], goods_info['orderStatus'], goods_info['price'], goods_info['priceRangeName'], goods_info['quantity'], goods_info['goodPattern']\\\n",
    "                               , goods_info['goodPatternId'], goods_info['goodsCateId'], goods_info['goodsCateName'], img_str, img_str_1))\n",
    "\n",
    "                    con.commit()\n",
    "                if status == 'remove':\n",
    "                    cu.execute(\"insert into goods_info_remove values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?, ?)\" , \\\n",
    "                               (goods_info['goodsId'], goods_info['appointmentedStatus'], goods_info['goodsInfo'], goods_info['shopId'], \\\n",
    "                                goods_info['title'], goods_info['goodsMaterialId']\\\n",
    "                               , goods_info['goodsSn'], goods_info['goodsTypeId'], goods_info['has_cert'], goods_info['isAppointmented'], goods_info['isConcern']\\\n",
    "                               , goods_info['isRecommended'], goods_info['isimplant'], goods_info['orderStatus'], goods_info['price'], goods_info['priceRangeName'], goods_info['quantity'], goods_info['goodPattern']\\\n",
    "                               , goods_info['goodPatternId'], goods_info['goodsCateId'], goods_info['goodsCateName']))\n",
    "                    cu.execute('delete from goods_info where goodsId = ' + str(goods_id))\n",
    "                    con.commit()\n",
    "     \n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "            con.rollback()\n",
    "    print('更新完成')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cu.close()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 将数据库返回值的变为list（仅是第一列）\n",
    "def tuple2list(tuple_list):\n",
    "    out = []\n",
    "    for xx in tuple_list:\n",
    "        out.append(str(xx[0]))\n",
    "    return out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 原始数据收集\n",
    "def init():\n",
    "    db_shop_list(update = False) # 获得商铺列表\n",
    "    db_shop2goods(update = False) # 获得物品列表\n",
    "    \n",
    "init()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "token = get_token()\n",
    "cu.execute('select distinct goods_id from shop2goods')\n",
    "goods_ids = tuple2list(cu.fetchall())\n",
    "db_goods_info(goods_ids, append = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def db2csv(table_name, colnames, path):\n",
    "    cu.execute('select distinct * from %s' %table_name)\n",
    "    data = cu.fetchall()\n",
    "    write_csv(data, colnames, path)\n",
    "    \n",
    "def write_csv(data, colnames, path):\n",
    "    \n",
    "    csvfile = open(path, 'wb')  \n",
    "    csvfile.write(codecs.BOM_UTF8) # 二进制写入bom解决excel打开乱码问题\n",
    "    csvfile.close()\n",
    "    csvfile = open(path, 'a', encoding = 'utf-8')  \n",
    "    writers = csv.writer(csvfile) \n",
    "    writers.writerow(colnames)\n",
    "    writers.writerows(data)\n",
    "    csvfile.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''\n",
    "更新逻辑，检查商家的数据增减情况，然后根据最新的商家信息更新物品信息，物品信息与元数据匹配，\n",
    "增加的与减少的分布建立新的数据库，同时对原数据库进行增减\n",
    "'''\n",
    "\n",
    "def update():\n",
    "    '''\n",
    "    比较商家是否增加或减少\n",
    "    '''\n",
    "    db_shop_list(update = True) # 获得最新商铺列表\n",
    "    db_shop2goods(update = True) # 获得最新物品列表\n",
    "    colnames = ['shopId', 'shopLogo' , 'shopMaster','shopName', 'shopSn', 'shopTotalGoods']\n",
    "    date_str = str(data.today())\n",
    "    cu.execute('select * from shop_list_new where shopId not in (select shopId from shop_list)')\n",
    "    shop_list_add = con.fetchall()\n",
    "    if len(shop_list_add) > 0:\n",
    "        print('商家增加了%d所，将以csv文件导出' %len(shop_list_add))\n",
    "        \n",
    "        db2csv(shop_list_add, colnames, '增加商家-%s.csv'% date_str)\n",
    "    else:\n",
    "        print('商家并未增加')\n",
    "    cu.execute('select * from shop_list where shopId not in (select shopId from shop_list_new)')\n",
    "    shop_list_remove = con.fetchall()\n",
    "    if len(shop_list_remove) > 0:\n",
    "        print('商家减少了%d所，将以csv文件导出' %len(shop_list_remove))\n",
    "        db2csv(shop_list_remove, colnames, '减少商家-%s.csv'% date_str)\n",
    "    else:\n",
    "        print('商家并没有减少')\n",
    "    \n",
    "    # 比较物品的增删\n",
    "    goods_info_add_colnames = ['shopId', 'shopLogo' , 'shopMaster','shopName', 'shopSn', 'shopTotalGoods', \\\n",
    "           'goodsId' , 'appointmentedStatus' , 'goodsInfo' , 'shopId' , 'title' , 'goodsMaterialId' ,\\\n",
    "                'goodsSn' , 'goodsTypeId' , 'has_cert' ,'isAppointmented' , 'isConcern' , \\\n",
    "                'isRecommended' ,'isimplant' , 'orderStatus' , 'price', 'priceRangeName' , 'quantity' ,\\\n",
    "                'goodPattern', 'goodPatternId' , 'goodsCateId', 'goodsCateName', 'img_str', 'img_str_1']\n",
    "    cu.execute('select goods_id from shop2goods_new where goods_id not in (select goods_id from shop2goods)')\n",
    "    goods_add = cu.fetchall()\n",
    "    if len(goods_add)>0 :\n",
    "        print('物品增加了%d个，将以csv文件导出' %len(goods_add))\n",
    "        goods_add_id = tuple2list(goods_add)\n",
    "        db_goods_info(goods_add_id, status = 'add') # 将新加入的写入 goods_info 和 goods_info_add 数据库\n",
    "        cu.execute('select * from shop_list_new inner join goods_info_add on shop_list_new.shopId = goods_info_add.shopId')\n",
    "        shop_goods_add = cu.fetchall()\n",
    "        \n",
    "        write_csv(goods_info_add, goods_info_add_colnames, '增加物品-%s.csv'% date_str )\n",
    "    else:\n",
    "        print('物品并未增加')\n",
    "    \n",
    "    cu.execute('select goods_id from shop2goods where goods_id not in (select goods_id from shop2goods_new)')\n",
    "    goods_add = cu.fetchall()\n",
    "    goods_info_remove_colnames = ['shopId', 'shopLogo' , 'shopMaster','shopName', 'shopSn', 'shopTotalGoods', \\\n",
    "           'goodsId' , 'appointmentedStatus' , 'goodsInfo' , 'shopId' , 'title' , 'goodsMaterialId' ,\\\n",
    "                'goodsSn' , 'goodsTypeId' , 'has_cert' ,'isAppointmented' , 'isConcern' , \\\n",
    "                'isRecommended' ,'isimplant' , 'orderStatus' , 'price', 'priceRangeName' , 'quantity' ,\\\n",
    "                'goodPattern', 'goodPatternId' , 'goodsCateId', 'goodsCateName']\n",
    "    if len(goods_add)<0 :\n",
    "        print('物品减少了%d个，将以csv文件导出' %len(goods_add))\n",
    "        goods_add_id = tuple2list(goods_add)\n",
    "        db_goods_info(goods_add_id, status = 'remove') # 将删除的写入 goods_info_remove 数据库，并从goods_info数据库中删除\n",
    "        cu.execute('select * from shop_list inner join goods_info_remove on shop_list.shopId = goods_info_remove.shopId')\n",
    "        shop_goods_remove = cu.fetchall()\n",
    "        \n",
    "        write_csv(goods_info_remove, goods_info_add_colname, '增加物品-%s.csv'% date_str )\n",
    "    else:\n",
    "        print('物品并未减少')\n",
    "    # 导出数据\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def db_rename():\n",
    "    '''\n",
    "    将更新的数据表的名称修改为 普通表\n",
    "    ALTER TABLE testtable RENAME TO testtable2;\n",
    "    '''\n",
    "    cu.execute('drop table shop_list')\n",
    "    cu.execute('alter table shop_list_new rename to shop_list')\n",
    "    cu.execute('drop table shop2goods')\n",
    "    cu.execute('alter table shop2goods_new rename to shop_goods')\n",
    "    cu.execute('drop table goods_info_add')\n",
    "    cu.execute('drop table goods_info_remove')\n",
    "    con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cu.close()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cu.execute('select * from goods_img_url')\n",
    "img_url_all = cu.fetchall()\n",
    "\n",
    "colnames = ['goodsId', 'goods_img_url']\n",
    "write_csv(img_url_all, colnames, '物品图片.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "cu.execute('select * from shop_list inner join goods_info on shop_list.shopId = goods_info.shopId')\n",
    "shop_goods_all = cu.fetchall()\n",
    "colnames = ['shopId', 'shopLogo' , 'shopMaster','shopName', 'shopSn', 'shopTotalGoods', \\\n",
    "           'goodsId' , 'appointmentedStatus' , 'goodsInfo' , 'shopId' , 'title' , 'goodsMaterialId' ,\\\n",
    "            'goodsSn' , 'goodsTypeId' , 'has_cert' ,'isAppointmented' , 'isConcern' , 'isRecommended' ,\\\n",
    "            'isimplant' , 'orderStatus' , 'price', 'priceRangeName' , 'quantity' , 'goodPattern', \\\n",
    "            'goodPatternId' , 'goodsCateId', 'goodsCateName', 'img_str', 'img_str_1']\n",
    "\n",
    "write_csv(shop_goods_all, colnames, '商家-物品信息.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cu.execute('select * from shop_list')\n",
    "data = cu.fetchall()\n",
    "colnames = ['shopId', 'shopLogo' , 'shopMaster','shopName', 'shopSn', 'shopTotalGoods']\n",
    "#            'goodsId' , 'appointmentedStatus' , 'goodsInfo' , 'shopId' , 'title' , 'goodsMaterialId' , 'goodsSn' , 'goodsTypeId' , 'has_cert' ,'isAppointmented' , 'isConcern' , 'isRecommended' ,'isimplant' , 'orderStatus' , 'price', 'priceRangeName' , 'quantity' , 'goodPattern', 'goodPatternId' , 'goodsCateId', 'goodsCateName']\n",
    "write_csv(data, colnames, '商家列表.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}