把获取的维基百科主页词条及链接存入mysql数据库

wiki2mysql.py

from urllib.request import urlopen
from bs4 import BeautifulSoup
import  re
import pymysql.cursors


#请求url并把结果用utf-8编码
resp = urlopen("https://en.wikipedia.org/wiki/Main_Page").read().decode("utf-8")
#使用beautifulsoup去解析
soup=BeautifulSoup(resp,'html.parser')
#获取所有以/wiki/开头的a标签的Href属性
listUrls = soup.findAll("a",href=re.compile("^/wiki/"))
#输出所有的词条对应的名称和url
for url in listUrls:
    #过滤以.jpg或.JPG结尾的url
    if not re.search('\.(jpg|JPG)$',url['href']):
        #输出url的文字和链接
        #string 只能获取一个 get_text()获取标签下所有的文字
        print(url.get_text(),"<----->" , "https://en.wikipedia.org" + url["href"])

        #获取数据库链接
        connection = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='',
            db='wikiurl',
            charset='utf8mb4'
        )
        try:
            #获取会话指针
            with connection.cursor() as cursor:
                #创建sql语句
                sql = "insert into `urls`(`urlname`,`urlhref`) values(%s,%s)"
                #执行sql语句
                cursor.execute(sql,(url.get_text(),"https://en.wikipedia.org" + url["href"]))
                #提交
                connection.commit()
        finally:
            connection.close()