【爬虫_数据库】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("\n","",title[0])
        context = re.sub("\n","",blog.xpath('./div[3]/div/text()')[0])
        datatime = re.findall(r'posted @ (.*)\n',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)