Migrating TablePress Tables with WP Migrate DB Pro

Website content creators often put together new content on a staged copy of their website hosted on a local computer. However, once the new post or page is ready to be published, they often face the challenge of how best to efficiently transfer that content to the live website. One solution to this problem, discussed in a previous article, comes in the form of the Local Sync application from ManageWP. However, Local Sync remains in experimental form and suffers from some serious application-breaking errors.

Another, more developed solution comes in the form of the WordPress plugin WP Migrate DB Pro from Delicious Brains. When set up appropriately, WP Migrate DB Pro can be made to work for publishing the majority of post and page data to a live WordPress website.

However, WordPress is designed to integrate third-party code in the form of plugins, making it almost impossible to design a universally-compatible, conflict-free migration or content-publishing plugin. As a result, even WP Migrate DB Pro too runs into the occasional caveat.

One popular WordPress plugin that requires special treatment when it comes to using WP Migrate DB Pro to publish new content is the TablePress plugin by Tobias Bäthge, which is used to display tabular data on WordPress websites. In this article, we discuss why TablePress does not play well with WP Migrate DB Pro for content-publishing and how to work around the issues to successfully deploy TablePress tables when using the migration plugin.

Publishing TablePress tables with WP Migrate DB Pro

When using WP Migrate DB Pro to transfer new content, migrating the wp_posts and wp_postmeta MySQL database tables (as well as any media files displayed within the new content) is usually enough to publish the new content to a live website. However, for the TablePress plugin, migrating just these two database tables is not sufficient to display new TablePress tables on the live website.  

In addition to wp_posts and wp_postmeta, TablePress also requires the migration of the wp_options database table as critical additional data for the plugin is found in this MySQL table as well. However, the wp_options table also contains numerous other unrelated website settings and data for other plugins. Some of these settings and data are unique to the live environment and replacing the live version of the wp_options table as a whole with the one from the local environment can cause all manner of issues for the live website.

Therefore, to avoid any disruption to the live website when publishing new content containing TablePress tables, only the specific records that pertain to TablePress in the wp_options database table should be migrated. Unfortunately, this cannot be done through the WP Migrate DB Pro interface (yet - maybe someone at Delicious Brains is listening?) but instead needs to be performed separately alongside the machinations of the migration plugin.

Migrating the TablePress wp_options records: Manually

One way to transfer specific database records from the local database to the live one is to do it manually using the website database application, phpMyAdmin, which accompanies most website server software. This can be done through the following steps:

1. Using phpMyAdmin, go to the local website database and click on the wp_options table.

2. Set the 'Number of rows' dropdown menu to the maximum. Then type in 'tablepress' into the 'Filter rows' query box to filter for TablePress records. This should bring up two rows at the top of the list called: 'tablepress_plugin_options' and 'tablepress_tables' (as shown in the image below).

3. Select these two TablePress records (using the checkboxes) and click the 'Export' button located below the record list. This will take you to the Export tab for the database. 

4. In the Export tab, choose 'Custom - display all possible options' to determine how the data should be exported. Make sure the 'Format' dropdown menu is set to 'SQL', and that the 'Output' option is set to 'View output as text' before clicking 'Go' at the bottom of the page.

5. On the resulting text output, select and copy the whole of the "INSERT INTO ..." command (highlighted in blue in the below image).

6. Go to the cPanel (or equivalent) of the live website and log into phpMyAdmin provided by the web host. Select the database of the live website and choose its SQL tab at the top of the page. You will be presented with a large query box into which you can paste the "INSERT INTO... command that you just copied from the local database. In the pasted text, replace the word "INSERT" with the word "REPLACE". Leave everything else unchanged and click "Go " at the bottom of the page.

Result: If all has gone well, you should be presented with a green checkmark and confirmation that 4 rows have been affected. Why 4 rows? The 'REPLACE' command first deletes the 2 existing records before adding the 2 new ones so, technically, a total of 4 rows will have been modified.

Migrating the TablePress wp_options records: Using a Python script

The (rather tedious) manual migration process described above can be condensed into a Python script (shown below) which can be run any time a new post or page containing one or more TablePress tables has been published using the WP Migrate DB Pro content-publishing process. This script will just replace the two TablePress records in the wp_options table on the live website leaving all other records untouched.

Prerequisites

1. Ensure that Python is installed on your computer: To use this 'TablePress' Python script, you first must have Python (version 3) installed on your computer. Once Python has been confirmed as installed, in addition to Python's standard libraries, you will also need the 'mysql connector' driver, which allows Python to interact with MySQL databases. This library can be installed using Python's package installer (pip; which comes with Python), by typing the following into the command line or terminal: 

pip install mysql-connector-python

2. Replace the variables at the top of the Python script with your own: In the TablePress Python script itself, just replace the database credentials at the top of the script with your own before saving the file.

3. Ensure that you are authorised to make changes to your MySQL database remotely: Finally, for the script to be able to make changes to your remote MySQL database, you (or rather, your IP address) should also be authorised to connect to the remote MySQL database. This can usually be set up in the control panel your web host provides to you to manage your website. For example, for SiteGround hosting, these instructions should be followed.

Running the script

To run the TablePress Python script, go into your command line facility (terminal on a Mac) of your computer, traverse to the directory where the script is stored, and tell Python to run the script., The command should look something like this :

python tablepressOptionsUpdate.py

You should then get feedback (in the terminal) as to whether the process was completed successfully or, if there was an error, this should also show up to provide you with further information for troubleshooting.