Python With MySQL Database

In this tutorial, you will learn how to connect your Python application to a MySQL database and perform CRUD (Create Read Update Delete) operations on the database.

To get started with this tutorial, you must have MySQL installed on your computer.

Download Free MySQL here at https://dev.mysql.com/downloads/mysql

Lets create a simple Python application and add dependencies needed for this example:

  1. Create a folder with a project name of your choice. Example: my_project
  2. Open a terminal and navigate your command line to the project folder you just created:
  3. 
        cd my_project
    
  4. Inside the project folder, create a virtualenv with any valid name using the command virtualenv name:
  5. 
        virtualenv env
    
  6. Python requires a MySQL Driver to connect and interact with the MySQL database, so lets install PyMySQL driver using the command pip3 install PyMySQL:
  7. 
        pip3 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 = ("mandy@tutorialsbuddy.com", "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 = [("mandy@tutorialsbuddy.com", "Test123"),
               ("helen@tutorialsbuddy.com", "Test1234"),
               ("sally@tutorialsbuddy.com", "Test1235"),
               ("kelly@tutorialsbuddy.com", "Test1236"),
               ("maddy@tutorialsbuddy.com", "Test1237"),
               ("hanson@tutorialsbuddy.com", "Test1238"),
               ("peter@tutorialsbuddy.com", "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 = ("peter@tutorialsbuddy.com")
        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 = ("peter2@tutorialsbuddy.com","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()