Python With MySQL Database

In this tutorial, you will learn how to use Python with MySQL database to perform CRUD (Create Read Update Delete) operations.

To complete this tutorial, you must have MySQL installed on your computer.

You can download Free MySQL for Windows at https://dev.mysql.com/downloads/windows/installer

To install MySQL on Ubuntu, see here.

Lets start by creating a Python application and add dependencies needed for this example:

Create a project folder with the name of your choice:


    mkdir sample-app-mysql

Navigate to the project folder you just created:


    cd sample-app-mysql

Create a virtual environment inside your project directory using the following command:


    virtualenv env

Here, env is the name of the virtual environment for this project. The above command creates env folder in the root directory of the project. This env folder contains files and folders such as bin and lib.

To install dependencies in the virtual environment of your project, activate your project virtual environment first. Use the following command to activate the virtual environment:

On Ubuntu/Linux/Mac


    source env/bin/activate

On Windows


env\Scripts\activate

On Windows 10, we may get "Running Scripts is Disabled on this System" error. To fix this error, see here.

On successful activation of your project virtual environment, you will see that the next line of the terminal starting with the (env).

Python requires a MySQL Driver to connect and interact with the MySQL database, so lets install PyMySQL driver using the following command:


    pip install PyMySQL

Connecting to MySQL

After installing the PyMySQL driver, create a main.py file inside your project folder and write the following code to connect to your MySQL:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='your_username',
                             password='your_password',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

If the above code runs without any error then your application is ready to interact with the MySQL.

Creating a Database

The code below connects to the MySQL and creates a database:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "CREATE DATABASE tb_test"
        cursor.execute(sql)

    # connection is not autocommit by default. So you must commit to save
    # the changes.
    connection.commit()
finally:
    connection.close()

If the above code runs without any error then your code is working.

Creating a Table

The code below connects to the specified MySQL database and creates a table in the database:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             db="tb_test",
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new user table
        sql = """CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,
        email varchar(255) COLLATE utf8_bin NOT NULL,
        password varchar(255) COLLATE utf8_bin NOT NULL,
        PRIMARY KEY (id))
        ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1"""
        cursor.execute(sql)

    # connection is not autocommit by default. So you must commit to save
    # the changes.
    connection.commit()
finally:
    connection.close()

Insert Into Table

The code below connects to the specified MySQL database and inserts data into the specified table:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             db="tb_test",
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new user table
        sql = "INSERT INTO user (email, password) VALUES(%s, %s)"
        val = ("[email protected]", "Test123")
        cursor.execute(sql, val)

    # connection is not autocommit by default. So you must commit to save
    # the changes.
    connection.commit()
    print(cursor.rowcount, "record inserted.")
finally:
    connection.close()

Insert Multiple Rows Into Table

The code below connects to the specified MySQL database and inserts multiple rows into the specified table:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             db="tb_test",
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new user table
        sql = "INSERT INTO user (email, password) VALUES(%s, %s)"

        val = [("[email protected]", "Test123"),
               ("[email protected]", "Test1234"),
               ("[email protected]", "Test1235"),
               ("[email protected]", "Test1236"),
               ("[email protected]", "Test1237"),
               ("[email protected]", "Test1238"),
               ("[email protected]", "Test1239")]

        cursor.executemany(sql, val)

    # connection is not autocommit by default. So you must commit to save
    # the changes.
    connection.commit()
    print(cursor.rowcount, "record inserted.")
finally:
    connection.close()

Read Rows From Table

The code below connects to the specified MySQL database and reads rows from the specified table:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             db="tb_test",
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        
        sql = "SELECT * FROM user"

        cursor.execute(sql)
        results = cursor.fetchall()

    for u in results:
        print(u)
finally:
    connection.close()

Read Rows With Filter From Table

The code below connects to the specified MySQL database and reads rows by filtering using the WHERE clause from the specified table:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             db="tb_test",
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:

        sql = "SELECT * FROM user where email = %s"
        val = ("[email protected]")
        cursor.execute(sql,val)
        results = cursor.fetchall()

    for u in results:
        print(u)
finally:
    connection.close()

Update Table Data

The code below connects to the specified MySQL database and updates a row by id in the specified table:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             db="tb_test",
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:

        sql = "UPDATE user SET email = %s, password = %s WHERE id = %s"
        val = ("[email protected]","Test321",1)
        cursor.execute(sql,val)
        
    # connection is not autocommit by default. So you must commit to save
    # the changes.
    connection.commit()
    print(cursor.rowcount, "record updated.")

finally:
    connection.close()

Delete Table Data

The code below connects to the specified MySQL database and deletes a row by id in the specified table:

main.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='Testing123',
                             db="tb_test",
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:

        sql = "DELETE FROM user WHERE id = %s"
        val = (1)
        cursor.execute(sql, val)

    # connection is not autocommit by default. So you must commit to save
    # the changes.
    connection.commit()
    print(cursor.rowcount, "record deleted.")

finally:
    connection.close()