在天行数据通过API拿到数据并放入数据库

先创建数据库与表，记得设置字符编码为utf8。否则存中文会报错。

create database news DEFAULT CHARACTER  set utf8;

create table tb_newslist (
id int AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) DEFAULT '',
url VARCHAR(150));

通过requests获得数据，连接数据库，并将数据写入。

import requests
import pymysql


key = '7e45e6dd2okjygf7d2b5e4d7137uyd42'

news_dict = {}
for page in range(1, 11):
    resp = requests.get(f'http://api.tianapi.com/huanbao/index?key={key}&page={page}&num=10')
    for data_dict in resp.json()["newslist"]:
        news_dict[data_dict['title']] = data_dict['url']

conn = pymysql.connect(host='59.119.250.88', port=3306,
                       database='news', charset='utf8',
                       user='root', password='Tianmao.520', autocommit=False)

try:
    # 获取游标
    with conn.cursor() as cursor:
        # 通过游标对数据库操作
        for new in news_dict:
            result = cursor.execute(
                'insert into tb_newslist(title, url) values (%s,%s)', (new, news_dict[new])
            )
            # 操作成功，提交
            conn.commit()
except pymysql.MySQLError as err:
    print(err)
    # 操作失败，回滚
    conn.rollback()
finally:
    # 关闭连接
    conn.close()

根据输入内容查找相关新闻，返回新闻标题与链接。

import pymysql


conn = pymysql.connect(host='59.119.250.88', port=3306,
                       database='news', charset='utf8',
                       user='root', password='Tianmao.520', autocommit=False)

name = input('请输入查询内容')
try:
    # 获取游标
    with conn.cursor() as cursor:
        # 通过游标对数据库操作
        cursor.execute('select title, url from tb_newslist where title like %s',(f'%{name}%'))
        for new in cursor.fetchall():
            print(new)

except pymysql.MySQLError as err:
    print(err)
    
finally:
    # 关闭连接
    conn.close()