1. What is MySQL?
-
MySQL is an Open Source Relational Database Management System (RDBMS).
-
Data is stored in the form of tables (rows and columns).
-
It uses SQL (Structured Query Language).
2. Why Connect MySQL with Python?
-
Python programs can store and manage large data using MySQL.
-
Useful in:
-
School management systems
-
Banking systems
-
Online forms
-
Result processing
-
3. Python Module Used
-
mysql.connector -
It allows Python to communicate with MySQL server.
Import Statement
import mysql.connector
4. Installing MySQL Connector
pip install mysql-connector-python
5. Steps to Connect MySQL with Python
-
Import module
-
Create connection
-
Create cursor
-
Execute SQL query
-
Fetch data (if required)
-
Commit changes
-
Close connection
6. Creating Connection Object
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="school"
)
Connection Object
-
Represents the connection between Python and MySQL
-
Stored in a variable (e.g.,
mydb)
7. Cursor Object
mycursor = mydb.cursor()
What is Cursor?
-
Cursor is used to execute SQL commands
-
It acts as a pointer to records
8. Important Cursor Functions
8.1- execute ()
Executes single SQL statement.
mycursor.execute("SELECT * FROM student")
8.2- executemany ()
Executes same SQL statement multiple times.
sql = "INSERT INTO student VALUES (%s, %s, %s)"
data = [(1,'Amit',80),(2,'Neha',90)]
mycursor.executemany(sql, data)
8.3- fetchone ()
-
Fetches only one record at a time
-
Returns a tuple
row = mycursor.fetchone()
print(row)
Use: When only one record is needed.
8.4- fetchall ()
-
When Fetches all records
-
When Returns a list of tuples.
rows = mycursor.fetchall()
for r in rows:
print(r)
8.5- fetchmany (n)
- Fetches n number of records.
rows = mycursor.fetchmany(2)
8.5- row count ()
- Returns number of rows affected.
print(mycursor.rowcount)
9. Creating Database
We use following syntax.
mycursor.execute("CREATE DATABASE school")
10. Creating Table
We use following syntax.
mycursor.execute(
"CREATE TABLE student (roll INT, name VARCHAR(30), marks INT)"
)
11. INSERT Operation
mycursor.execute(
"INSERT INTO student VALUES (1,'Amit',85)"
)
mydb.commit()
12. commit ( ) Function
- It saves changes permanently.
- Required after:
-
INSERT
-
UPDATE
-
DELETE
-
-
If commit( ) is not used, data will not be saved
mydb.commit()
13. SELECT Operation
We use following syntax
mycursor.execute("SELECT * FROM student")
data = mycursor.fetchall()
14. WHERE Clause
We use following syntax.
mycursor.execute(
"SELECT * FROM student WHERE marks > 80"
)
15. UPDATE Operation
We use following syntax.
mycursor.execute(
"UPDATE student SET marks = 95 WHERE roll = 1"
)
mydb.commit()
16. DELETE Operation
We use following syntax.
mycursor.execute(
"DELETE FROM student WHERE roll = 3"
)
mydb.commit()
17. Closing Cursor
We use following syntax.
mycursor.close()
18. Closing Connection
We use following syntax.
mydb.close()
Why Close?
-
Releases memory
-
Ends database connection safely
19. Difference between fetchone () and fatchall ()
| fetchone() | fetchall() |
|---|---|
| Returns single record | Returns all records |
| Tuple | List of tuples |
| Less memory | More memory |
20. Common Errors
-
MySQL server not running
-
Wrong username/password
-
Database/table does not exist
-
Missing commit( )
21. Advantages of MySQL with Python
-
Fast data processing
-
Secure database handling
-
Easy integration
-
Used in real-world applications
22. Important Exam Keywords
-
RDBMS
-
Cursor
-
Connection object
-
Commit
-
Fetch functions
-
SQL queries
23. Exam Tip (Very Important)
👉 Always write programs in this order:
Import module
Create connection
Create cursor
Execute query
Fetch / Commit
Close connection
