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

🔒 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()