Forem: Godbless Lucky Osu

Integrating Python with MySQL Databases: An Introduction and Practical Guide

Godbless Lucky Osu — Sun, 20 Jul 2025 16:30:52 +0000

Connecting MySQL with Python
- Installing MySQL
- Installing MySQL Connector/Python
- Establishing an Actual Connection with MySQL Database
Executing SQL Statements (Working with Cursors)
- Creating a Cursor Object
- Basic cursor Usage
- Fetching Data From the Result of SQL Query
- Inserting Records in Tables Using cursor.execute()
Closing Connection
- What Happens When Connection Fails
Conclusion

MySQL is a very popular relational database management system. Interaction with data is critical for software application and so programming languages need a way to connect to databases and perform various operations on databases (like to store, update, query or delete data in a database).

We'll be focusing on how to integrate a Python program with MySQL database.

As you go through this article, you'll practically see and learn how you can connect and interact with MySQL from your Python code.

To fully understand the tutorials in this article, it is recommended that you have a good understanding of Python concepts like control flows, loops, functions, classes and objects, exception handling, and packages, modules, and pip (the package installer for Python).

Also, if you are new to databases, I wrote an article that provides a simple but comprehensive overview of SQL databases (specifically MySQL). You can check it out here. It is recommended that you spend sometime understanding SQL databases and SQL basic commands before going further in this article.

Connecting MySQL with Python

Installing MySQL

You can skip this if you already have MySQL installed on your system. However if you do not, then the first requirement here is to actually have the MySQL Server installed on the operating system you are currently working from. The MySQL official documentation provides a guide on how to do this on various operating systems (Windows, macOS, Linux, Solaris).

Installing MySQL Connector/Python

To interact with MySQL database from your Python code, you need a database driver. A database driver is a software that allows applications to connect and interact with a specific DBMS (Database Management System). The driver translates application requests into a language that the database understands.

Typically, a database driver is usually obtained as a third-party library/package. The most common and recommended driver for connecting Python to MySQL is mysql-connector-python. The mysql-connector-python is a third-party Python library that acts as a bridge between Python and MySQL databases, through which Python can interact with MySQL servers, allowing for the execution of queries, manipulation of data and managing of database objects (like tables).

The recommended way to install MySQL Connector/Python is with pip package manager. Run the command below in your terminal or command line.

pip install mysql-connector-python

Using the above command, pip downloads and installs mysql-connector-python library in your environment — global or virtual. When installed, it provides a top-level package called mysql

Note: When working with Python, setting up a virtual environment is recommended, to isolate your work/project from the system's global Python installation and other virtual environments.

Once you have the mysql-connector-python library installed, you are now ready to use its packages and modules in your Python program.

Establishing an Actual Connection with MySQL Database

The first step to take when you want to interact with a MySQL server is to establish a connection with it. To connect, you need to import the mysql.connector package.

import mysql.connector

The package contains modules like connection, errors, errorcode, etc. The connection module houses the connect() method that is used to establish a connection to a MySQL database server.

The connect() method (function) needs three important parameters to establish a connection. It takes these parameters and returns a MySQLConnection object. These three parameters are:

host — host represents the server name or ip on which MySQL is running.
user — user represents the database user we want to use to connect connect and interact with the database.

Note: By default the username for MySQL database is root. However, in practice, you must avoid using the root user for interactions. This is because using the root user violates the principle of least privilege which is a security risk. It is recommended that you create a custom-user and grant only the permissions required. Then use that custom-user as user in your Python program. This restricts what your program can do.

password — password is the password of your database (the one you created when installing MySQL server).

The connect() method also accepts other parameters but they are optional — like database which represents the database name you want to connect to.

Important: The errors and errorcode modules in mysql-connector-python provide structured ways to handle exceptions that may occur during connection or database operations. It is recommended to wrap your database connection code in a try-except block to gracefully handle any errors.

Now, let's import the errors and errorcode module

from mysql.connector import errors, errorcode

Now, let's establish a connection to the MySQL server.

# Connect to MySQL server
try:
    # Create a MySQLConnection object
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="your_db"
    )
    print(connection)

# Handle specific programming-related MySQL errors
except errors.ProgrammingError as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Access denied: incorrect username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)

You have to replace the connection details with your actual login credentials and database name. The code uses these credentials to try to establish a connection with your MySQL server. If successful, it returns a MySQLConnection object, which is stored in the connection variable. If not successful, the creation of the object fails and an error message is returned. The except block runs and tries to gracefully handle this failure.

Note: The database parameter although optional is recommended in most cases as it tells MySQL to immediately switch to that specific database after establishing connection (just like the USE command in SQL).

Make sure to avoid hard-coding your login credentials (username, password, database) directly in your Python script. This is a serious security risk. A more secure way to store sensitive data, is by using environment variables. In Python, the python-dotenv package lets you store environment variables outside your code and load them into your program. This keeps sensitive data out of your script or source code.

To do this, you must have the python-dotenv package installed in your development environment. Then create a .env file in the root directory of your project, and save all your secrets as key-value pairs in it. See a more elaborate explanation of how to this here

Here's what your code would look when you use dotenv

import mysql.connector
from mysql.connector import errors, errorcode
import os
from dotenv import load_dotenv

# Load environment variables from a .env file
load_dotenv()

DB_HOST = os.getenv("DB_HOST")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_NAME = os.getenv("DB_NAME")

# Connect to MySQL server
try:
    # Create a MySQLConnection object
    connection = mysql.connector.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )
    print(connection)

# Handle specific programming-related MySQL errors
except errors.ProgrammingError as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Access denied: incorrect username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)

The above method is more secure. If all your details are correct and the try block above runs successfully, it'll return the output below

If any of your login credential (username or password) is incorrect, the except block runs and you'll get the output below

However, if your credentials are correct but your database name is incorrect, you'll get a different output like the one below.

Executing SQL Statements (Working with Cursors)

To execute SQL query/queries in Python, you'll need to use a cursor, which is an instance of MySQLCursor class A cursor is an object used to execute SQL statements and retrieve data from a database. It is created after establishing a connection to the MySQL database, like we've done above. To be more exact, the cursor is created from your database connection object.

Creating a Cursor Object

cursor = connection.cursor()

Basic `cursor` Usage

The execute() method is used to execute queries. You have to provide the query as a string, either passed directly as an argument or stored in a variable first before passing to cursor.execute(). Once a query is passed to cursor.execute(), it is sent to the database engine for execution. See example of passing the query directly below:

# Create Books table
cursor.execute("""
CREATE TABLE IF NOT EXISTS Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(130) NOT NULL,
    price DOUBLE,
    publication_date DATE
)
""")

The more cleaner and readable way to do this is to first store the query as a string in a named variable. Then pass this variable to cursor.execute(). See how to do this below:

# Create Books table
create_book_table = """
CREATE TABLE IF NOT EXISTS Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(130) NOT NULL,
    price DOUBLE,
    publication_date DATE
)
"""
cursor.execute(create_book_table)

In the code above, the query is stored in the create_book_table variable. This variable is then passed to cursor.execute() for execution.

Fetching Data From the Result of SQL Query

There are various methods provided by MySQLCursor class, three (3) of which are used to retrieve the result set of a query.

fetchall() — This method retrieves/fetches all rows in the result set of the last executed statement, and returns them as a list of tuples.
fetchone() — This returns the next row of the result set as a tuple.
fetchmany(size) — this returns the next size number of rows. Size here represents the number of rows you want to retrieve. For instance, cursor.fetchmany(4) will return the next 4 rows from a query result.

Alright, as an example let's retrieve the schema information of the Books table we just created.

cursor.execute("DESCRIBE Books")
result = cursor.fetchall()
for row in result:
    print(row)

Once the code above is executed, you would receive an output like the one below.

Note: The cursor stores the result set for your query temporarily. The fetch methods is how we access the rows from memory.

Inserting Records in Tables Using `cursor.execute()`

Let's insert data (a book) into our Books table.

But before we get into it, remember the Books table we created has 4 columns, namely:

book_id
title
price
publication_date

Also, remember that the book_id column was defined with the AUTO_INCREMENT attribute when the table was created. Since the book_id column has the AUTO_INCREMENT attribute, you do not need to manually provide a value for it when inserting a new record. This is because, the database will automatically generate and assign the next available integer value for book_id each time a new row is inserted.

With these in mind, let's get into it practically.

from datetime import date

# Insert a new book into Books table
sql = """
    INSERT INTO Books (title, price, publication_date)
    VALUES (%s, %s, %s)
"""
data = ("Integrating Python with MySQL", 100.00, date(2025, 7, 19))

cursor.execute(sql, data)

# commit the changes
connection.commit()

print("Data committed successfully")

We imported the date class from the datetime module because the publication_date column in the Books table is of type DATE

Also, you may notice a connection.commit() statement at the end of the code. This is used to explicitly commit (save) any changes made to the database during the current session. In mysql-connector-python, changes made by operations such as INSERT, UPDATE, or DELETE are not automatically committed. The connector uses transactional behavior by default, meaning:

Your changes remain pending (in memory only)
They are not permanently saved to the database until you call commit()
If an error occurs before committing, you can use rollback() to undo the changes

When you forget to commit(), the following happens

Changes won't persist in the database
Once the connection is closed or rolled back, your uncommitted changes are lost.

Closing Connection

Once you are done working with the database, you must never forget to close the connection to release the resources used for the connection. Always close your cursor and connection to avoid resource leaks.

Here's how to do it

cursor.close()
connection.close()

A cursor leak happens when your program opens a connection or cursor but never closes it. Overtime, it can cause:

memory exhaustion or resource exhaustion on your system or the database server.
slower application performance.

When you interact with a database using Python, you create:

A connection object (to the database)
A cursor object (to send SQL statements and retrieve results)

Both processes use system and server resources, so you must always make sure to close them.

What Happens When Connection Fails

Now, although you can close your cursor and connection with the code above, it is not the recommended way to do it.

When you look at the code that handles the connection to MySQL server, if mysql.connector.connect() fails, this is what happens:

No connection object is created
Python jumps immediately to the except block
So cursor and connection do not exist yet

This becomes a problem because, if you try to run the:

cursor.close()
connection.close()

you'll get an error like this:

To avoid running into an error when trying to close your cursor and connection, it is recommended that you always check if the variables exist in your code and are valid before trying to close them.

The way I recommended is to use finally block in try-except-finally and have your cursor and connection variables initialized to None beforehand.

With this method, even if your try block fails before creating a connection, finally will still run. Remember that the finally block always runs irrespective of whether the prior code was successfully executed or not. And checking if the variable still has the value None or not protects against calling .close() on a variable that was never defined.

connection = None
cursor = None

finally:
    # Safely close cursor and connection if they were created
    if cursor is not None:
        cursor.close()
    if connection is not None and connection.is_connected():
        connection.close()

connection.is_connected() ensures connection is still open before closing it.

Conclusion

In this article, you learned how to integrate a Python application with a MySQL database using the mysql-connector-python library. We backed theory with practical examples on how to:

Install and use the mysql-connector-python driver.
Establish a connection to a MySQL database from Python.
Use a cursor object to execute SQL statements.
Create and insert data into a MySQL table using Python.
Retrieve data using fetchone(), fetchmany(), and fetchall().
Use connection.commit() to persist changes.
Handle potential database errors using try-except with errors and errorcode.
Properly close your cursor and connection to avoid resource leaks
Safely clean up connections using finally and None checks

Putting it all together:

import mysql.connector                                  from mysql.connector import errors, errorcode
import os
from dotenv import load_dotenv
from datetime import date

# Load environment variables from a .env file
load_dotenv()

DB_HOST = os.getenv("DB_HOST")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_NAME = os.getenv("DB_NAME")

# Initialize connection and cursor
connection = None
cursor = None

# Connect to MySQL server and execute queries
try:
    # Create a MySQLConnection object
    connection = mysql.connector.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )
    print(connection)

    try:
        # Create a cursor object
        cursor = connection.cursor()

        # Variable store for SQL query
        create_book_table = """
        CREATE TABLE IF NOT EXISTS Books (
            book_id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(130) NOT NULL,
            price DOUBLE,
            publication_date DATE
        )
        """

        # Create the Books table
        cursor.execute(create_book_table)

        # Get Books table schema (columns define in the Books table)
        cursor.execute("DESCRIBE Books")
        result = cursor.fetchall()
        for row in result:
            print(row)

        # Insert a new book into Books table
        sql = """
            INSERT INTO Books (title, price, publication_date)
            VALUES (%s, %s, %s)
        """
        data = ("Integrating Python with MySQL", 100.00, date(2025, 7, 19))

        cursor.execute(sql, data)

        # commit the changes
        connection.commit()
        print("Data committed successfully")

        # Retrieve all records in the Books table
        cursor.execute("SELECT * FROM Books")
        result = cursor.fetchall()
        print(result)

    except mysql.connector.Error as err:
        print(err)

# Handle specific programming-related MySQL errors during connection
except errors.ProgrammingError as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Access denied: incorrect username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)

finally:
    # Safely close cursor and connection if they were created
    if cursor is not None:
        cursor.close()
    if connection is not None and connection.is_connected():
        connection.close()

Finally, you would most likely be using version control during development, so make sure to add .env to your .gitignore. This is to ensure that you do not mistakenly commit and push .env (which contains sensitive information) to version control platforms like GitHub. Pushing your secrets to version control platforms exposes them to the public.

Getting Started with Databases: SQL & MySQL

Godbless Lucky Osu — Sun, 06 Jul 2025 22:09:59 +0000

Databases are at the heart of modern software systems. Having an efficient data storage and retrieval mechanism is essential in making sure that that platforms run smoothly. Whether it's an e-commerce platform, a mobile app, or a simple backend system, having an efficient way to store and retrieve data ensures the continued operations of the platform. Most applications would be useless without them.

This article walks through foundational database concepts using SQL and MySQL, based on my recent learning journey. Alright, let's get into it.

What is a Database?

A database is simply a collection of structured information (or data), typically stored electronically. Software applications generate large amount of data from user inputs (e.g a signup form), manipulation of existing data, etc. Therefore, having an organized way of storing these newly generated information, so that applications can access and retrieve them when needed, is essential.

This is why a database is important. It lets you arrange information in a structured way, making it easier to work with.

So in the technology world, a database is just one big central repository for storing data in a structured way that makes it easy for software applications to find and utilize information when needed. This here is the main purpose of a database — to store huge amounts of data for secure and speedy access by other programs.

How We Create Databases — Database Management System (DBMS)

If databases exist, then a way through which they are created must also exist right?

The way we create databases is by the use of something we call a Database Management System (DBMS).

A Database Management System is simply a software for creating and managing databases. It also enables user to create, query, update, modify, and delete data in a database. Think of it as an interface between you (or your application) and the raw data stored in your storage disk.

Core responsibilities of a database management system includes but not limited to creating databases and tables, adding, editing, and deleting data via queries, fetching specific data using queries, managing user access, security, backups, recovery, performance, etc.

A database management system has different components but one important component is the Storage Engine. The Storage Engine is the component responsible for how data is actually stored on disk. It interacts with the underlying file system of the operating system to store and manage data.

Read more about Database Management System (DBMS) here.

Types of Databases

Relational Databases

Relational databases (also called SQL databases) store and organize information into tables with rows (also called tuples) and columns (attributes). Relational (SQL) databases enforce a strict schema, meaning that the structure of the data (tables, columns, and data types) must be defined in advance. In a relational database, different tables have relationships between them (there is a link to explore what relationships are in SQL). These relationships are formed or established using primary keys and foreign keys. Relational databases are very well suited for systems that require data integrity and complex querying such as financial systems.

Talking about querying, relational databases support a language called Structured Query Language (SQL) for querying and manipulating data.

Some widely used database management systems that can be used for creating and managing relational databases include MySQL, MariaDB, PostgreSQL, Oracle. Each of these systems supports structured data stored in rows and columns, enforces relationships between tables, and uses SQL for interacting with the data. However, each has its own unique strengths and ideal use cases.

Must-Know Relational Database Concepts

Now, in a bid to keep this article as concise as possible, I’ll simply list these concepts as links so you can explore each one in more detail.

Non Relational Databases(or NoSQL Databases)

Relational databases have certain limitations e.g., they are not (or rather less) useful for storing unstructured data and are generally difficult to scale. Non relational databases in contrast are designed to handle unstructured and semi-structured data and are designed to scale horizontally.

Think of Non Relational Database (or NoSQL Database) as an umbrella term, that covers the different database models that typically do not implement SQL for defining or querying data. Each database model that does not support SQL have significant difference between them, but they are all collectively referred to as non relational database. Example, MongoDB (a non relational database) store data in the form of JSON-like documents, but Amazon DynamoDB (also a non relational database) store data as a collection of key-value pairs. They both differ significantly from each other but are both referred to as non relational or NoSQL database (simply because they do not use SQL for querying and manipulating data)

Popular examples of database management systems that can be used for creating and managing NoSQL databases includes MongoDB, Cassandra, Amazon DynamoDB.
NoSQL databases offer scalability, high performance, and are often preferred for big data, real-time applications, and distributed systems.

SQL and Common Data Types in SQL

Structured Query Language (SQL) is the standardized language for managing, manipulating, and querying data in relational databases. SQL provides a standardized way to perform various operations on databases.

1. Numeric Types

Used to store numbers.

INT, SMALLINT, BIGINT – Whole numbers
DECIMAL, NUMERIC – Precise decimal values
FLOAT, DOUBLE – Floating-point numbers (use with care for precision)

2. Character Types

Used to store text.

CHAR(n) – Fixed-length strings
VARCHAR(n) – Variable-length strings
TEXT – Long-form text

3. Date & Time Types

Used to store temporal values.

DATE – Stores date only (YYYY-MM-DD)
TIME – Stores time only (HH:MM:SS)
DATETIME, TIMESTAMP – Date and time

4. Boolean Type

Used for logical values.

BOOLEAN – Typically when you define a column as BOOLEAN MySQL automatiocally stored it as TINYINT(1). That is BOOLEAN = TINYINT(1). This column takes a BOOLEAN value (TRUE or FALSE) and store it as 0 or 1 (0 for FALSE and 1 for TRUE).

CHAR vs VARCHAR: What's the Difference?

CHAR(n) always stores exactly n characters (space-padded if shorter).
VARCHAR(n) stores up to n characters and saves space by only using what's needed.

Use CHAR when all entries have the same length (e.g., country codes), and VARCHAR for variable-length text (e.g., names or addresses).

SQL Commands You Should Know

CREATE — This is use to create a new database or new table in a database.

USE — This command works a bit like cd in linux. It tells the database management system to navigate and start using the selected database for all commands that would follow. Say we have database_A and database_B already created, and we want to create a table in database_B, how does the database management system know what database to use for creating the table? We'd have to specify what database we want to use for the command we're about to run. The USE command is how we tell MySQL for example, what database we want to work with.

Example Use Case:

CREATE DATABASE IF NOT EXISTS john_book_store;

USE john_book_store;

CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(130)
);

ALTER — The ALTER command is used to modify an existing table structure. Which means it can be used to add or remove a column in a table.

Now, let's add a new column (author_id) to our Books table above.

ALTER TABLE Books
ADD publication_date DATE;

DROP — The DROP command deletes an entire existing table or database with all its data. This should be used carefully as any data deleted is permanently lost.

Now, say we have a table named Authors that hold the information of various authors and we want to delete this entire table, this is how we do it.

DROP TABLE Authors;

INSERT — This one is used to add new row(s) into a table.

Let's add a new book (title = Adventure of John Doe) to the Books table.

INSERT INTO Books (book_id, title, publication_date)
VALUES (1, 'Adventure of John Doe', '2025-07-05');

UPDATE — I started writing this article yesterday during work period and couldn't finish it. Now if I want to change the publication_date of my book (Adventure of John Doe) which I just added to the Books table, I can do that using the UPDATE command.

Let's see how it works.

UPDATE Books
SET publication_date = '2025-07-06'
WHERE book_id = 1;

The WHERE statement is used to filter records. In SQL, it is called a Clause and there are other clauses as well, like ORDER BY, GROUP BY, JOIN, HAVING and some others. Clauses help us control what data should be read or modified and how data retrieved should be presented.

SELECT — The SELECT statement is used to retrieve data from a database table.
For example, say we want to retrieve the title of the book we just added, this is how we can do it.

SELECT title
FROM Books
WHERE book_id = 1;

This tells the database to retrieve only the title column in the table called Books. The addition WHERE clause filters the result by telling the database to return the title only for the row where book_id is equal to 1.

DELETE — This statement is used to remove a row or multiple row from a database table. Now let's delete the book we just added.

DELETE FROM Books
WHERE book_id = 1;

Note that the WHERE clause is recommended to filter the row or rows to be delete. Without it, all rows in the table will be deleted.

Combining Everything We've Learned: Let's Design an Online Bookstore

You should have MySQL installed and ready before going further. In case you do not have MySQL installed already, a simple google search should do the trick. There are numerous resources online that you can follow through step-by-step to setup MySQL on whatever Operating System (Windows, MacOS, Linux) you are using.

Now let’s see an example of how we might design a simple database for our bookstore:

Database Name: `john_book_store`

Tables:

-- Create the database
CREATE DATABASE IF NOT EXISTS john_book_store;

-- Use the database
USE john_book_store;

-- Now Let's start creating the schema (tables and their relationships)

-- Create the Authors table
CREATE TABLE Authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(215) NOT NULL
);

-- Create the Books table
CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(130) NOT NULL,
    author_id INT,
    price DOUBLE,
    publication_date DATE,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)       
);

-- Create the Customers table
CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(215) NOT NULL,
    email VARCHAR(215),
    address TEXT
);

-- Create the Orders table
CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- Create the Order_Details table
CREATE TABLE Order_Details (
    orderdetailid INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    book_id INT,
    quantity DOUBLE,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

Specific Explanations

CREATE DATABASE IF NOT EXISTS john_book_store; — This whole statement creates a new database named john_book_store, but only if it doesn't already exist. If there's already a database with that name, this line is skipped without error. The next line USE john_book_store will still run successfully, navigating to the existing database.

However, if you just do CREATE DATABASE john_book_store and the john_book_store database already exists, then the CREATE DATABASE john_book_store statement will fail, and your script would produce an error message similar to the one below.

I ran into that error while trying to create a database using Python.

AUTO_INCREMENT — This tells MySQL to automatically increase count (e.g., 1, 2, 3, ...) with each new row added to the specific column.
PRIMARY KEY — You should already be familiar with PRIMARY KEY if you explored the link I provided at the beginning of this article. This is how to practically implement it. Any column which has been specified as the PRIMARY KEY provides a way to identify each row in the table.

Note that it is recommended to define a PRIMARY KEY for any table you create.

FOREIGN KEY (author_id) REFERENCES Authors(author_id) ); — This statement sets up a FOREIGN KEY. That is, it tells the database that author_id in the Books table must match author_id in the Authors table.

Conclusion

Understanding databases is really important if you want to build modern applications. Almost every application today uses a database to store, update and retrieve information.

In this article, we've only explored basic concepts by introducing what databases are, the different types, and dived a bit deeper into SQL and it's basic commands.

We've only set the ball rolling, you should explore databases more and their use cases (e.g., when to use a SQL database and when NoSQL is ideal).

I'll be publishing a short article on how to connect Python to a SQL database soon, so watch out for it.

CONQUERING THE CLOUD RESUME CHALLENGE: MY JOURNEY

Godbless Lucky Osu — Fri, 24 May 2024 00:18:51 +0000

As someone coming from a non-IT background wanting to transition into the IT space and looking to build a career specifically as a DevOps Engineer, I enrolled in an AWS Cloud Computing program, while also taking time to learn other fundamental concepts. Upon completing the program, I successfully passed both the AWS Cloud Practitioner and Solutions Architect - Associate exams, earning both certifications.

However, I knew certifications were just not enough. I wanted to dive into real projects where I could roll up my sleeves and actually build something using AWS services. To accomplish this, I started looking out for simple projects that I could begin with, however, a friend recommended the Cloud Resume Challenge (although not beginner-friendly) as a great project to explore (and he wasn't wrong😊😊). So, I had a look at it and saw the challenge as an opportunity to bridge the gap between theory and practical experience with the services it covered. Little did I know the adventure that awaited me...

The cloud resume challenge is actually a challenge by Forrest Brazeal to help people prepare for a job in the cloud industry. This post therefore is my way of sharing my experience, challenges, and skills gained during my pursuit of the Cloud Resume Challenge.

You can find my deployed website here to explore the project in action. Also, my repository, which stores all the codes and resources.

The Beginning: How I Decided to Tackle the Challenge

Actually, the Cloud Resume Challenge consists of six(6) chunks of work (chunk 0-5) with each chunk consisting of a couple of steps to complete. I tackled the challenge one chunk at a time. Additionally, there are optional "Mods" sections (Developer, DevOps, and Security). Although optional, I decided to tackle the DevOps Mod alongside each chunk of the main challenge. It was an invaluable opportunity to go above and beyond in my pursuit of excellence, and to also expand my understanding of DevOps. I skipped the first chunk (chunk 0) since I already had the requirement——a Cloud Certification.

Chunk 1: Building the Frontend

Setting up the resume website's frontend seemed straightforward at first. I utilized HTML/CSS to craft a simple site and stored its contents in an S3 bucket. However, I later refined a template from BootstrapMade and used it instead. I wanted to create a CloudFront distribution to serve the content to viewers globally and even though I had done this before, I decided to read further about CloudFront. This is how I discovered that using a REST API endpoint as the origin, and restricting access with an Origin Access Control (OAC) was a more secure way to go about this as opposed to using a website endpoint as the origin (besides, this is AWS's recommended way). You can read about the differences between REST and website API endpoint here. With the above configuration, I made sure that my website was only accessible via CloudFront, not directly from S3.

Further to this, I created an SSL/TLS certificate in ACM, and attached it to the CloudFront distribution. This was to ensure that CloudFront uses secure HTTP (HTTPS) to serve the website.
I then configured Amazon Route53 to route traffic from my existing domain to my CloudFront distribution.

See a screen snip of the website below

DevOps Way

Lastly, upon verification that my website was working flawlessly, I decided to do things the DevOps way. I converted the S3, Route53, Certificate Manager and CloudFront resources into code using CloudFormation. Then, I set up a GitHub repository and pushed my website codes and CloudFormation template there.

See my architectural diagram for the frontend below.

Chunk 2 and 3: Tackling the Backend and Testing

As someone with no prior programming experience, Chunk 2 was a tough nut to crack. Most of my time was spent researching, experimenting, and seeking help from online resources and forums (including ChatGPT). I worked with serverless technologies (DynamoDB, Lambda, API Gateway) and built the application using AWS SAM. Everything——from writing the Lambda function using Python, workshopping permissions between Lambda and DynamoDB, to configuring Amazon API Gateway and leveraging JavaScript fetch API to update and retrieve visitor count——felt like a puzzle to solve.

Another significant challenge I faced was navigating CORS errors and authenticating token issues. After days of persistence, I finally solved the whole puzzle and had all sevices communicating smoothly.

DevOps Way

To wrap up the chunk 2, I set up monitoring for my backend using CloudWatch, Amazon SNS and Lambda. The approach was simple yet effective: I set up CloudWatch Alarms to monitor certain metrics, have notifications sent to my SNS topic when any Alarm enters an alarm state and finally, SNS topic triggers Lambda which in turn extracts the error message from SNS and sends this message to my slack channel, using the slack credentials stored as a parameter in AWS SSM.

The integration of these tools not only enhanced the resilience of my application but also deepened my understanding of DevOps principles and practices.

See my architectural diagram for the backend below.

Testing

I embraced Cypress for writing end-to-end API and website testing. Cypress has good documentation, so it was pretty easy to get around it.

Conclusively, Chunk 2 especially was a challenging yet rewarding chapter in my Cloud Resume Challenge journey as it helped me gain invaluable insights into backend development and the concept of serverless.

Chunk 4: CI/CD and Infrastructure as Code

This phase of the project although not less significant, was easier and faster to accomplish. I guess one of the reasons was because I had automated the creation of all resources using CloudFormation and AWS SAM. I did make an adjustment which was to transfer my CloudFormation resources into my AWS SAM template. This was because I found out that AWS SAM supports the syntax of CloudFormation. So, I didn't see the need for having two different templates plus, it helped simplify the build and deploy process.

I also implemented CI/CD with GitHub Actions. Since I had a single repository for both backend and frontend, I implemented a multi-job workflow with separate YAML files and both are triggered upon any push to the main branch. One YAML file defines a job that synchronizes website contents to the designated S3 bucket and the other defines a job that builds, deploys, and tests the application built with AWS SAM. I used GitHub's OIDC provider to authenticate to AWS and allow my workflow access to the needed resources in my AWS account. This was to follow security best practice of using temporary credentials for AWS access.

You can see in the screen snips of both successful jobs below, that the second job includes a step for configuring AWS credentials.

Bringing It All Together

The true magic happened when I pulled down all the application resources and redeployed everything just by pushing a change to my repository. I saw all the pieces converged together to form the complete application. I was very thrilled to see the frontend of my website come to life, seamlessly integrated with backend services and automated testing, without having to spend time manually configuring anything again. It made me trully appreciate the power of automation and CI/CD.

See the Complete architecture below

Deployed Website

You can find my deployed website here to explore the project in action. Also, my repository, which stores all the codes and resources.

Lessons Learned and Growth Achieved

Through the Cloud Resume Challenge, I discovered the benefits of automation, version control, CI/CD, monitoring and observability, iteration, continuous learning, and of course the importance of implementing proper application security measures. Each chunk I took and conquered became an opportunity to grow, whether it was troubleshooting errors, reading documentations, getting familiarized with a new technology, or reiterating on the way I went about solving a particular problem. As a result, I've built some invaluable skills.

Looking Ahead

After completing the challenge and reflecting on my journey, I realize that the Cloud Resume Challenge is just the beginning. With the newfound skills and confidence I picked, I'm more determined than ever to pursue my goal of becoming an excellent DevOps Engineer. I will be taking time to build my programming skills with Python. In addition, I will be taking on even more projects, starting with a project by David Thomas (Improving application performance using Amazon Elasticache). The journey would undoubtedly be challenging, but just like the Cloud Resume Challenge, I know the rewards will also be undoubtedly worth it.

Final Thoughts

To anyone considering embarking on the Cloud Resume Challenge, my advice is simple: take the leap. As Forrest Brazeal aptly put it, "It's not a tutorial; it's a project spec." And what a project it turned out to be! It's a journey of conquering challenges, unlocking new skills, and discovery of the endless possibilities there are in the Cloud.

Collaboration

If you have some cool project and would like some collaboration to learn and tackle it together, you can reach out to me on LinkedIn. Let's connect and explore the boundless possibilities of cloud technology, and create innovative solutions together.

Deploy a Static Website Using Amazon S3 (detailed)

Godbless Lucky Osu — Thu, 18 Apr 2024 15:38:45 +0000

INTRODUCTION

DEPLOY A STATIC WEBSITE ON AMAZON S3
First Task: Creating an S3 bucket to host the static website contents
Second Task: Uploading Source Code to your S3 Bucket
Third Task: Creating a Bucket Policy to Grant Public Read Access

ENHANCE DATA SECURITY ON AMAZON S3 THROUGH A PROTECTIVE MEASURE: VERSIONING
First Task: Enabling versioning
Second Task: Confirming that versioning works as expected

OPTIMIZE COSTS OF S3 OBJECT STORAGE BY IMPLEMENTING A COMPREHENSIVE DATA LIFECYCLE PLAN

INTRODUCTION

One of the simplest architectures that can be implemented on AWS is creating a static website by hosting it entirely on Amazon S3 (Simple Storage Service). It is especially suitable for businesses that want to establish a web presence, showcasing their business details such as location, opening and closing hours, services and products, achievements, testimonials, who they are, etc. Simply put, Amazon S3 in addition to other use cases, can be used to host static websites if you configure the S3 bucket for website hosting. It is important to note, though, that S3 does not support server-side scripting (that is, dynamic websites); however, AWS offers other services that enable you to host dynamic websites.

S3 is ideal because of the various benefits it offers which include scalability, durability (11 9s of durability), availability (four 9s of availability), fine-grained security and performance.

In Amazon S3, data files are stored as objects (you can read more about object storage here: cloud.google.com/learn/what-is-object-storage). Objects are placed in a bucket that you define yourself. The name of your bucket must be globally unique across all AWS Regions. You can store as much objects as you want/need, however, a single/individual object cannot be larger than 5TB.

To get more details about S3, including its storage classes and storage management, see the Amazon S3 Developer Guide

Moving forward, the content of today’s post will focus on using Amazon Simple Storage Service (Amazon S3) to build a static website and implementing architectural best practices to protect and manage your data.

Here’s what we’d be doing:

Deploy a static website on Amazon S3.
Enhance data security on Amazon S3 through a protective measure.
Optimize Cost by implement a comprehensive data lifecycle plan within Amazon S3.

Alright, let's dive in already!

DEPLOY A STATIC WEBSITE ON AMAZON S3

Note: Before starting, make sure you have all the website codes extracted and ready for uploading. I got the demo code for this from free-css.com/free-css-templates/page290/bri..

To host the static website, we will be creating an S3 bucket, uploading the code to the bucket, and creating a bucket policy to automatically make the website contents publicly accessible on the web even when new changes to codes (i.e. new objects) are uploaded (this is because by default all objects uploaded to an S3 bucket are private).

First Task: Creating an S3 bucket to host the static website contents

Use the search feature in the AWS console, and search for s3. Click on the Create bucket button

Input a unique bucket name (remember that S3 bucket names must be globally unique across all regions). In my own case, I used my name which is globally unique by the way (winks).
Select a region to create your bucket. (consider proximity to customers when selecting a region)

Disable Block all public access. You need to allow public access to the S3 bucket containing your website files, if you want your website to be publicly accessible on the internet. Disabling "Block all public access" ensures that the necessary permissions are set to make your website content publicly readable.
Click on the acknowledgement check button to confirm your agreement.

Now, scroll down and click on Create bucket. Congratulations! You just created your bucket successfully.

Now that your bucket has been created, you have to Enable static website hosting on your bucket. Enabling static website hosting provides you with an endpoint URL where customers or the public can access your static website.

To enable website hosting on your bucket, scroll down in your S3 console. From the list of S3 buckets, select the bucket that you just created (the bucket you’re looking for will ideally carry the unique name you specified during creation).

Within the selected bucket, go to the "Properties" tab. This is where you can configure various settings for your S3 bucket.

Under the properties tab, scroll down to the bottom, and you’ll find Static Website Hosting. Click on Edit in the top-right corner.

Select enable, leave the hosting type as Host a static website, and specify the index document (usually index.html) and, optionally, the error document.

Scroll down and click on Save changes. You now have an endpoint URL where customers can access your static website.

Second Task: Uploading Source Code to your S3 Bucket

You now need to upload the static website code to S3 bucket

Navigate back to the "Objects" tab.
Click on "Upload" to open the file upload interface.

Select Add files to bring up your directories. Navigate to the directory containing the source code files of your static website. This can includes HTML, CSS, JavaScript, images, and others. You can either use the file selection dialog to choose the files, or you can drag and drop them into the upload area.

Note: When hosting a static website on Amazon S3, it's important to remember that S3 does not support server-side scripting. Make sure JavaScript included in your files is executed on the client side only. Server-side scripting is not part of S3 functionality and so, would not be processed. For dynamic content, consider using a server-based hosting solution

Click the orange colour "Upload" button and wait for some seconds/minutes for your files to upload into S3.

You will see a "Succeeded" status in all your uploads, like the screenshot below.

Congratulations! You've successfully uploaded your source codes into the S3 bucket.

Note: By default, these objects uploaded to the S3 bucket are private, meaning they can only be accessed by you (the AWS account that owns the bucket). To make your static website publicly accessible, you have to configure a bucket policy that grants read access to everyone (public access). This will take us to the third task.

Third Task: Creating a Bucket Policy to Grant Public Read Access

Navigate to the "Permissions" tab and scroll downward to find Bucket policy.

Click on the Edit button on the far right of the screen.

Scroll down and click on "Add new statement", you can also find it if you look toward the right of your screen.

Locate the Filter services (search bar at the far right of the screen), type in S3 and click on the first option (S3)

Scroll down to locate GetObject and click on it. > Note: "s3:GetObject" is a permission that grants the right to retrieve objects (files) from the specified S3 bucket.

Add the wildcard "" to the "Principal*" in the policy.
Copy your Bucket ARN just above the policy space and paste it in your "Resource." Note that you must add /* just after the pasted policy. The /* means that the policy affects all objects (new and old) in your bucket.

Scroll down and click on Save changes.

Your final bucket policy should look like this below:

Note that the ARN format for Amazon S3 resources is as follows:

arn:aws:s3:::bucket_name/object_key

Therefore, your own Bucket ARN would be different. If you are copying this policy, make sure you change the ARN to reflect yours. Read more about adding a bucket policy in S3 here

Here's the JSON code:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Statement1",
            "Effect": "Allow",
            "Principal": "*",
            "Action": [
                "s3:GetObject"
            ],
            "Resource": "arn:aws:s3:::godbless-v3-s3-bucket/*"
        }
    ]
}

Note: This policy grants the "s3:GetObject" permission to everyone ("Principal": "") for all objects in your bucket ("Resource": "arn:aws:s3:::godbless-v3-s3-bucket/"). This means that anyone can read the objects in your S3 bucket.

Alright, time to get the reward for our hard work so far. Remember the last step in the First Task right? Now is the time to use it. Navigate to the Properties tab again and scroll down to Static website hosting, where you will find your website endpoint (URL).

Open it in a new browser tab, and if you did everything right, you should find your website application. Congratulations! You just successfully hosted your website and made it publicly accessible.

ENHANCE DATA SECURITY ON AMAZON S3 THROUGH A PROTECTIVE MEASURE: VERSIONING

One Amazon S3 best practice is enabling Versioning. It is very likely that you will need to make many updates to your website as a number of things change.

If you are working with critical data or in collaborative environments where multiple users contribute to data, versioning can help prevent accidental data loss (a strategy to prevent the accidental overwrite and deletion of website objects).

Versioning keeps multiple versions of an object in the same bucket, preserving, retrieving, and restoring every version. If you delete an object, Amazon S3 inserts a delete marker, making it the current version. You can restore previous versions at any time.

When you overwrite an object, a new version is created. Buckets can be unversioned (default), versioning-enabled, or versioning-suspended.

Note: Once versioning is enabled, it cannot be changed back to unversioned, but you can suspend versioning on a bucket if needed. Also, enabling versioning can increase storage costs because each version of an object consumes additional storage.

For further information about versioning, see https://docs.aws.amazon.com/AmazonS3/latest/userguide/Versioning.html

Alright, now let's dive in already.

First Task: Enabling Versioning

In the S3 console, navigate to the "Properties" tab, locate Bucket versioning and click on "Edit".

In the new window, choose Enable and click on Save changes

Versioning has been enabled in your bucket.

Second Task: Confirming that Versioning works as expected

Objects in S3 are immutable, meaning that once you upload an object, you cannot modify it directly. Therefore, if you want to make changes to a file, you typically need to make the necessary modifications or updates to the file on your local computer. Then you can upload the modified file back to the S3 bucket, which creates a new version of the object.

You could change your whole HTML code in your index.html file, or even your CSS or Javascript code.

To confirm that versioning has been implemented in this case, we are only going to modify a single line of code in the index.html file. Let's get started!

Make the changes you want to the appropriate file and save it.

Note that if you can’t locate the file on your local computer anymore, you can simply download the file from S3 to your local computer and make the changes. To do this, simply navigate to the "Objects" tab, locate the object you want to make changes to (in my case, the index.html file), select it by clicking on the check button, and the download button should come alive. Click on it to download your file. I used the index.html file only as an example here; you can make changes to any file you want.

Upload the updated file to your S3 bucket. (I already showed you how to do this in the second task we did when deploying our website).
When your file has been successfully uploaded, go back to the Objects tab and click on "Show versions" as shown below. You should see the current version of your file as well as the previous versions.

Now, when you open yoru website, you will notice the changes you made. See the screenshot of mine below. I only made modifications to a single line of code

OPTIMIZE COSTS OF S3 OBJECT STORAGE BY IMPLEMENTING A COMPREHENSIVE DATA LIFECYCLE PLAN

When you enable versioning, the size of the S3 bucket will continue to grow as you upload new objects and versions. To save costs, you can choose to implement a strategy to retire some of those older versions.

Implementing or configuring a lifecycle policy will automatically move older versions of objects between storage classes or Permanently delete previous versions of objects, depending on the action you specify. This cycling reduces your overall cost because you pay less for data as it becomes less important over time.

A lifecycle configuration is a set of rules that define actions that Amazon S3 applies to a group of objects.

In our case, we are going to configure a policy with two rules. The first rule will move older versions of the objects in our source bucket to S3 Standard-Infrequent Access (S3 Standard-IA) after 30 days (1 month). The second rule will eventually expire the objects after 365 days (a year).

Let's get started…

Navigate to the "Management" tab, locate Lifecycle rules and click on "Create lifecycle rule"

In the new window, enter your rule name.

Choose the scope of the lifecycle rule. Select "Apply to all objects in the bucket" and click on "I acknowledge that this rule will apply to all objects in the buckets".

Note: If you want to limit the rule to specific objects, then select the first option and specify the object prefix, tag key and value. (in our case, we want the rule to affect all objects in our bucket, which is why we went with the second option).

To set the first rule which will move older versions of the objects in your source bucket to a different storage class;

Choose "Move noncurrent versions of objects between storage classes".
Choose your desired storage class. Here we are going with Standard-IA.

Input the number of days after which older versions of objects will move to the new storage class. (here, I choose 30 days after which objects would move into Standard-IA).

To set a second rule which will expire the objects after a particular period of time;

Scroll back up and select "Permanently delete noncurrent versions of objects".

Scroll down, and you'll see a new rule has appeared, specify the number of days (in our case, we choose 365 days).

Scroll down and click on Create rule.

Hurray! You have successfully configured a lifecycle for your objects to manage how they are stored throughout their lifecycle.

The policy we’ve just set above will move previous versions of your source bucket objects to S3 Standard-IA after 30 days. The policy will also permanently delete the objects that are in S3 Standard-IA after 365 days.

For more information on Lifecyle Policy, see this AWS Documentation.

CONCLUSION

We successfully utilized Amazon S3 to host a static website, leveraging its simplicity and scalability. By following the outlined steps, we created an S3 bucket, uploaded the website source code, and configured static website hosting. This approach is particularly advantageous for businesses aiming to establish a web presence and showcase essential information.

Additionally, we implemented key architectural best practices to enhance data security (Versioning) and optimize costs (Lifecycle policy).

Forem: Godbless Lucky Osu

Integrating Python with MySQL Databases: An Introduction and Practical Guide

Table of Contents

Connecting MySQL with Python

Installing MySQL

Installing MySQL Connector/Python

Establishing an Actual Connection with MySQL Database

Executing SQL Statements (Working with Cursors)

Creating a Cursor Object

Basic cursor Usage

Fetching Data From the Result of SQL Query

Inserting Records in Tables Using cursor.execute()

Closing Connection

What Happens When Connection Fails

Conclusion

Getting Started with Databases: SQL & MySQL

What is a Database?

How We Create Databases — Database Management System (DBMS)

Types of Databases

Relational Databases

Must-Know Relational Database Concepts

Non Relational Databases(or NoSQL Databases)

SQL and Common Data Types in SQL

1. Numeric Types

2. Character Types

3. Date & Time Types

4. Boolean Type

CHAR vs VARCHAR: What's the Difference?

SQL Commands You Should Know

Combining Everything We've Learned: Let's Design an Online Bookstore

Database Name: john_book_store

Tables:

Conclusion

CONQUERING THE CLOUD RESUME CHALLENGE: MY JOURNEY

The Beginning: How I Decided to Tackle the Challenge

Chunk 1: Building the Frontend

DevOps Way

Chunk 2 and 3: Tackling the Backend and Testing

DevOps Way

Testing

Chunk 4: CI/CD and Infrastructure as Code

Bringing It All Together

Deployed Website

Lessons Learned and Growth Achieved

Looking Ahead

Final Thoughts

Collaboration

Deploy a Static Website Using Amazon S3 (detailed)

Table of contents

INTRODUCTION

DEPLOY A STATIC WEBSITE ON AMAZON S3

First Task: Creating an S3 bucket to host the static website contents

Second Task: Uploading Source Code to your S3 Bucket

Third Task: Creating a Bucket Policy to Grant Public Read Access

ENHANCE DATA SECURITY ON AMAZON S3 THROUGH A PROTECTIVE MEASURE: VERSIONING

First Task: Enabling Versioning

Second Task: Confirming that Versioning works as expected

OPTIMIZE COSTS OF S3 OBJECT STORAGE BY IMPLEMENTING A COMPREHENSIVE DATA LIFECYCLE PLAN

CONCLUSION

Basic `cursor` Usage

Inserting Records in Tables Using `cursor.execute()`

Database Name: `john_book_store`