Python and MySQL: A Practical Introduction for Data Analysis

This article was published as a part of the Data Science Blogathon

Introduction

Let’s look at a practical example of how to make SQL queries to a MySQL server from Python code: CREATE, SELECT, UPDATE, JOIN, etc.

Most applications interact with data in some form. Therefore, programming languages ​​(Python is no exception) provide tools for storing data sources and accessing them. MySQL is one of the most fantastic and rich database management systems ( DBMS ). Last year it was ranked second after Oracle in the database rankings.

Using the techniques described in this tutorial, you can effectively integrate a MySQL database into your Python application. In this tutorial, we will develop a small MySQL database for a movie rating system and learn how to grab data from it using Python code.

What you will get to know after this tutorial is:

• Connect your application to the MySQL database

• Retrieval of data via a query for the required data from the database

• Handle exceptions thrown when accessing the database

To get the most out of this tutorial, it is advisable to have a working knowledge of Python concepts such as looping, functions, and exception handling. You must also have a basic understanding of SQL-queries, such as, and . for SELECT DROP CREATE JOIN

Comparing MySQL to Another SQL Databases

SQL stands for Structured Query Language is a widely-used programming language for managing relational databases. You may have heard of various SQL-based DBMS: MySQL, PostgreSQL, SQLite, and SQL Server. All of these databases comply with SQL standards but differ in detail.

Because of its open-source code, MySQL quickly became the market leader in SQL solutions. MySQL is currently used by most of the famous tech firms like Google, LinkedIn, Uber, Netflix, Twitter, and more.

Besides the support from the open-source community, there are other reasons for MySQL’s success:

1. Easy to install- MySQL is designed to be user-friendly. The database is easy to create and customize. MySQL is available for major operating systems including Windows, macOS, Linux, and Solaris.

2. Speed- MySQL has a reputation for being a fast database solution. This DBMS also scales well.

3. User rights and security- MySQL allows you to set password security levels, add and remove privileges to user accounts. User rights management looks much simpler than in many other DBMS such as PostgreSQL, where managing configuration files requires some skill.

Installing MySQL Server and MySQL Connector

MySQL Server and MySQL Connector are the only two software that you need to get started with this tutorial. MySQL Server will provide the resources needed to work with the database. After starting the server, you should be able to connect your Python application to it using the MySQL Connector / Python.

Installing MySQL Server

The official documentation describes the recommended ways to download and install MySQL Server. There are instructions for all popular operating systems, including Windows, macOS, Solaris, Linux, and many more.

For Windows, your best bet is to download the MySQL installer and let it take care of the process. The Installation Manager will also help you configure the security settings for your MySQL server. On the accounts page, you will need to enter a password for the root account and, if desired, add other users with different privileges.

Setting up a MySQL account

Other useful tools such as MySQL Workbench can be customized using the installers. A convenient alternative to installing on an operating system is to deploy MySQL using Docker.

Installing MySQL Connector / Python

Database driver – software that allows an application to connect to and interact with a DBMS. These drivers are usually supplied as separate modules. The standard interface that all Python database drivers must conform to is described in PEP 249. To install the driver (connector), we will use the package manager: pip

pip install mysql-connector-python

pip will install the connector into the currently active environment. To work with a project in isolation, we recommend setting up a virtual environment.

Let’s check the installation result by running the following command in the Python terminal:

import mysql.connector

If the import statement runs without errors, then it is successfully installed and ready to use. MySQL.connector

Establishing a connection to the MySQL server

MySQL is a server-side database management system. One server can contain multiple databases. To interact with the database, we must establish a connection to the server. Step by step interaction for a Python program with a MySQL-based database looks like this:

1. We connect to the MySQL server.

2. We create a new database (if necessary).

3. We connect to the database.

4. We execute the SQL query, collect the results.

5. We inform the database if changes have been made to the table.

6. Lastly, just close the connection to the MySQL server.

Whatever the application, the first step is to link the application and database together.

Connecting to MySQL Server from Python

To establish a connection, use the module. This function takes parameters, and, and returns an object. Credentials can be obtained as a result of input from the user: connect() mysql.connector host user password MySQLConnection

from getpass import getpass
from mysql.connector import connect, Error
try:
    with connect(
        host="localhost",
        user = input ("Username:"),
         password = getpass ("Password:"),
    ) as connection:
        print(connection)
except Error as e:
    print(e)

The object is stored in a variable that we will use to access the MySQL server. A few important points: MySQLConnection connection

• Wrap all database connections in blocks. This will make it easier to catch and examine any exceptions. try … except

• Remember to close the connection after you finish accessing the database. Unused open connections lead to unexpected errors and performance problems. The code uses the context manager ( with … as …) for this.

• You should never embed credentials (username and password) in string form in a Python script. This is bad deployment practice and poses a serious security risk. The code above asks for your login credentials. For this, a built-in module is used to hide the entered password.

So, we have established a connection between our program and the MySQL server. Now you need to either create a new database or connect to an existing one.

Create a new database

To create a new database, for example with a name, you need to execute the SQL statement: online_movie_rating

CREATE DATABASE online_movie_rating;

Note

MySQL requires you to put a semicolon ( 😉 at the end of a statement. However, MySQL Connector/Python automatically adds a semicolon at the end of each query.

To execute an SQL query, we need a cursor that abstracts the process of accessing database records. MySQL Connector / Python provides a corresponding class, an instance of which is also called a cursor. MySQLCursor

Let’s pass our request to create a database: online_movie_rating

try:
    with connect(
        host="localhost",
        user = input ("Username:"),
         password = getpass ("Password:"),
    ) as connection:
        create_db_query = "CREATE DATABASE online_movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(create_db_query)
except Error as e:
    print(e)

The request is stored as a string in a variable and then passed for execution to CREATE DATABASE create_db_query cursor.execute()

If a database with the same name already exists on the server, we will receive an error message. Using the same object as before, let’s run a query to see all the tables stored in the database: MySQLConnection SHOW DATABASES

try:
    with connect(
        host="localhost",
        user = input ("Username:"),
         password = getpass ("Password:"),
    ) as connection:
        show_db_query = "SHOW DATABASES"
        with connection.cursor() as cursor:
            cursor.execute(show_db_query)
            for db in cursor:
                print(db)
except Error as e:
    print(e)

OUTPUT

Enter username: root

Enter password: ········

(‘information_schema’,)

(‘mysql’,)

(‘online_movie_rating’,)

(‘performance_schema’,)

(‘sys’,)

The above code will print the names of all databases located on our MySQL server. The command in our example also dumped databases that are automatically created by the MySQL server and provide access to database metadata and server settings. SHOW DATABASES

Connecting to an existing database

So, we have created a database called. To connect to it, we simply supplement the call with a parameter: online_movie_rating connect() database

try:
    with connect(
        host="localhost",
        user = input ("Username:"),
         password = getpass ("Password:"),
        database="online_movie_rating",
    ) as connection:
        print(connection)
except Error as e:
    print(e)

Creating, modifying, and dropping tables

In this section, we discuss how to use Python to perform some basic queries: ’,’ and ‘.’ CREATE TABLE DROP ALTER

Defining the database schema

Let’s start by creating a database schema for the movie rating system. Take the database comprised of three tables:

1. movies- general information about films:

• Id

• +

• title

• release year

• genre

• collection_in_mi

2. reviewers- information about the people who published the ratings of the films:

1. id

2. first_name

3. last_name

3. ratings- information about the ratings of films by reviewers:

1. movie_id (foreign key)

2. reviewer_id (foreign key)

3. rating

These three tables are sufficient for the purposes of this guide.

Film rating system diagram

The tables in the database are related to each other: movies and reviewers must have a many-to-many relationship: one movie can be viewed by multiple reviewers, and one reviewer can review multiple movies. The table ratings connect the movies table to the reviewer’s table.

Creating tables using the CREATE TABLE statement

To create a new table in MySQL, we need to use the operator. The following MySQL query will create our database table: CREATE TABLE movies online_movie_rating

CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
);

If you have encountered SQL before, you will understand the meaning of the above query. The MySQL dialect has some distinctive features. For example, MySQL offers a wide range of data types, including, and so on. In addition, MySQL uses the keyword when the column value should be automatically incremented when new records are inserted. YEAR INT BIGINT AUTO_INCREMENT

To create a table, you need to pass the specified query to the cursor.execute()

create_movies_table_query = """
CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
)
"""
with connection.cursor() as cursor:
    cursor.execute(create_movies_table_query)
    connection.commit()

Pay attention to the operator. By default, the MySQL connector does not auto-commit transactions. In MySQL, modifications mentioned in a transaction only happen when we use the command at the end. To make changes to a table, always call this method after every transaction. connection.commit() COMMIT

Let’s repeat the procedure for the table: reviewers

create_reviewers_table_query = “””

CREATE TABLE reviewers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
)
"""
with connection.cursor() as cursor:
    cursor.execute(create_reviewers_table_query)
    connection.commit()
Finally, let's create a table ratings:
create_ratings_table_query = """
CREATE TABLE ratings (
    movie_id INT,
    reviewer_id INT,
    rating DECIMAL(2,1),
    FOREIGN KEY(movie_id) REFERENCES movies(id),
    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id),
    PRIMARY KEY(movie_id, reviewer_id)
)
"""
with connection.cursor() as cursor:
    cursor.execute(create_ratings_table_query)
    connection.commit()

The implementation of foreign key relationships in MySQL is slightly different and has limitations compared to standard SQL. In MySQL, both parent and child of a foreign key must use the same storage engine — the underlying software component that the database management system uses to perform SQL operations. MySQL offers two kinds of such mechanisms:

1. Transactional storage engines are transaction-safe and allow you to roll back transactions using simple commands such as. Many popular MySQL engines fall into this category, including InnoDB and NDB. rollback

2. Non-transactional storage engines rely on manual code to undo statements committed to the database. These are, for example, MyISAM and MEMORY.

InnoDB is the most popular default storage engine. By enforcing foreign key constraints, it helps maintain data integrity. This means that any foreign key CRUD operation is pre-checked to ensure that it does not lead to inconsistency between different tables.

Note that the table uses columns and two foreign keys, acting together as a primary key. This feature ensures that a reviewer cannot rate the same film twice. ratings movie_id reviewer_id

The same cursor can be used for multiple hits. In this case, all calls will become one atomic transaction. For example, you can execute all statements with one cursor, and then commit the transaction at once:

CREATE TABLE

with connection.cursor() as cursor:

cursor.execute(create_movies_table_query)

cursor.execute(create_reviewers_table_query)

cursor.execute(create_ratings_table_query)

connection.commit()

Displaying Table Schema Using the DESCRIBE Statement

We have created three tables and can view the schema using the operator. DESCRIBE

Assuming you already have an object in a variable, we can print the results obtained. This method retrieves all lines from the last executed statement: MySQLConnection connection cursor.fetchall()

show_table_query = "DESCRIBE movies"
with connection.cursor() as cursor:
    cursor.execute(show_table_query)
    # Fetch rows from last executed query
    result = cursor.fetchall()
    for row in result:
        print(row)

OUTPUT

(‘id’, ‘int(11)’, ‘NO’, ‘PRI’, None, ‘auto_increment’)

(‘title’, ‘varchar(100)’, ‘YES’, ”, None, ”)

(‘release_year’, ‘year(4)’, ‘YES’, ”, None, ”)

(‘genre’, ‘varchar(100)’, ‘YES’, ”, None, ”)

(‘collection_in_mil’, ‘int(11)’, ‘YES’, ”, None, ”)

After executing the above code, we should get a table containing information about the columns in the table. For each column, information is displayed about the data type, whether the column is a primary key, and so on. movies

Changing the schema of a table using the ALTER statement

The name column in the table contains the movie’s box office in millions of dollars. We can write the following MySQL statement to change the data type of an attribute from to collection_in_mil movies collection_in_mil INT DECIMAL

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

DECIMAL(4,1) indicates a decimal number, which can have a maximum of four figures, of which one corresponds to the tenth discharge, for example, and so on. d. 120.1 3.4 38.0

alter_table_query = """
ALTER TABLE movies
MODIFY COLUMN collection_in_mil DECIMAL(4,1)
"""
show_table_query = "DESCRIBE movies"
with connection.cursor() as cursor:
    cursor.execute(alter_table_query)
    cursor.execute (show_table_query)
     # Get rows from the last executed query
     result = cursor.fetchall ()
     print ("Movie table schema after modification:")
    for row in result:
        print(row)
      The movie table schema after making changes:
('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('title', 'varchar(100)', 'YES', '', None, '')
('release_year', 'year(4)', 'YES', '', None, '')
('genre', 'varchar(100)', 'YES', '', None, '')
('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '')

As shown in the output, the attribute changed its type too. Note that in the above code, we are calling twice, but only fetches rows from the most recently executed query, which is. collection_in_mil DECIMAL(4,1) cursor.execute() cursor.fetchall() show_table_query

Dropping tables using the DROP statement

To delete tables, use the operator. Dropping a table is an irreversible process. If you run the code below, you will need to invoke the query on the table again: DROP TABLE CREATE TABLE ratings

drop_table_query = "DROP TABLE ratings"
with connection.cursor() as cursor:
    cursor.execute(drop_table_query)

Inserting records into tables

Let’s fill the tables with data. In this section, we will look at two ways to insert records using the MySQL Connector in Python code.

The first method works well when the number of records is small. The second one is better suited for real-life scenarios. .execute() .executemany()

Inserting records with .execute ()

The first approach uses the same method that we have been using so far. We write a request and send it to the cursor.execute() INSERT INTO cursor.execute()

insert_movies_query = """
INSERT INTO movies (title, release_year, genre, collection_in_mil)
VALUES
    ("Forrest Gump", 1994, "Drama", 330.2),
    ("3 Idiots", 2009, "Drama", 2.4),
    ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5),
    ("Good Will Hunting", 1997, "Drama", 138.1),
    ("Skyfall", 2012, "Action", 304.6),
    ("Gladiator", 2000, "Action", 188.7),
    ("Black", 2005, "Drama", 3.0),
    ("Titanic", 1997, "Romance", 659.2),
    ("The Shawshank Redemption", 1994, "Drama",28.4),
    ("Udaan", 2010, "Drama", 1.5),
    ("Home Alone", 1990, "Comedy", 286.9),
    ("Casablanca", 1942, "Romance", 1.0),
    ("Avengers: Endgame", 2019, "Action", 858.8),
    ("Night of the Living Dead", 1968, "Horror", 2.5),
    ("The Godfather", 1972, "Crime", 135.6),
    ("Haider", 2014, "Action", 4.2),
    ("Inception", 2010, "Adventure", 293.7),
    ("Evil", 2003, "Horror", 1.3),
    ("Toy Story 4", 2019, "Animation", 434.9),
    ("Air Force One", 1997, "Drama", 138.1),
    ("The Dark Knight", 2008, "Action",535.4),
    ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1),
    ("The Lion King", 1994, "Animation", 423.6),
    ("Pulp Fiction", 1994, "Crime", 108.8),
    ("Kai Po Che", 2013, "Sport", 6.0),
    ("Beasts of No Nation", 2015, "War", 1.4),
    ("Andadhun", 2018, "Thriller", 2.9),
    ("The Silence of the Lambs", 1991, "Crime", 68.2),
    ("Deadpool", 2016, "Action", 363.6),
    ("Drishyam", 2015, "Mystery", 3.0)
"""
with connection.cursor() as cursor:
    cursor.execute(insert_movies_query)
    connection.commit()

The table is now filled with thirty entries. In the end, the code calls. Remember to call after making any changes to the table. movies connection.commit() .commit()

Inserting records with .executemany ()

The previous approach is well suited for the smaller record that can be inserted easily via code. But usually, the data is stored in a file or generated by another script. Here’s where it comes in handy. The method takes two parameters: .executemany()

1. A query containing placeholders for the records to be inserted.

2. List of records to insert.

Let’s take an approach to fill the table: reviewers

insert_reviewers_query = """
INSERT INTO reviewers
(first_name, last_name)
VALUES ( %s, %s )
"""
reviewers_records = [
    ("Chaitanya", "Baweja"),
    ("Mary", "Cooper"),
    ("John", "Wayne"),
    ("Thomas", "Stoneman"),
    ("Penny", "Hofstadter"),
    ("Mitchell", "Marsh"),
    ("Wyatt", "Skaggs"),
    ("Andre", "Veiga"),
    ("Sheldon", "Cooper"),
    ("Kimbra", "Masters"),
    ("Kat", "Dennings"),
    ("Bruce", "Wayne"),
    ("Domingo", "Cortes"),
    ("Rajesh", "Koothrappali"),
    ("Ben", "Glocker"),
    ("Mahinder", "Dhoni"),
    ("Akbar", "Khan"),
    ("Howard", "Wolowitz"),
    ("Pinkie", "Petit"),
    ("Gurkaran", "Singh"),
    ("Amy", "Farah Fowler"),
    ("Marlon", "Crafford"),
]
with connection.cursor() as cursor:
    cursor.executemany(insert_reviewers_query,
                       reviewers_records)
    connection.commit()

This code takes placeholders for two strings that are inserted into. Placeholders act as format specifiers and help to reserve space for a variable within a string. %s insert_reviewers_query

Let’s fill in the table in the same way: ratings

insert_ratings_query = """
INSERT INTO ratings
(rating, movie_id, reviewer_id)
VALUES ( %s, %s, %s)
"""
ratings_records = [
    (6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17),
    (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4),
    (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10),
    (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19),
    (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9),
    (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19),
    (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15),
    (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20),
    (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13),
    (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17),
    (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4),
    (9.8, 13, 1)
]
with connection.cursor() as cursor:
    cursor.executemany(insert_ratings_query, ratings_records)
    connection.commit()

All three tables are now filled with data. The next step is to figure out how to interact with this database.

Reading records from the database

So far, we have only created database items. It’s time to run a few queries and find the properties we are interested in. In this section, we will learn how to read records from database tables using the operator. SELECT

Reading records with a SELECT statement

To get records, you need to send to the request and return the result using: cursor.execute() SELECT cursor.fetchall()

select_movies_query = "SELECT * FROM movies LIMIT 5"
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    result = cursor.fetchall()
    for row in result:
        print(row)

OUTPUT

(1, ‘Forrest Gump’, 1994, ‘Drama’, Decimal(‘330.2’))

(2, ‘3 Idiots’, 2009, ‘Drama’, Decimal(‘2.4’))

(3, ‘Eternal Sunshine of the Spotless Mind’, 2004, ‘Drama’, Decimal(‘34.5’))

(4, ‘Good Will Hunting’, 1997, ‘Drama’, Decimal(‘138.1’))

(5, ‘Skyfall’, 2012, ‘Action’, Decimal(‘304.6’))

The variable contains the records returned. It is a list of tuples representing individual records in a table. result .fetchall()

In the above query, we use a keyword to limit the number of rows received from the operator. Developers are often used to paginate output when processing large amounts of data. LIMIT SELECT LIMIT

In MySQL, two non-negative numeric arguments can be passed to an operator: LIMIT

SELECT * FROM movies LIMIT 2,5;

When using two numeric arguments, the first specifies an offset, which in this example is 2, and the second limits the number of rows returned to 5. That is, the query from the example will return rows 3 through 7.

select_movies_query = "SELECT title, release_year FROM movies LIMIT 2, 5"
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for row in cursor.fetchall():
        print(row)

OUTPUT

(‘Eternal Sunshine of the Spotless Mind’, 2004)

(‘Good Will Hunting’, 1997)

(‘Skyfall’, 2012)

(‘Gladiator’, 2000)

(‘Black’, 2005)

Filtering Results with WHERE

Table entries can also be filtered using. To get all films with a box office of over $ 300 million, run the following query: WHERE

select_movies_query = """
SELECT title, collection_in_mil
FROM movies
WHERE collection_in_mil > 300
ORDER BY collection_in_mil DESC
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)
('Avengers: Endgame', Decimal('858.8'))
('Titanic', Decimal('659.2'))
('The Dark Knight', Decimal('535.4'))
('Toy Story 4', Decimal('434.9'))
('The Lion King', Decimal('423.6'))
('Deadpool', Decimal('363.6'))
('Forrest Gump', Decimal('330.2'))
('Skyfall', Decimal('304.6'))

The phrase in the query allows you to sort the fees from highest to lowest. ORDER BY

MySQL provides many string formatting operations such as for string concatenation. For example, movie titles are usually displayed along with the release year to avoid confusion. Let’s get the names of the five most profitable films along with their release dates: CONCAT

select_movies_query = """
SELECT CONCAT(title, " (", release_year, ")"),
      collection_in_mil
FROM movies
ORDER BY collection_in_mil DESC
LIMIT 5
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)

OUTPUT

(‘Avengers: Endgame (2019)’, Decimal(‘858.8’))

(‘Titanic (1997)’, Decimal(‘659.2’))

(‘The Dark Knight (2008)’, Decimal(‘535.4’))

(‘Toy Story 4 (2019)’, Decimal(‘434.9’))

(‘The Lion King (1994)’, Decimal(‘423.6’))

If you do not want to use and do not need to get all records, you can use the cursor methods and: LIMIT .fetchone() .fetchmany()

• .fetchone() Retrieves the next row of the result as a tuple, or if there are no more rows available. None

• .fetchmany() Retrieves a list of the next set of rows as a tuple. To do this, an argument is passed to it, which by default is 1. If there are no more rows available, the method returns an empty list.

Again, extract the titles of the five highest-grossing films by year, but this time using: .fetchmany()

select_movies_query = """
SELECT CONCAT(title, " (", release_year, ")"),
      collection_in_mil
FROM movies
ORDER BY collection_in_mil DESC
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchmany(size=5):
        print(movie)
    cursor.fetchall()

OUTPUT

(‘Avengers: Endgame (2019)’, Decimal(‘858.8’))

(‘Titanic (1997)’, Decimal(‘659.2’))

(‘The Dark Knight (2008)’, Decimal(‘535.4’))

(‘Toy Story 4 (2019)’, Decimal(‘434.9’))

(‘The Lion King (1994)’, Decimal(‘423.6’))

You may have noticed an additional challenge. We do this to clean up any remaining unread results. cursor.fetchall() .fetchmany()

Before executing any other statements on the same connection, you must clear any unread results. Otherwise, an exception is thrown. InternalError

JOIN Multiple Tables

To find out the names of the five highest-rated movies, run the following query:

select_movies_query = """
SELECT title, AVG(rating) as average_rating
FROM ratings
INNER JOIN movies
    ON movies.id = ratings.movie_id
GROUP BY movie_id
ORDER BY average_rating DESC
LIMIT 5
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)

OUTPUT

(‘Night of the Living Dead’, Decimal(‘9.90000’))

(‘The Godfather’, Decimal(‘9.90000’))

(‘Avengers: Endgame’, Decimal(‘9.75000’))

(‘Eternal Sunshine of the Spotless Mind’, Decimal(‘8.90000’))

(‘Beasts of No Nation’, Decimal(‘8.70000’))

You can find the name of the reviewer with the most ratings like this:

select_movies_query = """
SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num
FROM reviewers
INNER JOIN ratings
    ON reviewers.id = ratings.reviewer_id
GROUP BY reviewer_id
ORDER BY num DESC
LIMIT 1
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)
('Mary Cooper', 4)

As you can see, most of the reviews were written by Mary Cooper.

The process of executing a query always remains the same: we pass the query to get the results using. cursor.execute() .fetchall()

Updating and deleting records from the database

In this section, we will update and remove some of the entries. We will select the required lines using a keyword. WHERE

UPDATE command

Imagine a reviewer Amy Farah Fowler is married to Sheldon Cooper. She changed her last name to Cooper and we need to update the database. To update records in MySQL, use the operator: UPDATE

update_query = """
UPDATE
    reviewers
SET
    last_name = "Cooper"
WHERE
    first_name = "Amy"
"""
with connection.cursor() as cursor:
    cursor.execute(update_query)
    connection.commit()

The code passes the update request to and makes the necessary changes to the table. cursor.execute() .commit() reviewers

Let’s say we want to allow reviewers to change grades. The program needs to know, and the new. SQL example: movie_id reviewer_id rating

UPDATE
    ratings
SET
    rating = 5.0
WHERE
    movie_id = 18 AND reviewer_id = 15;
SELECT *
FROM ratings
WHERE
    movie_id = 18 AND reviewer_id = 15;
The specified queries first update the rating and then output the updated one. Let's write a Python script that will allow us to adjust the grades:
modify_ratings.py
from getpass import getpass
from mysql.connector import connect, Error
movie_id = input("Enter movie id: ")
reviewer_id = input("Enter reviewer id: ")
new_rating = input("Enter new rating: ")
update_query = """
UPDATE
    ratings
SET
    rating = "%s"
WHERE
    movie_id = "%s" AND reviewer_id = "%s";
SELECT *
FROM ratings
WHERE
    movie_id = "%s" AND reviewer_id = "%s"
""" % (
    new_rating,
    movie_id,
    reviewer_id,
    movie_id,
    reviewer_id,
)
try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        with connection.cursor() as cursor:
            for result in cursor.execute(update_query, multi=True):
                if result.with_rows:
                    print(result.fetchall())
            connection.commit()
except Error as e:
    print(e)

OUTPUT

Enter movie id: 18

Enter reviewer id: 15

Enter new rating: 5

Enter username: root

Enter password: ········

To pass multiple requests to the same cursor, we assign a value to the argument. In this case, it returns an iterator. Each item in the iterator corresponds to a cursor object that executes the instruction passed in the request. The above code starts a loop on this iterator, calling for each cursor object. multi True cursor.execute() for .fetchall()

If no result set was obtained for the operation, then an exception is thrown. To avoid this error, in the code above, we use a property that indicates whether the last performed operation created rows. .fetchall() cursor.with_rows

While this code does the job, the instruction, as it stands, is a tempting target for hackers. It is vulnerable to a SQL injection attack that could allow attackers to corrupt or misuse the database. WHERE

For example, if the user submits, and as input, then the result would look like this: movie_id = 18 reviewer_id = 15 ratings = 5.0

$ python modify_ratings.py

Enter movie id: 18

Enter reviewer id: 15

Enter new rating: 5.0

Enter username:

Enter password:

The score for and changed too. But if you were a hacker, you could send a hidden command to the input: movie_id = 18 reviewer_id = 15 5.0

$ python modify_ratings.py

Enter movie id: 18

Enter reviewer id: 15″; UPDATE reviewers SET last_name = “A

Enter new rating: 5.0

Enter username:

Enter password:

Again, the output shows that the reported rating has been changed to 5.0. What changed?

The hacker intercepted the data update request. An update request will change all records in the reviewer’s table: last_name “A”

>>> select_query = “””

… SELECT first_name, last_name

… FROM reviewers

… “””

>>> with connection.cursor() as cursor:

… cursor.execute(select_query)

… for reviewer in cursor.fetchall():

… print(reviewer)

…

(‘Chaitanya’, ‘A’)

(‘Mary’, ‘A’)

(‘John’, ‘A’)

(‘Thomas’, ‘A’)

(‘Penny’, ‘A’)

(‘Mitchell’, ‘A’)

(‘Wyatt’, ‘A’)

(‘Andre’, ‘A’)

(‘Sheldon’, ‘A’)

(‘Kimbra’, ‘A’)

(‘Kat’, ‘A’)

(‘Bruce’, ‘A’)

(‘Domingo’, ‘A’)

(‘Rajesh’, ‘A’)

(‘Ben’, ‘A’)

(‘Mahinder’, ‘A’)

(‘Akbar’, ‘A’)

(‘Howard’, ‘A’)

(‘Pinkie’, ‘A’)

(‘Gurkaran’, ‘A’)

(‘Amy’, ‘A’)

(‘Marlon’, ‘A’)

The above code displays and for all records in the table of reviewers. An SQL injection attack corrupted this table, changing all records to “A”. first_name last_name last_name

There is a quick solution to prevent such attacks. Do not add user-supplied query values ​​directly to the query string. Better to update the script by sending request values ​​as arguments to .execute()

modify_ratings.py
from getpass import getpass
from mysql.connector import connect, Error
movie_id = input("Enter movie id: ")
reviewer_id = input("Enter reviewer id: ")
new_rating = input("Enter new rating: ")
update_query = """
UPDATE
    ratings
SET
    rating = %s
WHERE
    movie_id = %s AND reviewer_id = %s;
SELECT *
FROM ratings
WHERE
    movie_id = %s AND reviewer_id = %s
"""
val_tuple = (
    new_rating,
    movie_id,
    reviewer_id,
    movie_id,
    reviewer_id,
)
try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        with connection.cursor() as cursor:
            for result in cursor.execute(update_query, val_tuple, multi=True):
                if result.with_rows:
                    print(result.fetchall())
            connection.commit()
except Error as e:
    print(e)

Note that placeholders are no longer enclosed in string quotes. verifies that the values ​​in the tuple given as an argument are of the required data type. If the user tries to enter some problematic characters, the code will throw an exception: %s cursor.execute()

OUTPUT

$ python modify_ratings.py

Enter movie id: 18

Enter reviewer id: 15″; UPDATE reviewers SET last_name = “A

Enter new rating: 5.0

Enter username:

Enter password:

1292 (22007): Truncated incorrect DOUBLE value: ’15”;

This approach should always be used when you include user input in a request. Take the time to learn about other ways to prevent SQL injection attacks.

Deleting records: the DELETE command

The procedure for deleting records is very similar to updating them. Since this is an irreversible operation, we recommend that you first run the query with the same filter to ensure that you are deleting the records you want. For example, to remove all movie ratings, data, we can first run the appropriate query: DELETE SELECT reviewer_id = 7 SELECT

select_movies_query = """
SELECT reviewer_id, movie_id FROM rating
WHERE review_id = 7
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)

OUTPUT

(2, 7)

(2, 8)

(2, 12)

(2, 23)

The above code snippet displays a pair, and for entries in the table estimates, for which. After making sure that these are the records to be deleted, let’s execute the query with the same filter: reviewer_id movie_id reviewer_id = 2 DELETE

delete_query = "DELETE FROM ratings WHERE reviewer_id = 2"
with connection.cursor() as cursor:
    cursor.execute(delete_query)
    connection.commit()

Other ways to connect Python and MySQL

In this tutorial, we introduced the MySQL Connector / Python, which is the officially recommended means of interacting with a MySQL database from a Python application. Here are a couple of other popular connectors:

• mysqlclient is a library that is a competitor to the official connector and is actively being supplemented with new functions. Since the core of the library is written in C, it has better performance than the official pure Python connector. The big drawback is that mysqlclient is quite difficult to set up and install, especially on Windows.

• MySQLdb is legacy software that is still used in commercial applications today. Written in C and faster MySQL Connector / Python, but only available for Python 2.

These drivers act as interfaces between your program and the MySQL database. In fact, you just send your SQL queries through them. However, many developers prefer to use the object-oriented paradigm for data management, not SQL queries.

Object-relational mapping ( ORM ) is a process that allows not only the query but also the manipulation of data from a database directly usingOOPs. The ORM library encapsulates the code needed to manipulate data, freeing developers from the need to use SQL queries. Here are the most popular ORM libraries for combining Python and SQL:

• SQLAlchemy is an ORM that simplifies communication between Python and other SQL databases. You can create different engines for different databases like MySQL, PostgreSQL, SQLite, etc.

• peewee is a lightweight and fast ORM library with a simple configuration, which is very useful when your interaction with the database is limited to fetching a few records. If you need to copy individual records from a MySQL database to a CSV file, then peewee is the best choice.

• The Django ORM is one of the most powerful parts of the Django web framework, allowing you to easily interact with a variety of SQLite, PostgreSQL, and MySQL databases. Many Django-based applications use the Django ORM for data modelling and basic queries, however, for more complex tasks, developers usually use SQLAlchemy.

Conclusion

In this tutorial, we took a look at how to integrate a MySQL database into your Python application. We also developed a test sample of the MySQL database and interacted with it directly from Python code. Python has connectors for other DBMSs such as MongoDB and PostgreSQL. We will be glad to know what other materials on Python and databases you would be interested in.

Shikha Gupta 22 Sep 2021