Python11-豆瓣图书排行榜爬取.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "追风筝的人 [美] 卡勒德·胡赛尼  为你，千千万万遍 8.9\n",
      "小王子 [法] 圣埃克苏佩里  献给长成了大人的孩子们 9.0\n",
      "围城 钱锺书  对于“人艰不拆”四个字最彻底的违抗 8.9\n",
      "解忧杂货店 [日] 东野圭吾  一碗精心熬制的东野牌鸡汤，拒绝很难 8.6\n",
      "活着 余华  活着本身就是人生最大的意义 9.1\n",
      "白夜行 [日] 东野圭吾  暗夜独行的残破灵魂，爱与恶本就难分难舍 9.1\n",
      "挪威的森林 [日] 村上春树  村上之发轫，多少人的青春启蒙 8.0\n",
      "嫌疑人X的献身 [日] 东野圭吾  数学好是一种极致的浪漫 8.9\n",
      "三体 刘慈欣  你我不过都是虫子 8.8\n",
      "不能承受的生命之轻 [捷克] 米兰·昆德拉  朝向媚俗的一次伟大的进军 8.5\n",
      "红楼梦 [清] 曹雪芹 著  谁解其中味？ 9.6\n",
      "梦里花落知多少 郭敬明  只是青春留下的余烬 7.1\n",
      "达·芬奇密码 [美] 丹·布朗  一切畅销的因素都有了 8.2\n",
      "看见 柴静  在这里看见中国 8.8\n",
      "百年孤独 [哥伦比亚] 加西亚·马尔克斯  尼采所谓的永劫复归，一场无始无终的梦魇 9.2\n",
      "1988：我想和这个世界谈谈 韩寒  车手韩寒的公路小说 7.9\n",
      "何以笙箫默 顾漫  倒追有风险，入行需谨慎 7.8\n",
      "平凡的世界（全三部） 路遥  中国当代城乡生活全景 9.0\n",
      "简爱 [英] 夏洛蒂·勃朗特  灰姑娘在十九世纪 8.5\n",
      "哈利·波特与魔法石 [英] J. K. 罗琳  羽加迪姆勒维奥萨！ 9.0\n",
      "白夜行 东野圭吾  封面剧透 9.2\n",
      "三体Ⅱ 刘慈欣  无边的黑暗森林，比第一部更为恢弘壮丽 9.3\n",
      "飘 [美国] 玛格丽特·米切尔  革命时期的爱情，随风而逝 9.3\n",
      "送你一颗子弹 刘瑜  犀利又温柔，穿过胸口隐隐作痛 8.6\n",
      "三体Ⅲ 刘慈欣  终章，何去何从 9.2\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "from lxml import etree\n",
    "res=requests.get('https://book.douban.com/top250?start=0')\n",
    "html=etree.HTML(res.text)\n",
    "book_names=html.xpath('//div[@class=\"pl2\"]/a/@title')\n",
    "book_authors=html.xpath('//p[@class=\"pl\"]/text()')\n",
    "book_ratios=html.xpath('//span[@class=\"rating_nums\"]/text()')\n",
    "book_quotes=html.xpath('//span[@class=\"inq\"]/text()')\n",
    "# print(book_authors)\n",
    "# print(book_ratios)\n",
    "# print(len(book_ratios))\n",
    "# print(book_quotes)\n",
    "for i in zip(book_names,book_quotes,book_ratios):\n",
    "    a,b,c=i\n",
    "#     print(a,b,c)\n",
    "authors=[]\n",
    "for i in book_authors:\n",
    "    #print(i.split('/')[0])\n",
    "    authors.append(i.split('/')[0])\n",
    "# print(authors)\n",
    "\n",
    "for i in zip(book_names,authors,book_quotes,book_ratios):\n",
    "    a,b,c,d=i\n",
    "#     print(i)\n",
    "    print(a,b,c,d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "insert into book (book_name,book_author,book_quote,book_radio) values('追风筝的人','[美] 卡勒德·胡赛尼 ','为你，千千万万遍','8.8')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('小王子','[法] 圣埃克苏佩里 ','献给长成了大人的孩子们','9.0')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('围城','钱锺书 ','对于“人艰不拆”四个字最彻底的违抗','8.9')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('活着','余华 ','活着本身就是人生最大的意义','9.1')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('解忧杂货店','[日] 东野圭吾 ','一碗精心熬制的东野牌鸡汤，拒绝很难','8.6')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('白夜行','[日] 东野圭吾 ','暗夜独行的残破灵魂，爱与恶本就难分难舍','9.1')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('挪威的森林','[日] 村上春树 ','村上之发轫，多少人的青春启蒙','8.0')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('嫌疑人X的献身','[日] 东野圭吾 ','数学好是一种极致的浪漫','8.9')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('三体','刘慈欣 ','你我不过都是虫子','8.8')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('不能承受的生命之轻','[捷克] 米兰·昆德拉 ','朝向媚俗的一次伟大的进军','8.5')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('红楼梦','[清] 曹雪芹 著 ','谁解其中味？','9.5')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('梦里花落知多少','郭敬明 ','只是青春留下的余烬','7.2')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('达·芬奇密码','[美] 丹·布朗 ','一切畅销的因素都有了','8.2')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('看见','柴静 ','在这里看见中国','8.8')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('百年孤独','[哥伦比亚] 加西亚·马尔克斯 ','尼采所谓的永劫复归，一场无始无终的梦魇','9.2')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('1988：我想和这个世界谈谈','韩寒 ','车手韩寒的公路小说','7.9')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('何以笙箫默','顾漫 ','倒追有风险，入行需谨慎','8.0')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('平凡的世界（全三部）','路遥 ','中国当代城乡生活全景','9.0')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('简爱','[英] 夏洛蒂·勃朗特 ','灰姑娘在十九世纪','8.5')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('哈利·波特与魔法石','[英] J. K. 罗琳 ','羽加迪姆勒维奥萨！','9.0')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('飘','[美国] 玛格丽特·米切尔 ','革命时期的爱情，随风而逝','9.3')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('三体Ⅱ','刘慈欣 ','无边的黑暗森林，比第一部更为恢弘壮丽','9.2')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('白夜行','东野圭吾 ','封面剧透','9.2')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('送你一颗子弹','刘瑜 ','犀利又温柔，穿过胸口隐隐作痛','8.6')\n",
      "insert into book (book_name,book_author,book_quote,book_radio) values('三体Ⅲ','刘慈欣 ','终章，何去何从','9.2')\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "conn=pymysql.connect(host='localhost',port=3306,user='root',passwd='',db='test')\n",
    "conn.set_charset('utf8')\n",
    "cursor=conn.cursor()\n",
    "for i in zip(book_names,authors,book_quotes,book_ratios):\n",
    "#     a,b,c,d=i\n",
    "#     sql=\"insert into book (book_name,book_author,book_quote,book_radio) values('%s','%s','%s','%s')\"%(a,b,c,d)\n",
    "    sql=\"insert into book (book_name,book_author,book_quote,book_radio) values('%s','%s','%s','%s')\"%i\n",
    "    print(sql)\n",
    "    cursor.execute(sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "存取第一0页\n",
      "存取第一1页\n",
      "存取第一2页\n",
      "存取第一3页\n",
      "存取第一4页\n",
      "存取第一5页\n",
      "存取第一6页\n",
      "存取第一7页\n",
      "存取第一8页\n",
      "存取第一9页\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "import pymysql\n",
    "conn=pymysql.connect(host='localhost',port=3306,user='root',passwd='',db='test')\n",
    "conn.set_charset('utf8')\n",
    "cursor=conn.cursor()\n",
    "\n",
    "from lxml import etree\n",
    "url='https://book.douban.com/top250?start={}'\n",
    "def get_data(page):\n",
    "    res=requests.get(url.format(str(page)))\n",
    "    html=etree.HTML(res.text)\n",
    "    book_names=html.xpath('//div[@class=\"pl2\"]/a/@title')\n",
    "    book_authors=html.xpath('//p[@class=\"pl\"]/text()')\n",
    "    book_ratios=html.xpath('//span[@class=\"rating_nums\"]/text()')\n",
    "    book_quotes=html.xpath('//span[@class=\"inq\"]/text()')\n",
    "\n",
    "    authors=[]\n",
    "    for i in book_authors:\n",
    "        authors.append(i.split('/')[0])\n",
    "    return zip(book_names,authors,book_quotes,book_ratios)\n",
    "\n",
    "    \n",
    "for i in range(10):\n",
    "    data=get_data(i*25)\n",
    "    for j in data:\n",
    "        sql=\"insert into book (book_name,book_author,book_quote,book_radio) values('%s','%s','%s','%s')\"%j\n",
    "        cursor.execute(sql)\n",
    "    print('存取第一%d页'%i+1)"
   ]
  },
  {
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}