import requests
from lxml import etree
from bs4 import BeautifulSoup
import pymysql
import csv

url = "https://fanqienovel.com/page/7143038691944959011"
headers = {"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36 SLBrowser/9.0.3.5211 SLBChan/105"}
r = requests.get(url, headers = headers)
html = r.text
# print(html)

soup = BeautifulSoup(html, "lxml")
chapter_title = soup.select("div.info-name > h1")
for i in chapter_title:
    title = i.text
    # print(title)

dom = etree.HTML(html)
chapter = dom.xpath('//div[@class="chapter-item"]/a')[0:10]
chapter_list = []
for i in chapter:
    chapters = i.text
    # print(chapters)
    chapter_list.append(chapters)
# print(chapter_list)

chapter_url = soup.find_all("a", class_="chapter-item-title")[1:11]
urls_list = []
for i in chapter_url:
    urls = "https://fanqienovel.com" + i.get('href')
    # print(urls)
    urls_list.append(urls)
# print(urls_list)

with open("十日终焉.csv", "w", encoding="utf-8", newline="") as fp:
    # value = [[f"{chapters}", f"{urls}"]]
    # print(value)
    value = []
    for a, b in zip(chapter_list, urls_list):
        value.append([a, b])
    # print(value)
    writer = csv.writer(fp)
    writer.writerow(["chapters", "urls"])
    writer.writerows(value)

con = pymysql.connect(host="localhost", port=3306, user="root", password="123456", database="python", charset="utf8")
list = []
for a, b in zip(chapter_list, urls_list):
    list.append((a, b))
# print(list)
cursor = con.cursor(pymysql.cursors.DictCursor);
try:
    cursor.execute("create table novel(chapters varchar(255) primary key,urls varchar(255) not null)")
except:
    cursor.execute("delete from novel")
sql = """
insert into novel (chapters, urls) values (%s, %s)
"""
cursor.executemany(sql, list)
con.commit()
cursor.close()
con.close()