Python抓取数据并存入到mysql

﹏ヽ暗。殇╰゛Y 2021-12-22 03:43 266阅读 0赞

#!/usr/bin/env python
    #coding:utf-8
    '''
    Created on Jul 21, 2013
    
    @author: belong
    '''
    
    import urllib
    import re
    import datetime
    import MySQLdb
    import time
    
    start_url = "https://xively.com/search?q=temperature&category="
    
    '''
    工具类
    '''
    class Tools:
        def write_log(self, level, info):
            print '[' + level + ']' + info
        
        #正则表达式匹配，返回值：匹配结果（字符串类型）
        #pattern表示正则表达式，single为布尔型变量，       
        def match_regex(self, pattern, src, single):
            result = ""
            com = re.compile(pattern, re.I|re.DOTALL)
            matchers = com.findall(src)
            for matcher in matchers:
                result += matcher + " "
                if single:  # 如果只需要一个则结束
                    break
            return result.strip()  #strip函数返回去掉两端空格的字符串
        
        #将其他字符集转化为unicode字符集，返回值：unicode解码得到的字符串
        #unicode相当与asciistring.decode('ascii')
        def to_unicode(self, s, charset):
            if charset != "":
                s = unicode(s, charset)
            return s
        
    '''**********************************************************'''       
    '''
    url爬取，页面获取类
    '''          
    class Crawler:
        # urlùurls
        def get_url(self, url):
            fp = urllib.urlopen(url)
        #     pattern = re.compile("http://.*?\.com",re.I)
    #         pattern = re.compile(r'[a-zA-z]+://[^\s]*\.html?', re.I)
            pattern = re.compile(r'https://xively.com/feeds/\d+', re.I)
            while True:
                data = fp.read()
                if data:
                    urls = pattern.findall(data)
                else:
                    break
            fp.close()
            return urls
        
        #获取html页面
        def get_page(self,url):
            fp = urllib.urlopen(url)#获取socket._fileobject object对象
            page = fp.read()#读取整个html页面的字符串
            return page
        
        # 下载url对应网页
        def download_url(self, url, filename):
            Tools().write_log("Info", "开始下载")
            f = open(filename, 'w')#以写的方式打开文件filename
            f.write(self.get_page(url));#将页面字符串写入文件中
            f.close()
            Tools().write_log("Info", "网页下载成功")
        
        # 广度优先遍历
        def broad_traverse(self, start_url, number):
            print "\n"*2
            print '====='*20
            Tools().write_log("Info", "开始遍历")
            start_time = datetime.datetime.now()#遍历开始时间
            visited = []#存放已访问过的url的列表
            unvisited = []#存放未访问过的url的列表
            unvisited.append(start_url)
            while len(unvisited):#当未访问的url数目不为0时，执行
                if len(visited) < number:#当已经访问的url数目少于预设值时，执行
                    url = unvisited.pop(0);#pop(i)返回值：索引为i的元素，并将其从列表中删除
                    print "---"*20
                    print url, len(visited),len(unvisited)
                    i = len(visited)
                    self.download_url(url, str(i) + '.html')#将url整个页面下载本地硬盘
                    visited.append(url)#加入已访问的url列表
                    url_list = self.get_url(url)#提取url页面的其他所有url链接，有重复
                    for eachlink in url_list:
                        if ((unvisited.count(eachlink) == 0) & (visited.count(eachlink) == 0)):
                            unvisited.append(eachlink)#具有唯一性的url加入问访问的url列表
                else:
                    break
            Tools().write_log("Info", "遍历成功")
            end_time = datetime.datetime.now()#遍历结束时间
            interval = end_time - start_time
            Tools().write_log("Info", "遍历时间：%s" % (interval))
            print "====="*20
            print "\n"*2
            return visited 
    
    '''**********************************************************'''       
    '''
    内容提取类
    '''
    class DataExtractor():
        
        # 1设备id
        def get_id(self, data):
            device_id = Tools().match_regex(r'id:(\d*?),', data, False)
            return device_id
            
        # 2设备title
        def get_title(self, data):
            title = Tools().match_regex(r'title:(.*?),p', data, False)
            return title
        
        # 3设备描述
        def get_description(self, data):
            description = Tools().match_regex(r'description:(.*?),', data, False)
            return description
        
        # 4设备状态
        def get_status(self, data):
            status = Tools().match_regex(r'status:(.*?),', data, True)
            return status
        
        # 5设备位置
        def get_location(self, data):
            location = Tools().match_regex(r'location:{(.*?)}', data, True)
            return location
        
        # 获取页面字符串编码类型
        def get_charset(self, data):
            charset = Tools().match_regex(r'charset="(.*?)"', data, True)
            return charset
        
        # 设备全部信息
        def get_info(self, url):
            try:
                data = Crawler().get_page(url)
                charset = self.get_charset(data)
                data = Tools().to_unicode(data, charset)#将页面unicode解码
                data = Tools().match_regex(r'JSON.parse(.*?);', data, False)
                data = re.sub(r'\\\"', "", data)
                print data
            except:
                Tools().write_log("Error", url + "获取页面失败")
                raise
            rst = {}  # rst字典
            rst['id'] = self.get_id(data)
            rst['title'] = self.get_title(data)
            rst['description'] = self.get_description(data)
            rst['status'] = self.get_status(data)
            rst['location'] = self.get_location(data)
            return rst
        
        def get_all(self,url_list):
            rst_list = []
            for url in url_list:
                rst = self.get_info(url)
                rst_list.append(rst)
            return rst_list
        
    '''**********************************************************'''               
    '''
    存入数据库
    '''
    class DBAdapter:
    
        def db_connect(self):
            conn = MySQLdb.connect(host = "localhost",
                                   user = "root",
                                   passwd = "123456",
                                   charset = "utf8")
            return conn
        
        def run(self, url_list):
            conn = self.db_connect()#mysql
            cur = conn.cursor()#获取游标
    #         sql1 = "CREATE DATABASE db_test DEFAULT CHARSET utf8 COLLATE utf8_general_ci"
    #         cur.execute(sql1)#utf8db_test
    
            #选择数据库
            conn.select_db('db_test')
            
            sql1 = "drop table pageinfo"
            cur.execute(sql1)
            
            #创建表pageinfo
            sql2 = "create table if not exists pageinfo(\
                        id int,\
                        url varchar(80),\
                        device_id varchar(30),\
                        title varchar(80),\
                        description varchar(100),\
                        status varchar(10),\
                        location varchar(200))"
            cur.execute(sql2)
            
            count = 0
            rst_list = DataExtractor().get_all(url_list)
            sql3 = "insert into pageinfo values(%s,%s,%s,%s,%s,%s,%s)"
            for i in range(0,len(rst_list)):
                param = (count,
                         url_list[i],
                         rst_list[i]['id'],
                         rst_list[i]['title'],
                         rst_list[i]['description'],
                         rst_list[i]['status'],
                         rst_list[i]['location'])
                cur.execute(sql3,param)#param是元组
                count += 1
            print "\n"*2
            print '====='*20
            print "获取所有结果:"
            cur.execute('select * from pageinfo')
              
            #重置游标位置，0为偏移量，mode=absolute | relative,默认为relative,   
            cur.scroll(0,mode='absolute')  
            results = cur.fetchall()
            for r in results:
                print '%s\t%s\t%s\t%s\t%s\t%s\t%s' % r
            
            cur.close()
            conn.commit()        
            conn.close()
            
    '''**********************************************************'''       
    '''
    主函数
    '''         
    def main():
        begin = time.time()#程序运行起始时刻
        url_list = Crawler().broad_traverse(start_url, 5)#获取遍历之后的url列表
        dbAdapter = DBAdapter()#实例化DBAdapter类
        dbAdapter.run(url_list)
        end = time.time()#程序结束时刻
        print "总共消耗时间： ",(end-begin)
    
    if __name__ == "__main__":
        main()