Python - MySQL Database

Until now, we have discussed Python Database Programming with Oracle Database. Now we will learn how to work with another popular relational Database called MySQL.

MySQL is a widely used Relational Database Management System (RDBMS). It is used to store, organize, retrieve, update and manage data in the form of tables. MySQL uses SQL — Structured Query Language for performing Database operations.

Simple Definition

MySQL is an RDBMS used to store and manage
data in the form of related tables.

For example, an organization can maintain:

Company Database
      │
      ├── employees
      ├── departments
      ├── customers
      ├── products
      └── orders

Each table stores information related to a particular entity.

What is a Database?

A Database is an organized collection of related data. For example, consider employee information:

Employee Number
Employee Name
Employee Salary
Employee Address

Instead of maintaining this information in separate files, we can store it systematically inside a Database.

What is an RDBMS?

RDBMS stands for Relational Database Management System. In an RDBMS, data is mainly organized into tables consisting of rows and columns.

employees

+------+--------+--------+
| eno  | ename  | esal   |
+------+--------+--------+
| 100  | Sachin | 50000  |
| 200  | Dhoni  | 60000  |
| 300  | Kohli  | 70000  |
+------+--------+--------+

Here:

• employees is the table.
• eno, ename and esal are columns.
• Each horizontal entry is a row or record.

MySQL is called a Relational Database Management System because it stores information in tables and supports relationships between tables. Different tables can be related using keys such as Primary Key and Foreign Key.

SQL vs MySQL

SQL and MySQL are not exactly the same thing.

SQL	MySQL
Structured Query Language	Relational Database Management System
A language	Database software
Used to communicate with relational Databases	Uses SQL for Database operations

For example, SELECT * FROM employees; is an SQL statement that can be executed by MySQL.

Features of MySQL

• MySQL is a Relational Database Management System.
• It supports SQL.
• It is available as open-source software, with commercial offerings also available.
• It follows a client-server architecture.
• It supports multiple users and can manage multiple Databases.
• It supports tables, rows and columns.
• It supports primary keys, foreign keys, indexes, views, stored procedures and triggers.
• It supports transactions with transactional storage engines such as InnoDB.
• It provides security and user-management features.
• It can be accessed from programming languages such as Python.

MySQL generally follows a client-server architecture. The MySQL Server manages Databases, while clients send requests to the Server.

              MySQL Server
                   │
          ┌────────┼────────┐
          │        │        │
          ▼        ▼        ▼
       Client 1 Client 2 Client 3
          │        │        │
       Python    CLI     Workbench

Examples of MySQL clients include:

• MySQL command-line client
• MySQL Workbench
• Python applications
• Web applications

MySQL Server

The MySQL Server is responsible for managing MySQL Databases. It performs operations such as creating Databases, creating tables, storing records, retrieving records, updating records, deleting records, managing users, controlling permissions and processing SQL statements.

Client
  │
  │ SQL Request
  ▼
MySQL Server
  │
  ▼
Database
  │
  ▼
Result
  │
  ▼
Client

MySQL Client

A MySQL Client is a program used to communicate with the MySQL Server. For example, the MySQL command-line client allows us to type SQL commands directly. Later, our Python program will also act as a client of the MySQL Server.

After MySQL Server and the client tools are installed, we can connect to MySQL from the command line.

A common command is:

mysql -u root -p

Here:

Part	Meaning
mysql	Starts the MySQL command-line client
-u	Specifies the username
root	Username
-p	Requests the password securely

Login Example

Enter mysql -u root -p. MySQL asks for the password:

Enter password:

After successful authentication, the MySQL prompt is displayed:

mysql>

Now SQL and MySQL client commands can be executed.

Command Prompt
      │
      ▼
mysql -u root -p
      │
      ▼
Enter Password
      │
      ▼
Authentication
      │
      ▼
mysql>

The semicolon ; is normally used to terminate an SQL statement. For example:

mysql> SHOW DATABASES;

A MySQL Server installation contains system Databases used internally for administration, metadata and other Server functionality.

Common system Databases include:

information_schema
mysql
performance_schema
sys

The exact Databases visible on a particular installation can vary according to the MySQL version, configuration and any Databases already created by the user.

information_schema

information_schema provides metadata about the Databases and objects available on the MySQL Server. Metadata means data about data. It can provide information about Databases, Tables, Columns, Constraints, Privileges and other Database objects.

mysql

The mysql Database is an important system Database. It stores information used by the MySQL Server for areas such as user accounts, privileges, authentication-related information and server-related system information. It should not be treated like an ordinary application Database.

performance_schema

The performance_schema Database provides information used to monitor MySQL Server execution and performance. It can help inspect different Server activities and performance-related events.

sys

The sys schema provides convenient views and other objects that make performance and diagnostic information easier to understand. It works with information available from MySQL's internal metadata and performance facilities.

To display the Databases available to the current MySQL account, use:

SHOW DATABASES;

Example:

mysql> SHOW DATABASES;

The output may look similar to:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Additional user-created Databases may also appear.

Create a New Database

A new Database can be created using the CREATE DATABASE statement.

CREATE DATABASE database_name;

Example:

CREATE DATABASE employee_db;

After successful execution, the Database employee_db is created. Running SHOW DATABASES; again now may include:

+--------------------+
| Database           |
+--------------------+
| employee_db        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Select a Database using USE

Before creating or working with tables, we normally select the required Database.

USE database_name;

Example:

USE employee_db;

After this command, employee_db becomes the default Database for statements that do not explicitly specify another Database.

Check Current Database

We can check the currently selected Database using:

SELECT DATABASE();

If employee_db is selected, the result will be similar to:

+------------+
| DATABASE() |
+------------+
| employee_db|
+------------+

If no default Database has been selected, DATABASE() can return NULL.

After selecting employee_db, we can create an employees table.

CREATE TABLE employees(
    eno INT,
    ename VARCHAR(20),
    esal DOUBLE,
    eaddr VARCHAR(20)
);

The table contains four columns:

Column	Data Type	Purpose
eno	INT	Employee Number
ename	VARCHAR(20)	Employee Name
esal	DOUBLE	Employee Salary
eaddr	VARCHAR(20)	Employee Address

Understanding MySQL Data Types

Different columns can store different types of values.

Data Type	Purpose	Example
INT	Integer numbers	100
VARCHAR(n)	Variable-length text	Sachin
DOUBLE	Floating-point numeric values	50000.50
DATE	Date values	2026-07-25

Display Tables

To display tables in the currently selected Database, use:

SHOW TABLES;

If employees exists, the output will be similar to:

+-----------------------+
| Tables_in_employee_db |
+-----------------------+
| employees             |
+-----------------------+

View Table Structure using DESC

To view the structure of a table, we can use:

DESC employees;

or:

DESCRIBE employees;

The output displays information such as column names, data types, whether NULL is allowed, key information, default values and extra attributes.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eno   | int         | YES  |     | NULL    |       |
| ename | varchar(20) | YES  |     | NULL    |       |
| esal  | double      | YES  |     | NULL    |       |
| eaddr | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Records can be inserted using the SQL INSERT statement.

INSERT INTO employees
VALUES (100, 'Sachin', 50000, 'Mumbai');

INSERT INTO employees
VALUES (200, 'Dhoni', 60000, 'Ranchi');

INSERT INTO employees
VALUES (300, 'Kohli', 70000, 'Delhi');

Now the table contains three employee records.

Retrieve Employee Records

To retrieve all employee records, use:

SELECT * FROM employees;

Example output:

+------+--------+-------+--------+
| eno  | ename  | esal  | eaddr  |
+------+--------+-------+--------+
| 100  | Sachin | 50000 | Mumbai |
| 200  | Dhoni  | 60000 | Ranchi |
| 300  | Kohli  | 70000 | Delhi  |
+------+--------+-------+--------+

Here:

• SELECT retrieves data.
• * means all columns.
• FROM employees specifies the source table.

Select Specific Columns

We do not always have to retrieve every column.

SELECT eno, ename FROM employees;

This retrieves only the employee number and employee name.

+------+--------+
| eno  | ename  |
+------+--------+
| 100  | Sachin |
| 200  | Dhoni  |
| 300  | Kohli  |
+------+--------+

Filtering Records using WHERE

The WHERE clause can be used to retrieve records that satisfy a condition.

SELECT * FROM employees
WHERE eno = 100;

Output:

+------+--------+-------+--------+
| eno  | ename  | esal  | eaddr  |
+------+--------+-------+--------+
| 100  | Sachin | 50000 | Mumbai |
+------+--------+-------+--------+

Existing data can be modified using the UPDATE statement.

UPDATE employees
SET esal = 55000
WHERE eno = 100;

This changes the salary of employee number 100.

Before
esal = 50000
      │
      ▼
UPDATE
      │
      ▼
After
esal = 55000

Delete Employee Record

The DELETE statement removes records from a table.

DELETE FROM employees
WHERE eno = 300;

This removes the employee whose employee number is 300.

The WHERE condition is very important because DELETE FROM employees; without a WHERE clause attempts to delete all rows from the table.

Drop Employees Table

To remove the complete table definition and its data, use:

DROP TABLE employees;

This is different from DELETE.

DELETE	DROP TABLE
Deletes rows	Removes the table itself
Table remains	Table definition is removed
Can use WHERE	Does not use WHERE to remove individual rows

Drop a Database

A complete Database can be removed using:

DROP DATABASE database_name;

Example:

DROP DATABASE employee_db;

This removes the Database and its objects. Important: DROP DATABASE is destructive and should be used carefully.

SQL commands are commonly grouped according to their purpose.

Category	Meaning	Examples
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	INSERT, UPDATE, DELETE
DQL	Data Query Language	SELECT
DCL	Data Control Language	GRANT, REVOKE
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT

• DDL commands are used to define or modify Database structures. Important commands include CREATE, ALTER, DROP and TRUNCATE.
• DML commands are used to manipulate table records. Important commands include INSERT, UPDATE and DELETE.
• DQL is used to query data. The main command is SELECT, which retrieves data from the table.
• TCL commands include COMMIT, ROLLBACK and SAVEPOINT. COMMIT makes transaction changes permanent, and ROLLBACK can cancel applicable uncommitted transaction changes.
• DCL is mainly related to Database permissions. GRANT gives privileges and REVOKE removes previously granted privileges.

Important MySQL Commands

Command	Purpose
SHOW DATABASES;	Display available Databases
CREATE DATABASE db;	Create a Database
USE db;	Select a default Database
SELECT DATABASE();	Show the current default Database
SHOW TABLES;	Display tables in the selected Database
DESC table;	Display table structure
CREATE TABLE	Create a table
INSERT INTO	Insert records
SELECT	Retrieve records
UPDATE	Modify records
DELETE	Delete records
DROP TABLE	Remove a table
DROP DATABASE	Remove a Database

The following sequence demonstrates the basic MySQL workflow. First, log in with mysql -u root -p.

SHOW DATABASES;

CREATE DATABASE employee_db;

SHOW DATABASES;

USE employee_db;

SELECT DATABASE();

CREATE TABLE employees(
    eno INT,
    ename VARCHAR(20),
    esal DOUBLE,
    eaddr VARCHAR(20)
);

SHOW TABLES;

DESC employees;

INSERT INTO employees
VALUES (100, 'Sachin', 50000, 'Mumbai');

INSERT INTO employees
VALUES (200, 'Dhoni', 60000, 'Ranchi');

INSERT INTO employees
VALUES (300, 'Kohli', 70000, 'Delhi');

SELECT * FROM employees;

After inserting the records, SELECT * FROM employees; produces:

+------+--------+-------+--------+
| eno  | ename  | esal  | eaddr  |
+------+--------+-------+--------+
| 100  | Sachin | 50000 | Mumbai |
| 200  | Dhoni  | 60000 | Ranchi |
| 300  | Kohli  | 70000 | Delhi  |
+------+--------+-------+--------+

MySQL vs Oracle Database

Both MySQL and Oracle Database are relational Database systems, but they are separate products with different ecosystems and features.

MySQL	Oracle Database
Relational Database system	Relational Database system
Owned and developed by Oracle Corporation	Developed by Oracle Corporation
Has open-source Community Edition and commercial editions	Primarily commercial, with certain free editions available
Commonly used for web and general-purpose applications	Commonly used in enterprise Database environments
Uses SQL with MySQL-specific features	Uses SQL with Oracle-specific features
Can be accessed from Python	Can be accessed from Python

From MySQL Command Line to Python

At this stage, we are learning MySQL commands directly. After understanding these commands, we can execute similar Database operations from Python. A Python MySQL driver provides the connection between Python and MySQL.

Current Stage

User
 │
 ▼
MySQL Client
 │
 ▼
MySQL Server


Next Stage

Python Program
 │
 ▼
MySQL Driver
 │
 ▼
MySQL Server
 │
 ▼
Database

Python cannot communicate with MySQL Database directly. We need suitable Database driver or connector software between Python and MySQL.

A Database Driver is software that allows an application to communicate with a Database Management System.

Database Driver =
Software that provides communication
between an application and a Database.

Suppose we write a Python program and want to retrieve employee information from MySQL. Python needs a library that knows how to:

• Connect to the MySQL Server
• Authenticate the Database user
• Send SQL statements
• Receive query results
• Handle transactions
• Handle Database errors
• Close Database connections

Without Driver

Python  ─────X─────► MySQL


With Driver

Python
  │
  ▼
MySQL Driver
  │
  ▼
MySQL Server

What is a Connector?

A connector is a software component that enables an application to establish communication with a Database system. In Python MySQL programming, the terms driver and connector are often used when discussing the library that provides Database connectivity. Once the connector is installed, Python can import its modules and use the provided API.

Python to MySQL Architecture

The Python program sends SQL commands through the connector. The connector communicates with the MySQL Server and returns the result to the Python application.

              Python Application
                     │
                     ▼
              Python DB API /
              Connector API
                     │
                     ▼
             MySQL Connector
                     │
                     ▼
                MySQL Server
                     │
                     ▼
                  Database
                     │
                     ▼
                   Table

MySQL Drivers Available for Python

Several libraries can be used to communicate with MySQL from Python. Examples include mysql-connector-python, PyMySQL and mysqlclient. In this tutorial, we will use mysql-connector-python, which provides the mysql.connector Python module used in our programs.

Package Name vs Import Name

One important point is that the package name used during installation and the module name used inside Python are different.

Purpose	Name
Package installation	mysql-connector-python
Python import	mysql.connector

Installation:

python -m pip install mysql-connector-python

Import:

import mysql.connector

Do not confuse these two names.

Before installing MySQL Connector/Python, verify that Python and pip are available.

Check Python:

python --version

Depending on the system, you may also use py --version or python3 --version. Check pip:

python -m pip --version

If Python and pip are configured correctly, their version information will be displayed.

Installing the Connector

The connector can be installed from the Python Package Index using pip.

Recommended command:

python -m pip install mysql-connector-python

On systems where the Python launcher is used:

py -m pip install mysql-connector-python

On some Linux/macOS environments:

python3 -m pip install mysql-connector-python

A commonly seen command is pip install mysql-connector-python. However, the python -m pip install mysql-connector-python form is generally clearer because it explicitly runs pip through the selected Python interpreter. The command downloads and installs the connector into the Python environment associated with that interpreter.

Possible Installation Output

During installation, output may look similar to:

Collecting mysql-connector-python
Downloading mysql_connector_python-...
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-...

The important message is similar to Successfully installed mysql-connector-python.

Verify the Installation

After installation, we can inspect the package using:

python -m pip show mysql-connector-python

The command displays information such as Name, Version and Location. We can also display installed Python packages using:

python -m pip list

If mysql-connector-python appears in the package list, the package is installed in that Python environment.

The simplest test is to import the module. If the connector is unavailable in the current Python environment, Python may report an import error such as:

ModuleNotFoundError: No module named 'mysql'

The complete connector test program imports mysql.connector, displays a confirmation message and prints the installed connector version.

The first statement import mysql.connector asks Python to locate and load the mysql.connector module. Then the print() statements display the confirmation message and the installed connector version. The exact version number can differ from system to system.

Finding the Installed Module

We can inspect where the imported module is located using:

import mysql.connector

print(mysql.connector.__file__)

The output may point to a location similar to .../site-packages/mysql/connector/__init__.py.

site-packages is a common directory where third-party Python packages are installed. When we install the connector with pip, it is normally installed into a package location associated with that Python environment, and Python's import system can then locate the installed module.

MySQL Connector imported successfully
Connector Version: 9.x.x

PATH is an operating-system environment variable. It contains a list of directories that the operating system searches when we type executable commands.

PATH =
Directories searched by the operating system
for executable commands.

For example, when we type python, the operating system may search directories listed in PATH to locate the Python executable. On Windows, Python-related PATH entries may look similar to:

C:UsersUserAppDataLocalProgramsPythonPython3xxC:UsersUserAppDataLocalProgramsPythonPython3xxScripts

Do not copy a path blindly. Always use the actual Python installation directories on your computer.

In Command Prompt on Windows, display the current PATH using echo %PATH%, locate the Python command using where python and locate pip using where pip. On Linux or macOS, the PATH value can commonly be displayed using echo $PATH, Python located using which python3 and pip located using which pip3.

What is PYTHONPATH?

PYTHONPATH is an optional environment variable that can add directories to Python's module search path.

PYTHONPATH =
Optional directories added to Python's
module search path.

When Python executes import some_module, it searches locations available through its import system, including entries in sys.path. If PYTHONPATH is configured, its directories can be added to that search path.

On Windows Command Prompt, check it using echo %PYTHONPATH%; on Linux/macOS, echo $PYTHONPATH. If no explicit PYTHONPATH has been configured, the variable may be empty or undefined - this is completely normal for many Python installations.

Normally, no manual PYTHONPATH configuration is required when MySQL Connector/Python is installed correctly into the Python environment being used. A package installed correctly with pip into the active Python environment is available through that environment's site-packages.

PATH vs PYTHONPATH

Feature	PATH	PYTHONPATH
Used By	Operating system / shell	Python import system
Main Purpose	Locate executable commands	Add module-search directories
Example	Finding python	Helping Python locate custom modules
Related To	Commands and executables	Python imports

PATH vs sys.path

PATH and sys.path are also different concepts. The operating system may use PATH to locate the python executable, while Python uses sys.path to locate importable modules. Inside app.py, import mysql.connector uses Python's import search path, represented by sys.path, to locate the module.

Python's current module search path can be inspected using sys.path:

import sys

for path in sys.path:
    print(path)

ModuleNotFoundError

Suppose we execute import mysql.connector and receive ModuleNotFoundError: No module named 'mysql'. This usually means that the required package is not available to the Python interpreter running the program. Possible reasons include:

• The connector is not installed.
• The connector was installed for a different Python installation.
• A different virtual environment is active.
• The IDE is using another Python interpreter.

First check which Python interpreter is being used with python --version, then check the package with python -m pip show mysql-connector-python. If it is not installed, execute python -m pip install mysql-connector-python and test with python -c "import mysql.connector; print(mysql.connector.__version__)". If the command succeeds but the code still fails inside an IDE, verify that the IDE is using the same Python interpreter.

Multiple Python Installations

A computer may contain multiple Python installations. If the connector is installed for Python A but the program runs with Python B, the import can fail even though the package is installed. Using python -m pip install mysql-connector-python helps associate the package installation with the Python interpreter being invoked. From inside Python, sys.executable displays the interpreter path.

Virtual Environments

Python projects are often created inside a virtual environment, which provides an isolated Python package environment for a project. If a virtual environment is being used, install the connector into that environment.

python -m venv venv

On Windows Command Prompt, activation commonly uses venv\Scripts\activate; on Windows PowerShell, .\venv\Scripts\Activate.ps1; on Linux/macOS, source venv/bin/activate. After activation, install the connector with python -m pip install mysql-connector-python.

Connector Installation vs MySQL Server Installation

Installing MySQL Connector/Python is different from installing MySQL Server. Installing python -m pip install mysql-connector-python does not install the MySQL Database Server itself. The connector can import successfully even if the MySQL Server is currently stopped. Therefore, successful import confirms the connector installation, but it does not by itself confirm that a Database connection can be established.

MySQL Server	MySQL Connector/Python
Runs and manages MySQL Databases	Allows Python to communicate with MySQL
Stores Database data	Provides Python connectivity APIs
Database Server software	Python package

Connection Test Program

After the connector is installed and the MySQL Server is running, we can perform a basic connection test. Replace your_password with the password configured for the MySQL account being used.

Connection Parameters

The connection function receives Database connection information. mysql.connector.connect() attempts to establish a connection with the MySQL Server, con.is_connected() checks whether the Connection object reports an active connection, Database-related connector errors are handled using except mysql.connector.Error as e, and the connection is finally closed using con.close().

Parameter	Purpose
host	MySQL Server hostname or address
user	MySQL username
password	Password for the MySQL account

For a local MySQL Server, host="localhost" is commonly used.

Common Connection Problems

Even after the connector is installed correctly, Database connection errors can occur. Common reasons include:

• MySQL Server is not running.
• Incorrect username, password, hostname or port.
• User does not have required privileges.
• Server is not accepting the requested connection.
• Network or firewall configuration prevents access to a remote Server.

Problem	Likely Area
ModuleNotFoundError	Python package/environment problem
python command not found	Python installation/PATH problem
pip command not found	pip command/PATH/environment problem
Access denied	MySQL authentication/privilege problem
Cannot connect to MySQL Server	Server/network/configuration problem

Recommended Setup Verification

Use the following sequence to verify the Python-MySQL environment.

1. Check Python: python --version
2. Check pip: python -m pip --version
3. Install Connector: python -m pip install mysql-connector-python
4. Verify Package: python -m pip show mysql-connector-python
5. Test Import: python -c "import mysql.connector; print(mysql.connector.__version__)"
6. Verify MySQL Server: mysql -u root -p
7. Test Connection from Python: mysql.connector.connect(...)

Connected to MySQL successfully
MySQL connection closed

Now we will use Python to perform actual Database operations on a MySQL Database. In this application, we will connect Python with MySQL, create a Cursor, create an employees table, insert employee records, use commit() to save inserted records, execute a SELECT query and display employee information.

Application Requirements:

• Python is installed.
• MySQL Server is installed and running.
• mysql-connector-python is installed.
• A valid MySQL username and password are available.
• The required Database exists. For example, create it with CREATE DATABASE employee_db; and verify it using SHOW DATABASES;.

The application uses the employee_db Database and the employees table with the columns eno, ename, esal and eaddr.

Note: If you run this same program repeatedly without clearing the table or adding a uniqueness constraint, additional copies of these employee records will be inserted.

Employees table created successfully
3 records inserted successfully

Employee Information
(100, 'Sachin', 50000.0, 'Mumbai')
(200, 'Dhoni', 60000.0, 'Ranchi')
(300, 'Kohli', 70000.0, 'Delhi')
MySQL connection closed

Step 1 — Import MySQL Connector

The program starts with import mysql.connector. This imports the MySQL Connector/Python module and provides functionality for connecting to MySQL, creating Cursor objects, executing SQL queries, managing transactions, fetching query results and handling MySQL errors.

Step 2 — Establish MySQL Connection

The connection is established using:

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="employee_db"
)

Parameter	Purpose
host	Specifies the MySQL Server
user	Specifies the MySQL username
password	Specifies the MySQL account password
database	Specifies the Database to use

Replace your_password with the password configured for your MySQL account.

Step 3 — Create Cursor Object

After establishing the connection, create a Cursor:

cursor = con.cursor()

The Cursor object is used to communicate with MySQL through SQL statements.

Connection
    │
    ▼
con.cursor()
    │
    ▼
Cursor
    │
    ├── execute()
    ├── executemany()
    ├── fetchone()
    ├── fetchmany()
    └── fetchall()

Step 4 — Create Employees Table

cursor.execute("""
    CREATE TABLE IF NOT EXISTS employees(
        eno INT,
        ename VARCHAR(20),
        esal DOUBLE,
        eaddr VARCHAR(20)
    )
""")

IF NOT EXISTS prevents an error when a table with the same name already exists. If the table already exists, it is not recreated.

SQL Part	Meaning
CREATE TABLE	Creates a new table
IF NOT EXISTS	Create it only when the named table does not already exist
employees	Table name

Step 5 — Prepare INSERT Query with Placeholders

sql = """
    INSERT INTO employees(eno, ename, esal, eaddr)
    VALUES (%s, %s, %s, %s)
"""

The %s placeholders represent values that will be supplied separately through the connector. There are four placeholders because four column values are inserted. Values should normally be supplied separately instead of concatenating user input directly into SQL strings.

Step 6 — Employee Records

Multiple employee records are stored in a list. Each tuple represents one employee:

records = [
    (100, "Sachin", 50000, "Mumbai"),
    (200, "Dhoni", 60000, "Ranchi"),
    (300, "Kohli", 70000, "Delhi")
]

The tuple values correspond to (eno, ename, esal, eaddr).

Step 7 — Insert Multiple Records using executemany()

cursor.executemany(sql, records)

executemany() executes the parameterized SQL operation for each parameter set in the supplied sequence.

Method	Typical Purpose
execute()	Execute one SQL statement with one parameter set
executemany()	Execute the same parameterized operation for multiple parameter sets

Step 8 — Save Records using commit()

con.commit()

commit() commits the current transaction so the inserted records become permanent. This is an important step after transaction-changing operations such as INSERT, UPDATE and DELETE when autocommit is not being used.

If a transaction-changing operation is executed but not committed, its changes may remain uncommitted. If the transaction is later rolled back or the connection ends without committing, those uncommitted changes may not be preserved.

INSERT
  │
  ▼
Uncommitted Changes
  │
  ├── commit()   → Save Changes
  │
  └── rollback() → Cancel Applicable Changes

Step 9 — Check rowcount

print(cursor.rowcount, "records inserted successfully")

cursor.rowcount reports the number of rows affected by the operation according to the connector's result. For this example, it should normally display 3 records inserted successfully.

Step 10 — Execute SELECT Query

cursor.execute("SELECT * FROM employees")

SELECT retrieves data, * retrieves all columns and FROM employees specifies the source table.

Step 11 — Retrieve Records using fetchall()

data = cursor.fetchall()

fetchall() retrieves all remaining rows from the query result. Each employee row is represented as a tuple, for example (100, 'Sachin', 50000.0, 'Mumbai').

Step 12 — Display Employee Data

for row in data:
    print(row)

During each iteration, one employee record is assigned to row and printed. Each row is a tuple, so individual column values can be accessed using indexes: row[0] is eno, row[1] is ename, row[2] is esal and row[3] is eaddr.

Exception Handling and Cleanup

MySQL Connector errors can be handled using except mysql.connector.Error as e. The exception object e contains information about the Database error. Errors can occur because of incorrect username, incorrect password, MySQL Server not running, Database does not exist, invalid SQL syntax, invalid table name, constraint violations or connection problems.

When a transaction-changing operation fails, we may roll back uncommitted changes using con.rollback().

except mysql.connector.Error as e:
    if 'con' in locals() and con.is_connected():
        con.rollback()

    print("MySQL Error:", e)

The finally block is used to release Database resources. It executes whether the main operation succeeds or fails.

finally:
    if 'cursor' in locals():
        cursor.close()

    if 'con' in locals() and con.is_connected():
        con.close()

The if 'con' in locals() check verifies that the variable con was created in the current local scope before trying to use it. This can be useful when an exception occurs before the connection or Cursor object is successfully created. cursor.close() releases resources associated with the Cursor, and con.close() closes the Connection after Database operations are completed.

Methods Used in This Application

Method / Attribute	Purpose
mysql.connector.connect()	Connect to MySQL Server
con.cursor()	Create Cursor
cursor.execute()	Execute one SQL statement
cursor.executemany()	Execute parameterized SQL for multiple parameter sets
con.commit()	Commit transaction changes
con.rollback()	Roll back applicable uncommitted changes
cursor.fetchall()	Retrieve all remaining query rows
cursor.rowcount	Report affected row count
con.is_connected()	Check connection state
cursor.close()	Close Cursor
con.close()	Close Connection

SQL Statements Used

SQL Statement	Purpose
CREATE TABLE	Create employees table
INSERT INTO	Insert employee records
SELECT	Retrieve employee records

Each row returned by fetchall() is a tuple, so individual column values can be accessed using indexes. For a row such as (100, 'Sachin', 50000.0, 'Mumbai'):

Expression	Column	Example Value
row[0]	eno	100
row[1]	ename	Sachin
row[2]	esal	50000.0
row[3]	eaddr	Mumbai

Inserting a Single Record using execute()

If we want to insert only one employee, we can use execute() instead of executemany().

sql = """
    INSERT INTO employees(eno, ename, esal, eaddr)
    VALUES (%s, %s, %s, %s)
"""

employee = (400, "Rohit", 80000, "Mumbai")

cursor.execute(sql, employee)

con.commit()

print(cursor.rowcount, "record inserted successfully")

This inserts one employee record and prints 1 record inserted successfully.

Dynamic Insert using Keyboard Input

Employee information can also be collected from the user. The entered values are supplied separately from the SQL statement.

import mysql.connector

try:
    con = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="employee_db"
    )

    cursor = con.cursor()

    eno = int(input("Enter Employee Number: "))
    ename = input("Enter Employee Name: ")
    esal = float(input("Enter Employee Salary: "))
    eaddr = input("Enter Employee Address: ")

    sql = """
        INSERT INTO employees(eno, ename, esal, eaddr)
        VALUES (%s, %s, %s, %s)
    """

    employee = (eno, ename, esal, eaddr)

    cursor.execute(sql, employee)

    con.commit()

    print(cursor.rowcount, "record inserted successfully")

except mysql.connector.Error as e:
    print("MySQL Error:", e)

finally:
    if 'cursor' in locals():
        cursor.close()

    if 'con' in locals() and con.is_connected():
        con.close()

Example execution:

Enter Employee Number: 500
Enter Employee Name: Rahul
Enter Employee Salary: 90000
Enter Employee Address: Bangalore
1 record inserted successfully

The resulting record is (500, 'Rahul', 90000.0, 'Bangalore').

Placeholders and Query Safety

With MySQL Connector/Python, parameterized SQL commonly uses %s. Even for numeric values, the placeholder is written as %s, and the connector handles the supplied Python values appropriately.

Avoid building SQL statements by directly joining user input into the query, for example:

query = "INSERT INTO employees VALUES (" + user_input + ")"

Instead, use parameterized queries, which are safer and handle values more reliably.

Running the Program Multiple Times

The table is created using CREATE TABLE IF NOT EXISTS employees(...), so if the table already exists, it is not recreated. However, the INSERT statements still execute every time the program runs, so duplicate rows may be added because the current table definition does not declare eno as unique.

If each employee number must be unique, we can define eno as a primary key:

CREATE TABLE IF NOT EXISTS employees(
    eno INT PRIMARY KEY,
    ename VARCHAR(20),
    esal DOUBLE,
    eaddr VARCHAR(20)
);

A primary key uniquely identifies each record. Then inserting the same eno again causes a duplicate-key error instead of silently creating another employee with the same number.

Employee Number  : 100
Employee Name    : Sachin
Employee Salary  : 50000.0
Employee Address : Mumbai

Employee Number  : 200
Employee Name    : Dhoni
Employee Salary  : 60000.0
Employee Address : Ranchi

Employee Number  : 300
Employee Name    : Kohli
Employee Salary  : 70000.0
Employee Address : Delhi