Python爬取金山词霸每日一句,存储到MySQL中

先附上源码

import json
import requests
import pymysql

host = 'localhost'
port = 3306
user = 'root'
passwd = '213113'
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;

接下来就是用pycharm运行代码

  1. 将python代码粘贴到pycharm中,第二三行会有红线报错,是没有导入相应的包QQ截图20201205140417.png
  2. 打开控制台,输入pip install requestspip install pymysql,回车QQ截图20201205140546.png
  3. 打开navicat,当然也可以用其他数据库管理工具,比如sqlyog

新建数据库取名为information_storage,字符集设为utf8,新建查询,将上面的代码粘贴进去,点击运行QQ截图20201205141331.png

  1. 然后去pycharm中连接数据库QQ截图20201205142055.png
  2. 填写信息,测试连接QQ截图20201205142801.png
  3. 记得程序中的数据库的用户名和密码改成自己的
  4. 运行代码,可以看到数据库中已经有了一条记录,说明成功!!

QQ截图20201205143414.png

  1. hahaha说道:

    7uuuhh

发表评论

电子邮件地址不会被公开。 必填项已用*标注