转成sqlite数据库中

- 日理万妓 2021-11-09 08:58 299阅读 0赞

[tobbc.py][]

#导入数据库驱动
    import mysql.connector
    import sqlite3
    import pymysql
    import re
    '''
    配置mysql数据库
    '''
    SqlitePath = "C:/Users/kang9/Desktop/BBC/bin/Debug/Ted.sqlite"
    replacement_patterns = [
    (r'won\'t', 'will not'),
    (r'can\'t', 'cannot'),
    (r'i\'m', 'i am'),
    (r'ain\'t', 'is not'),
    (r'(\w+)\'ll', '\g<1> will'),
    (r'(\w+)n\'t', '\g<1> not'),
    (r'(\w+)\'ve', '\g<1> have'),
    (r'(\w+)\'s', '\g<1> is'),
    (r'(\w+)\'re', '\g<1> are'),
    (r'(\w+)\'d', '\g<1> would'),
        (r"s\'",'s'),
        (r"\'", ''),
    (r"O\'", ''),
        (r"o\'", '')]
    
    class RegexpReplacer(object):
        def __init__(self, patterns=replacement_patterns):
            self.patterns = [(re.compile(regex), repl) for (regex, repl) in patterns]
        def replace(self, text):
            s = text
            for (pattern, repl) in self.patterns:
                (s, count) = re.subn(pattern, repl, s)
            return s
    
    
    
    mydb = mysql.connector.connect(
        host="localhost",  # 数据库主机地址
        user="root",  # 数据库用户名
        passwd="iyu123456",  # 数据库密码
        database = "newstest"
    )
    #创建mysql游标
    mycursor = mydb.cursor()
    #创建sqlite游标
    #一定要填写绝对路径
    conn = sqlite3.connect(SqlitePath)
    cursor = conn.cursor()
    #插入一条记录
    mycursor.execute("select * from  newstitle")
    myresult = mycursor.fetchall()
    replacer = RegexpReplacer()
    for x in myresult:
        pic = replacer.replace(x[10])
        title = replacer.replace(x[1])
        # title_cn = replacer.replace(x[5])
        creatTime = replacer.replace(x[12])
        DescCn = replacer.replace(x[2])
        CategoryName = replacer.replace(x[6])
    
        # print("x[0]:",x[0])
        # print("pic:",pic)
        # print("title:",title)
        # # print("title_cn:",title_cn)
        # print("x[8]:",x[8])
        # print("creatTime:",creatTime)
        # print("x[16]:",x[16])
        # print("DescCn:",DescCn)
        # print("CategoryName:",CategoryName)
        print("ReadCount:",x[14])
        print("ReadCount:",x[25])
        sql = "INSERT INTO bbc(Id,Pic,Title,Title_cn,Type,      Category,  CreateTime,Flag,DescCn,CategoryName,Sound,ReadCount) " \
                      "VALUES (%s,'%s','%s',  '%s'  , '%s',        '%s'      ,'%s'       ,%s , '%s',     '%s'   ,'%s','%s')" %\
                             (x[0],pic,title,x[5],x[25],CategoryName,creatTime,x[16],DescCn,CategoryName,x[8],x[14])
        try:
            cursor.execute(sql)
            print("\033[0;36;40m", "插入新闻内容成功", "\033[0m")
            conn.commit()
        except Exception as e:
            print("\033[0;31;40m", "插入失败：", e, "\033[0m","打印：：：：：",  sql)
            conn.rollback()
    
    
    
    
    sql = "select * from bbc"
    cursor.execute(sql)
    values = cursor.fetchall()
    for value in values:
        print(value)
    
    conn.close()
    # cursor.close()
    mycursor.close()

[tobbcdetail.py][]

# coding: utf-8
    import sqlite3
    import mysql.connector
    import mysql
    import pymysql
    import time
    import json
    import re
    
    SqlitePath = "C:/Users/kang9/Desktop/BBC/bin/Debug/Ted.sqlite"
    replacement_patterns = [
    (r'won\'t', 'will not'),
    (r'can\'t', 'cannot'),
    (r'i\'m', 'i am'),
    (r'ain\'t', 'is not'),
    (r'(\w+)\'ll', '\g<1> will'),
    (r'(\w+)n\'t', '\g<1> not'),
    (r'(\w+)\'ve', '\g<1> have'),
    (r'(\w+)\'s', '\g<1> is'),
    (r'(\w+)\'re', '\g<1> are'),
    (r'(\w+)\'d', '\g<1> would'),
        (r"s\'",'s'),
        (r"\'", ''),
    (r"O\'", ''),
        (r"o\'", '')]
    
    class RegexpReplacer(object):
        def __init__(self, patterns=replacement_patterns):
            self.patterns = [(re.compile(regex), repl) for (regex, repl) in patterns]
        def replace(self, text):
            s = text
            for (pattern, repl) in self.patterns:
                (s, count) = re.subn(pattern, repl, s)
            return s
    
    replacer = RegexpReplacer()
    
    conn_from = pymysql.connect("localhost", "root", "iyu123456", "newstest", charset='utf8')
    print("ok")
    _from = conn_from.cursor()
    conn_to = sqlite3.connect(SqlitePath)
    print("连接sqlite3成功")
    _to = conn_to.cursor()
    
    # 处理detail表格
    count3 = 0
    sql_4 = 'SELECT DISTINCT NewsID FROM newstext'
    _from.execute(sql_4)
    idlist = _from.fetchall()
    for id in idlist:
        sql_5 = 'select Id from bbcdetail where Id = ?'
        params_5 = (id)
        _to.execute(sql_5, params_5)
        if not _to.fetchall():
            itemid = id[0] # 所有在newstitle 里面的newsId
            sql_3 = 'SELECT EndTiming, ParaId,IdIndex, Sentence_cn,Timing,Sentence,NewsID,AudioSrc FROM newstext WHERE NewsID = %s'
            params_3 = (itemid)
            _from.execute(sql_3, params_3)
            d = _from.fetchall()   #d此时是每一整篇新闻分成的句子及其后面数据为一个元组构成的大元组
            d = list(d)
            paranum = 0
            list1 = list(range(len(d)))
            for p in d:
                # 条件表达式
                a = ' ' if p[0] is None else p[0]
                b = ' ' if p[4] is None else p[4]
                list1[paranum] = {"EndTiming":a,"ParaId": str(p[1]), "IdIndex":str(p[2]), "Sentence_cn": p[3],
                                  "Timing":b,"Sentence": p[5],}
                paranum += 1
            data = [json.dumps(w, ensure_ascii=False) for w in list1]
    
            print("id is the ",itemid)
    
            sql_33 = 'SELECT Category,CreatTime,Title,Sound,Pic,Flag,DescCn,Title_cn,ReadCount FROM newstitle WHERE NewsID = %s'
            params_33 = (itemid)
            try:
                _from.execute(sql_33, params_33)
                dd = _from.fetchall()  # d此时是每一整篇新闻分成的句子及其后面数据为一个元组构成的大元组
                # print("Flag",dd[0][5],",DescCn",dd[0][6],",Title_cn",dd[0][7],",ReadCount",dd[0][8])
            except Exception as e:
                print("错误是",e)
    
            aha = ' ' if dd[0][7] is None else dd[0][7]
            descontent = dd[0][6]
            descontent = str(descontent).replace('"',"'")
            # result = '{"result": 1,"data": [' + ','.join(data) + '],"id":' + str(itemid)+',"title":{'+ '"Category":'+dd[0][0]+',"CreateTime":'+dd[0][1]+',"Title":'+dd[0][2]+',"Sound":'+ dd[0][3]+'"Pic":'+dd[0][4]+',"Type":'+dd[0][0]+',"DescCn":'+(dd[0][5])+'"Title_cn:"'+dd[0][6]+',"ReadCount":'+str(dd[0][7])+'}}'
            result = '{"result": 1,"data": [' + ','.join(data) + '],"id":"' + str(itemid)+'","title":{'+ '"Category":"'+dd[0][0]+'","CreateTime":"'+dd[0][1]+'","Title":"'+dd[0][2]+'","Sound":"'+ dd[0][3] +'","Pic":"'+dd[0][4]+'","Flag":"'+str(dd[0][5])+'","Type":"'+dd[0][0]+'","DescCn":"'+descontent+'","Title_cn":"'+aha+'","series":"0","CategoryName":"'+dd[0][0]+'","id":"'+str(itemid)+'","ReadCount":"'+str(dd[0][8])+'"},"message": "success"}'
    
            pic = replacer.replace(result)
            sql_6 = "insert into bbcdetail (Id, detail) values ('%s', '%s')" % (id[0],  pic)
            try:
                _to.execute(sql_6)
                conn_to.commit()
            except Exception as e:
                print("这又是一次错误",e)
            count3 += 1
            print("插入detail成功")
    print("今天插入detail内容{}条".format(count3))

[tobbc.py]: http://tobbc.py
[tobbcdetail.py]: http://tobbcdetail.py