Python Forum
Pushing mysql db data from one schema to another schema?
Thread Rating:
  • 1 Vote(s) - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pushing mysql db data from one schema to another schema?
#1
Hi Experts!

I have created a python script for extracting,transforming the product related data from different merchants and after this I am loading the required data into the merchantdb schema in mysql db.

Now after this I want to push the data from merchantdb schema to prodsearchdb schema.

I have written initial version of the script as below-
import pymysql                                    
from sqlalchemy import create_engine, text        
import pandas as pd

credentials = {
    "UAT": {
        "host": 'AWS_HOST_NAME_UAT',
        "port": PORT_NUM,
        "read_db": 'SCHEMA_NAME_1_UAT',
        "write_db": 'SCHEMA_NAME_2_UAT',
        "user": 'admin',
        "password": 'PASSWORD'
    },
    "Prod": {
        "host": 'AWS_HOST_NAME_PROD',
        "port": PORT_NUM,
        "read_db": 'SCHEMA_NAME_1',
        "write_db": 'SCHEMA_NAME_1',
        "user": 'USER_ID',
        "password": 'PASSWORD'
    }
}

# Create connectors
con_uat = create_engine('mysql+pymysql://' +
                             credentials['UAT']['user'] + ':' +
                             credentials['UAT']['password'] + '@' +
                             credentials['UAT']['host'] + ':' +
                             str(credentials['UAT']['port']) + '/' +
                             credentials['UAT']['read_db'] +
                             "?charset=utf8",
                             echo=False)

con_prod_read = create_engine('mysql+pymysql://' +
                             credentials['Prod']['user'] + ':' +
                             credentials['Prod']['password'] + '@' +
                             credentials['Prod']['host'] + ':' +
                             str(credentials['Prod']['port']) + '/' +
                             credentials['Prod']['read_db'] +
                             "?charset=utf8",
                             echo=False)


con_prod_write = create_engine('mysql+pymysql://' +
                             credentials['Prod']['user'] + ':' +
                             credentials['Prod']['password'] + '@' +
                             credentials['Prod']['host'] + ':' +
                             str(credentials['Prod']['port']) + '/' +
                             credentials['Prod']['write_db'] +
                             "?charset=utf8",
                             echo=False)

# move merchant table
sql = "SELECT * FROM merchant"
df = pd.read_sql(sql, con_uat)
# print(df)
df.to_sql(name='merchant', con=con_prod_read, if_exists='replace', index=False)
But it is not look good to me, need your suggestion how to improve/complete above python script?

I am using Python3.6,Mysql.

Any comment on this guyz?
Is the script is fine or is there any other pythonic way to achieve this ?
Reply
#2
If you have a field that is unique in merchant, that is all you need to keep track of a you have the rest of the info still in merchant. Also, don't post your actual password, and change it to something else in the post above.
Reply
#3
Thanks woooee I will follow the same.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Using MySQL database images or data dumps Planetary_Assault_Systems 1 1,772 Jul-20-2021, 04:40 PM
Last Post: Larz60+
  Mysql and data manipulation disloyalzeus 5 48,854 Dec-20-2019, 08:22 AM
Last Post: buran
  Problem with saving data and loading data to mysql kirito85 4 3,870 Feb-08-2019, 10:53 AM
Last Post: kirito85

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020