mysql python3 转移_Python3 实现 从MySQL数据库中将记录转移到Sqlite中

#导入数据库驱动

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()