Python and MySQL Database: A Practical Introduction

Installing MySQL Server

The official documentation details the recommended way to download and install MySQL server. You’ll find instructions for all popular operating systems, including Windows, macOS, Solaris, Linux, and many more.

For Windows, the best way is to download MySQL Installer and let it take care of the entire process. The installation manager also helps you configure the security settings of the MySQL server. On the Accounts and Roles page, you need to enter a password for the root (admin) account and also optionally add other users with varying privileges:

While you must specify credentials for the root account during setup, you can modify these settings later on.

Although you only need the MySQL server for this tutorial, you can also set up other helpful tools like MySQL Workbench using these installers. If you don’t want to install MySQL directly in your operating system, then deploying MySQL on Linux with Docker is a convenient alternative.

Installing MySQL Connector/Python

A database driver is a piece of software that allows an application to connect and interact with a database system. Programming languages like Python need a special driver before they can speak to a database from a specific vendor.

These drivers are typically obtained as third-party modules. The Python Database API (DB-API) defines the standard interface with which all Python database drivers must comply. These details are documented in PEP 249. All Python database drivers, such as sqlite3 for SQLite, psycopg for PostgreSQL, and MySQL Connector/Python for MySQL, follow these implementation rules.

Many popular programming languages have their own database API. For example, Java has the Java Database Connectivity (JDBC) API. If you need to connect a Java application to a MySQL database, then you need to use the MySQL JDBC connector, which follows the JDBC API.

Similarly, in Python you need to install a Python MySQL connector to interact with a MySQL database. Many packages follow the DB-API standards, but the most popular among them is MySQL Connector/Python. You can get it with pip:

$ pip install mysql-connector-python

pip installs the connector as a third-party module in the currently active virtual environment. It’s recommended that you set up an isolated virtual environment for the project along with all the dependencies.

To test if the installation was successful, type the following command on your Python terminal:

>>> import mysql.connector

If the above code executes with no errors, then mysql.connector is installed and ready to use. If you encounter any errors, then make sure you’re in the correct virtual environment and you’re using the right Python interpreter.

Make sure that you’re installing the correct mysql-connector-python package, which is a pure-Python implementation. Beware of similarly named but now depreciated connectors like mysql-connector.

Establishing a Connection

The first step in interacting with a MySQL server is to establish a connection. To do this, you need connect() from the mysql.connector module. This function takes in parameters like host, user, and password and returns a MySQLConnection object. You can receive these credentials as input from the user and pass them to connect():

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        print(connection)
except Error as e:
    print(e)

The code above uses the entered login credentials to establish a connection with your MySQL server. In return, you get a MySQLConnection object, which is stored in the connection variable. From now on, you’ll use this variable to access your MySQL server.

There are several important things to notice in the code above:

• You should always deal with the exceptions that might be raised while establishing a connection to the MySQL server. This is why you use a try … except block to catch and print any exceptions that you might encounter.

• You should always close the connection after you’re done accessing the database. Leaving unused open connections can lead to several unexpected errors and performance issues. The above code takes advantage of a context manager using with, which abstracts away the connection cleanup process.

• You should never hard-code your login credentials, that is, your username and password, directly in a Python script. This is a bad practice for deployment and poses a serious security threat. The code above prompts the user for login credentials. It uses the built-in getpass module to hide the password. While this is better than hard-coding, there are other, more secure ways to store sensitive information, like using environment variables.

You’ve now established a connection between your program and your MySQL server, but you still need to either create a new database or connect to an existing database inside the server.

Creating a New Database

In the last section, you established a connection with your MySQL server. To create a new database, you need to execute a SQL statement:

CREATE DATABASE books_db;

The above statement will create a new database with the name books_db.

To execute a SQL query in Python, you’ll need to use a cursor, which abstracts away the access to database records. MySQL Connector/Python provides you with the MySQLCursor class, which instantiates objects that can execute MySQL queries in Python. An instance of the MySQLCursor class is also called a cursor.

cursor objects make use of a MySQLConnection object to interact with your MySQL server. To create a cursor, use the .cursor() method of your connection variable:

cursor = connection.cursor()

The above code gives you an instance of the MySQLCursor class.

A query that needs to be executed is sent to cursor.execute() in string format. In this particular occasion, you’ll send the CREATE DATABASE query to cursor.execute():

from getpass import getpass