Pushing mysql db data from one schema to another schema?

[PrateekG]

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 ?

[woooee]

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.

[PrateekG]

Thanks woooee I will follow the same.