IPv6网址保存数据库代码

storeIpv6ToDB.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import urllib2
from pyquery import PyQuery as pyq
import MySQLdb

'''
def download():
    content = urllib2.urlopen('http://www.daoiqi.com/iptv6.html').read()
    print content
    fileHandle = open ( 'iptv6.html', 'w' )
    fileHandle.write(content)
    fileHandle.close()
'''

'''
读取本地文本，比从网络上读取快些。方便测试使用
'''
def readFile(filename):
    fileHandle = open(filename)
    text = fileHandle.read()
    fileHandle.close()
    return text

'''
处理每个频道的数据
'''
def _channelli(dom):
    channelname = pyq(dom).find(".iptvChannelTitleSpan")
    channel = {}
    channel['name'] = channelname.text()
    channel['source'] = []

    school = pyq(dom).find("li>a")

    for i in school:
        link = pyq(i).attr("href")
        schoolname = pyq(i).text()

        ch = { 'school_name' : schoolname , 'link' : link   }

        channel['source'].append(ch)

   # print channel
    return channel
#end _channelli


'''
{
    'name' : 'CCTV1',
    'source' : [
                { 'school_name': '中国科技大学' , 'link': 'mms://tv/cctv1' }
                ]
}

'''
def store2db(data):
    try:
        conn=MySQLdb.connect(host='localhost',user='root',passwd='root',port=3306,charset="utf8")
        cur=conn.cursor()

        conn.select_db('xxxdbname')
        for source in data['source']:
            source['showname'] = source['school_name']
            source['channel_name'] = data['name']
            #print source

            qmarks = ', '.join(['%s'] * len(source))
            columns = ', '.join(source.keys())
            qry = "Insert Into tvlist (%s) Values (%s)" % (columns, qmarks)
            #print source.values()
            n = cur.execute(qry, source.values())
            #end for

        print "======= end %s success ====" % data['name']

    except MySQLdb.Error,e:
        print e

    conn.commit() # commit transite
    conn.close()

##end store()

if __name__ == '__main__':
    text = readFile('iptv6.html')
#print text

    d = pyq(text)
    channel = d("ul#cctvmms>li, ul#othermms>li")

    for i in channel:
        data = _channelli(i)
        store2db(data)

    print "end all"

#end if

updatesql.sql

-- 数据库表格的创建
CREATE TABLE `tvlist` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `channel_name` varchar(255) DEFAULT NULL COMMENT '频道名称',
  `showname` varchar(255) DEFAULT NULL COMMENT '显示的名字(比如北邮1)',
  `school_name` varchar(255) DEFAULT NULL COMMENT '学校名称',
  `link` varchar(255) DEFAULT NULL,
  `isShow` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1显示.是否显示，可能网址已经废弃了',
  PRIMARY KEY (`id`),
  KEY `channel` (`channel_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=720 DEFAULT CHARSET=utf8

--在storeIpv6ToDB.py保存到数据库中的时候，执行以下步骤
--更改数据库的一些基本信息，将学校在数据库中使用全名存放
update tvlist set school_name = '中国石油大学(华东)' where school_name = '中国石油大学(华东)(需要安装解码器)';
update tvlist set school_name = '北京邮电大学' where school_name = '北邮';
update tvlist set school_name = '中国科技大学' where school_name = '中科大';
update tvlist set school_name = '大连理工大学' where school_name = '大连理工';