import pymysql
import requests
import re

#獲取資源并下載
def resp(listURL):
 #連接數(shù)據(jù)庫(kù)
 conn = pymysql.connect(
  host = '127.0.0.1',
  port = 3306,
  user = 'root',
  password = '******',  #數(shù)據(jù)庫(kù)密碼請(qǐng)根據(jù)自身實(shí)際密碼輸入
  database = 'pachong', 
  charset = 'utf8'
 )
 #創(chuàng)建數(shù)據(jù)庫(kù)游標(biāo)
 cursor = conn.cursor()
 #創(chuàng)建列表t_movieTOP250（執(zhí)行sql語句）
 cursor.execute('create table t_movieTOP250(id INT PRIMARY KEY auto_increment NOT NULL ,movieName VARCHAR(20) NOT NULL 			,pictrue_address VARCHAR(100))')
 try:
  # 爬取數(shù)據(jù)
  for urlPath in listURL:
# 獲取網(wǎng)頁源代碼
response = requests.get(urlPath)
html = response.text
# 正則表達(dá)式
namePat = r'alt="(.*?)" src='
imgPat = r'src="(.*?)" class='
# 匹配正則（排名【用數(shù)據(jù)庫(kù)中id代替，自動(dòng)生成及排序】、電影名、電影海報(bào)（圖片地址））
res2 = re.compile(namePat)
res3 = re.compile(imgPat)
textList2 = res2.findall(html)
textList3 = res3.findall(html)
# 遍歷列表中元素,并將數(shù)據(jù)存入數(shù)據(jù)庫(kù)
for i in range(len(textList3)):
 cursor.execute('insert into t_movieTOP250(movieName,pictrue_address) 			VALUES("%s","%s")' % (textList2[i],textList3[i]))
  #從游標(biāo)中獲取結(jié)果
  cursor.fetchall()
  #提交結(jié)果
  conn.commit()
  print("結(jié)果已提交")
 except Exception as e:
  #數(shù)據(jù)回滾
  conn.rollback()
  print("數(shù)據(jù)已回滾")
 #關(guān)閉數(shù)據(jù)庫(kù)
 conn.close()
#top250所有網(wǎng)頁網(wǎng)址
def page(url):
 urlList = []
 for i in range(10):
  num = str(25*i)
  pagePat = r'?start=' + num + '&filter='
  urL = url+pagePat
  urlList.append(urL)
 return urlList

if __name__ == '__main__':
 url = r"https://movie.douban.com/top250"
 listURL = page(url)
 resp(listURL)