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!")