In our last tutorial, we explained how to connect to PostgreSQL from within a Python application and how to perform create, read, update and delete (CRUD) operations on PostgreSQL. This tutorial will be similar, but instead of PostgreSQL, we’ll to connect a Python application to a MySQL database, which is another commonly used relational database. Specifically, we’ll show you how to perform a variety of database operations on MySQL from within your Python application.

To execute the scripts in this tutorial, you need to have an instance of a MySQL database running on your localhost or any server you can access. To see how to download and install MySQL for various operating systems, take a look at this link.

Installing Required Libraries

You’ll be using Python’s mysql-connector-python module to perform database operations on MySQL databases in this tutorial. To download the module, execute the following script on your command terminal.

pip install mysql-connector-python

Performing CRUD Operations on MySQL via Python

Connecting to MySQL Server

Before you can perform any operation on a MySQL database, you need to connect to the MySQL server instance that contains the database. To connect to a MySQL server instance, you’ll need the hostname, username and password for the MySQL server instance. The following script initialize variables that contain these values.

import mysql.connector

host_name = "localhost"
user_name = "root"
password = ""

Update the values in the strings to match your database, then pass the hostname, username and password to the connect() method of the mysql.connector module, as shown below.

my_con = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=password
        )

The connect() method returns a connection object which can subsequently be used to perform different operations directly on your MySQL server.

Creating a Database

To create a database, you have to write a string query for database creation. Specifically, you need to get the cursor from the connection object. The cursor object is used to execute queries on MySQL server. Next, to create a new database, the CREATE DATABASE query is passed to the execute() method of the cursor object as shown in the following script.

cursor = my_con.cursor()
query = "CREATE DATABASE patient_db"
cursor.execute(query)

The above script creates a dummy database named patient_db. To perform any operation on your new database, you first have to connect to the database. Previously, we created a connection to the MySQL server, now we need to connect to the patient_db database as shown below.

host_name = "localhost"
user_name = "root"
password = ""
db_name = "patient_db"

my_db_con = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=password,
            database=db_name
        )

Now that we have established a connection with the patient_db database, we are ready to execute queries on this database.

Get Our Python Developer Kit for Free

I put together a Python Developer Kit with over 100 pre-built Python scripts covering data structures, Pandas, NumPy, Seaborn, machine learning, file processing, web scraping and a whole lot more - and I want you to have it for free. Enter your email address below and I'll send a copy your way.

Yes, I'll take a free Python Developer Kit

Creating a Table

The first operation we’ll perform on the patient_db database is to create a new table. To create a new table, the query that creates the table is passed to the execute() method of the cursor object as shown below. The following script creates a table named patients with four columns: id, name, age and sex.

create_table_query = """
CREATE TABLE IF NOT EXISTS patients (
  id INT AUTO_INCREMENT,
  name TEXT NOT NULL,
  age INT,
  sex TEXT,
  PRIMARY KEY (id)
) ENGINE = InnoDB
"""

cursor = my_db_con.cursor()
cursor.execute(create_table_query)
my_db_con.commit()

)

Inserting Records

Let’s now insert some records in our new patients table you created in the last section. To insert records, you have to pass the INSERT query to the execute() method of the cursor object. The following script creates a query that inserts records of 5 fictional patients in the patients table.

create_patients_query = """
INSERT INTO
  `patients` (`name`, `age`, `sex`)
VALUES
  ('Laura', 24, 'female'),
  ('Jospeh', 41, 'male'),
  ('Angel', 33, 'female'),
  ('Elisabeth', 37, 'female'),
  ('Joel', 19, 'male');
"""

In the following script, the query that inserts records is passed to the execute() method of the cursor object. Once you execute the script below, your 5 records will be inserted in the patients table.

cursor = my_db_con.cursor()
cursor.execute(create_patients_query)
my_db_con.commit()

Reading Records

To select a record, you simply have to write a SELECT query and store it in a string variable. The query is then passed to the execute() method of the cursor object. To return actual records, you need to call the fetchall() method of the cursor object. The following script fetches all the records from the patients table and displays them on the console.

select_patients_query = "SELECT * FROM patients"
cursor.execute(select_patients_query)

patients = cursor.fetchall()

for patient in patients:
    print(patient)

Output:

(1, 'Laura', 24, 'female')
(2, 'Jospeh', 41, 'male')
(3, 'Angel', 33, 'female')
(4, 'Elisabeth', 37, 'female')
(5, 'Joel', 19, 'male')

Updating Records

Updating records is similar to inserting and selecting records. The UPDATE query is passed to the execute() method of the cursor object. The following script updates all the records in the patients table by replacing “female” with an “f” in the sex column.

update_patients_query = """
UPDATE
  patients
SET
  sex = 'f'
WHERE
  sex = 'female'

"""

cursor.execute(update_patients_query)
my_db_con.commit()

To see if the records are actually updated, execute the SELECT query as shown in the following script:

select_patients_query = "SELECT * FROM patients"
cursor.execute(select_patients_query)

patients = cursor.fetchall()

for patient in patients:
    print(patient)

The output below shows that in the sex column, all values of “female” have been replaced with an “f”.

Output:

(1, 'Laura', 24, 'f')
(2, 'Jospeh', 41, 'male')
(3, 'Angel', 33, 'f')
(4, 'Elisabeth', 37, 'f')
(5, 'Joel', 19, 'male')

Deleting Records

To delete a record, you have to pass the DELETE query to the execute() method of the cursor object. The following script deletes all the records from the patients table where the value for the sex column is “male”.

delete_patients_query =  "DELETE FROM patients WHERE sex = 'male'"

cursor.execute(delete_patients_query)
my_db_con.commit()

Let’s verify if the records where the sex column contains “male” were deleted by executing the following SELECT query.

select_patients_query = "SELECT * FROM patients"
cursor.execute(select_patients_query)

patients = cursor.fetchall()

for patient in patients:
    print(patient)

The output shows that records for all the male patients have been removed from our patients table.

Output:

(1, 'Laura', 24, 'f')
(3, 'Angel', 33, 'f')
(4, 'Elisabeth', 37, 'f')

Complete Code

The complete code for connecting and performing create, read, update and delete (CRUD) operations on a MySQL database using Python is presented below, for convenience:

# pip install mysql-connector-python

# Creating a Connection with MySQL Server
import mysql.connector


host_name = "localhost"
user_name = "root"
password = ""


my_con = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=password
        )

# Creating a Database inside MySQL Server

cursor = my_con.cursor()
query = "CREATE DATABASE patient_db"
cursor.execute(query)

# Connecting to the newly created database
host_name = "localhost"
user_name = "root"
password = ""
db_name = "patient_db"

my_db_con = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=password,
            database=db_name
        )

# Creating a table inside the database


create_table_query = """
CREATE TABLE IF NOT EXISTS patients (
  id INT AUTO_INCREMENT,
  name TEXT NOT NULL,
  age INT,
  sex TEXT,
  PRIMARY KEY (id)
) ENGINE = InnoDB
"""

cursor = my_db_con.cursor()
cursor.execute(create_table_query)
my_db_con.commit()

# Inserting Records in a Table

create_patients_query = """
INSERT INTO
  `patients` (`name`, `age`, `sex`)
VALUES
  ('Laura', 24, 'female'),
  ('Jospeh', 41, 'male'),
  ('Angel', 33, 'female'),
  ('Elisabeth', 37, 'female'),
  ('Joel', 19, 'male');
"""

cursor = my_db_con.cursor()
cursor.execute(create_patients_query)
my_db_con.commit()

# Selecing Records from a Table

select_patients_query = "SELECT * FROM patients"
cursor.execute(select_patients_query)

patients = cursor.fetchall()

for patient in patients:
    print(patient)

# Updating Records in a Table

update_patients_query = """
UPDATE
  patients
SET
  sex = 'f'
WHERE
  sex = 'female'

"""

cursor.execute(update_patients_query)
my_db_con.commit()

# Deleting Records from a Table

delete_patients_query =  "DELETE FROM patients WHERE sex = 'male'"

cursor.execute(delete_patients_query)
my_db_con.commit()

This is just one sample of how you can use Python to interact with databases. We have several Python SQL tutorials, including

We also have application-specific Python/SQL tutorials showing you how to

We find these tutorials really helpful, and we hope you do, too. For more tutorials like these, subscribe using the form below:

Get Our Python Developer Kit for Free

I put together a Python Developer Kit with over 100 pre-built Python scripts covering data structures, Pandas, NumPy, Seaborn, machine learning, file processing, web scraping and a whole lot more - and I want you to have it for free. Enter your email address below and I'll send a copy your way.

Yes, I'll take a free Python Developer Kit