Python3 实现从MySQL数据库中将记录转移到Sqlite中

素颜马尾好姑娘i 2021-12-12 22:07 296阅读 0赞

#导入数据库驱动
    import mysql.connector
    import sqlite3
    import pymysql
    import re
    '''
    配置mysql数据库
    '''
    
    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="root",  # 数据库密码
        database = "suibian"
    )
    #创建mysql游标
    mycursor = mydb.cursor()
    #创建sqlite游标
    #一定要填写绝对路径
    conn = sqlite3.connect("D:/桌面/VOA/0.VOA常速1 (3)/VOA常速/bin/Debug/ls.sqlite")
    cursor = conn.cursor()
    #插入一条记录
    sql = "INSERT INTO bbc (Id,Pic,Title,Title_cn,Sound,Type,Category,CreateTime,Flag,DescCn,CategoryName) VALUES "
    mycursor.execute("select * from  newstitle")
    myresult = mycursor.fetchall()
    for x in myresult:
        replacer = RegexpReplacer()
        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)
        sql = "INSERT INTO bbc(Id,Pic,Title,Title_cn,Type,      Category,  CreateTime,Flag,DescCn,CategoryName,Sound) " \
                      "VALUES (%s,'%s','%s',  '%s'  , '%s',        %s      ,'%s'       ,%s , '%s',     '%s'   ,'%s')" %\
                             (x[0],pic,title,title_cn,"LiveScience",1,     creatTime,x[16],DescCn,CategoryName,x[8])
        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()

#导入数据库驱动
    import mysql.connector
    import sqlite3
    import pymysql
    import re
    '''
    配置mysql数据库
    '''
    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="root",  # 数据库密码
        database = "suibian"
    )
    #创建mysql游标
    mycursor = mydb.cursor()
    #创建sqlite游标
    #一定要填写绝对路径
    conn = sqlite3.connect("D:/桌面/VOA/0.VOA常速1 (3)/VOA常速/bin/Debug/ls.sqlite")
    cursor = conn.cursor()
    #插入一条记录
    sql = "INSERT INTO bbc (Id,Pic,Title,Title_cn,Sound,Type,Category,CreateTime,Flag,DescCn,CategoryName) VALUES "
    sql2 = "INSERT INTO bbc (NewsID,ParaId,IdIndex,Timing,EndTiming,Sentence,ImgWords,ImgPath,ImgDesc,Sentence_cn,Sentence_jp,primary key, AudioSrc)"
    
    # mycursor.execute("select * from  newstitle")
    mycursor.execute("select * from newstext")
    
    myresult = mycursor.fetchall()
    for x in myresult:
        rep = RegexpReplacer()
        Sentence = rep.replace(x[5])
        ImgPath = rep.replace(x[7])
        print("ImgPath:", ImgPath)
        ImgDesc = rep.replace(x[8])
        sql = "INSERT INTO newstext(NewsID,ParaId,IdIndex,Timing,EndTiming,Sentence,ImgWords,ImgPath,ImgDesc,Sentence_cn,Sentence_jp, AudioSrc) " \
                             "VALUES ('%s'   ,'%s',      '%s',    '%s'  , '%s',    '%s'   ,   '%s'  ,  '%s' , '%s',     '%s'    ,  '%s',  '%s')" % \
              (x[0], x[1],   x[2],  x[3] , x[4] , Sentence, x[6], pymysql.escape_string(ImgPath), ImgDesc, x[9], x[10] , x[12])
        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","打印：：：：：\n",  sql)
            conn.rollback()
    sql = "select * from newstext"
    cursor.execute(sql)
    values = cursor.fetchall()
    for value in values:
        print(value)
    
    conn.close()
    cursor.close()
    mycursor.close()