Python with MySQL

MySQL is a commonly used database management system (DBMSs) today. In this year’s DB-Engines Ranking, it was ranked second only to Oracle DBMS. Because most software programs require some data interaction, programming languages such as Python allow storing and accessing various data sources.

SQL (Structured Query Language) is a common computer language for managing relational databases. SQL-based DBMSs come in a variety of flavors. MySQL, PostgreSQL, SQLite, and SQL Server are among the most popular. All of these databases adhere to SQL standards, albeit to varying degrees. MySQL, which has been open-source since its conception in 1995, soon established itself as the market leader in SQL solutions. MySQL is a component of the Oracle ecosystem as well. While the main functionality is free, there are some commercial add-ons available.

Install MySQL in Python

A database driver enables an application to connect to and interact with a database system. Before a programming language, such as Python can communicate with a database from a specific vendor, it requires a dedicated driver.

Third-party modules are often used to obtain these drivers. The Python Database API (DB-API) establishes the standard interface that all Python database drivers must adhere to. PEP 249 contains these specifics. These implementation standards are followed by all Python database drivers, including sqlite3 for SQLite, psycopg for PostgreSQL, and MySQL Connector/Python for MySQL.

To communicate with a MySQL database in Python, the user must first install a Python MySQL connector. Many packages adhere to the DB-API specifications. However, the most often used is MySQL Connector/Python. It is possible to install it using pip:

$ pip install mysql-connector-python

The connector is installed as a third-party module in the presently active virtual environment via pip. It is worthwhile to create a separate virtual environment for the project and all of its dependencies.

Type the following command into the Python terminal to see if the installation was successful:

>>> import mysql.connector

Establish Connection

The first step in dealing with a MySQL server is to connect to it. To accomplish this, the user must use connect() from the MySQL.connector module. This function accepts host, user, and password inputs and returns a MySQLConnection object.

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:

Create a New Database

The user must execute a SQL statement to create a new database.

CREATE DATABASE new_db;

To run a SQL query in Python, the user must employ a cursor, which abstracts access to database records. MySQL Connector/Python supplies the MySQLCursor class to the user, instantiating objects capable of running MySQL queries in Python.

Cursor objects communicate with your MySQL server via a MySQLConnection object. To make a cursor, utilise the connection variable’s.cursor() method:

cursor = connection.cursor()

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:
        create_db_query = "CREATE DATABASE online_movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(create_db_query)
except Error as e:
    print(e)

Following the execution of the preceding code, the user may have a new database called online movie rating in your MySQL server.

The CREATE DATABASE query is saved as a string in the create DB query variable and then executed by the cursor. execute(). The code employs a context manager in conjunction with the cursor object.

Other useful articles: