MySQL now provides support for pure python based access to mysql database. Use the python mysql connector in your program for accessing the mysql database. Run the following command in your machine's command prompt to install the driver (this assumes you already have python3 installed),

If you get access denied error for the above, you may need to add sudo prefix to the above command.

Once you have the mysql connector installed, obtain the mysql server url, username, password and database name. Replace the variables in the following script with the values of the above. You may also want to replace the query written for wordpress database. Note that the following script is written for python3 and above.

The following python script connects to a mysql database configured for wordpress and then prints all the published posts along with the category under which it is published. You can easily customize this script for your queries.

# pip3 install mysql-connector-python
import mysql.connector

# Need to give access to your machine if using dreamhost MySQL instance
mysql_server = "replace_this_server_name"
mysql_user = "replace_this_user"
mysql_password = "replace_this_password"
mysql_db_name = "replace_this_db_name"

connection = mysql.connector.connect(user=mysql_user,
                  password=mysql_password,
                  host=mysql_server,
                  database=mysql_db_name)
try:
  cursor = connection.cursor()
  cursor.execute("""
    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;
  """)
  for row in cursor:
    print(row[0]+","+row[1])
finally:
  connection.close()

If you are using a mysql database provisioned by a cloud service provider such as Dreamhost, you may have to enable direct access to the database from your machine. This option is usually available on the cloud service provider portal. For example, if you are using Dreamhost, click on the mysql username from the Dreamhost panel and then add "%" to the list of "Allowable hosts". This gives user permission to connect from any IP. Alternatively you can give the specific external IP of the machine as well. Without this option, you will get "_mysql_connector.MySQLInterfaceError: Access denied for user and mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user" message.