import mysql.connector

# ensure that we have the correct name for the wp_options table  
wp_options_table = "wp_options"

#local database credentials
local_host = "localhost"
local_user = "root"
local_password = "root"
local_port = "8889"
local_database = "local_db"

#remote database credentials
remote_host = "192.170.110.35"
remote_user = "remote_user"
remote_password = "jiww9239jkflks0123okk"
remote_port = "3306"
remote_database = "remote_db"

# function to connect to a MySQL database
def connect_database (hostname, username, pwd, portnum, db): 
    try: 
        mydb = mysql.connector.connect(\
                               host = hostname,\
                               user = username,\
                               password = pwd,\
                               port = portnum,\
                               database = db)
    except mysql.connector.Error as err:
        print(err)
    return mydb



# Retrieve the Tablepress entries from the local wp_options database table
sql_request = 'SELECT * FROM ' + wp_options_table + ' WHERE option_name like "tablepress%"'

local_db = connect_database (local_host, local_user, local_password, local_port, local_database)
    
try:
    mycursor = local_db.cursor()
    mycursor.execute(sql_request)
    local_tablepress_options = mycursor.fetchall()
    local_db.close()  
except mysql.connector.Error as err:
    print(err)


# Now connect to the remote database
remote_db = connect_database (remote_host, remote_user, remote_password, remote_port, remote_database)

try:
    mycursor = remote_db.cursor()
    mycursor.execute(sql_request)
    remote_tablepress_options = mycursor.fetchall()
    remote_db.close()  
except mysql.connector.Error as err:
    print(err)

# check to see if you have the correct Tablepress records and their table numbers and names match the remote ones 
if (local_tablepress_options[0][0] == remote_tablepress_options[0][0])\
& (local_tablepress_options[0][1] == remote_tablepress_options[0][1] == "tablepress_plugin_options")\
& (local_tablepress_options[1][0] == remote_tablepress_options[1][0])\
& (local_tablepress_options[1][1] == remote_tablepress_options[1][1] == "tablepress_tables"):
    # Replace Tablepress records in remote wp_options table
    sql_replace = 'REPLACE INTO `vtd_options` (`option_id`, `option_name`, `option_value`, `autoload`) VALUES '\
    + str(local_tablepress_options[0]) + ', ' + str(local_tablepress_options[1]) + '; ' 
    
    remote_db = connect_database (remote_host, remote_user, remote_password, remote_port, remote_database)
    
    try:
        mycursor = remote_db.cursor()
        mycursor.execute(sql_replace)
        remote_db.commit()
        mycursor.close()
        remote_db.close()
    except mysql.connector.Error as err:
            print(err)
    print ("The TablePress wp_options records on the remote server have been updated")

else: 
    print("error: the local TablePress records do not match the remote ones, therefore, nothing has been changed!")