【爬虫_数据库】Python爬取数据库内容并写入数据库

爬取博客园内容并写入数据库

import requests
from lxml import etree
import re
from python_link_mysql import MysqlObject  # 参考上一个博客

# 实例化python链接数据库类
mysql = MysqlObject(host="localhost",
                    user="root",
                    password=None,
                    port=3306,
                    database="ai_data")
create_table_sql = """
create table if not exists bokeyuan(
title varchar(150),
context varchar(300),
datetime varchar(50),
reader varchar(10),
preview varchar(10))
"""
# 创建表
mysql.execute(create_table_sql)


url ="https://www.cnblogs.com/mxbs/"
header = {
          
   
        "cookie":_ga=GA1.2.281854837.1645530703; _gid=GA1.2.262901704.1645530703; _gat_gtag_UA_476124_1=1; __gads=ID=0c051b5943fd3333:T=1645530703:S=ALNI_MaQArJ7SiaAn4Rjc9AXaGCRWuKfaQ; .AspNetCore.Antiforgery.b8-pDmTq1XM=CfDJ8GsLOKiGtk1Au0UP1SouGdU8CMKdx0Y8rN5n5xQPJ8L6vKVdNIngzmuFO9O1sFxQ39zyOdTJsoVg97GF7LOqEQm1XeRw0keTULoZD4E0pmaSdbhWjrTGzWU9sV3qXY8t1hDWG4rgerN_5cZFvSBtZjE; Hm_lvt_866c9be12d4a814454792b1fd0fed295=1645530703,1645530726; Hm_lpvt_866c9be12d4a814454792b1fd0fed295=1645530726,
        "user-agent":Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.102 Safari/537.36
}

response = requests.get(url=url, headers=header)
res = response.text

blogs_xpath = etree.HTML(res)
blogs = blogs_xpath.xpath(//*[@id="mainContent"]/div/div)
for blog in blogs:
    title = blog.xpath(./div[2]/a/span/text())
    if title:  # 最后一个是空列表，为了不报错，需要加上***************************************************************
        title = re.sub("
","",title[0])
        context = re.sub("
","",blog.xpath(./div[3]/div/text())[0])
        datatime = re.findall(rposted @ (.*)
,blog.xpath(./div[5]/text())[0])[0]
        reader = blog.xpath(./div[5]/span[1]/text())[0]
        preview = blog.xpath(./div[5]/span[2]/text())[0]
        
        
        # 利用sql写入数据库
        insert_sql = f"""
        insert into bokeyuan VALUES("{
            
     title}","{
            
     context}","{
            
     datatime}","{
            
     reader}","{
            
     preview}");
        """
        mysql.execute(insert_sql)