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.

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?”

The fascinating world of Python programming opens a multitude of possibilities, whether you’re interested in data science, machine learning, web development, or game development. And we’re here to help navigate that journey!

Continue Your Python Journey with Zenva

At Zenva, we believe in empowering learners of all levels with high-quality courses that span a range of programming topics. Our Python courses can turn a beginner into an experienced professional and expose advanced coders to new concepts and applications.

The Python Mini-Degree

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.

Flexible Learning at Your Fingertips

With Zenva, you have the flexibility to learn at your own pace and access courses at any time. We understand that everyone’s learning journey is unique, and we strive to provide resources that cater to every learner’s needs.

To continue your Python journey and get a head start on your career, explore our courses and embark on the educational path that suits you best.

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.

FREE COURSES

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

ACCESS FOR FREE