没有契合的数据库迁移工具，用pymysql实现一个
妙音
posted @ 2019年1月01日 11:21
in mysql
, 4078 阅读
版本迭代少不了数据迁移，python有自己的数据库迁移工具migrate。如果有的是其它开发语言，或者没有契合的迁移工具。
怎么自己做一个？
环境说明
项目开发语言:java 数据库: mysql 迁移脚本: python python工具包: pymysql
实现思路
目标是将老版本的数据转为新版本的数据。
1. show tables 查询所有的表 ---得到所有表名 2. 注意trancate table_name 保证新表干净 -- 清环境 3. python在执行sql后可以在游标的属性cursor.description中看到表的字段信息 -- 得到所有字段名、类型 4. 查询数据生成insert语句。版本之间有变化的做特殊处理 --数据映射 5. 每生成500条commit一次到新库 -- 批量commit 6. mysqldump一份数据，在开发环境做好测试，没问题就可以用了。
前方有坑
留意数据精度问题
示例
import pymysql
FROM_DB = dict(
ip="xxxxxxxxxx",
username="xxxx",
password="xxxx",
db_name= "db1"
)
TO_DB = dict(
ip="xxxxxxxxxx",
username="xxxx",
password="xxxx",
db_name="db2"
)
class WebDB:
def __init__(self, ip, username, password, db_name='xxx'):
self.ip = ip
self.username = username
self.password = password
self.db_name = db_name
self.conn = None
self.cursor = None
def __enter__(self):
self.conn = pymysql.connect(self.ip, self.username, self.password, self.db_name)
self.cursor = self.conn.cursor()
return self
def __exit__(self, exctype, excvalue, traceback):
if self.cursor:
self.cursor.close()
if self.conn:
self.conn.close()
class MigrateDB(object):
def start(self):
self.clean()
self.import_data()
def clean(self):
with WebDB(**TO_DB)as db:
db.cursor.execute("show tables;")
for tables in db.cursor.fetchall():
table_name = tables[0]
db.cursor.execute("truncate %s;" % table_name)
def import_data(self):
with WebDB(**FROM_DB) as from_db:
with WebDB(**TO_DB) as to_db:
#所有表
from_db.cursor.execute("show tables;")
for from_tables in from_db.cursor.fetchall():
from_table_name = from_tables[0]
from_db.cursor.execute("select * from %s;" % from_table_name)
items = self.to_dict(from_db.cursor.fetchall(), from_db.cursor.description)
print("import table: %s" % from_table_name)
to_table_name = from_table_name
for i, item in enumerate(items):
# 需要映射转换的表，生成专门的sql
if from_table_name == "xxxx":
to_table_name = "xxxxx"
item = self.gen_insert_sql_for_xxx(item)
sql = self.gen_insert_sql(to_table_name, item)
to_db.cursor.execute(sql, args=item)
# 每500条commit一次
if i != 0 and i % 500 == 0:
to_db.conn.commit()
to_db.conn.commit()
def to_dict(self, rows, description):
"""
记录转为字典
:param rows:
:param description:
:return:
"""
for row in rows:
item = dict()
for i, field in enumerate(description):
field_name = field[0]
item[field_name] = row[i]
yield item
def gen_insert_sql(self, table_name, item):
sql = "insert into %s(%s) values(%s)"
keys = item.keys()
key_str = ",".join(keys)
value_str = ",".join(["%%(%s)s" % k for k, v in item.items()])
return sql % (table_name, key_str, value_str)
if __name__ == "__main__":
m = MigrateDB()
m.start()
来源
2020年10月22日 12:43
Just saying thanks will not just be sufficient, for the fantasti c lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates. 먹튀
2020年10月23日 13:36
Just saying thanks will not just be sufficient, for the fantasti c lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates. next one
2020年10月25日 14:14
This is my first time i visit here and I found so many interesting stuff in your blog especially it's discussion, thank you. website design for restaurant
2020年10月26日 21:32
Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for.
2020年10月28日 17:49
The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought you have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention. Best Japanese Porn Sites
2020年10月29日 02:11
Amazing knowledge and I like to share this kind of information with my friends and hope they like it they why I do.. Blog Comments Offpage SEO
2020年10月30日 19:17
Carry on the great operate, My spouse and i go through number of blogposts for this site along with I do think that your particular world wide web web site can be true exciting and possesses acquired encircles involving amazing data.
2020年11月04日 20:36
Just saying thanks will not just be sufficient, for the fantasti c lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates. Seo webagency
2020年11月04日 20:48
Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also. Leadgeneratie
2020年11月04日 20:56
After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article. Seo
2020年11月04日 21:05
Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained! Leads genereren
2020年11月09日 15:36
This particular papers fabulous, and My spouse and i enjoy each of the perform that you have placed into this. I’m sure that you will be making a really useful place. I has been additionally pleased. Good perform! find trusted hackers for hire online
2020年11月09日 20:43
Vitamin X are specialists in Anti-Ageing and Liquid Vitamins. Our range includes the award winning Magic Potion 10,000MG Liquid Marine Hydrolyzed Collagen Types 1 & 2 Anti-Ageing Drink which is packed full of super ingredients
2020年11月11日 02:40
I’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives indepth information. thanks for this nice article... https://www.jalalibd.com/
2020年11月18日 13:15
A debt of gratitude is in order for sharing the information, keep doing awesome... I truly delighted in investigating your site. great asset...
2020年11月19日 14:22
A couple of months ago I discovered another website that talked in depth about this topic. I am glad you were able to shed some light on what’s really happening out there. Some webistes are overtly biased towards things like this. Where do you think the industry is going in response to this?
2020年11月22日 20:34
Tummy Tuck Revision Deciding to have a tummy tuck surgery was a huge deal. You spent time thoroughly researching the procedure and choose a good surgeon. Even 12 months have passed, and you don't like the results, don’t worry about it. Tummy tuck revision is a surgical procedure that helps to address problems that have arisen from the initial tummy tuck surgery. What is a tummy tuck revision? Tummy tuck surgery (also called abdominoplasty) is a surgical procedure that removes extra fat from the lower and middle abdomen. Tummy tuck surgery can also flatten the abdominal area and tighten separate abdominal muscles. After tummy tuck surgery, people recommend having a tummy tuck revision surgery for different reasons, including Post-operative weight loss or weight gain or weight loss, a new pregnancy, or because they’re unhappy with the results from the first tummy tuck procedure. Why you may need to get tummy tuck revision surgery? 1- Scar revision: The final look and outcome of your tummy tuck scars will depend on the genetics of your scar formation and how your body heals. Although there are unique surgical procedures to reduce scarring, scar thickening and malposition can still occur. In certain cases, the Cosmetic Surgeon will be able to change the location of the scar, but this will depend on the skin tone and the amount of residual skin you have. 2- Umbilical distortion: Your umbilicus (belly button) can become distorted due to; - A visible scar around the umbilic will become noticeable if the umbilic is not rendered deep enough. - Umbilical stenosis. This occurs when the umbilicus becomes small after surgery. - An elongated slit emerges when too much skin has been removed, increasing the tension of the closure. When a new umbilic is formed, the vertical pull of the skin causes the shape to change from a round to an elliptical and elongated shape. - Extended umbilics can occur for a variety of reasons, including stretched skin in the radial direction, due to an inherent laxity of the skin, or if the new umbilic is not properly dimensioned. It is important to remember that the belly button should be no larger than 1.5 cm in vertical and horizontal measurements. 3- Unsatisfactory overall appearance of the tummy tuck: Some patients with a tummy tuck need to have raised abdominal skin to recreate the contours of the abdomen. It is important to remember that if you need to do so, the abdominal scar would be higher than average due to excess skin that has been already excreted. 4- Epigastric bulginess: You would have a bulging abdominal area if the stitching of the abdominal wall muscles above your belly button has not been appropriate. To address this problem, your Cosmetic Surgeon will suture the abdominal wall from the lower part of the chest to the lower part of the abdomen. Experience is the most significant factor in the choice of a cosmetic surgeon for any surgical procedure. We recommend that you look for a board-certified cosmetic surgeon who conducts the test procedure regularly and has proven safe outcomes. To have revision tummy tuck istanbul with our board-certified surgeon contact us!
2020年11月23日 13:28
This is my first time visit to your blog and I am very interested in the articles that you serve. Provide enough knowledge for me. Thank you for sharing useful and don't forget, keep sharing useful info: best review sites
2020年11月24日 19:02
This article is one of the best in the history of articles. I'm a antique 'Article' collector and I sometimes read them interesting
2020年11月28日 23:58
Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our.
2020年12月14日 15:01
This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
2020年12月15日 14:56
I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.
2020年12月16日 02:12
This is a good post. This post gives truly quality information. I’m definitely going to look into it..
2020年12月17日 20:03
I have browsed most of your posts. This post is probably where I got the most
2020年12月18日 16:17
Thanks so much for this information. I have to let you know I concur on several of the points you make here and others may require some further review. but I can see your viewpoint.
2020年12月19日 02:05
Hi, I reading your post. This is very nice article.I want to twite to my followers. sisteme de copiat please contact me
2020年12月19日 18:12
Hi, I reading your post. This is very nice article.I want to twite to my followers. sisteme de copiat
2020年12月20日 05:53
Thanks for your post. I’ve been thinking about writing a very comparable post over the last couple of weeks, I’ll probably keep it short and sweet and link to this instead if thats cool. Thanks. Crawley Accountants
2020年12月20日 06:01
Superior post, keep up with this exceptional work. It's nice to know that this topic is being also covered on this web site so cheers for taking the time to discuss this! Thanks again and again! East Grinstead Accountants
2020年12月21日 16:49
Took me time to understand all of the comments, but I seriously enjoyed the write-up. It proved being really helpful to me and Im positive to all of the commenters right here! Its constantly nice when you can not only be informed, but also entertained! I am certain you had enjoyable writing this write-up.
2020年12月22日 21:50
Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info. Airconditioning Ham
2020年12月22日 21:53
Thanks for your post. I’ve been thinking about writing a very comparable post over the last couple of weeks, I’ll probably keep it short and sweet and link to this instead if thats cool. Thanks. Webdesign
2020年12月22日 21:54
This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. Webdesign
2020年12月22日 21:56
Your content is nothing short of bright in many forms. I think this is friendly and eye-opening material. I have gotten so many ideas from your blog. Thank you so much. Airco installateur Bornem
2020年12月22日 21:59
This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here keep up the good work Webdesign Limburg
2020年12月22日 22:02
This article was written by a real thinking writer without a doubt. I agree many of the with the solid points made by the writer. I’ll be back day in and day for further new updates. Airconditioning Wellen
2020年12月22日 22:05
Wow, What an Outstanding post. I found this too much informatics. It is what I was seeking for. I would like to recommend you that please keep sharing such type of info.If possible, Thanks. Warmtepomp
2020年12月22日 22:08
This article was written by a real thinking writer without a doubt. I agree many of the with the solid points made by the writer. I’ll be back day in and day for further new updates. Airconditioning Nieuwerkerken
2020年12月22日 22:12
I know this is one of the most meaningful information for me. And I'm animated reading your article. But should remark on some general things, the website style is perfect; the articles are great. Thanks for the ton of tangible and attainable help. Warmtepomp installatie
2020年12月22日 22:15
You delivered such an impressive piece to read, giving every subject enlightenment for us to gain information. Thanks for sharing such information with us due to which my several concepts have been cleared. Airconditioning Heers
2020年12月22日 22:20
You there, this is really good post here. Thanks for taking the time to post such valuable information. Quality content is what always gets the visitors coming. Warmtepompinstallateur
2020年12月22日 22:23
Excellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing. Rendement zonnepanelen
2020年12月22日 22:27
I appreciate this article for the well-researched content and excellent wording. I got so interested in this material that I couldn’t stop reading. Your blog is really impressive. Airconditioning Zutendaal
2020年12月22日 22:30
Great write-up, I am a big believer in commenting on blogs to inform the blog writers know that they’ve added something worthwhile to the world wide web!.. Zonnepanelen kopen
2020年12月22日 23:02
Wow, excellent post. I'd like to draft like this too - taking time and real hard work to make a great article. This post has encouraged me to write some posts that I am going to write soon. Zonnepanelen prijs
2020年12月22日 23:13
Wow, What an Outstanding post. I found this too much informatics. It is what I was seeking for. I would like to recommend you that please keep sharing such type of info.If possible, Thanks. Warmtepomp
2020年12月22日 23:15
This article was written by a real thinking writer without a doubt. I agree many of the with the solid points made by the writer. I’ll be back day in and day for further new updates. Airconditioning Nieuwerkerken
2020年12月22日 23:24
i read a lot of stuff and i found that the way of writing to clearifing that exactly want to say was very good so i am impressed and ilike to come again in future.. Airconditioning Beringen
2020年12月22日 23:29
I have been impressed after read this because of some quality work and informative thoughts. I just want to say thanks for the writer and wish you all the best for coming! Your exuberance is refreshing. Zonnepanelen prijs
2020年12月22日 23:34
Hello, I have browsed most of your posts. This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this. Are you aware of any other websites on this subject. Airconditioning Leopoldsburg
2020年12月22日 23:40
Only strive to mention one's content can be as incredible. This clarity with your post is superb! Thanks a lot, hundreds of along with you should go on the pleasurable get the job done. Zonnepanelen plaatsing
2020年12月22日 23:44
Remarkable article, it is particularly useful! I quietly began in this, and I'm becoming more acquainted with it better! Delights, keep doing more and extra impressive! Airconditioning Geel
2020年12月22日 23:48
It is a good site post without fail. Not too many people would actually, the way you just did. I am impressed that there is so much information about this subject that has been uncovered and you’ve defeated yourself this time, with so much quality. Good Works! Lead generation met SEO
2020年12月22日 23:51
Thank you for sharing a bunch of this quality contents, I have bookmarked your blog. Please also explore advice from my site. I will be back for more quality contents. Airconditioning balen
2020年12月22日 23:54
Today, I was just browsing along and came upon your blog. Just wanted to say good blog and this article helped me a lot, due to which I have found exactly I was looking. SEO no cure no pay
2020年12月22日 23:58
It is truly a well-researched content and excellent wording. I got so engaged in this material that I couldn’t wait reading. I am impressed with your work and skill. Thanks. Airconditioning Diepenbeek
2020年12月23日 00:02
Excellent website! I adore how it is easy on my eyes it is. I am questioning how I might be notified whenever a new post has been made. Looking for more new updates. Have a great day! Webdesign Limburg
2020年12月25日 13:10
Please share more like that.
2020年12月25日 19:30
nice bLog! its interesting. thank you for sharing...
2020年12月28日 01:09
I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.
2020年12月28日 18:26
I have a mission that I’m just now working on, and I have been at the look out for such information
2020年12月30日 17:23
Hi, I reading your post. This is very nice article.I want to twite to my followers. news-page.php?i=kiu-organizes-19th-graduation-ceremony
2021年1月08日 15:37
This article is one of the best in the history of articles. I'm a antique 'Article' collector and I sometimes read them interesting
2021年1月10日 16:30
I truly appreciate this post. I have been looking everywhere for this! Thank goodness I found it on Bing. You have made my day! Thank you again
2023年9月19日 16:57
When you use a genuine service, you will be able to provide instructions, share materials and choose the formatting style. slot nexus gacor
2023年9月22日 21:26
This is such a great resource that you are providing and you give it away for free. Avaliador Premiado
2023年10月04日 23:37
Friend, this web site might be fabolous, i just like it. บาคาร่า
2023年10月10日 02:01
I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.. taruhan olahraga
2023年10月17日 17:47
All the contents you mentioned in post is too good and can be very useful. I will keep it in mind, thanks for sharing the information keep updating, looking forward for more posts.Thanks AVALIADOR PREMIADO
2023年10月21日 03:54
Thank you for the update, very nice site.. JOGO DO TIGRE
2023年10月22日 16:41
Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. บาคาร่าเว็บไหนดี2022
2023年11月07日 18:32
Thanks for taking the time to discuss that, I feel strongly about this and so really like getting to know more on this kind of field. Do you mind updating your blog post with additional insight? It should be really useful for all of us. FORTUNE TIGER
2024年1月09日 15:15
Shop high quality <a href="https://hellstarbrand.store/hoodie/">Hellstar Hoodie</a> at a sale price. Get up to 30% off online from uk store.
2024年1月10日 17:26
Shop high quality <a href="https://carsickoclothing.com//">Carsicko Beanie</a> at a sale price. Get up to 30% off online from uk store.
2024年1月10日 17:53
Shop high quality <a href="https://carsickoclothing.com/hoodie/">Carsicko Hoodie</a> at a sale price. Get up to 30% off online from uk store.
2024年1月27日 03:00
Shop the high quality <a href="https://spider555clothing.com/hoodie/">555 Hoodie</a> at a sale price.get up to 30% off on online store.Fast shipping worldwild.
2024年2月28日 22:52
hodgdon tite group offers the largest selection of quality smokeless propellants for any reloading application.TITEGROUP is a double base, spherical propellant that was designed for accuracy. Because of the unique design, this powder provides flawless ignition with all types of primers including the lead-free versions. Unlike pistol powders of the past, powder position in large cases (45 Colt, 357 Magnum and others) has virtually no effect on velocity and performance. Cowboy Action, Bullseye and Combat Shooters should love this one! TITEGROUP has it all, low charge weight, clean burning, mild muzzle report and superb, uniform ballistics.