#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018/7/3 20:25

# @Author  : baoshan

# @Site    :

# @File    : iciba.py.py

# @Software: PyCharm Community Edition

# 爬取金山词霸每日一句放到数据库中存储

import json

import requests

import pymysql

host = '10.10.1.10'

port = 3306

user = 'root'

passwd = 'root'

dbme = 'information_storage'

table = 'iciba_daily_sentence'

conn = pymysql.connect(host=host, user=user, passwd=passwd, db=dbme, port=port, charset="utf8")

cur = conn.cursor()

data = requests.get("http://open.iciba.com/dsapi/").text

jdata = json.loads(data)

dateline = jdata.get("dateline")

caption = jdata.get("caption")

content = jdata.get("content")

fenxiang_img = jdata.get("fenxiang_img")

note = jdata.get("note")

picture = jdata.get("picture")

picture2 = jdata.get("picture2")

translation = jdata.get("translation")

tts = jdata.get("tts")

sql = 'insert into %s (dateline,caption,content,fenxiang_img,note,picture,picture2,translation,tts) ' \

      'values ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s","%s")' \

      % (table, dateline,caption,content,fenxiang_img,note,picture,picture2,translation,tts)

print(sql)

try:

    cur.execute(sql)

except:

    print("error")

print(json.dumps(jdata, ensure_ascii=False))

conn.commit()

conn.close()

CREATE TABLE `iciba_daily_sentence` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `dateline` char(10) DEFAULT NULL,

  `caption` varchar(20) DEFAULT NULL,

  `content` varchar(255) DEFAULT NULL,

  `fenxiang_img` varchar(255) DEFAULT NULL,

  `note` varchar(255) DEFAULT NULL,

  `picture` varchar(255) DEFAULT NULL,

  `picture2` varchar(255) DEFAULT NULL,

  `translation` varchar(255) DEFAULT NULL,

  `tts` varchar(255) DEFAULT NULL,

  `gmt_create` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  UNIQUE KEY `uniq_dateline` (`dateline`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;