Module 20 - MySQL Basics & Python Integration

1. RDBMS,Normalization, DB Models, DB Structures

1. Relational Database Management Systems (RDBMS):

● Definition: An RDBMS organizes data into related tables with rows and columns. It uses
SQL (Structured Query Language) to manage and query data.
● Features:
○ Structured Data: Data is stored in tables, where each table has a specific schema.
○ Relationships: Tables can relate to each other via primary keys and foreign
keys.
○ ACID Compliance: Ensures atomicity, consistency, isolation, and durability of
transactions.
○ Data Integrity: Enforces constraints like unique, not null, and foreign key
relationships.

MySQL as an RDBMS:

● Lightweight: Easy to set up and use for small to large-scale applications.

● Open Source: Free to use, with enterprise versions available for advanced features.
● Popular Use Cases: Web applications, CMSs like WordPress, e-commerce platforms.

2. Normalization:

Normalization is a database design process to reduce redundancy and improve data integrity by
organizing data into smaller, related tables.

● Normalization Levels:
○ 1NF (First Normal Form): Eliminate repeating groups by ensuring each column
contains atomic (indivisible) values.
○ 2NF (Second Normal Form): Remove partial dependencies by ensuring all
non-key attributes are fully functionally dependent on the primary key.
 ○ 3NF (Third Normal Form): Remove transitive dependencies (non-key attributes
depending on other non-key attributes).
○ BCNF (Boyce-Codd Normal Form): A stricter version of 3NF, ensuring no
anomalies arise from functional dependencies.
● Example of Normalization:

Unnormalized Table:
OrderID | CustomerName | ProductName | Quantity
------------------------------------------------
1 | John Doe | Widget A | 10
1 | John Doe | Widget B | 5

Normalized Structure:

Orders Table:

OrderID | CustomerName
----------------------
1 | John Doe
OrderDetails Table:
OrderID | ProductName | Quantity
---------------------------------
1 | Widget A | 10
1 | Widget B | 5
3. Database Models:

Database models describe how data is structured, stored, and retrieved. Key models include:

● Relational Model: Organizes data into tables (used by RDBMS like MySQL).
● Hierarchical Model: Data is stored in a tree-like structure (used in XML databases).
● Network Model: Data is organized in a graph structure (used in legacy systems).
● Document Model: Semi-structured data is stored as documents (e.g., JSON, XML).
MySQL Database Model:

● MySQL follows the relational model, using tables, rows, and columns.
● Relationships are defined via primary keys (unique identifiers) and foreign keys
(references to other tables).

4. Database Structures:

Database structure refers to the physical and logical organization of data.

● Logical Structure:
○ Tables: Core data storage units.
○ Views: Virtual tables based on SQL queries.
○ Indexes: Improve query performance.
○ Stored Procedures: Encapsulate reusable SQL logic.
○ Triggers: Automate actions based on events.
● Physical Structure:
○ Data is stored in files on disk.
○ MySQL storage engines (e.g., InnoDB, MyISAM) manage how data is stored and
retrieved.
■ InnoDB: Supports transactions, foreign keys, and row-level locking.
■ MyISAM: Optimized for read-heavy workloads, but lacks ACID
compliance.

Examples with MySQL:

1. Creating a Database:

CREATE DATABASE SchoolDB;

USE SchoolDB;

2. Creating a Table:

CREATE TABLE Students (

 StudentID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Major VARCHAR(50)
);

3. Inserting Data:

INSERT INTO Students (Name, Age, Major)

VALUES ('Alice', 20, 'Computer Science');

4. Querying Data:

SELECT * FROM Students WHERE Major = 'Computer Science';

5. Joining Tables:

SELECT [Link], [Link]

FROM Orders
INNER JOIN Customers ON [Link] =
[Link];

Best Practices for MySQL RDBMS:

1. Normalize, but not excessively: Avoid over-normalization as it can impact query

performance.
2. Use Indexes: Optimize query performance but balance against write performance.
3. Backup Regularly: Use mysqldump or other tools for regular backups.
4. Monitor Performance: Use tools like EXPLAIN and SHOW STATUS to analyze
queries.
2. Table, View, Index, Constraints, Data Types,Operators

1. Tables

● Definition: A table is the fundamental storage structure in MySQL, consisting of rows

(records) and columns (fields).

Syntax to Create a Table:

CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
Example:
CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Position VARCHAR(50),
Salary DECIMAL(10, 2) CHECK (Salary > 0),
DepartmentID INT
);
2. Views

● Definition: A view is a virtual table based on a SQL query. It does not store data itself
but presents data dynamically from the underlying tables.
● Advantages:
○ Simplifies complex queries.
○ Enhances security by limiting access to specific columns or rows.

Syntax to Create a View:

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;
Example:
CREATE VIEW HighEarners AS
SELECT Name, Position, Salary
FROM Employees
WHERE Salary > 50000;

3. Indexes

● Definition: An index is a performance optimization feature that speeds up data retrieval.

● Types of Indexes in MySQL:
○ Primary Key Index: Automatically created when a primary key is defined.
○ Unique Index: Ensures all values in a column are unique.
○ Full-Text Index: Supports full-text searches in CHAR, VARCHAR, and TEXT
columns.
○ Composite Index: Indexes multiple columns.

Syntax to Create an Index:

CREATE INDEX index_name ON table_name (column1, column2);
Example:
CREATE INDEX idx_salary ON Employees (Salary);

4. Constraints

● Definition: Constraints enforce rules at the table and column level to ensure data
integrity.
● Types of Constraints in MySQL:
○ PRIMARY KEY: Uniquely identifies each record.
○ FOREIGN KEY: Enforces referential integrity between two tables.
 ○ NOT NULL: Ensures a column cannot store NULL values.
○ UNIQUE: Ensures all values in a column are unique.
○ CHECK: Ensures a column satisfies a specific condition.
○ DEFAULT: Assigns a default value to a column if none is provided.

Example:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50) UNIQUE NOT NULL,
ManagerID INT,
CONSTRAINT fk_manager FOREIGN KEY (ManagerID) REFERENCES
Employees(EmployeeID)
);

5. Data Types

● Categories of Data Types in MySQL:

○ Numeric:
■ INT, TINYINT, SMALLINT, BIGINT
■ DECIMAL(m, d), FLOAT, DOUBLE
○ String:
■ CHAR(n), VARCHAR(n)
■ TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT
■ BLOB (Binary Large Object)
○ Date and Time:
■ DATE, DATETIME, TIMESTAMP, TIME, YEAR

Examples:
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
 Name VARCHAR(100),
Price DECIMAL(10, 2),
ManufactureDate DATE
);

6. Operators

Operators perform operations on data. MySQL supports various categories of operators:

● Arithmetic Operators:
○ +, -, *, /, %

Example:
SELECT Price * Quantity AS TotalCost FROM Orders;

● Comparison Operators:
○ =, !=, <>, <, <=, >, >=

Example:
SELECT * FROM Employees WHERE Salary > 50000;

● Logical Operators:
○ AND, OR, NOT

Example:

SELECT * FROM Employees WHERE Position = 'Manager' AND Salary >

70000;

● Bitwise Operators:
○ &, |, ^, ~, <<, >>
Example:
SELECT 5 & 3 AS BitwiseAnd;

● String Operators:
○ CONCAT(), LIKE, INSTR()

Example:
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM
Employees;

● Set Operators:
○ UNION, UNION ALL, INTERSECT, EXCEPT

Example:
SELECT Name FROM Employees WHERE Position = 'Manager'
UNION
SELECT Name FROM Employees WHERE Salary > 80000;
Putting It All Together

Here’s an example to demonstrate the concepts:

-- Create the Employees table

CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Position VARCHAR(50),
Salary DECIMAL(10, 2) CHECK (Salary > 0),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES
Departments(DepartmentID)
);
-- Create a view to display high earners
CREATE VIEW HighEarners AS
SELECT Name, Position, Salary
FROM Employees
WHERE Salary > 75000;

-- Add an index on the Salary column

CREATE INDEX idx_salary ON Employees (Salary);

-- Query the view

SELECT * FROM HighEarners;

3. Data Definition Languages (DDL)

Data Definition Language (DDL) is a set of SQL commands used to define, modify, and
manage the structure of database objects such as tables, views, indexes, and schemas. DDL
statements do not manipulate the data within the database; they focus on the schema and
structural aspects.

Key DDL Commands in MySQL

Here’s a breakdown of the most commonly used DDL commands with examples:

1. CREATE

The CREATE statement is used to create new database objects, such as databases, tables, views,
or indexes.

Create a Database:
CREATE DATABASE SchoolDB;
Create a Table:
CREATE TABLE Students (
StudentID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT,
Major VARCHAR(50)
);
Create a View:
CREATE VIEW CS_Students AS
SELECT Name, Major
FROM Students
WHERE Major = 'Computer Science';
Create an Index:
CREATE INDEX idx_major ON Students (Major);

2. ALTER

The ALTER statement is used to modify the structure of an existing table or other database
objects.

Add a New Column:

ALTER TABLE Students ADD Email VARCHAR(100);
Modify an Existing Column:
ALTER TABLE Students MODIFY Age TINYINT;
Rename a Column:
ALTER TABLE Students CHANGE Name FullName VARCHAR(150);
Rename a Table:
RENAME TABLE Students TO UniversityStudents;
Drop a Column:
ALTER TABLE Students DROP COLUMN Email;
3. DROP

The DROP statement is used to delete database objects permanently, including databases, tables,
views, or indexes.

Drop a Database:
DROP DATABASE SchoolDB;
Drop a Table:
DROP TABLE Students;
Drop a View:
DROP VIEW CS_Students;
Drop an Index:
DROP INDEX idx_major ON Students;

4. TRUNCATE

The TRUNCATE statement is used to delete all rows from a table while retaining its structure.
This is faster than DELETE since it does not log each row deletion.

Example:
TRUNCATE TABLE Students;
5. RENAME

The RENAME statement allows you to rename a database or table.

Rename a Table:
RENAME TABLE Students TO Alumni;

DDL Command Characteristics

1. Auto-Commit: DDL commands automatically commit the changes, making them

permanent.
 2. Schema-Level Changes: DDL commands alter the database schema rather than
manipulating the data.
3. Non-Reversible: Many DDL operations, such as DROP, are non-reversible.

Examples Demonstrating DDL Commands

Create a Database and Table:

CREATE DATABASE LibraryDB;

USE LibraryDB;

CREATE TABLE Books (

BookID INT AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Author VARCHAR(100),
PublishedYear YEAR,
Genre VARCHAR(50)
);

Alter the Table:

-- Add a new column for ISBN

ALTER TABLE Books ADD ISBN VARCHAR(20);

-- Modify the Genre column to hold longer text

ALTER TABLE Books MODIFY Genre VARCHAR(100);

-- Rename the column Title to BookTitle

ALTER TABLE Books CHANGE Title BookTitle VARCHAR(200);
Truncate and Drop:

-- Remove all records from the Books table

TRUNCATE TABLE Books;

-- Permanently delete the Books table

DROP TABLE Books;

-- Drop the entire LibraryDB database

DROP DATABASE LibraryDB;

Best Practices for DDL in MySQL

1. Plan Before Altering: Changes to schema should be carefully planned as they may
impact the application using the database.
2. Backup Before Dropping: Always create a backup before using DROP or TRUNCATE
commands.
3. Use Constraints: Enforce integrity by using constraints (e.g., NOT NULL, UNIQUE,
FOREIGN KEY).
4. Avoid Frequent Alterations: Repeatedly altering tables can degrade performance.

4. Data Manipulation Language (DML)

Data Manipulation Language (DML) commands are used to interact with the data stored in a
database. They allow you to retrieve, insert, update, and delete data within database tables.

Key DML Commands in MySQL

Here’s an overview of the DML commands:

1. INSERT
The INSERT statement is used to add new rows to a table.

Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1,
value2, ...);
Examples: Insert a Single Row:
INSERT INTO Employees (Name, Position, Salary)
VALUES ('John Doe', 'Manager', 75000);
Insert Multiple Rows:
INSERT INTO Employees (Name, Position, Salary)
VALUES
('Alice Smith', 'Developer', 60000),
('Bob Brown', 'Analyst', 55000);

2. SELECT

The SELECT statement is used to retrieve data from one or more tables.

Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Examples: Retrieve All Columns:
SELECT * FROM Employees;
Retrieve Specific Columns:
SELECT Name, Salary FROM Employees;
Filter Rows:
SELECT Name, Salary
FROM Employees
WHERE Salary > 60000;
Order Results:
SELECT Name, Salary
FROM Employees
ORDER BY Salary DESC;

3. UPDATE

The UPDATE statement is used to modify existing data in a table.

Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Examples: Update a Single Row:
UPDATE Employees
SET Salary = 80000
WHERE Name = 'John Doe';
Update Multiple Rows:
UPDATE Employees
SET Salary = Salary + 5000
WHERE Position = 'Developer';

4. DELETE

The DELETE statement is used to remove rows from a table.

Syntax:
DELETE FROM table_name WHERE condition;
Examples: Delete a Specific Row:
DELETE FROM Employees
WHERE Name = 'Bob Brown';
Delete Multiple Rows:
DELETE FROM Employees
WHERE Salary < 50000;
Delete All Rows (Keep Table Structure):
DELETE FROM Employees;

Additional Features in DML

Transactions

Transactions ensure that a series of DML operations are treated as a single unit of work. They
maintain data integrity and consistency.

● Key Commands:
○ START TRANSACTION: Begins a transaction.
○ COMMIT: Saves changes made in the transaction.
○ ROLLBACK: Reverts changes made in the transaction.

Example:
START TRANSACTION;

UPDATE Employees SET Salary = Salary + 10000 WHERE Position =

'Manager';
DELETE FROM Employees WHERE Salary < 40000;

-- If no issues, commit the changes

COMMIT;

-- If there's an issue, roll back changes

-- ROLLBACK;
Using JOINs in SELECT

To fetch data from multiple tables:

Example:
SELECT [Link], [Link]
FROM Employees
INNER JOIN Departments
ON [Link] = [Link];

Using Subqueries

Subqueries allow you to use the result of one query in another.

Example:
SELECT Name
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Best Practices for DML Commands

1. Use Transactions for Critical Updates: To ensure data consistency, wrap critical DML
commands in transactions.
2. Test with SELECT Before Running Updates/Deletes:

Example:
-- Test
SELECT * FROM Employees WHERE Salary < 50000;

-- Then execute
DELETE FROM Employees WHERE Salary < 50000;

○
 3. Avoid Omitting WHERE Clause: Ensure WHERE conditions are used with UPDATE and
DELETE to avoid unintentional changes.
4. Use Batching for Large Data Loads: For inserting/updating a large volume of data,
process in batches to avoid performance issues.

5. Queries & Subqueries

Queries in MySQL are commands that interact with the database to retrieve, insert, update, or
delete data. A subquery is a query nested inside another query, used to perform complex
filtering, comparisons, or calculations.

Types of Queries

1. Simple Queries: Basic SELECT queries for retrieving data.

2. Compound Queries: Use of clauses like WHERE, GROUP BY, ORDER BY, and
HAVING.
3. Join Queries: Combine data from multiple tables.
4. Subqueries: Queries within another query for dynamic filtering or calculations.

Subqueries in MySQL

A subquery is enclosed in parentheses and can be used in various clauses of a SELECT,

INSERT, UPDATE, or DELETE statement.

1. Types of Subqueries

1. Single-Row Subquery: Returns a single value (e.g., scalar result).

2. Multi-Row Subquery: Returns multiple rows.
3. Multi-Column Subquery: Returns multiple columns.

Basic Syntax of Subqueries

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column FROM another_table
WHERE condition);

Examples of Queries and Subqueries

1. Simple Subquery in WHERE Clause

Filter rows based on a value obtained from another query.

Example:
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

○ Finds employees earning more than the average salary.

2. Subquery in FROM Clause (Derived Table)

Use a subquery as a temporary table.

Example:
SELECT Department, AVG(Salary) AS AvgSalary
FROM (SELECT Department, Salary FROM Employees WHERE Salary >
50000) AS HighEarners
GROUP BY Department;

○ Filters high earners and calculates average salary by department.

3. Subquery in SELECT Clause

Use a subquery to calculate a derived value for each row.

Example:
SELECT Name,
(SELECT DepartmentName FROM Departments WHERE
[Link] = [Link]) AS Department
FROM Employees;

○ Retrieves each employee's name and their department.

4. Subquery with IN Clause

Check for values in a list returned by a subquery.

Example:
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments
WHERE Location = 'New York');

●
○ Finds employees working in New York-based departments.

5. Subquery with EXISTS Clause

Check if a subquery returns any rows.

Example:
SELECT Name
FROM Employees
WHERE EXISTS (SELECT 1 FROM Departments WHERE
[Link] = [Link] AND Location =
'London');
 ○ Finds employees in departments located in London.

6. Correlated Subquery

A subquery that refers to columns from the outer query.

Example:
SELECT Name, Salary
FROM Employees e1
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE
[Link] = [Link]);

○ Finds employees earning more than the average salary in their department.

7. Subquery with Aggregate Functions

Use aggregate functions like SUM, AVG, MAX, MIN in subqueries.

Example:
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > (SELECT SUM(Salary) FROM Employees WHERE
Department = 'HR');

○ Finds departments where the total salary exceeds the HR department’s total salary.

8. Subquery in UPDATE Statement

Update records based on a condition from a subquery.

Example:
UPDATE Employees
SET Salary = Salary * 1.10
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE
DepartmentName = 'Sales');

●
○ Gives a 10% salary raise to employees in the Sales department.

9. Subquery in DELETE Statement

Delete rows based on a subquery.

Example:
DELETE FROM Employees
WHERE DepartmentID NOT IN (SELECT DepartmentID FROM
Departments);

●
○ Deletes employees whose department no longer exists.

Key Differences Between Subqueries and Joins

Feature Subquery Join

Purpose Nested query for filtering or Combine rows from multiple

calculations. tables.

Performance Can be slower for large datasets. Usually faster with proper indexes.

Readability Easier to read for simple use cases. More complex but better for
multi-table ops.

Usage Filters, derived columns, Combining related tables or data

scalar/aggregate results. sources.

Best Practices for Subqueries

1. Use Indexes: Ensure columns in subqueries are indexed to improve performance.

 2. Avoid Deep Nesting: Excessive nesting of subqueries can reduce readability and
performance.
3. Consider Joins: For large datasets, replace subqueries with joins when possible.
4. Test and Optimize: Use EXPLAIN to analyze and optimize query execution plans.

6. Joins

In MySQL, joins are used to combine rows from two or more tables based on related columns.
Joins are one of the most powerful and frequently used features of SQL, enabling you to retrieve
data spread across multiple tables in a relational database.

Types of Joins in MySQL

1. INNER JOIN
2. LEFT JOIN (LEFT OUTER JOIN)
3. RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL JOIN (FULL OUTER JOIN) - Not directly supported in MySQL but can be
simulated.
5. CROSS JOIN
6. SELF JOIN

1. INNER JOIN

● Combines rows from two tables when there is a match in the specified columns.

Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];
Example:
SELECT [Link], [Link]
FROM Employees
INNER JOIN Departments
ON [Link] = [Link];

○ Retrieves employees and their department names for rows where there is a
matching DepartmentID.

2. LEFT JOIN (LEFT OUTER JOIN)

● Returns all rows from the left table, and the matched rows from the right table. If no
match exists, NULL is returned for columns from the right table.

Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON [Link] = [Link];
Example:
SELECT [Link], [Link]
FROM Employees
LEFT JOIN Departments
ON [Link] = [Link];

○ Retrieves all employees, including those without a department.

3. RIGHT JOIN (RIGHT OUTER JOIN)

● Returns all rows from the right table, and the matched rows from the left table. If no
match exists, NULL is returned for columns from the left table.

Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON [Link] = [Link];
Example:
SELECT [Link], [Link]
FROM Employees
RIGHT JOIN Departments
ON [Link] = [Link];

○ Retrieves all departments, including those without employees.

4. FULL JOIN (FULL OUTER JOIN)

● Combines results of both LEFT JOIN and RIGHT JOIN. Rows with no match in either
table will have NULL in columns from the other table.
● Note: MySQL does not support FULL JOIN directly but it can be simulated using
UNION.

Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON [Link] = [Link]
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON [Link] = [Link];
Example:
SELECT [Link], [Link]
FROM Employees
LEFT JOIN Departments
ON [Link] = [Link]
UNION
SELECT [Link], [Link]
FROM Employees
RIGHT JOIN Departments
ON [Link] = [Link];

5. CROSS JOIN

● Returns the Cartesian product of two tables. Every row from the first table is combined
with every row from the second table.

Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;
Example:
SELECT [Link], [Link]
FROM Employees
CROSS JOIN Departments;

○ Retrieves every possible combination of employees and departments.

6. SELF JOIN

● A table is joined with itself to compare rows within the same table.

Syntax:
SELECT A.column1, B.column2
FROM table_name A, table_name B
WHERE condition;
Example:
SELECT [Link] AS Employee1, [Link] AS Employee2
FROM Employees E1
INNER JOIN Employees E2
ON [Link] = [Link];

○ Retrieves employees and their managers from the same Employees table.

Examples of Join Queries

Inner Join with Aggregate Functions

SELECT [Link], COUNT([Link])

AS EmployeeCount
FROM Departments
INNER JOIN Employees
ON [Link] = [Link]
GROUP BY [Link];

● Counts the number of employees in each department.

Left Join to Find Missing Matches

SELECT [Link], [Link]
FROM Employees
LEFT JOIN Departments
ON [Link] = [Link]
WHERE [Link] IS NULL;

● Finds employees not assigned to any department.

Cross Join with a Filter
SELECT [Link], [Link]
FROM Employees E
CROSS JOIN Departments D
WHERE [Link] = 'HR';

● Retrieves all employees paired with the HR department.

Join Performance Optimization Tips

1. Index Key Columns: Ensure the columns used in ON or WHERE conditions are indexed.
2. Minimize Data in Joins: Use only the columns you need.
3. Use Appropriate Joins: Avoid unnecessary joins; use the most efficient join type for
your query.
4. Analyze Execution Plans: Use EXPLAIN to understand how MySQL processes your
join query.
5. Filter Early: Use WHERE or ON clauses to filter data before performing the join.

7. MySQL Connector Python

MySQL Connector is a Python library used to interact with MySQL databases. It allows Python
programs to connect to a MySQL database, execute SQL queries, and retrieve data.

The mysql-connector-python library is the official connector provided by Oracle. You

can install it using pip and use it to perform common database operations in Python.

1. Installation

To install the MySQL Connector for Python, you can use pip:

pip install mysql-connector-python

2. Connecting to MySQL Database
To establish a connection with a MySQL database, you need to use the connect() function
from the [Link] module.

Syntax:

import [Link]

db_connection = [Link](
host="hostname", # e.g., 'localhost'
user="username", # MySQL username
password="password", # MySQL password
database="database_name" # The database you want to connect
to
)

● Example:

import [Link]

# Establishing a connection to the database

db_connection = [Link](
host="localhost",
user="root",
password="your_password",
database="employees"
)

# Creating a cursor object to interact with the database

cursor = db_connection.cursor()
# Closing the connection
db_connection.close()

3. Executing SQL Queries

Once the connection is established, you can use a cursor object to execute SQL queries. The
cursor is responsible for executing queries and retrieving results.

Example - Executing a SELECT Query:

import [Link]

# Establishing a connection
db_connection = [Link](
host="localhost",
user="root",
password="your_password",
database="employees"
)

# Creating a cursor object

cursor = db_connection.cursor()

# Executing a SELECT query

[Link]("SELECT Name, Position FROM Employees WHERE
Salary > 50000")

# Fetching results
results = [Link]()
for row in results:
print(row)

# Closing the cursor and connection

[Link]()
db_connection.close()

Example - Executing an INSERT Query:

import [Link]

# Establishing a connection
db_connection = [Link](
host="localhost",
user="root",
password="your_password",
database="employees"
)

# Creating a cursor object

cursor = db_connection.cursor()

# Executing an INSERT query

[Link]("INSERT INTO Employees (Name, Position, Salary)
VALUES ('John Doe', 'Manager', 75000)")

# Committing the transaction

db_connection.commit()
print("Record inserted successfully!")

# Closing the cursor and connection

[Link]()
db_connection.close()

4. Fetching Results from Queries

You can fetch results in the following ways:

1. fetchall() – Fetches all rows of a query result.

2. fetchone() – Fetches the next row of a query result.
3. fetchmany(size) – Fetches a specific number of rows.

Example - Fetching All Results:

[Link]("SELECT * FROM Employees")

rows = [Link]()
for row in rows:
print(row)

Example - Fetching One Result:

[Link]("SELECT Name, Position FROM Employees WHERE

EmployeeID = 1")
row = [Link]()
print(row)

5. Using Parameters in Queries

You can use placeholders (%s) to prevent SQL injection and pass parameters dynamically to
queries.

Example - Executing a Query with Parameters:

import [Link]

# Establishing a connection
db_connection = [Link](
host="localhost",
user="root",
password="your_password",
database="employees"
)

# Creating a cursor object

cursor = db_connection.cursor()

# Executing a SELECT query with parameters

[Link]("SELECT Name, Position FROM Employees WHERE
Salary > %s", (50000,))

# Fetching results
results = [Link]()
for row in results:
print(row)

# Closing the cursor and connection

[Link]()
db_connection.close()

6. Committing Changes (INSERT, UPDATE, DELETE)

When you perform insert, update, or delete operations, you need to commit the transaction to
save the changes to the database.

db_connection.commit()

If you want to roll back any changes that haven't been committed yet, you can use:

db_connection.rollback()

7. Handling Errors and Exceptions

It is important to handle exceptions that may arise during database operations. You can use
try-except blocks to catch errors such as connection issues or query syntax errors.

Example - Error Handling:

import [Link]
from [Link] import Error

try:
db_connection = [Link](
host="localhost",
user="root",
password="your_password",
database="employees"
)

cursor = db_connection.cursor()
 [Link]("SELECT * FROM NonExistentTable")

# Fetch results if query executes successfully

results = [Link]()
for row in results:
print(row)

except Error as e:
print(f"Error: {e}")

finally:
if db_connection.is_connected():
[Link]()
db_connection.close()

8. Closing the Connection

Always close the connection and cursor when done to free up resources.

[Link]()
db_connection.close()

Useful Tips

● Connection Pooling: If you're working with multiple concurrent connections, consider

using connection pooling to improve performance.
● EXPLAIN: Use EXPLAIN to analyze the performance of SQL queries.
 ● Use Transactions: For multiple related database operations, ensure to commit the
transaction to maintain consistency.

8. Install & Import MySQL connector module

To use MySQL Connector in Python, you need to first install the connector module and then
import it into your Python script. Here’s how you can do it:

1. Install MySQL Connector Python

To install the MySQL Connector module, you can use the pip package manager.

Open your command-line terminal or command prompt and run the following command:

pip install mysql-connector-python

This will install the MySQL Connector Python library.

2. Import MySQL Connector in Python

Once the library is installed, you can import it into your Python script to start interacting with
MySQL databases.

Here’s how you can import the connector:

import [Link]

Now, you can use the [Link] module to establish connections, execute SQL
queries, and fetch results.

Full Example - Installation and Import

1. Install MySQL Connector:

Run the command in the terminal:
pip install mysql-connector-python

2. Import the Module:

In your Python script:

import [Link]

Verifying Installation

To verify that the connector is correctly installed, you can try to connect to a MySQL database
and execute a simple query. Here's a quick example:

import [Link]

# Attempting to connect to the MySQL database

db_connection = [Link](
host="localhost", # Replace with your host
user="root", # Replace with your MySQL username
password="your_password", # Replace with your MySQL password
database="test_db" # Replace with your database name
)

# Checking if the connection was successful

if db_connection.is_connected():
print("Connection established successfully!")
else:
print("Failed to connect.")

# Closing the connection

db_connection.close()

This code will confirm if the connector is installed and working correctly by attempting to
connect to the database.

9. Use the connect(), cursor(), execute() methods; Extract result using

fetchall()

Here’s a complete example of how to use the connect(), cursor(), execute(), and
fetchall() methods to interact with a MySQL database using the MySQL Connector for
Python.

Steps:

1. Connect to the MySQL Database using connect().

2. Create a Cursor Object with cursor().
3. Execute SQL Queries using execute().
4. Fetch Results using fetchall().

Example:

import [Link]

# Step 1: Establish a connection to the MySQL database

db_connection = [Link](
host="localhost", # Replace with your MySQL host
user="root", # Replace with your MySQL username
password="your_password", # Replace with your MySQL password
database="test_db" # Replace with your database name
)
# Step 2: Create a cursor object to interact with the database
cursor = db_connection.cursor()

# Step 3: Execute an SQL query to fetch data

[Link]("SELECT * FROM Employees") # Replace "Employees"
with your table name

# Step 4: Extract the result using fetchall()

results = [Link]()

# Step 5: Loop through the results and print each row

for row in results:
print(row)

# Step 6: Close the cursor and database connection

[Link]()
db_connection.close()

Explanation of Steps:

1. connect(): This method establishes a connection to your MySQL database with the
provided credentials and database name.
2. cursor(): The cursor object is used to execute SQL queries.
3. execute(): Executes the provided SQL query. In this example, it selects all rows from
the Employees table.
4. fetchall(): Retrieves all rows of the query result and returns them as a list of tuples.
Each tuple represents a row of data.
 5. Loop through results: Iterate through the result set and print each row.
6. Closing resources: Always close the cursor and database connection when done.

Notes:

● You can replace the SQL query with any valid SELECT query based on your database
structure.
● fetchall() retrieves all rows returned by the query, but if you're expecting large
result sets, consider using fetchone() (for single rows) or fetchmany(n) (for
multiple rows).

10. Other Ways to Connect Python and MySQL

In addition to using MySQL Connector for Python (mysql-connector-python), there are

several other libraries and approaches to connect Python with MySQL databases. Below are
some popular alternatives:

1. MySQLdb (MySQL-python)

MySQLdb is one of the oldest and most commonly used libraries to connect Python with
MySQL. It is part of the MySQL-python package, but it may require additional installation
steps.

Installation:

pip install mysqlclient

Example:

import MySQLdb

# Establishing a connection
db_connection = [Link](
host="localhost",
user="root",
passwd="your_password",
db="test_db"
)

# Creating a cursor object

cursor = db_connection.cursor()

# Executing a query
[Link]("SELECT * FROM Employees")

# Fetching all results

results = [Link]()
for row in results:
print(row)

# Closing the cursor and connection

[Link]()
db_connection.close()

2. PyMySQL

PyMySQL is a pure-Python MySQL client, which is simpler to install and use, and works
similarly to mysql-connector-python.

Installation:
pip install PyMySQL

Example:

import pymysql

# Establishing a connection
db_connection = [Link](
host="localhost",
user="root",
password="your_password",
database="test_db"
)

# Creating a cursor object

cursor = db_connection.cursor()

# Executing a query
[Link]("SELECT * FROM Employees")

# Fetching all results

results = [Link]()
for row in results:
print(row)

# Closing the cursor and connection

[Link]()
db_connection.close()
3. SQLAlchemy

SQLAlchemy is an Object Relational Mapper (ORM) that provides a higher-level interface to

interact with databases. It supports MySQL, SQLite, PostgreSQL, and other databases. It can be
used either for raw SQL queries or with ORM-based models.

Installation:

pip install sqlalchemy

For MySQL support, you need to install the MySQL driver (like PyMySQL or mysqlclient):

pip install PyMySQL

Example (Using Raw SQL with SQLAlchemy):

from sqlalchemy import create_engine

# Create an engine that connects to the MySQL database

engine =
create_engine('mysql+pymysql://root:your_password@localhost/test
_db')

# Establishing a connection
db_connection = [Link]()
# Executing a query
result = db_connection.execute("SELECT * FROM Employees")

# Fetching all results

for row in result:
 print(row)

# Closing the connection

db_connection.close()

4. Flask-SQLAlchemy (For Flask Applications)

If you are building a web application using Flask, Flask-SQLAlchemy is a convenient

extension to integrate SQLAlchemy for ORM-based database management.

Installation:

pip install flask flask-sqlalchemy

Example (Flask Application with SQLAlchemy):

from flask import Flask

from flask_sqlalchemy import SQLAlchemy

# Initialize Flask app and SQLAlchemy

app = Flask(__name__)
[Link]['SQLALCHEMY_DATABASE_URI'] =
'mysql+pymysql://root:your_password@localhost/test_db'
db = SQLAlchemy(app)

# Define a model
class Employee([Link]):
id = [Link]([Link], primary_key=True)
name = [Link]([Link](100))
position = [Link]([Link](100))
# Query the database
@[Link]('/')
def index():
employees = [Link]()
for employee in employees:
print(f'{[Link]}, {[Link]}')
return "Check your console for employee data!"

if __name__ == '__main__':
[Link](debug=True)

5. Tortoise ORM (Async ORM)

For asynchronous applications (using asyncio), you can use Tortoise ORM. It's an
asynchronous ORM that works well with databases, including MySQL.

Installation:

pip install tortoise-orm

Example:

from tortoise import Tortoise, run_async

from [Link] import Model
from tortoise import fields

# Define a model
class Employee(Model):
id = [Link](pk=True)
name = [Link](max_length=255)
 position = [Link](max_length=255)

# Connect to MySQL and execute a query

async def run():
await [Link](
db_url='mysql://root:your_password@localhost/test_db',
modules={'models': ['__main__']}
)
await Tortoise.generate_schemas()

# Fetch data
employees = await [Link]()
for employee in employees:
print(f'{[Link]}, {[Link]}')

await Tortoise.close_connections()

# Run the asynchronous function

run_async(run())

6. Django ORM (For Django Projects)

If you are working with Django, the framework has its own ORM that simplifies MySQL (and
other databases) integration.

Installation:

pip install django

Example (Django Settings for MySQL):

In your Django project’s [Link], set up the MySQL database connection:

DATABASES = {
'default': {
'ENGINE': '[Link]',
'NAME': 'test_db',
'USER': 'root',
'PASSWORD': 'your_password',
'HOST': 'localhost',
'PORT': '3306',
}
}

Then, you can use Django’s ORM to interact with the MySQL database, defining models and
performing queries like:

from [Link] import Employee

# Querying all employees
employees = [Link]()
for employee in employees:
print([Link], [Link])
Choosing the Right Connector/Library

● Simple SQL Queries: mysql-connector-python or PyMySQL are great options

for straightforward use cases.
● Advanced Usage (ORM): Use SQLAlchemy for more advanced features like ORM or
raw SQL queries.
● Flask Applications: Flask-SQLAlchemy works seamlessly with Flask.
● Asynchronous Applications: For async programming, Tortoise ORM is ideal.
● Django Applications: Use Django’s built-in ORM if you're working with Django.