import urllib.request
 
url = 'https://datachart.500.com/ssq/history/newinc/history.php?start=1&end=20109'
request = urllib.request.Request(url)
request.add_header('user-agent',
     'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.183 Safari/537.36')
response = urllib.request.urlopen(request)
buf = response.read()
data = buf.decode('utf-8')
# 爬取數據保存到文件
fileOb = open('history.html', 'w', encoding='utf-8') # 打開一個文件，沒有就新建一個
fileOb.write(data)
fileOb.close()

from lxml import etree
from collections import namedtuple
import mysql2
 
fileOb = open('history.html', 'r', encoding='utf-8') # 打開一個文件
doc = fileOb.read()
html = etree.HTML(doc) # 把字符串轉化為可處理的格式
two_colour = namedtuple('two_colour', 'code,red_1,red_2,red_3,red_4,red_5,red_6,blue,create_date')
 
 
# two_colour = namedtuple('two_colour', 'a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16')
 
 
def test1():
 # red = html.xpath("http://tbody/tr/td[@class='t_cfont2']/text()[1]")
 # blue = html.xpath("http://tbody/tr/td[@class='t_cfont4']/text()[1]")
 all_ball = html.xpath("http://tbody/tr[@class='t_tr1']/td/text()")
 # print(all_ball)
 for i in range(len(all_ball)):
  dict = two_colour(all_ball[0], all_ball[1], all_ball[2], all_ball[3], all_ball[4], all_ball[5], all_ball[6],
       all_ball[7], all_ball[15])
  print(dict)
  for j in range(16):
   del all_ball[0]
  mysql2.saveDouBan(dict)
 print(all_ball)
 
 
if __name__ == '__main__':
 test1()

import pymysql
# 新的寫法，要注意
from dbutils.pooled_db import PooledDB
 
POOL = PooledDB(
 creator=pymysql, # 使用鏈接數據庫的模塊
 maxconnections=6, # 連接池允許的最大連接數，0和None表示不限制連接數
 mincached=2, # 初始化時，鏈接池中至少創(chuàng)建的空閑的鏈接，0表示不創(chuàng)建
 maxcached=5, # 鏈接池中最多閑置的鏈接，0和None不限制
 maxshared=3,
 # 鏈接池中最多共享的鏈接數量，0和None表示全部共享。PS: 無用，因為pymysql和MySQLdb等模塊的 threadsafety都為1，所有值無論設置為多少，_maxcached永遠為0，所以永遠是所有鏈接都共享。
 blocking=True, # 連接池中如果沒有可用連接后，是否阻塞等待。True，等待；False，不等待然后報錯
 maxusage=None, # 一個鏈接最多被重復使用的次數，None表示無限制
 setsession=[], # 開始會話前執(zhí)行的命令列表。
 ping=0,
 # ping MySQL服務端，檢查是否服務可用。
 host='127.0.0.0',
 port=3306,
 user='root',
 password='123456',
 database='python_test',
 charset='utf8'
)
 
 
def func():
 # 檢測當前正在運行連接數的是否小于最大鏈接數，如果不小于則：等待或報raise TooManyConnections異常
 # 否則 則優(yōu)先去初始化時創(chuàng)建的鏈接中獲取鏈接 SteadyDBConnection。
 # 然后將SteadyDBConnection對象封裝到PooledDedicatedDBConnection中并返回。
 # 如果最開始創(chuàng)建的鏈接沒有鏈接，則去創(chuàng)建一個SteadyDBConnection對象，再封裝到PooledDedicatedDBConnection中并返回。
 # 一旦關閉鏈接后，連接就返回到連接池讓后續(xù)線程繼續(xù)使用。
 conn = POOL.connection()
 
 # print(th, '鏈接被拿走了', conn1._con)
 # print(th, '池子里目前有', pool._idle_cache, '\r\n')
 
 cursor = conn.cursor()
 cursor.execute('select * from two_clour_two')
 result = cursor.fetchall()
 for i in result:
  print(i)
 conn.close()
 
 
# 數據庫插入操作
def saveDouBan(dict):
 conn = POOL.connection()
 cursor = conn.cursor()
 sql = "insert into two_clour_two (`code`, `red_1`, `red_2`, `red_3`, `red_4`, `red_5`, `red_6`, `blue`,`create_date`) values(\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\")" % (
  str(dict[0]), str(dict[1]), str(dict[2]), str(dict[3]), str(dict[4]), str(dict[5]), str(dict[6]),
  str(dict[7]), str(dict[8]))
 cursor.execute(sql)
 print('Successful')
 conn.commit() # 寫入數據庫一定要commit，否則數據沒有數據
 cursor.close()
 
 
if __name__ == '__main__':
 func()

for i in range(len(all_ball)):