Python mysql-python及pycurl使用一例

#环境：CentOS Linux release 7.5.1804 (Core) mini安装，使用python2.7

#使用pucurl对输入的url地址进行测试，将结果存放到mysql中，代码来之网上收集并自行修改

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb
import os,sys
import time
import sys
import pycurl
import datetime

now = datetime.datetime.now()

class joincontents:
    def __init__(self):
        self.contents = ''
    def callback(self,curl):
        self.contents = self.contents + curl
    #self.contents = self.contents + curl.decode('utf-8')

def curlurl(url):
    t = joincontents()
    c = pycurl.Curl()
    c.setopt(pycurl.WRITEFUNCTION,t.callback)
    c.setopt(pycurl.ENCODING, 'gzip')
    c.setopt(pycurl.URL,url)
    c.perform()
    
    SQL_NAMELOOKUP_TIME =  c.getinfo(c.NAMELOOKUP_TIME)*1000
    SQL_CONNECT_TIME =  c.getinfo(c.CONNECT_TIME)*1000
    SQL_PRETRANSFER_TIME =   c.getinfo(c.PRETRANSFER_TIME)*1000
    SQL_STARTTRANSFER_TIME = c.getinfo(c.STARTTRANSFER_TIME)*1000
    SQL_TOTAL_TIME = c.getinfo(c.TOTAL_TIME)*1000
    SQL_HTTP_CODE =  c.getinfo(c.HTTP_CODE)
    SQL_SIZE_DOWNLOAD =  c.getinfo(c.SIZE_DOWNLOAD)
    SQL_HEADER_SIZE = c.getinfo(c.HEADER_SIZE)
    SQL_SPEED_DOWNLOAD=c.getinfo(c.SPEED_DOWNLOAD)

    #print("HTTP状态码：%s" %(SQL_HTTP_CODE))
    #print("DNS解析时间：%.2f ms"%(SQL_NAMELOOKUP_TIME))
    #print("建立连接时间：%.2f ms" %(SQL_CONNECT_TIME))
    #print("准备传输时间：%.2f ms" %(SQL_PRETRANSFER_TIME))
    #print("传输开始时间：%.2f ms" %(SQL_STARTTRANSFER_TIME))
    #print("传输结束总时间：%.2f ms" %(SQL_TOTAL_TIME))
    #print("下载数据包大小：%d bytes/s" %(SQL_SIZE_DOWNLOAD))
    #print("HTTP头部大小：%d byte" %(SQL_HEADER_SIZE))
    #print("平均下载速度：%d bytes/s" %(SQL_SPEED_DOWNLOAD))

    con = MySQLdb.connect(host="localhost", user="root", passwd="password", db="urlrecord", port=3306)
    con.autocommit(1)
    cur = con.cursor()

    try:
        sql_isp_name = "ispname"

        sql = "insert into checkspeed (check_date, destinations_url, isp_name, http_code, namelookup_time, connect_time, \
               pretransfer_time, starttransfer_time, total_time, size_down, header_size, speed_down ) \
               values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"% (now,url,sql_isp_name,SQL_HTTP_CODE,SQL_NAMELOOKUP_TIME, \
               SQL_CONNECT_TIME,SQL_PRETRANSFER_TIME,SQL_STARTTRANSFER_TIME,SQL_TOTAL_TIME,SQL_SIZE_DOWNLOAD,SQL_HEADER_SIZE,SQL_SPEED_DOWNLOAD)

        cur.execute(sql)
        stus = cur.fetchall()
        
    except:
        import traceback
        traceback.print_exc()
        con.rollback()
    finally:
        cur.close()
        con.close()


if __name__ == '__main__':    
    for urladdress in open(r'url_list.txt').readlines():
        url = urladdress.strip('\n')
        curlurl(url)

#将需要测速的url地址放入url_list.txt文件中

 

#数据库表

CREATE TABLE `checkspeed` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `check_date` datetime DEFAULT NULL,
  `sources_ipaddress` varchar(15) DEFAULT NULL,
  `destinations_url` varchar(100) DEFAULT NULL,
  `interface_ipaddress` varchar(15) DEFAULT NULL,
  `isp_name` varchar(50) DEFAULT NULL,
  `http_code` varchar(3) DEFAULT NULL,
  `namelookup_time` varchar(15) DEFAULT NULL,
  `connect_time` varchar(15) DEFAULT NULL,
  `pretransfer_time` varchar(15) DEFAULT NULL,
  `starttransfer_time` varchar(15) DEFAULT NULL,
  `total_time` varchar(15) DEFAULT NULL,
  `size_down` varchar(15) DEFAULT NULL,
  `header_size` varchar(15) DEFAULT NULL,
  `speed_down` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1376 DEFAULT CHARSET=latin1;