Are you searching for any of the following:
Connect to Cloudways database in Python
Read WordPress tables in Python

Then you are in the right place!!

Introduction about Cloudways

Cloudways is a versatile managed hosting platform catering particularly to WordPress users, providing affordability, functionality, and customized pricing plans for small to medium businesses, agencies, and e-commerce stores. The platform offers a suite of features including automated backups, fast load speeds, a choice of cloud servers like DigitalOcean and Google Cloud, and 24/7 live chat support. Over the years, Cloudways has earned a strong reputation within the WordPress community by innovating and adapting to customer needs, notably with its vertical scaling feature that allows flexible server resource management.

Setting the Credentials

Whitelist your IP Address

To access Cloudways database, you need to add the IP address to the whitelist, this can be done easily as follows:

1. Go the server that you would like to access
2. Navigate to Security
3. Then go to MySQL
4. Add your IP address
5. Then hit Save

To get your IP address. you can either use the command in the terminal “ipconfig” as follows:

or you can use the following code:

from requests import get

ip = get('https://api.ipify.org').content.decode('utf8')
print('My public IP address is: {}'.format(ip))

Database Credentials

To get the database credentials, you would need to navigate to the application that you want to access its database, then select “Access Details”, then “Database”, and there you will find:

• Database Name (DB Name)
• Username
• Password

Save them in a file, as we will need them in the next section.

Connect to the database in Python

This code segment exemplifies how one can use Python to interact with SQL databases, fetch data using SQL queries, and process it further using pandas, which is a powerful tool for data analysis and manipulation.

# ------- IMPORTS ---------
import pandas as pd
from sqlalchemy import create_engine

DB_CREDS = {
'user_name': '<your_username>',
'pw': '<your_password>',
'host_name': '<your_server_ip_address>',
'database': '<your_database_name>'
}
# ------- FUNCTIONS ---------
def read_sql(query):
    db_connection_str = f"mysql+pymysql://{DB_CREDS['user_name']}:{DB_CREDS['pw']}@{DB_CREDS['host_name']}/{DB_CREDS['database']}"
    db_connection = create_engine(db_connection_str)
    df = pd.read_sql(query, con=db_connection)    
    return df


# ------- MAIN ---------
query = """
SELECT *
FROM wp_posts
"""

df_wp_posts = read_sql(query)

It serves as a straightforward approach for fetching data from a MySQL database and storing it as a pandas dataframe. It begins with the importation of necessary libraries, including pandas for data manipulation and sqlalchemy for database interaction.

The script defines a dictionary, DB_CREDS, which contains the credentials required to establish a connection with a MySQL database. This includes the username, password, host IP address, and database name, which are all placeholders in this case and should be replaced by the actual credentials in a real-world scenario.

Next, a function named read_sql is defined. This function takes in a SQL query string as its argument and establishes a connection to the MySQL database using the credentials dictionary DB_CREDS. The connection string follows a particular syntax, adhering to the format "mysql+pymysql://username:password@hostname/database". After establishing the connection, the function executes the provided SQL query and loads the results into a pandas dataframe. The dataframe is then returned as the output of the function.

In the main section of the code, a SQL query is defined as a multi-line string, aiming to select all records from a table named wp_posts. The read_sql function is then invoked with this query as its argument. The returned dataframe containing the results of the SQL query is stored in the df_wp_posts variable.

Finally, if you don’t have an account on Cloudways, you can create Cloudways account using the following link and get $25 free credit!! –> https://vrlps.co/ogyqsg7/cp

I hope that the article was helpful for you, if you like my content and want to support me you can buy me a coffee: https://www.buymeacoffee.com/sambadie