# 1. 連接到mysql數(shù)據(jù)庫(kù)
mysql -h127.0.0.1 -uroot -p123456
# 2. 建庫(kù)
create database maoyandb charset utf8;
# 3. 切換數(shù)據(jù)庫(kù)
use maoyandb;
# 4. 創(chuàng)建數(shù)據(jù)表
create table filmtab(
name varchar(100),
star varchar(400),
time varchar(30)
);

db = pymysql.connect('localhost','root','123456','maoyandb')

cursor = db.cursor()

#第一種方法：編寫(xiě)sql語(yǔ)句，使用占位符傳入相應(yīng)數(shù)據(jù)
sql = "insert into filmtab values('%s','%s','%s')" % ('刺殺,小說(shuō)家','雷佳音','2021')
cursor.excute(sql)
第二種方法：編寫(xiě)sql語(yǔ)句，使用列表傳參方式
sql = 'insert into filmtab values(%s,%s,%s)'
cursor.execute(sql,['刺殺,小說(shuō)家','雷佳音','2021'])

db.commit()

cursor.close()
db.close()

# -*-coding:utf-8-*-
import pymysql
#創(chuàng)建對(duì)象
db = pymysql.connect('localhost','root','123456','maoyandb')
cursor = db.cursor()
# sql語(yǔ)句執(zhí)性，單行插入
info_list = ['刺殺,小說(shuō)家','雷佳音,楊冪','2021-2-12']
sql = 'insert into movieinfo values(%s,%s,%s)'
#列表傳參
cursor.execute(sql,info_list)
db.commit()
# 關(guān)閉
cursor.close()
db.close()

db = pymysql.connect('localhost','root','123456','maoyandb',charset='utf8')
cursor = db.cursor()
# sql語(yǔ)句執(zhí)性，列表元組
info_list = [('我不是藥神','徐崢','2018-07-05'),('你好,李煥英','賈玲','2021-02-12')]
sql = 'insert into movieinfo values(%s,%s,%s)'
cursor.executemany(sql,info_list)
db.commit()
# 關(guān)閉
cursor.close()
db.close()

# coding=gbk
from urllib import request
import re
import time
import random
from ua_info import ua_list
import pymysql
class MaoyanSpider(object):
    def __init__(self):
        #初始化屬性對(duì)象
        self.url = 'https://maoyan.com/board/4?offset={}'
        #數(shù)據(jù)庫(kù)連接對(duì)象
        self.db = pymysql.connect(
            'localhost','root','123456','maoyandb',charset='utf8')
        #創(chuàng)建游標(biāo)對(duì)象
        self.cursor = self.db.cursor()
    def get_html(self,url):
        headers = {'User-Agent':random.choice(ua_list)}
        req = request.Request(url=url,headers=headers)
        res = request.urlopen(req)
        html = res.read().decode()
        # 直接解析
        self.parse_html(html)
    def parse_html(self,html):
        re_bds = '<div class="movie-item-info">.*?title="(.*?)".*?<p class="star">(.*?)</p>.*?class="releasetime">(.*?)</p>'
        pattern = re.compile(re_bds,re.S)
        r_list = pattern.findall(html)
        self.save_html(r_list)
    def save_html(self, r_list):
        L = []
        sql = 'insert into movieinfo values(%s,%s,%s)'
        # 整理數(shù)據(jù)
        for r in r_list:
            t = (
                r[0].strip(),
                r[1].strip()[3:],
                r[2].strip()[5:15]
            )
            L.append(t)
            print(L)
        # 一次性插入多條數(shù)據(jù) L:[(),(),()]
        try:
            self.cursor.executemany(sql,L)
            # 將數(shù)據(jù)提交數(shù)據(jù)庫(kù)
            self.db.commit()
        except:
            # 發(fā)生錯(cuò)誤則回滾
            self.db.rollback()
    def run(self):
        for offset in range(0,11,10):
            url = self.url.format(offset)
            self.get_html(url)
            time.sleep(random.uniform(1,3))
        # 斷開(kāi)游標(biāo)與數(shù)據(jù)庫(kù)連接
        self.cursor.close()
        self.db.close()
if __name__ == '__main__':
    start=time.time()
    spider = MaoyanSpider()
    spider.run()
    end=time.time()
    print("執(zhí)行時(shí)間:%.2f" % (end-start))