import requests
from scrapy.selector import Selector
import MySQLdb

conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="article_spider", charset="utf8")
curcor = conn.cursor()


def crawl_ips():
    # 爬取西刺的免费代理
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.104 Safari/537.36"
    }
    for i in range(2737):
        re = requests.get("http://www.xicidaili.com/nn/{0}".format(i), headers=headers)
        selector = Selector(text=re.text)
        all_trs = selector.css("#ip_list tr")

        ip_list = []
        for tr in all_trs[1:]:
            speed_str = tr.css(".bar::attr(title)").extract()[0]
            if speed_str:
                speed = float(speed_str.split("秒")[0])
            all_texts = tr.css("td::text").extract()

            ip = all_texts[0]
            port = all_texts[1]
            type = all_texts[5]

            ip_list.append((ip, port, type, speed))

        for ip_info in ip_list:
            curcor.execute(
                "insert proxy_ip(ip, port, speed, type) VALUES('{0}', '{1}', {2}, '{3}')".format(
                    ip_info[0], ip_info[1], ip_info[3], ip_info[2]
                )
            )
            conn.commit()


class GetIP(object):
    def delete_ip(self, ip):
        # 删除无效代理
        delete_sql = """
            delete from proxy_ip WHERE ip='{0}'
        """.format(ip)
        curcor.execute(delete_sql)
        conn.commit()
        return True

    def judge_ip(self, ip, port, ip_type):
        # 判断ip是否可用
        http_url = "http://www.baidu.com"
        proxy_url = "{2}://{0}:{1}".format(ip, port, ip_type)
        try:
            proxy_dict = {
                "{0}".format(ip_type): proxy_url
            }
            response = requests.get(http_url, proxies=proxy_dict)
        except Exception as e:
            print("invalid ip and port")
            self.delete_ip(ip)
            return False
        else:
            code = response.status_code
            if code >= 200 and code < 300:
                print("effective ip")
                return True
            else:
                print("invalid ip and port")
                self.delete_ip(ip)
                return False

    def get_random_ip(self):
        # 从数据库中随机获取一个可用的ip
        random_sql = """
        SELECT ip, port, type FROM proxy_ip
        ORDER BY RAND()
        LIMIT 1
        """
        result = curcor.execute(random_sql)
        for ip_info in curcor.fetchall():
            ip = ip_info[0]
            port = ip_info[1]
            ip_type = ip_info[2]

            judge_re = self.judge_ip(ip, port, ip_type)
            if judge_re:
                return "{2}://{0}:{1}".format(ip, port, ip_type)
            else:
                return self.get_random_ip()

# print(crawl_ips())
if __name__ == "__main__":
    get = GetIP()
    get.get_random_ip()
import requests
from scrapy.selector import Selector
import MySQLdb

conn = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="article_spider", charset="utf8")
curcor = conn.cursor()


def crawl_ips():
    # 爬取西刺的免费代理
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.104 Safari/537.36"
    }
    for i in range(2737):
        re = requests.get("http://www.xicidaili.com/nn/{0}".format(i), headers=headers)
        selector = Selector(text=re.text)
        all_trs = selector.css("#ip_list tr")

        ip_list = []
        for tr in all_trs[1:]:
            speed_str = tr.css(".bar::attr(title)").extract()[0]
            if speed_str:
                speed = float(speed_str.split("秒")[0])
            all_texts = tr.css("td::text").extract()

            ip = all_texts[0]
            port = all_texts[1]
            type = all_texts[5]

            ip_list.append((ip, port, type, speed))

        for ip_info in ip_list:
            curcor.execute(
                "insert proxy_ip(ip, port, speed, type) VALUES('{0}', '{1}', {2}, '{3}')".format(
                    ip_info[0], ip_info[1], ip_info[3], ip_info[2]
                )
            )
            conn.commit()


class GetIP(object):
    def delete_ip(self, ip):
        # 删除无效代理
        delete_sql = """
            delete from proxy_ip WHERE ip='{0}'
        """.format(ip)
        curcor.execute(delete_sql)
        conn.commit()
        return True

    def judge_ip(self, ip, port, ip_type):
        # 判断ip是否可用
        http_url = "http://www.baidu.com"
        proxy_url = "{2}://{0}:{1}".format(ip, port, ip_type)
        try:
            proxy_dict = {
                "{0}".format(ip_type): proxy_url
            }
            response = requests.get(http_url, proxies=proxy_dict)
        except Exception as e:
            print("invalid ip and port")
            self.delete_ip(ip)
            return False
        else:
            code = response.status_code
            if code >= 200 and code < 300:
                print("effective ip")
                return True
            else:
                print("invalid ip and port")
                self.delete_ip(ip)
                return False

    def get_random_ip(self):
        # 从数据库中随机获取一个可用的ip
        random_sql = """
        SELECT ip, port, type FROM proxy_ip
        ORDER BY RAND()
        LIMIT 1
        """
        result = curcor.execute(random_sql)
        for ip_info in curcor.fetchall():
            ip = ip_info[0]
            port = ip_info[1]
            ip_type = ip_info[2]

            judge_re = self.judge_ip(ip, port, ip_type)
            if judge_re:
                return "{2}://{0}:{1}".format(ip, port, ip_type)
            else:
                return self.get_random_ip()

# print(crawl_ips())
if __name__ == "__main__":
    get = GetIP()
    get.get_random_ip()
# 爬取西刺的免费代理 # 删除无效代理 delete from proxy_ip WHERE ip='{0}' # 判断ip是否可用 # 从数据库中随机获取一个可用的ip SELECT ip, port, type FROM proxy_ip         ORDER BY RAND()         LIMIT 1 # print(crawl_ips())