I run this website on wordpress powered by a mysql database. Very often I want to take a look at all the published posts on the site. By quickly looking at the list of post titles I can avoid posting duplicate or similar articles. If you have a wordpress blog with plenty of posts, you may need a list of all your wordpress posts along with the category name. You can then check whether you already have a post on the topic you are planning to publish. In this article I will provide four MySQL queries which you can run on your wordpress mysql database to get a list of your published posts with category name.

The following mysql query prints all the published wordpress posts grouped by category name. Note that the query uses the default table names used in wordpress. If you are using a cloud provider such as Dreamhost, you may have a different name for your wordpress tables. In that case replace the table names in the following query.

SELECT wp_posts.post_title AS ‘Title’, wp_terms.name AS ‘Cateogry’
  FROM wp_posts
  INNER JOIN wp_term_relationships
  ON wp_posts.ID = wp_term_relationships.object_id
  INNER JOIN wp_terms
  ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
  INNER JOIN wp_term_taxonomy
  ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  ORDER BY wp_terms.name;

The following mysql query prints all the published wordpress posts with most recent one printed first. This allows you to analyse how frequently you are posting articles and take a look at your recent or old posts.

SELECT wp_posts.post_title AS ‘Title’,
       wp_terms.name AS ‘Cateogry’,
       wp_posts.post_date AS ‘Date’
  FROM wp_posts
  INNER JOIN wp_term_relationships
  ON wp_posts.ID = wp_term_relationships.object_id
  INNER JOIN wp_terms
  ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
  INNER JOIN wp_term_taxonomy
  ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  ORDER BY wp_posts.post_date DESC;

You can customise the above queries in different ways for your specific requirements. For example, the following mysql query prints posts only in a specific category,

SELECT wp_posts.post_title AS ‘Title’,
       wp_terms.name AS ‘Cateogry’
  FROM wp_posts
  INNER JOIN wp_term_relationships
  ON wp_posts.ID = wp_term_relationships.object_id
  INNER JOIN wp_terms
  ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
  INNER JOIN wp_term_taxonomy
  ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  AND wp_terms.name='Android';

The following mysql query only prints posts with more than 10 comments,

SELECT wp_posts.post_title AS ‘Title’, wp_terms.name AS ‘Cateogry’
    FROM wp_posts
    INNER JOIN wp_term_relationships
    ON wp_posts.ID = wp_term_relationships.object_id
    INNER JOIN wp_terms
    ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
    INNER JOIN wp_term_taxonomy
    ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE wp_posts.post_status = 'publish'
    AND wp_posts.post_type = 'post'
    AND wp_term_taxonomy.taxonomy = 'category'
    AND wp_posts.comment_count > 10
    ORDER BY wp_terms.name;

Azure Cosmos DB is a highly scalable and fully managed database service available on the Azure cloud platform. Using MongoDB API support, it is possible to store JSON documents in Azure Cosmos DB. If you are using Cosmos DB with MongoDB API support in your projects, sometimes you need quick Python scripts to analyse data. Python can be used to quickly run queries on Cosmos DB. If you are using python to connect to production database, use only the read-only access keys for the Cosmos DB. Read-only keys are available on a separate tab in the Azure portal page for Cosmos DB (Azure Cosmos DB => Connection String => Read-only Keys).

The following sample python program uses pymongo package to connect to Cosmos DB. If you don't have pymongo already installed, run the following command,

The following python program connects to the Azure Cosmos DB and then prints documents in a collection. Don't forget to replace the variables with the details of your database. Also note that the following program requires python 3.6 or above.

from pymongo import MongoClient
import json

# Get these values from the Azure portal page for your cosmos db account
cosmos_user_name = "cosmostestdbaccountname"
cosmos_password = "C7qC0lae6C6gNqRbiNYvhQgchsWWCLJpgLNA0hwA4IsJSLKDK=="
cosmos_url = "cosmostestdbaccountname.documents.azure.com:10255/?ssl=true&replicaSet=globaldb"
cosmos_database_name = "cosmostestdb"
cosmos_collection_name = "cosmostestcollection"

# This requires python 3.6 or above
uri = f'mongodb://{cosmos_user_name}:{cosmos_password}@{cosmos_url}'
mongo_client = MongoClient(uri)

# This is the name of the Mongo compatible database
my_db = mongo_client[cosmos_database_name]

# The name of the collection you are querying
my_col = my_db[cosmos_collection_name]
my_docs = my_col.find({})

# Prints all documents in the collection
for doc in my_docs:
  print(doc)

Azure Cosmos DB supports creation of databases which are compatible with MongoDB API. You can use standard MongoDB query language to access and modify data in such a database. However, please note that there are limitations in MongoDB API support and for aggregations, you will need to explicitly turn on aggregation support when creating the Cosmos DB account.

The following shell script can be used to create a MongoDB API compatible Cosmos DB database using Azure CLI. Follow these instructions if you don't have Azure CLI installed on your machine. Also note that the following script will run only on linux or mac systems.

The cosmos db shell script given below asks for the following parameters,

• Subscription id: This is subscription id under which you want all resources to be created. This value is available from the Azure portal under the "Cost management + Billing" section.
• Resource group name: In Azure, all resources are organized under a container called resource group. Give a name under which you want your cosmos db account to be organized.
• Cosmos db account name: This is the name you want to give to your cosmos db account. Note that you can have multiple databases under a single account. Also note that the name must be a valid domain name prefix and should be globally unique in Azure. You will be accessing your account as [cosmos_account_name].documents.azure.com.
• Cosmos db database name: This is the name you want to give your MongDB instance. This need not be globally unique.

Note that the location for the resource group is hard coded as "US West" region. However, you can easily change it to an input parameter. Resources created under the group will be automatically assigned to the same region.

In Cosmos DB, you can specify the scalability factor of request units (database pricing depends on this value) at the database level or at the collection level. The following script sets the request unit limit to 400 at the database level to minimize cost. This is ok for test systems, but in production you may need a higher limit or set the limits at the collection level.

Note the use of "--capabilities" flag to turn on the aggregations preview feature. I have also set "--default-consistency-level" set to "Strong" to enable consistent reads/writes at the cost of performance.

Once the database is created, you can view the database from the Azure portal. You can also access the DB credentials from the portal. Here is the Azure CLI script for creating MongoDB compatible database in Cosmos DB,

#!/bin/bash

declare resource_location="westus"
echo "Creating cosmos db in $resource_location"

if [[ -z "$subscription_id" ]]; then
  echo "Please enter subscription id:"
  read subscription_id
  [[ "${subscription_id:?}" ]]
fi

if [[ -z "$resource_group_name" ]]; then
  echo "Please enter resource group name for cosmos db:"
  read resource_group_name
  [[ "${resource_group_name:?}" ]]
fi
if [[ -z "$cosmos_account_name" ]]; then
  echo "Please enter cosmos account name:"
  read cosmos_account_name
  [[ "${cosmos_account_name:?}" ]]
fi
if [[ -z "$cosmos_db_name" ]]; then
  echo "Please enter cosmos database name:"
  read cosmos_db_name
  [[ "${cosmos_db_name:?}" ]]
fi

az group create \
  --subscription $subscription_id \
  --name $resource_group_name \
  --location $resource_location

az cosmosdb create \
  --subscription $subscription_id \
  --resource-group $resource_group_name \
  --name $cosmos_account_name \
  --kind MongoDB \
  --capabilities EnableAggregationPipeline \
  --default-consistency-level "Strong" \

az cosmosdb database create \
  --subscription $subscription_id \
  --resource-group $resource_group_name \
  --name $cosmos_account_name \
  --db-name $cosmos_db_name \
  --throughput 400

Python has a simple pen drawing library called turtle. Using simple movement commands, we can draw shapes using the python turtle library. When teaching python to children, turtle is a good library to introduce to get children excited about the language and its features.

The basic actions used in the following examples are,

• Draw a line with pen - forward() command
• Move without drawing - penup(), pendown() commands
• Turn the pen to an angle - left(), right() commands

The following python program draws a simple equilateral triangle,

import turtle

board = turtle.Turtle()

board.forward(100) # draw base

board.left(120)
board.forward(100)

board.left(120)
board.forward(100)

turtle.done()

The following python program draws a right angled triangle,

import turtle

board = turtle.Turtle()

board.forward(100) # draw base

board.left(90)
board.forward(100)

board.left(135)
board.forward(142)

turtle.done()

The following python program draws a star shape by drawing two identical isosceles triangles,

import turtle

board = turtle.Turtle()

# first triangle for star
board.forward(100) # draw base

board.left(120)
board.forward(100)

board.left(120)
board.forward(100)

board.penup()
board.right(150)
board.forward(50)

# second triangle for star
board.pendown()
board.right(90)
board.forward(100)

board.right(120)
board.forward(100)

board.right(120)
board.forward(100)

turtle.done()

SHA256 is a secure hash algorithm which creates a fixed length one way string from any input data. The algorithm is designed in such a way that two different input will practically never lead to the same hash value. This property can be used to verify the integrity of the data. If data and hash is obtained using different methods, we can verify the integrity of the data by computing the hash again and comparing it with the received hash.

SHA 256 hashing algorithm is widely used in security applications and protocols. The following python program computes the SHA256 hash value of a file. Note that the computed hash is converted to a readable hexadecimal string.

# Python program to find SHA256 hexadecimal hash string of a file
import hashlib

filename = input("Enter the input file name: ")
with open(filename,"rb") as f:
    bytes = f.read() # read entire file as bytes
    readable_hash = hashlib.sha256(bytes).hexdigest();
    print(readable_hash)

The above program may fail for large input files since we read the entire string to compute the hash. The following python program is an improved version capable of handling large files,

# Python program to find SHA256 hash string of a file
import hashlib

filename = input("Enter the input file name: ")
sha256_hash = hashlib.sha256()
with open(filename,"rb") as f:
    # Read and update hash string value in blocks of 4K
    for byte_block in iter(lambda: f.read(4096),b""):
        sha256_hash.update(byte_block)
    print(sha256_hash.hexdigest())

Here is a sample output from the above program,