When working on content sites or AI content distribution projects, how can you efficiently batch publish generated content to WordPress? This article will share how I built an automated content publishing workflow using a Python script, without relying on the WordPress backend or XML import, directly interfacing with the database for maximum efficiency.

💡 Scenario Background

Suppose you have an independent content generation platform (or a crawler system) that writes content into a MySQL database. You want to automatically publish eligible articles to your WordPress blog daily, reducing manual operations.

At this point, a Python script can help you achieve the following:

Pull article data for a specific day from the source database.
Automatically create WordPress users (if the article author doesn’t exist in WordPress).
Write articles to the WordPress ‘wp_posts` table, setting them as drafts.
Set featured images (not uploaded, only URL saved).
Bind article categories to a specific WordPress term.
Automatically set the publication time (can be used for subsequent scheduled publishing).

🧰 Overall Workflow Diagram

[Content Database] → [Python Script Processing] → [WordPress Database] ↑ ↓ Get Articles Write to wp_posts, wp_postmeta, wp_term_relationships Automatically create users (wp_users)

🛠️ Script Workflow Breakdown

1. Pass Date Parameter When Script Starts (Optional)

Execute via the command line:

python post_ai.py       # Defaults to today's articles
python post_ai.py 1     # Gets yesterday's articles

Internally, it will retrieve content for the corresponding date by subtracting day_offset from the current time.

2. Connect to the Source Database and Get Article Data for the Specified Date

SELECT * FROM articles_data
WHERE data_type_id = 49
AND create_time LIKE '2025-04-20%'

3. Iterate Through Each Article and Start Processing the Publishing Logic

✅ Get or Create WordPress User

cursor.execute(f"SELECT ID FROM wp_users WHERE user_login = '{user_name}'")

If it doesn’t exist, it writes to the wp_users table.

✅ Construct WordPress Post Content Extracted fields include:

Title (title / main_title)
Summary (description / subtitle)
Body (content)
Cover image URL (image_url)

Insert into wp_posts:

cursor.execute("""
INSERT INTO wp_posts (
    post_author, post_date, post_date_gmt, post_content, post_title,
    post_excerpt, post_status, comment_status, ping_status, post_name,
    post_modified, post_modified_gmt, post_type
) VALUES (...)
""")

✅ Set Featured Image

cursor.execute("""
INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
VALUES (%s, 'featured_image', %s)
""")

✅ Set Categories

category_map = {
    '263': 9,
    '264': 10,
    '265': 8,
    '266': 6
}

Insert into the wp_term_relationships table:

cursor.execute("""
INSERT INTO wp_term_relationships (object_id, term_taxonomy_id)
VALUES (%s, %s)
""")

📋 Example: Scheduling Logic for 8 Articles a Day

Num	Pubdate
1	08:00
2	11:00
3	14:00
…	…

⚙️ Usage (Command Line)

python post_ai.py       # Publish today's articles
python post_ai.py 1     # Publish yesterday's articles
python post_ai.py 2     # Publish articles from the day before yesterday

Link it with llms.txt for content discovery

For content sites, it is better not to stop at “publish into WordPress”.

A more complete workflow usually looks like this:

Publish the article into WordPress
Regenerate the site llms.txt
Submit both the new article URL and llms.txt through IndexNow

That way, the article is not only published, but also exposed through an updated discovery entry for AI search and crawlers. The practical next step is here:

2026 Webmaster Playbook: Automate llms.txt for AI Search with Python

🔒 Security Tips & Expansion Suggestions

It’s recommended to run this script on a test site first to avoid damaging production data.
The cover image URL can be uploaded as an attachment and generated as wp_attachment type.
If tags are needed, you can extend the script to write to wp_terms and wp_term_taxonomy.
It’s advisable to combine this with crontab for scheduled runs or integrate with Airflow for the publishing pipeline.

📄 Full Script

post_ai.py

import pymysql
import time
import datetime
import sys

db = pymysql.connect(host='localhost', user='root', password='password', database='source_db', charset='utf8mb4')
wp_db = pymysql.connect(host='localhost', user='wp_user', password='wp_pass', database='wordpress_db', charset='utf8mb4')
cursor = db.cursor()
wp_cursor = wp_db.cursor()

day_offset = int(sys.argv[1]) if len(sys.argv) > 1 else 0
target_date = (datetime.datetime.now() - datetime.timedelta(days=day_offset)).strftime('%Y-%m-%d')

sql = f"SELECT * FROM articles_data WHERE data_type_id = 49 AND create_time LIKE '{target_date}%'"
cursor.execute(sql)
results = cursor.fetchall()

interval = 180
start_time = datetime.datetime.now().replace(hour=8, minute=0)

print(f"Found {len(results)} articles for {day_offset} days ago.")

for idx, row in enumerate(results):
    user_name = row[2]
    title = row[4]
    content = row[6]
    description = row[8]
    image_url = row[7]
    category_id = row[13]

    post_time = start_time + datetime.timedelta(minutes=idx * interval)

    wp_cursor.execute(f"SELECT ID FROM wp_users WHERE user_login = '{user_name}'")
    result = wp_cursor.fetchone()
    if result:
        author_id = result[0]
    else:
        wp_cursor.execute("""
            INSERT INTO wp_users (user_login, user_pass, user_nicename, user_email, user_registered)
            VALUES (%s, %s, %s, %s, %s)
        """, (user_name, '123456', user_name, f"{user_name}@bobobk.com", post_time.strftime('%Y-%m-%d %H:%M:%S')))
        wp_db.commit()
        author_id = wp_cursor.lastrowid

    wp_cursor.execute("""
        INSERT INTO wp_posts (
            post_author, post_date, post_date_gmt, post_content, post_title,
            post_excerpt, post_status, comment_status, ping_status, post_name,
            post_modified, post_modified_gmt, post_type
        ) VALUES (%s, %s, %s, %s, %s, %s, 'draft', 'open', 'open', %s, %s, %s, 'post')
    """, (
        author_id, post_time, post_time, content, title,
        description, user_name, post_time, post_time
    ))
    wp_db.commit()
    post_id = wp_cursor.lastrowid

    wp_cursor.execute("""
        INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
        VALUES (%s, 'featured_image', %s)
    """, (post_id, image_url))

    category_map = {
        '263': 9,
        '264': 10,
        '265': 8,
        '266': 6
    }
    taxonomy_id = category_map.get(str(category_id), 6)
    wp_cursor.execute("""
        INSERT INTO wp_term_relationships (object_id, term_taxonomy_id)
        VALUES (%s, %s)
    """, (post_id, taxonomy_id))

    wp_db.commit()
    print(f"Posted article ID {row[0]} to WordPress as post ID {post_id}")

cursor.close()
wp_cursor.close()
db.close()
wp_db.close()

Automatically Publishing Articles to WordPress Using a Python Script: A Complete Workflow Analysis