爬取猫眼电影TOP100

这次是第一次存储在MySQL中，所以选择了相对简单易爬取的猫眼电影排行，但还是出现了问题。当我在创建表的时候，整个程序突然报错了，查看了一下sql语句，发现并没有什么错误，这是我直观的感受，下面是我创建表失败的代码以及报错的问题：

　　

    sql = 'create table if not exists maoyan(index varchar(50) not null,title varchar(60),actor varchar(100),score varchar(20),img varchar(255),primary key(indexs))'

TypeError: %d format: a number is required, not str

一开始碰到这个问题真的是疯了，怎么都找不到原因。后面经过网上查找资料，就尝试将index改为indexs，说实话我到现在也不知道为什么要这样，如果有同学知道，请不吝赐教，最后就直接展示代码。

# ！/usr/bin/env python
# encoding: utf-8

import requests
from lxml import etree
import csv,pymysql
import time

headers = {
    'Accept':'text/html,application/xhtml+xml,application/xml;q=0.9,'
             'image/webp,image/apng,*/*;q=0.8',
    'Connection': 'keep-alive',
    'Host': 'maoyan.com',
    'Referer': 'http://maoyan.com/board/',
    'User-Agent': 'Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.62 Safari/537.36'
}

def Get_HTML(url):
    r = requests.get(url,headers=headers,)
    r.raise_for_status()
    r.encoding = r.apparent_encoding
    #print(r.text)
    return r.text

def Parse_HTML(html,info):
    s = etree.HTML(html)
    divs = s.xpath('//*[@id="app"]/div/div/div[1]/dl/dd')
    for div in divs:
        ilt = {}

        ilt['indexs'] = div.xpath('./i/text()')[0]
        #print(type(ilt['indexs']))

        ilt['title'] = div.xpath('./div/div/div[1]/p[1]/a/text()')[0]
        print(ilt['title'])

        ilt['actor'] = div.xpath('./div/div/div[1]/p[2]/text()')[0].strip()
        #print(actor.strip())

        ilt['score'] = div.xpath('./div/div/div[2]/p/i[1]/text()')[0]+div.xpath(
            './div/div/div['
                           '2]/p/i[2]/text()')[0]
        #print(scores)

        ilt['img'] = div.xpath('./a/img[2]/@data-src')[0]
        #print(imgs)
        print("\n++++++++++++++++++分界线++++++++++++++++++\n",)

        info.append(ilt)
def Write_csv(info):
    with open("maoyanmovies.csv","w+",newline='',encoding='utf-8') as csvfile:
        try:
            header = ["index","title","actor","score","img"]
            writer = csv.DictWriter(csvfile,fieldnames=header)
            writer.writeheader()
            for item in info:
                writer.writerow(item)
            print("You're success!")
        except:
            print("Oh no!false")

def Write_MySQL(info):
    db = pymysql.connect(host='localhost',port=3306,user='root',password='forever.cl',db='spiders',charset='utf8')
    cursor = db.cursor()
    cursor.execute('select version()')
    data = cursor.fetchone()
    print('Data_version:',data)
    sql = 'create table if not exists maoyan(indexs varchar(50) not null,title varchar(60),actor varchar(100),score varchar(20),img varchar(255),primary key(indexs))'
    cursor.execute(sql)
    print("Create table successful")
    for item in info:
        keys = ','.join(item.keys())
        values = ','.join(['%s']*len(item.values()))
        print(item.values())
        sql = 'insert into maoyan({keys}) values({values})'.format(keys=keys,values=values)
        print(sql)
        try:
            cursor.execute(sql,tuple(item.values()))
            print("Successful")
            db.commit()
        except:
            print("NO!False")
            db.rollback()
    db.close()

def main():
    try:
        start_url = "http://maoyan.com/board/4?offset="
        info = []
        for i in range(10):
            url = start_url + str(i*10)
            html = Get_HTML(url)
            Parse_HTML(html,info)
            time.sleep(3)
            #Write_csv(info)
            Write_MySQL(info)
    except:
        print("False")

if __name__ == '__main__':
    main()

人生苦短，我用Python。

一起加油！！！