Three Ways to Use Python with MySQL: Procedural, OOP, and Modular Approaches

Introduction

The author plans to build a Python‑based exam system for a semester‑long course and will share the required technologies in a series of blog posts. This article focuses on one essential skill: interacting with MySQL from Python.

Understanding __name__ == '__main__'

When a .py file is executed directly, the built‑in variable __name__ equals '__main__'; when the file is imported as a module, __name__ equals the module’s filename. This check is commonly used to make a script both importable and executable.

Command‑Line Arguments

Command‑line arguments are passed to a program in the same line as the interpreter call, e.g., python3 pyt3.py 100 101 3. The sys module provides sys.argv to retrieve these values inside the script.

Using pymysql to Access MySQL

Since mysqldb is no longer supported in Python 3+, pymysql is the recommended library. Its typical workflow is:

Establish a connection to the database.

Obtain a cursor (a database cursor).

Execute SQL statements via cursor.execute().

Handle transactions: on success call connection.commit(); on error call connection.rollback().

Fetch results with cursor.fetchall() or similar methods.

A transaction groups a set of operations into an atomic unit—either all succeed or none are applied—preventing inconsistent states such as a failed bank transfer.

Three Approaches to Operate MySQL

1. Procedural (Plain) Method

This style follows a straightforward, step‑by‑step script. The code ends with a try/except block that commits on success or rolls back on exception, ensuring atomicity. An example scenario shows a bank transfer where, without a transaction, a cancelled account could cause loss of funds.

2. Object‑Oriented Method

All database operations are encapsulated inside a class. The class’s transfer method contains the same transaction logic as the procedural version. The script’s entry point checks if __name__ == '__main__' to run the class when executed directly.

3. Modular (Import‑Based) Method

The second (OOP) implementation is saved as temp2.py. Another script can simply import temp2 and invoke its class methods. In this case, temp2.py 's __name__ is 'temp2', not '__main__', so the entry‑point block does not run automatically.

Connection credentials (host, password) are redacted as xxx for security; users should replace them with their own values.