Connecting Python to MySQL: CRUD Operations with pymysql

Most real-world applications need to store and retrieve data that outlives the program itself. Databases are the standard solution, and Python makes it easy to talk to them. The pymysql library provides a pure-Python MySQL client that follows the Python DB-API 2.0 specification — a standard interface so that the same patterns (connect, cursor, execute, fetch, commit) work across virtually every relational database driver. Once you grasp those five steps you can adapt them to PostgreSQL, SQLite, or any other DB-API-compatible driver with minimal changes.

Installing pymysql

pymysql is not part of the Python standard library, so it must be installed separately. Run the following command in your terminal before running any of the examples below:

pip install pymysql

The course examples in Semana 3 were written and tested using pymysql. If you are working in a virtual environment (recommended), activate it before running pip install.

The `Database` Class — Connecting and Creating a Cursor

Wrapping the database connection in a class is a clean pattern: the constructor (__init__) opens the connection and stores both the connection object and the cursor object as instance variables that every method can share.

Semana 3/curso.py

import pymysql

class Database:
    def __init__(self, testa):
        self.connection = pymysql.connect(
            host     = 'sql863.main-hosting.eu',  # host o ip de la base datos
            user     = 'u484426513_apireact',      # usuario de la base datos
            password = 'i:![VW:3S#',               # password de la base datos
            db       = 'u484426513_apireact'        # nombre de la base datos
        )
        self.cursor = self.connection.cursor()  # crea la conexion hacia la base de datos
        print('Estoy conectado a la base de datos')

The four connection parameters have the following roles:

Parameter	Description
`host`	Hostname or IP address of the MySQL server.
`user`	MySQL username used to authenticate.
`password`	The password associated with that user.
`db`	The database (schema) name to select on connection.

self.connection.cursor() returns a cursor — the object you use to send SQL statements and receive results. One connection can spawn multiple cursors if needed.

Never hardcode credentials in production code. The examples above expose the host, username, and password in plain text for teaching purposes only. In a real project, store credentials in environment variables or a secrets manager and read them at runtime:

import os
import pymysql

self.connection = pymysql.connect(
    host     = os.environ["DB_HOST"],
    user     = os.environ["DB_USER"],
    password = os.environ["DB_PASSWORD"],
    db       = os.environ["DB_NAME"]
)

SELECT — Fetching All Records

cursor.execute(sql) sends a SQL string to the server. After a SELECT, call cursor.fetchall() to retrieve every matching row as a tuple. A for loop then unpacks each row by numeric index.

Semana 3/curso.py

def getCurso(self):
    sql = 'SELECT id, nombre, descripcion, tiempo, usuario FROM curso'

    try:  # atrapa los errores y no permite que la aplicacion se congele o caiga
        self.cursor.execute(sql)

        curso = self.cursor.fetchall()  # invoca todos los resultados que tenga
        # recorre el curso para ver los datos
        for item in curso:
            print('id',          item[0])
            print('nombre',      item[1])
            print('descripcion', item[2])
            print('tiempo',      item[3])
            print('usuario',     item[4])
            print('-----------------\n')

    except Exception as e:
        print('Error: ', e)
        raise

Each element of curso is a tuple whose positions correspond to the columns listed in the SELECT clause — item[0] is id, item[1] is nombre, and so on.

SELECT — Fetching a Single Record by ID

When you only need one row, use cursor.fetchone() instead of cursor.fetchall(). It returns a single tuple (or None if no row matched).

Semana 3/curso.py

def getCursoById(self, id):
    sql = 'SELECT id, nombre, descripcion, tiempo, usuario FROM curso WHERE id={}'.format(id)

    try:  # atrapa los errores y no permite que la aplicacion se congele o caiga
        self.cursor.execute(sql)

        curso = self.cursor.fetchone()  # invoca un resultado que tenga

        print('id',          curso[0])
        print('nombre',      curso[1])
        print('descripcion', curso[2])
        print('tiempo',      curso[3])
        print('usuario',     curso[4])
        print('-----------------\n')

    except Exception as e:
        print('Error: ', e)
        raise

INSERT — Creating a New Record

After executing an INSERT statement you must call self.connection.commit() to persist the change. Without it, the row is written only to a temporary transaction buffer and is rolled back when the connection closes.

Semana 3/curso.py

def crearCurso(self, nombre, descripcion, tiempo, usuario):
    sql = "INSERT INTO curso(id, nombre, descripcion, tiempo, usuario) VALUES ('{}','{}','{}','{}','{}')".format(
        0, nombre, descripcion, tiempo, usuario
    )

    try:  # atrapa los errores y no permite que la aplicacion se congele o caiga
        self.cursor.execute(sql)
        self.connection.commit()  # commit a la base de datos (update, insert, delete)

    except Exception as e:
        print('Error: ', e)
        raise

In the course the method is called like this:

database.crearCurso('Curso Python 2023', 'Curso Avanzado', '1 Mes', 'Prof Mario')

UPDATE — Modifying Existing Records

The class provides two update methods — one that updates only the nombre column and one that updates all editable columns at once.

def updateCursoById(self, id, nombre):
    sql = "UPDATE curso SET nombre='{}' WHERE id='{}'".format(nombre, id)

    try:  # atrapa los errores y no permite que la aplicacion se congele o caiga
        self.cursor.execute(sql)
        self.connection.commit()  # commit a la base de datos (update, insert, delete)

    except Exception as e:
        print('Error: ', e)
        raise

try/except Error Handling

Every method wraps its database call in a try/except Exception as e block. This pattern has two important effects:

It prevents the application from crashing silently — the error message is printed to the console so you know what went wrong.
raise re-throws the exception after printing, so the caller can also react to it (e.g., show an error to the user or attempt a retry).

try:
    self.cursor.execute(sql)
    curso = self.cursor.fetchall()
    # ... process results ...
except Exception as e:
    print('Error: ', e)
    raise

Using Exception as the catch-all type captures every non-system-exiting exception, including network failures, SQL syntax errors, and authentication problems — all realistic failure modes when working with a remote database.

The Simpler `DataBase` Class (`sc.py`)

The course also includes a leaner version of the database class in Semana 3/sc.py. It demonstrates the same connection pattern but with a minimal set of methods and an explicit close() call — a good starting template.

Semana 3/sc.py

import pymysql

class DataBase:
    def __init__(self):
        self.connection = pymysql.connect(
            host     = 'sql863.main-hosting.eu',
            user     = 'u484426513_apireact',
            password = 'i:![VW:3S#',
            db       = 'u484426513_apireact'
        )
        self.cursor = self.connection.cursor()
        print('Estoy conectado a una base de datos')

    def select_curso(self, id):
        print('id', id)
        sql = 'SELECT * FROM curso where id = {}'.format(id)
        try:
            self.cursor.execute(sql)
            curso = self.cursor.fetchone()
            print("nombre:", curso[1])
        except Exception as e:
            raise

    def select_all_curso(self):
        sql = 'SELECT * FROM curso'
        try:
            self.cursor.execute(sql)
            curso = self.cursor.fetchall()
            for item in curso:
                print("id:",          item[0])
                print("Nombre:",      item[1])
                print("Descripcion:", item[2])
                print("Tiem[p]:",     item[3])
                print("_______\n")
        except Exception as e:
            raise

    def update_curso(self, id, nombre):
        sql = "UPDATE curso SET nombre='{}' WHERE id = '{}'".format(nombre, id)
        try:
            self.cursor.execute(sql)
            self.connection.commit()  # es necesario
        except Exception as e:
            print(e)
            raise

    def close(self):
        self.connection.close()


database = DataBase()
database.select_curso(14)
database.update_curso(14, 'Mario')
database.select_curso(14)
database.close()

Notice that close() explicitly releases the connection back to the server. In longer-running applications this is important to avoid exhausting the server’s connection pool.

CRUD Method Summary

The table below maps each method across both classes to its SQL operation and whether a commit() is required.

Method	Class	SQL Operation	Requires `commit()`
`getCurso()`	`Database`	`SELECT … FROM curso`	No
`getCursoById(id)`	`Database`	`SELECT … WHERE id=?`	No
`crearCurso(…)`	`Database`	`INSERT INTO curso …`	Yes
`updateCursoById(id, nombre)`	`Database`	`UPDATE curso SET nombre …`	Yes
`updateCursoTiempoById(id, tiempo)`	`Database`	`UPDATE curso SET tiempo …`	Yes
`updateCursoTotalById(id, …)`	`Database`	`UPDATE curso SET … (all fields)`	Yes
`select_curso(id)`	`DataBase`	`SELECT * WHERE id=?`	No
`select_all_curso()`	`DataBase`	`SELECT * FROM curso`	No
`update_curso(id, nombre)`	`DataBase`	`UPDATE curso SET nombre …`	Yes

connection.commit() is only needed for write operations — INSERT, UPDATE, and DELETE. SELECT statements are read-only and do not modify the database, so they take effect immediately without a commit. Forgetting commit() after a write is one of the most common bugs when learning database programming: the code appears to succeed (no exception is raised) but the change never actually persists.

Getting Started

Week 1 — Python Basics

Week 2 — Control Flow

Week 3 — Functions & OOP

Week 4 — Advanced Topics

Web Basics

Connecting Python to MySQL: CRUD Operations with pymysql

Installing pymysql

The `Database` Class — Connecting and Creating a Cursor

SELECT — Fetching All Records

SELECT — Fetching a Single Record by ID

INSERT — Creating a New Record

UPDATE — Modifying Existing Records

try/except Error Handling

The Simpler `DataBase` Class (`sc.py`)

CRUD Method Summary

Build docs developers (and LLMs) love

Getting Started

Week 1 — Python Basics

Week 2 — Control Flow

Week 3 — Functions & OOP

Week 4 — Advanced Topics

Web Basics

Documentation Index

​Installing pymysql

​The Database Class — Connecting and Creating a Cursor

​SELECT — Fetching All Records

​SELECT — Fetching a Single Record by ID

​INSERT — Creating a New Record

​UPDATE — Modifying Existing Records

​try/except Error Handling

​The Simpler DataBase Class (sc.py)

​CRUD Method Summary

Build docs developers (and LLMs) love

Installing pymysql

The `Database` Class — Connecting and Creating a Cursor

SELECT — Fetching All Records

SELECT — Fetching a Single Record by ID

INSERT — Creating a New Record

UPDATE — Modifying Existing Records

try/except Error Handling

The Simpler `DataBase` Class (`sc.py`)

CRUD Method Summary