MySQL in Python Tutorial: Getting Started

MySQL is a relational database management system (RDBMS) that adopts a client-server model. All this means is that a computer (the “client”) that wants to access data must connect to the RDBMS server to do so. 

Python, on the other hand,  is a popular high-level, general-purpose programming language. Developers often use it to build websites and software, automate tasks, and analyze data. It can also be used with various databases, including MySQL. Python supports data query statements, data definition language, and data manipulation. This tutorial will show you how to work with MySQL in Python. 

Getting Started with MySQL in Python

To use MySQL in Python from your DataCamp workspace, you must use workspace integrations to connect to an existing hosted database. We are building our database from scratch, so we will not use DataCamp workspace for this tutorial. 

These are the two steps you must carry out to follow along with this tutorial: 

1. Install MySQL server to provide the services required for the managing the database. You can download and install it from the MySQL website. 
2. Install MySQL connector which is a Python drive that helps you integrate your Python applications to your MySQL databases; this can be installed simply with pip (Python’s package manager).

Once you’ve installed MySQL server: 

1. Clone this GitHub repository to your local machine – how to clone a repository on Github.
2. Create and activate a virtual environment – creation of vitrual environments. 
3. Install the packages requried using pip install -r requirements from the terminal.  
4. Create a .env file in the root directory.
1. Store the password to your authenticate your user with the MySQL server in a variable called PASSWORD – If you did not assign a root password, then the server does not require a password.  

Disclaimers

Each file in the repository is prefixed with a number to indicate its order in this tutorial.

For example,  04_data_insertion.py indicates that the module should be run 4th (after modules 01 - 03). You can also execute the entire code by running 00_execute_all.py.

Establishing a connection

MySQL follows a server-client model; Anytime a client wants to access data, it must connect to the RDBMS. 

The way to establish a connection with the server is to use the connect() method from the mysql.connector module…

import os

from mysql import connector
from dotenv import load_dotenv


load_dotenv()
PASSWORD = os.getenv("PASSWORD")

try:
    with connector.connect(
        host = "localhost",
        user = "root",
        password = PASSWORD
    ) as database:
        print(database)
except connector.Error as e:
    print(e)

"""
<mysql.connector.connection_cext.CMySQLConnection object at 0x00000188FA653970>
"""

See the full scope of arguments for the connect() method in the documentation. 

There are three things to note about this code: 

1. Exception and Error handling: It’s good practice to handle errors while establishing a connection to the MySQL server. 
2. Close the connection: We used a context manager, so we did not explicitly close the connection using the close() method. In general, connections must be closed once you’ve finished accessing the database to prevent unexpected errors from occurring. 
3. Sensitive data: Use environment variables to hide sensitive information, as sharing it poses a security threat. 

Now you’ve established a connection between your Python program and the MySQL server, you’re ready to connect to one of the many databases that may be available on the server. At present, we have none; this means we will have to create a new database inside the server. Let’s see how to do this in the next section. 

Note: Anytime the database is accessed, a connection with the server must be established. In further code examples, we will remove this from the code to prevent redundancy throughout the tutorial. However, it is included in the codebase.

Creating a new database in MySQL with Python

From here on, you’ll need SQL skills; DataCamp’s Introduction to SQL course is a good starting point to get you up to scratch. 

The typical way to create a database in MySQL is as follows: 

CREATE DATABASE book_ratings;

To perform the same query in Python, you must create an instance of the cursor() object. 

From the cursor() object,  you can SQL commands as a string to the execute() method, which will execute the code. 

Let’s create a database called “book_ratings…”

create_db = "CREATE DATABASE book_ratings"
with database.cursor() as cursor:
    cursor.execute(create_db)

Note: Running this code multiple times will raise an error to inform you the database already exists.

You can check the databases existing on a server as follows:

show_existing_db = "SHOW DATABASES"

with database.cursor() as cursor: 
    cursor.execute(show_existing_db)
    for db in cursor:
        print(db)

"""
('book_ratings',)
('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)
"""

The code above outputs the names of all the databases currently in our MySQL server. There i’s more than one because the MySQL server automatically generates databases to provide access to a variety of database metadata and server settings.

Connecting to an existing database

Let’s assume you’ve already created a database, but you’ve yet to connect to the server; how would you connect to the database directly? 

Easy. 

Pass the name of the database as an argument to the connect() method:

try:
    with connector.connect(
        host = "localhost",
        user = "root",
        password = PASSWORD,
        database = "book_ratings"
    ) as database:
       
        print(database})

except connector.Error as e:
    print(e)

"""
<mysql.connector.connection_cext.CMySQLConnection object at 0x0000016DD8C24970>
"""

Creating Tables

Information in a database is stored in a table. A table is merely a format that is used to store a  collection of related data consisting of columns and rows. 

There may be multiple tables within a database, even if they aren’t related to each other. You could also have a relational database containing a single table, as we will demonstrate in this section – expand your knowledge on relational databases with the Introduction to Relational Databases in SQL course. 

Table creation follows a similar approach to what you’ve seen already; 

• Write an SQL command as a string 
• Pass it to the execute() method of the cursor object. 

The SQL statement in this instance is CREATE TABLE.

Let’s use this to create a table for books:

create_books_table = """
CREATE TABLE books(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    genre VARCHAR(100),
    release_year YEAR(4)
)
"""
# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(create_books_table)
    existing_database.commit()

In the last line of the code, you may notice we used the commit() method on our existing_database object. We had to do this since the default behavior of the MySQL Python connector is not to automatically commit a transaction. 

Anytime you modify data for a table, you must call the commit() method – learn more about the commit() method in the MySQL docs. 

Some other important things to note in the code include: 

• NOT NULL: A constraint to enforce a column to not accept NULL values. 
• PRIMARY KEY: A unique value used to identify specific rows in a table. 
• AUTO_INCREMENT: Automatically generate a unique number for new records inserted into the table. 

We can check to see if our schema was created properly by using the DESCRIBE statement and then printing all the elements using the fetchall() method on our cursor object…

describe_books = "DESCRIBE books"
with existing_database.cursor() as cursor:
    cursor.execute(describe_books)
    books_schema = cursor.fetchall()
    for column in books_schema:
        print(column)

"""
('id', b'int', 'NO', 'PRI', None, 'auto_increment')
('title', b'varchar(100)', 'YES', '', None, '')
('author', b'varchar(100)', 'YES', '', None, '')
('genre', b'varchar(100)', 'YES', '', None, '')
('release_year', b'year', 'YES', '', None, '')
"""

Data insertion

In the previous sections, we created a table called books in our book_ratings database. At the moment, this table is empty. The next section will cover two approaches you have at your disposal to populate a table. 

Inserting a single record

There may be an instance where you have to insert a single record into a table. Although this is rare in a real-world instance, it’s still possible for you to do so as follows:

# INSERT SINGLE RECORD
insert_single_record = "INSERT INTO books (id, title, author, genre, release_year)\
    VALUES (%s, %s, %s, %s, %s)"
single_record = (
    "1", "Choose Yourself! Be Happy, Make Millions, Live the Dream", "James Altucher", "self-help", "2013"
    )

# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(insert_single_record, single_record)
    existing_database.commit()

Inserting multiple records

The more common way you’ll be inserting data into a table is with the executemany(operation_to_perform, seq_of_params) method of the cursor object; this allows you to insert multiple records at once. 

The structure of the operation we want to perform remains the same; we only update the sequence of parameters. 

Here’s how it looks in code: 

multiple_records = [
    (
        "2",
        "Skip the Line: The 10,000 Experiments Rule and Other Surprising Advice for Reaching Your Goals",
        "James Altucher",
        "self-help",
        "2021"        
    ),
    (
        "3",
        "The Power of No: Because One Little Word Can Bring Health, Abundance, and Happiness",
        "James Altucher",
        "self-help",
        "2014"
    ),
    ]

with existing_database.cursor() as cursor:
    cursor.executemany(insert_multiple_records, multiple_records)
    existing_database.commit()

Selecting data 

The SELECT statement is used to retrieve rows selected from one or more tables in a database. We can use SELECT in one of two ways: 

1. Select specific columns from a table.

select_specific_cols = "SELECT author, release_year FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_specific_cols)

1. Return all columns using an asterisk “*”

# Create cursor object
select_all_cols = "SELECT * FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_all_cols)

Sorting and Filtering data 

Sorting and filtering data enables you to return results you care about by providing specific criteria for the records to meet. 

Here are a few examples: 

WHERE

The WHERE statement lets you filter data based on a specified condition. 

For example, let’s say we want to extract the author's name and book title for any book made before and including 2010: 

condition = "SELECT author, title FROM books WHERE release_year <= 2010"
with existing_database.cursor() as cursor:
    cursor.execute(condition)

ORDER BY

We can also use the ORDER BY statement to sort the results in ascending (ASC) or descending (DESC) order. Note that the default mode is always in ascending order unless you explicitly state otherwise. 

Here’s how we would order our data by the release year in descending order:

order_by_year = "SELECT * FROM books ORDER BY release_year DESC"
with existing_database.cursor() as cursor:
    cursor.execute(order_by_year)

LIMIT

The LIMIT statement allows you to specify the number of records to return in the result set.

# Create cursor object
define_limit = "SELECT * FROM books LIMIT 3"
with existing_database.cursor() as cursor:
    cursor.execute(define_limit)

The code above limits the number of records to be returned to three. 

Updating and Deleting data

There will be times when data changes, is no longer useful, or is wrong. In this section, you’ll learn how to manage these instances with updates and deletions. 

UPDATE

The UPDATE query enables you to change existing values within a table without affecting its structure. 

For example, you may have to update a record if someone changes their name; This is what it would look like in code:

update_query = """
UPDATE
    books
SET
    author = "Big J"
WHERE
    author = "James Altucher"
"""
with existing_database.cursor() as cursor:
    cursor.execute(update_query)
    existing_database.commit()

DELETE

The DELETE query allows you to delete a record from a table:

drop_record = "DELETE FROM books WHERE release_year <= 2000"
with existing_database.cursor() as cursor:
    cursor.execute(drop_record)
    existing_database.commit()

In this code, we deleted all records with a release year less than or equal to 2000. 

DROP TABLE

The DROP TABLE statement lets you permanently remove a table from a database. Appending IF EXISTS allows you to cater to scenarios where you’re unsure if the table you wish to drop exists, but it’s totally optional. 

Let’s drop the table we created in our book_ratings database: 

drop_table = "DROP TABLE IF EXISTS books"
with existing_database.cursor() as cursor:
    cursor.execute(drop_table)
    existing_database.commit()

Conclusion

In this tutorial, you’ve learned how to establish a connection with a MySQL server in Python, create a new database, connect to an existing database, create a table, and perform various query operations on a MySQL table from Python.  

Check out the following resources to continue your learning:  

• Introduction to Databases in Python 
• SQL Server, PostgreSQL, MySQL… What’s the difference? Where do I start?
• SQL Courses for Beginners