• Home
  • Python Script to Bulk Upload new Wordpress Posts from a CSV file using WordPress Rest API

    Chris

    Administrator
    Staff member
    Learn how to quickly upload new WordPress posts in bulk using the WordPress API. This tutorial will show you how to connect to the WordPress Rest API using Python and Google Colab.

    Contents
    1, Create a WordPress Application Password
    2, Create a new Colab document and import the required Python modules.
    3, Connecting to the WordPress Rest API
    4, Upload CSV and remote files directly into Google Colab
    5, Fetching the CSV and pasting filename into the script
    6, Creating a function that builds the JSON Posts array
    7, Executing the script and monitoring the progress
    8, Colab link to the full script and demo of expected results

    WordPress thankfully has a fantastic fully functional Rest API that is surprisingly easy to use. The WordPress REST API is an interface that you can use to access WordPress from outside the WordPress installation itself. Using NodeJS or Python you can connect to the API to speed up productivity or use it for headless functionality.

    If you are completely new to the WordPress Rest API I suggest visiting the developer docs to help you familiarise yourself with the API endpoint and functionality - https://developer.wordpress.com/docs/api/

    To connect to WordPress I will be using Google Colab. Colab is a free Python Jupyter notebook environment in the cloud. Colab notebooks allow you to combine Python executable code and rich text within a single document.

    We will be connecting with the WordPress API to bulk upload new posts from a CSV file. You can literally create thousands of new WordPress posts in a few minutes. This Python script is fantastic at saving time on your content production.

    Below is a copy of the script if you don't require the tutorial
    Code:
    """Python WordPress Bulk Posts v1.0.ipynb
    
    This script: WordPress Bulk Posts v1.0 - Bulk upload Wordpress posts from CSV files. The CSV files can be uploaded via Google Colab or fetched from a remote location (upload from Google sheets coming in the next version).
    """
    
    # Import modules
    import pandas as pd
    import requests
    import csv
    import json
    import base64
    from tqdm.notebook import tqdm
    
    # WordPress Rest API credentials
    user = 'admin'
    password = 'kJid Xqyu d7K9 fRmK 7cfN d8c6'
    url = 'https://airwolf.dev/wp/wp-json/wp/v2'
    
    wp_connection = user + ':' + password
    
    token = base64.b64encode(wp_connection.encode())
    
    # Base64 Authentication WordPress
    headers = {'Authorization': 'Basic ' + token.decode('utf-8')}
    
    from google.colab import files
    uploaded = files.upload()
    
    # Paste your uploaded csv file name here - example: sample-data.csv or paste remote web link
    df = pd.read_csv("https://airwolf.dev/wp/sample_posts.csv")
    df
    
    # WP_Post_Insert Function to build the JSON array
    # (add or remove post schemas to suit your requirements)
    def wp_post_insert(post_title, post_content):
    
        post = {'title': post_title,
                'status': 'publish',
                'content': post_content,
                'author': '1',
                'format': 'standard'
                }
    
        wp_post_insert_request = requests.post(url + '/posts', headers=headers, json=post)
    
    # Executes the script and runs the TQDM progress bar
    for index, row in tqdm(df.iterrows()):
    # Add or remove WordPress post schemas varibles below
        wp_post_insert(row["post_title"], row["post_content"])

    This tutorial assumes that you have basic knowledge of WordPress, Python and Google Colab. If you do not have any experience in Python or Colab, do not worry, I shall keep this tutorial easy to follow and implement.

    1, Let’s begin with connecting to the API. You need to be an admin of WordPress for you to connect. Login into the WordPress dashboard, and navigate to Users > Profile. Scroll down to application passwords. Go ahead and generate a new password.

    wp1.jpg


    Once you’ve created a new application password, save it in a text doc somewhere as you cannot retrieve the password again. If you lose it, you will need to generate another. Next, we shall be heading over to Google Colab to start building the script and sync up our WordPress login credentials. If you have a Google account you can use Google Colab for free - https://colab.research.google.com/

    2, Create a new Colab document. We’re going to import the following python modules.

    Code:
    # Import modules
    import pandas as pd
    import requests
    import csv
    import json
    import base64
    from tqdm.notebook import tqdm

    3, Now we are going to connect to the WordPress Rest API. Enter your WordPress username, and application password and update the website address to match the website that you're going to use. If you intend to the script locally use localhost:8000.

    Code:
    # WordPress Rest API credentials
    user = 'admin'
    password = 'kJid Xqyu d7K9 fRmK 7cfN d8c6'
    url = 'https://yourwebsite.com/wp-json/wp/v2'
    
    wp_connection = user + ':' + password
    token = base64.b64encode(wp_connection.encode())
    
    # Base64 Authentication WordPress
    headers = {'Authorization': 'Basic ' + token.decode('utf-8')}

    4, This next part allows you to upload CSV files directly into Google Colab. If you prefer to store the CSV files remotely elsewhere this part is optional, the script will run without it.

    Code:
    from google.colab import files
    uploaded = files.upload()

    wp2.jpg


    5, Okay so now we need to tell the script where to retrieve the CSV file from. If you uploaded the file using the Colab file uploader you need to enter the file name. If you fetching from a remote location, enter the full remote file path. Note – fetching from Google Sheets is currently not supported. It will be coming soon.

    Code:
    # Paste your uploaded CSV file name here - example: sample-data.csv or paste remote weblink
    df = pd.read_csv("https://airwolf/sample_posts.csv")
    df
    When you start to execute the script, the import and CSV modules that we imported earlier will fetch the data and create a dataframe.

    wp3.jpg


    6, Next up we are going to create a function that builds the JSON array. We are going to populate the JSON array with WordPress post schemas. To keep it simple, this script utilizes 2 variables (post_title & post_content) with the remainder being static. You can add additional post schemas by adding post schemas separated by a comma. On a default WordPress installation, there are 23 post schemas.

    Code:
    # WP_Post_Insert Function to build the JSON array
    # (add or remove post schemas to suit your requirements)
    
    def wp_post_insert(post_title, post_content):
        post = {'title': post_title,
                'status': 'publish',
                'content': post_content,
                'author': '1',
                'format': 'standard'
                }
        wp_post_insert_request = requests.post(url + '/posts', headers=headers, json=post)

    The image below - an example of the WordPress database post schemas. You can find these using PHPMyAdmin
    wp4.jpg


    7, We are now on the final stage of the script. We need to execute the code. This next code snippet executes the script with progress status using the TQDM module.

    Code:
    # Executes the script and runs the TQDM progress bar
    for index, row in tqdm(df.iterrows()):
    # Add or remove WordPress post schemas varibles below
        wp_post_insert(row["post_title"], row["post_content"])

    If you are importing a high volume of posts, go make a cup of tea. For this tutorial, I have created around ~1600 dummy posts. The progress bar below is up to 429/1600 after 4 minutes. It took around 15 minutes to complete the 1600 dummy test posts to write to the database via the API.
    wp5.jpg

    The image below is the dummy posts being written to the sql database.
    wp6.jpg



    Download
    The complete script is available to download or copy from Google Colab here - https://colab.research.google.com/drive/1z6JprClNit5nR2Djiv82lRpEehjZ5Iw0?usp=sharing

    You can see the uploaded posts here - https://airwolf.dev/wp/
     
    Last edited: