selenium淘宝抓取自定义商品信息并存入SQLserver

'''
AUTHOR: vforlove
CREATED: 2018-08-04
IDE:PyCharm
version:3.6.6
'''
from selenium import webdriver
import pymssql
from pyquery import PyQuery as pq
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.wait import WebDriverWait
from selenium.common.exceptions import TimeoutException
from urllib.parse import quote

#启用Chrome无界面模式
chrome_options = webdriver.ChromeOptions()
chrome_options.add_argument('--headless')
browser = webdriver.Chrome(chrome_options = chrome_options)
wait = WebDriverWait(browser, 10)  #显式延时10s
KEY_WORD = 'python'  #自定义搜索内容

#数据库存储数据预处理（连接数据库，建表）
conn = pymssql.connect(host = 'FORLOVE', user = 'sa', password = ' ', database = 'spider', autocommit = True)
cursor = conn.cursor()
if not cursor:
    print('数据库连接失败！')
else:
    cursor.execute('use spider')
    print('数据库连接成功！')
sql = """IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='selenium_taobao') CREATE TABLE selenium_taobao(image varchar (150) , price varchar(10), title nvarchar(150) NOT NULL, shop nvarchar(40), location nvarchar(20), primary key(title))"""
cursor.execute(sql)

#定义抓取页面函数
def get_page(page):
    print('开始抓取第%s页' % page)
    try:
        url = 'https://s.taobao.com/search?q=' + quote(KEY_WORD)
        print(url)
        browser.get(url)
        #采用跳页抓取，若不是起始页，则进行跳页操作
        if page > 1:
            input = wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, '#mainsrp-pager div.form > input')))
            submit = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, '#mainsrp-pager div.form > span.J_Submit')))
            input.clear()
            input.send_keys(page)
            submit.click()
    #跳转成功判断
        print('开始页面判断')
        wait.until(EC.text_to_be_present_in_element((By.CSS_SELECTOR, '#mainsrp-pager li.item.active > span'), str(page)))
        print('已转至%s页' % page)
        wait.until(EC.presence_of_element_located((By.CSS_SELECTOR, '.m-itemlist .items .item')))
        #调用页面解析函数
        parse_products()
    except TimeoutException:
        print('超时！开始重试当前页！')
        get_page(page)
#定义页面解析函数
def parse_products():
    html = browser.page_source
    doc = pq(html)
    items = doc('#mainsrp-itemlist .m-itemlist .items .item').items()
    for item in items:
        products = {
            'image': item.find('.pic .img').attr('data-src'),
            'price': item.find('.price').text(),
            'title': item.find('.title').text(),
            'shop': item.find('.shop').text(),
            'location': item.find('.location').text()
        }
        print(products)
        save_to_sqlserver(products)
#定义存储数据库函数
table = 'selenium_taobao'
def save_to_sqlserver(products):
    keys = ','.join(products.keys())
    values = ','.join(['%s'] * len(products))
    insert_sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
    print(insert_sql)
    # 数据库插入操作
    try:
        print(tuple(products.values()))
        cursor.execute(insert_sql, tuple(products.values()))
        print('INSERT SUCCESSFUL!')
    except:
        print('INSERT FAILED!')
        conn.rollback()

MAX_PAGE = int(input('请输入想抓取的产品页数:'))

#start
if __name__ == '__main__':
    for page in range(1, MAX_PAGE + 1):
        get_page(page)

注意抓取下来的字段长度，如果INSERT FAILED错误则调整数据库中字段定义的长度