PyMySQL — Connecting Python to MySQL for Beginners

If you're completely new to coding, don't worry. Let's understand PyMySQL from the very beginning with simple examples.

What is PyMySQL?

Imagine you have:

• Python → where you write your program
• MySQL → where your data is stored

For example, your MySQL database might contain:

But Python cannot simply talk to MySQL by itself.

It needs something in the middle.

That is where PyMySQL comes in.

Python Program
      ↓
   PyMySQL
      ↓
 MySQL Database

PyMySQL is a Python library that helps your Python program connect to MySQL, send SQL commands, and receive results.

1. Install PyMySQL

First, install the PyMySQL library using pip:

pip install PyMySQL

After installing it, you can use it inside your Python program:

import pymysql

2. Connect Python to MySQL

Suppose you already have a MySQL database called shop.

You can connect Python to it like this:

import pymysql

conn = pymysql.connect(
    host="localhost",
    user="root",
    password="mypassword",
    database="shop"
)

Now let's understand what each part means.

host

host="localhost"

This tells Python where MySQL is running.

localhost means:

MySQL is running on the same computer as our Python program.

user

user="root"

This is your MySQL username.

password

password="mypassword"

This is your MySQL password.

database

database="shop"

This tells MySQL which database you want to use.

So the entire connection code basically means:

"Hey MySQL! I'm Python. Connect me to the shop database using this username and password."

3. What is a Cursor?

After connecting to MySQL, we need something that can actually send SQL commands.

That is called a cursor.

cursor = conn.cursor()

You can imagine the flow like this:

Python
   ↓
Connection
   ↓
Cursor
   ↓
SQL Command
   ↓
MySQL

The connection connects Python to MySQL.

The cursor is what we use to execute SQL commands.

4. Reading Data from MySQL

Imagine we have a table called students.

We can ask MySQL to give us all students:

cursor.execute("SELECT * FROM students")

This sends the SQL query to MySQL.

But the results are not automatically printed.

We need to fetch them.

students = cursor.fetchall()

print(students)

You might get something like:

(
    (1, "Rahul", 20),
    (2, "Priya", 21)
)

5. fetchone() vs fetchall()

If you only want one row:

student = cursor.fetchone()

print(student)

If you want all rows:

students = cursor.fetchall()

print(students)

A simple way to remember:

fetchone()  → Give me ONE row

fetchall()  → Give me ALL rows

6. Making Results Easier to Read with DictCursor

Normally, PyMySQL may give us a student like this:

(1, "Rahul", 20)

Then we need to remember which position represents which value:

student[0]  # ID
student[1]  # Name
student[2]  # Age

This can become confusing.

Instead, we can use something called DictCursor.

import pymysql
import pymysql.cursors

conn = pymysql.connect(
    host="localhost",
    user="root",
    password="mypassword",
    database="shop",
    cursorclass=pymysql.cursors.DictCursor
)

Now our data can look like this:

{
    "id": 1,
    "name": "Rahul",
    "age": 20
}

Now we can simply write:

student["name"]

instead of:

student[1]

This makes the code much easier to understand.

Example

with conn.cursor() as cursor:
    cursor.execute("SELECT * FROM students")

    students = cursor.fetchall()

    for student in students:
        print(student["name"])

The output might be:

Rahul
Priya
Arjun

7. Inserting Data into MySQL

Now let's add a new student to our database.

with conn.cursor() as cursor:
    cursor.execute(
        "INSERT INTO students (name, age) VALUES (%s, %s)",
        ("Saketh", 22)
    )

conn.commit()

After running this code, our table might look like:

8. Why Do We Use %s?

You may have noticed this:

VALUES (%s, %s)

And then:

("Saketh", 22)

The %s values are called placeholders.

For example:

cursor.execute(
    "SELECT * FROM students WHERE age = %s",
    (20,)
)

PyMySQL safely places the value 20 into the SQL query.

This is much safer than manually joining user input into an SQL string.

It also helps protect your application from attacks such as SQL injection.

❌ Don't manually add user input into SQL queries.

✅ Use %s placeholders.

9. What is commit()?

Imagine you run this:

cursor.execute(
    "INSERT INTO students (name, age) VALUES (%s, %s)",
    ("Arjun", 19)
)

You told MySQL to add Arjun.

But depending on your connection settings, that change may not yet be permanently saved.

To save the change, use:

conn.commit()

Think of commit() like pressing the Save button.

INSERT / UPDATE / DELETE
          ↓
      commit()
          ↓
   Changes Saved

10. What is rollback()?

Imagine you're making several database changes and something goes wrong.

You might not want to save those incomplete changes.

That's where rollback() helps.

conn.rollback()

The easiest way to remember:

commit()   → Save my changes

rollback() → Cancel my unsaved changes

Example

try:
    with conn.cursor() as cursor:
        cursor.execute(
            "UPDATE students SET age = %s WHERE id = %s",
            (23, 1)
        )

    conn.commit()

except Exception:
    conn.rollback()

If everything works:

conn.commit()

If something goes wrong:

conn.rollback()

11. Closing the MySQL Connection

When you're finished using the database, you should close the connection.

conn.close()

It's similar to opening and closing a file.

file = open(...)
file.close()

For MySQL:

conn = pymysql.connect(...)

# Work with database

conn.close()

This releases the database connection when your program no longer needs it.

12. Complete Beginner Example

Now let's put everything together.

import pymysql
import pymysql.cursors

conn = pymysql.connect(
    host="localhost",
    user="root",
    password="mypassword",
    database="shop",
    cursorclass=pymysql.cursors.DictCursor
)

try:

    with conn.cursor() as cursor:

        cursor.execute("SELECT * FROM students")

        students = cursor.fetchall()

        for student in students:
            print(student["name"])

finally:

    conn.close()

That's a basic working example of Python communicating with MySQL using PyMySQL.

13. The Most Important Thing to Remember

You don't need to memorize everything immediately.

Just understand this basic flow:

1. Install PyMySQL
        ↓
2. Connect to MySQL
        ↓
3. Create a Cursor
        ↓
4. Execute SQL
        ↓
5. Fetch the Results
        ↓
6. Commit if you changed data
        ↓
7. Close the Connection

Or even simpler:

Python
   ↓
PyMySQL
   ↓
MySQL
   ↓
Your Data

Quick Cheat Sheet

Final Definition

PyMySQL is a Python library that allows your Python code to talk to a MySQL database.

Whenever you see PyMySQL, just remember:

Python wants data
      ↓
PyMySQL carries the request
      ↓
MySQL finds the data
      ↓
PyMySQL brings it back
      ↓
Python uses the data