import requests
from lxml import etree
import sqlite3
url = 'http://book.zongheng.com/store.html'

res = requests.get(url)

# print(res.status_code)

xhtml = etree.HTML(res.text)

title = xhtml.xpath("//div[@class='bookname']/a/text()")

author = xhtml.xpath("//div[@class='bookilnk']/a[1]/text()")

type1 = xhtml.xpath("//div[@class='bookilnk']/a[2]/text()")

time1 = xhtml.xpath("//div[@class='bookilnk']/span[2]/text()")

linking = xhtml.xpath("//div[@class='bookimg']/a/img/@src")
print(title)
print(author)
# print(type1)
# print(time1)
# print(linking)


# with open("test.txt","a+") as f:
#     # f.writelines([i+''+j+'\n'+''+k+'\n'+''+l+'\n'+''+m+'\n' for i,j,k,l,m in zip(title,author,type1,time,linking)])
#     f.writelines([i+''+j+''+k for i,j,k in zip(title,author,type1)])

# import csv
# with open("test.csv","a+",newline='') as f:
#     w=csv.writer(f)
#     for i,j,k in zip(title,author,type1):
#         w.writerow([i,j,k])
    # f.writelines([i+''+j+'\n' for i,j in zip(title,author)])
    
dbname ='test.db'
conn =sqlite3.connect(dbname)
c =conn.cursor()

sql ='''
        create table if not existsq novelTest (
            bookname text not null primary key,
            bookauthor text not null
        );
        '''
c.execute(sql)

#插入数据
insertDate ='''
                INSERT INTO novelTest VALUES(?,?)
        '''
for i,j in zip(title,author):
    c.execute(insertDate,(i,j))

conn.commit()
conn.close()