python3实现读取一个数据库中的数据填充到另一个数据库中

python3实现读取一个数据库中的数据填充到另一个数据库中

#!/usr/bin/python# -*- coding: UTF-8 -*-import pymysql as MySQLdbimport redis#打开redis连接r=redis.Redis(host='localhost',port=6379,decode_responses=True)# 打开数据库连接db = MySQLdb.connect("localhost", "root", "test", "jian", charset='utf8',cursorclass = MySQLdb.cursors.DictCursor )db_test = MySQLdb.connect("localhost", "root", "test", "test", charset='utf8' )# 使用cursor()方法获取操作游标cursor = db.cursor()cursor_test=db_test.cursor()# SQL 查询语句sql = "SELECT * FROM person where id < 5"try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: name=row["name"] sex=row["sex"] birthday=row["birthday"] degree=row["degree"] top_education_field=row["top_education_field"] marital_status=row["marital_status"] current_country=row["current_country"] current_province=row["current_province"] current_city=row["current_city"] current_district=row["current_district"] current_address=row["current_address"] household_registration=row["household_registration"] id_card=row["id_card"] mobile=row["mobile"] email=row["email"] expected_country=row["expected_country"] expected_province=row["expected_province"] expected_city=row["expected_city"] expected_district=row["expected_district"] expected_address=row["expected_address"] expected_salary_min=row["expected_salary_min"] expected_salary_max=row["expected_salary_max"] current_situation=row["current_situation"] standard_job_industry=row["standard_job_industry"] standard_job_function=row["standard_job_function"] expected_job_industry=row["expected_job_industry"] expected_job_function=row["expected_job_function"] self_evaluation=row["self_evaluation"] sql_insert = "INSERT INTO person (name,sex,birthday,degree,top_education_field,marital_status,current_country,current_province,current_city,current_district,current_address,household_registration,id_card,mobile,email,expected_country,expected_province,expected_city,expected_district,expected_address,expected_salary_min,expected_salary_max,current_situation,standard_job_industry,standard_job_function,expected_job_industry,expected_job_function,self_evaluation) VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(name,sex,birthday,degree,top_education_field,marital_status,current_country,current_province,current_city,current_district,current_address,household_registration,id_card,mobile,email,expected_country,expected_province,expected_city,expected_district,expected_address,expected_salary_min,expected_salary_max,current_situation,standard_job_industry,standard_job_function,expected_job_industry,expected_job_function,self_evaluation) cursor_test.execute(sql_insert) person_id=row["id"] sql1="select * from person_education_experience where person_id='%d'"%(person_id) new_person_id=db_test.insert_id() print("^"*30) print(sql1) # 执行SQL语句 cursor.execute(sql1) # 获取所有记录列表 print("&"*30) results1 = cursor.fetchall() print(results1) if results1 != None: for row1 in results1: #print(row1) p_id=new_person_id start_time=row1["start_time"] end_time=row1["end_time"] institution_name=row1["institution_name"] education_field=row1["education_field"] education_description=row1["education_description"] education_level=row1["education_level"] sql_edu="insert into person_education_experience (person_id,start_time,end_time,institution_name,education_field,education_description,education_level) values('{}','{}','{}','{}','{}','{}','{}')".format(p_id,start_time,end_time,institution_name,education_field,education_description,education_level) cursor_test.execute(sql_edu) sql2="select * from person_work_experience where person_id='%d'"%(person_id) print("#"*30) # 执行SQL语句 cursor.execute(sql2) # 获取所有记录列表 results2 = cursor.fetchall() if results2 != None: for row2 in results2: #print(row2) p_id=new_person_id start_time=row2["start_time"] end_time=row2["end_time"] company_name=row2["company_name"] job_title=row2["job_title"] job_description=row2["job_description"] salary_min=row2["salary_min"] salary_max=row2["salary_max"] sql_work="insert into person_work_experience (person_id,start_time,end_time,company_name,job_title,job_description,salary_min,salary_max) values('{}','{}','{}','{}','{}','{}','{}','{}')".format(p_id,start_time,end_time,company_name,job_title,job_description,salary_min,salary_max) cursor_test.execute(sql_work) except: print("Error: unable to fecth data")# 关闭数据库连接db.close()db_test.close()

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。