Save posts to a WordPress Database – Working Notes

June 26, 2022

This is notes/overview of a project I started on to add several posts to WordPress (WP) from HTML files without having to copy and paste each file. These are just notes from that dropped project and the basic Python script to do this (github). Make sure you fully test before using, these are just reference notes only.

I started the project by how to get the completed CSV to go into the WordPress database, working backwards from what I needed as the end results. Once I got that done then I would now how I needed to write the program to pull what was needed and formatted from the HTML files.

This won’t be on how to parse through certain files, that will come later just on how to get them to save in Word Press’s MySQL database. This will help to know what items you will need to parse and help to get that setup. I’m splitting the programs, the parsing of HTML and the post to database, to make it more reusable in future. That way if I have to have another app like go through text files I can copy the HTML alter as needed when that’s done just run this app again. If this was for something on going then using functions, etc but as a one off program each time, I like to keep it as simple and reusable as possible.

I did a Python script to pull email addresses out of .eml files, that could help here.
These are the notes in case it helps others or if I need to revisit this idea again in the future.

The original project idea:

  • Put all the HTML files together into a folder, these were text only no images.
  • Script to be written, goes through and pulls the data: title; date (add a time); text with formatting for WordPress.
    • Save to a CSV file.
  • Create a local install of Word Press, setup with users; categories.
  • Save to the MySQL database for Word Press
  • Test then design and deploy to hosting site.

This will only focus on after the data has been gathered into a CSV file: whether it’s from HTML files or other sources. Some notes at bottom on gathering data.

The items being pulled from the original source would be:

  • Title
  • Date (hard coding adding a time)
  • Author, hard coding from number in WordPress
  • Content

Notes on the database columns should be headers in readable form:

*=They are needed or you’ll get a database error.

ID: This will auto fill in as you add to the DB.

Post Author*: You need these assigned to somebody. Create that user(s) then look up their number. #1 will be the person first created when installing WordPress. If others are needed add to WordPress you can find the number in the `wp_users` table.

Post date*/post date gmt*: if clean install you can use same for both, if you have changed the time zone in the settings you’ll need to calculate the different times. For scheduled posts use the date you want to go live.

Post content*: what is in the body of the post or page, we’ll dig more into this later, you’ll need to preformat it with WordPress tags.

Post title*: The title you want, use plain text, no formatting needed.

Post excerpt*: pass ‘’ or truncate post content.

Post status*: publish, future or draft. If you’re pulling old files you can hard code ‘publish’.

Comment_status, ping status: open or closed if you don’t pass it will mark it as open.

Post password: if you would like to assign a password before people can read the post. Leave blank for a normal post anybody can read.

Post name*: web friendly name lower case with spaces as ‘-‘ of the post title, removal of special characters. If Title: ‘My Awesome Post’; this would be ‘my-awesome-post’. This is set in the code sample but not fully tested.

To ping*, pinged*: fill out if you know you need these, this needs a value you can pass “”.

Post modified*/ post_modified_gmt*: same as post date; if scheduled use current date.

Post content filtered*: This is needed, you can pass in blank ‘’.

Post parent: you don’t need to pass; unless you need it to link to another post.

Guid*: <URL>+/?p=+<post ID>. You can get this from the database with the ‘Hello World’ post it will look something like: http://localhost:####/<site name>/?p=1 remove the one and pass in the rest.

Menu order: Not needed

Post type*: ‘post’ if creating pages ‘page’ images would be ‘attachment’

Post mime type: leave blank. If image it will need to be something like: png ‘image/png’

Comment count: leave black, it will add the comments as they come in, the system will auto assign 0.

Add a category:

Add all the categories you need first. Check on category names in the `wp_terms` table. Use the ‘term_id’ column, the name you gave it will be listed under ‘name’. You can also get the name by clicking on the category in WordPress and look for ‘tag_ID=’ in the URL bar.

To add a category add a link table entry to `wp_term_relationships`

object_id: the post #

term_taxonomy_id: Category number, check in Database wp_terms or click the cat in WP for ‘tag_ID=#’

Remember to test locally on either a fresh install or a local copy of your site. You’ll make to make sure you’re getting the post the way you want. Do not try this on a live website: plug-ins, themes, etc. could have bad results if its expecting something that isn’t passed correctly.

General notes:

Pass the title with regular text no HTML coding needed.

Paragraphs you will need to format these to how you want them to look, here’s a sample from a simple post I created inside WordPress to get some style sampling, make sure to wrap the HTML with the <wp> tags needed:

<!– wp:paragraph –>

<p>This is some test inside of there. <em>Italicized text</em>. <strong>Bold text</strong></p>

<!– /wp:paragraph –>

<!– wp:heading {“level”:3} –>

<h3>Header 3</h3>

<!– /wp:heading –>

Incomplete notes:

Images:

I was going to upload any images that were needed before import the posts then point to those.

An image inside the ‘post_content’ looks like this:

<!– wp:image {“id”:54,”sizeSlug”:”full”,”linkDestination”:”none”} –>

<figure class=”wp-block-image size-full”><img src=”http://localhost:##/<YourSite>/wp-content/uploads/2022/08/image.png” alt=”” class=”wp-image-54″/></figure>

<!– /wp:image –>

Aligned right add to the beginning same with left aligned.:

<!– wp:image {“align”:”right”,”id”:54…

You’ll want to work on a test post and see how the code looks in the database before passing it in.

Featured image insert:

When you add an image it creates a few references:

Wp_posts:

Everything as above; differences are:

Post_title/post_name: name of image

Guid: will point to your uploads folder such as: ‘http://localhost:##/testPost/wp-content/uploads/2022/08/image.png’

Post_type: attachment

Post_mime_type: type and the file type: ‘image/png’

Link table: wp_postmeta (2 entrees)

Meta_id = this is the unique #

Post_id = This links to the images number created in the wp_posts section

Meta_key = Featured image had ‘_wp_attached_file’

Meta_value = Featured image had image path from inside ‘../wp-content/uploads’ you only need to add the file name with extension.

2nd (size):

Meta_id = same

Post_id = same

Meta_key = ‘_wp_attachment_metadata’

Meta_value = This contains a lot of information and best to upload images then point to them using code.

3rd (thumbnail):

Meta_id = same

Post_id = This points to a different ID

Meta_key = ‘_thumbnail_id’

Meta_value = points to the 1st version

Image also saves in to the wp_posts location:

Use same info as post these are different:

post_status = ‘inherit’

post_parent = link to the post ‘10’ (need to get the post’s number to populate here)

Guid = path to the image folder ‘http://localhost:##/testPost/wp-content/uploads/2022/05/image.png’

Post_type = ‘attachment’

post_mime_type = image type ‘image/png’

Comments look at table: ‘wp_comments’

Categories are in ‘wp_terms’

If you’re looking for other Python to MySQL database information:

Related Articles

Sometimes the low-tech approach is the best way

Sometimes the low-tech approach is the best way

Have a family member looking to get me some family videos they had digitized, in one big file, about 80GB (they may not have optimized or know how). The need: The family member’s internet connection isn’t the best and could get disconnected at times, when this happens...

read more
AWS HTML Forms email and database working notes

AWS HTML Forms email and database working notes

Over the last weekend I worked to get a basic working version of 2 features for a HTML website forms to process through AWS. Those are proof of concepts (POC) to make sure that I can make them and write down the basic instructions for myself and notes of what to look...

read more
Received AWS Certified Cloud Practitioner Certificate

Received AWS Certified Cloud Practitioner Certificate

This past weekend I tested and received my AWS Certified Cloud Practitioner certification. When I started training for the test I had very little exposure to AWS. I studied for a little over a month, around 60 hours, before taking the test; which luckily I passed on...

read more

Pin It on Pinterest

Share This