Pymysql Tutorial – Complete Guide

Whether you’re an experienced coder or a complete novice, there’s one subject that can’t be avoided if you’re delving into the vastness of Python: databases. Without a doubt, they form the backbone of a plethora of applications, from web development to games and more. And what better way to interact with databases via Python than using PyMySQL, a Python library that offers a pure-Python MySQL client library. Let’s dive right into it.

What is PyMySQL?

In the simplest of terms, PyMySQL is a Python module that functions as a MySQL connector. It’s written in Python and helps you connect your Python programs with a MySQL database. This module gives Python the ability to interact with MySQL databases to execute SQL commands and retrieve data.

What is PyMySQL for?

The primary purpose of PyMySQL is to act as a bridge between Python and MySQL. This connection enables you to:

  • Execute SQL commands in the database via Python code.
  • Extract, add, or manipulate data stored in your database.
  • Perform database transactions like committing and rollback.

Why should I learn PyMySQL?

If you’re keen on Python and have a pressing need to handle databases, PyMySQL is a tool you need to master. Here’s why:

  • Highly useful: Python is increasingly popular in a wide range of fields, with database management being a crucial one. If you wish to interact with MySQL databases, then PyMySQL becomes invaluable.
  • Pure Python: Being written in pure Python, PyMySQL doesn’t require a separate MySQL C client library. This makes deployment easier and smoother.
  • Learn once, apply in many places: PyMySQL has widespread uses. Whether you develop in Django, Flask, or just pure Python, PyMySQL skills will be reusable.
CTA Small Image
FREE COURSES AT ZENVA
LEARN GAME DEVELOPMENT, PYTHON AND MORE
ACCESS FOR FREE
AVAILABLE FOR A LIMITED TIME ONLY

Getting Started with PyMySQL

Before we can jump into using PyMySQL, we need to ensure it’s installed in our Python working environment. We use pip, Python’s package installer, to do this. Open your terminal (or command line interface) and execute the following command:

pip install PyMySQL

Once you’ve successfully installed PyMySQL, the next step is to establish a connection from Python to your MySQL server. Here’s the basic setup:

import pymysql.cursors

# Establish a connection to your MySQL server.
connection = pymysql.connect(host='localhost',
                             user='your_username',
                             password='your_password')

Working with Databases

Now that we’ve connected to our MySQL server, let’s create a database. The following code snippet shows you how:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd')

try:
    with connection.cursor() as cursor:
        cursor.execute("CREATE DATABASE mydatabase")
finally:
    connection.close()

Executing SQL Queries

Database ready, it’s time to execute some SQL commands. Let’s create a table in our database:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='mydatabase')

try:
    with connection.cursor() as cursor:
        cursor.execute("""
        CREATE TABLE customers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(40),
            address VARCHAR(100)
        )
        """)
    connection.commit()
finally:
    connection.close()

Inserting Data

Let’s now insert some data into our table:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='mydatabase')

try:
    with connection.cursor() as cursor:
        cursor.execute("""
        INSERT INTO customers (name, address) 
        VALUES ('John Doe', '123 Main St')
        """)
    connection.commit()
finally:
    connection.close()

Querying Data

Accessing the data stored within our table is just as straightforward:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='mydatabase')

try:
    with connection.cursor() as cursor:
        cursor.execute("""
        SELECT * FROM customers
        """)
        result = cursor.fetchall()
        print(result)
finally:
    connection.close()

The above code will fetch all the data inside our ‘customers’ table and print it out.

Updating Data

Let’s say we have to update some information, like change the address of our customer ‘John Doe’. Here’s how you can do that:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='mydatabase')

try:
    with connection.cursor() as cursor:
        cursor.execute("""
        UPDATE customers 
        SET address='456 New St' 
        WHERE name='John Doe'
        """)
    connection.commit()
finally:
    connection.close()

Deleting Data

And, inevitably, there will be times when we need to remove data from our database. Let’s remove John’s record from our customers table:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='mydatabase')

try:
    with connection.cursor() as cursor:
        cursor.execute("""
        DELETE FROM customers 
        WHERE name='John Doe'
        """)
    connection.commit()
finally:
    connection.close()

Error Handling

Error handling is essential for smooth operation. PyMySQL provides the built-in Python exception ‘pymysql.Error’ for error handling. Let’s see a scenario:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='mydatabase')

try:
    with connection.cursor() as cursor:
        try:
            cursor.execute("""
            INSERT INTO customers (name, address) 
            VALUES ('John Doe', '123 Main St')
            """)
            connection.commit()
        except pymysql.Error as e:
            print(f"An error occurred: {e.args[0]}, {e.args[1]}")
finally:
    connection.close()

Following this guide, you’re now ready to dive into the real-world applications of PyMySQL. Here at Zenva, we provide comprehensive courses that help you get from zero to hero in Python and many more such subjects. Learning is a lifelong journey, and we’re here to guide you through it.

Where to Go Next?

Now that you’ve taken your first steps with PyMySQL and Python database interactions, you might be wondering, “what’s next?”

For a comprehensive dive into Python programming, check out our Python Mini-Degree. This collection of courses not only teaches Python programming but also walks you through creating games, building apps, and mastering data science and machine learning. Suitable for beginners and more experienced programmers alike, the Python Mini-Degree covers:

  • Coding basics and algorithms
  • Object-oriented programming
  • Game and application development
  • Data science and machine learning

Zenva’s courses are designed with interactive materials, live coding lessons, quizzes, and challenges to reinforce learning. Plus, on completion, you earn a certification to showcase your accomplishment.

Conclusion

While diving into databases and PyMySQL might seem daunting initially, you’re now equipped with the knowledge to fearlessly execute your first queries with Python! Continue to explore and practice, and these new skills will soon feel like second nature.

Our ultimate goal at Zenva is to enhance your learning journey, no matter where you’re starting from or where you aim to go. Our expansive library of high-quality courses, like the Python Mini-Degree, await to accompany you on this journey of growth and self-discovery. Remember, every great journey starts with a single step. Start yours today.

Did you come across any errors in this tutorial? Please let us know by completing this form and we’ll look into it!

FREE COURSES
Python Blog Image

FINAL DAYS: Unlock coding courses in Unity, Godot, Unreal, Python and more.