Python爬取金山詞霸每日一句，儲存到MySQL中

標籤：dai   ODB   timestamp   ror   creat   pytho   update   incr   cep   

 

#!/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 jsonimport requestsimport pymysqlhost = ‘10.10.1.10‘port = 3306user = ‘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/").textjdata = 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;

 

謝謝！

Python爬取金山詞霸每日一句，儲存到MySQL中