Loading notes...
Loading notes...
Class 12 • Chapter 10
Python uses connectors to link with MySQL, employing a cursor workflow to execute SQL commands and commit changes for persistent data management.
While Python is excellent at data processing and SQL is the gold standard for data storage, real-world applications (like banking systems or social media) require these two to work together. Python-MySQL Connectivity allows a Python program to act as a bridge, sending SQL commands to a database server and receiving results back for processing. This is achieved using a **Database Connector or Driver**—a specialized library that translates Python's instructions into a language the MySQL server understands. This integration enables developers to build dynamic, data-driven applications that keep information persistent and synchronized across multiple user sessions.
Every Python-to-MySQL interaction follows a predictable five-step process: **1. Import** the connector module (import mysql.connector). **2. Connect** to the server using mysql.connector.connect() with the host, user, and password. **3. Create a Cursor** object (db.cursor()) to perform operations. **4. Execute SQL** queries using the cursor.execute() method. **5. Process results** using methods like fetchone() or fetchall() if reading, or **Commit** the changes using db.commit() if writing or updating. Finally, always close both the cursor and the connection to ensure the database remains stable and performant.
import mysql.connector
# Creating the connection object
db = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="SchoolDB"
)
if db.is_connected():
print("Successfully connected to the database!")
cursor = db.cursor()When your application needs to modify the database—such as adding a new user or updating a salary—you use the **cursor.execute()** method with a DML command. However, even if the command is sent successfully, the database will not permanently save the changes until you call the **commit()** method on the main connection object. This is a critical security and reliability feature: if an error occurs mid-process, you can choose not to commit, ensuring the database remains in its last known 'clean' state without corrupted or incomplete data.
sql = "INSERT INTO Student (Roll, Name) VALUES (%s, %s)"
val = (102, "Aarav")
cursor.execute(sql, val)
db.commit() # Mandatory for saving modifications
print(cursor.rowcount, "record(s) inserted.")After executing a SELECT query, the results are stored in the cursor object. You can retrieve them using three primary methods: **fetchone()** returns the first matching row as a tuple, **fetchall()** returns all matching rows as a list of tuples, and **fetchmany(n)** returns exactly 'n' rows. These methods are 'sequential,' meaning that each call to fetchone() moves the virtual pointer to the next record. Once all results are fetched, the cursor becomes empty. For large datasets, processing rows one-by-one is significantly more memory-efficient than loading everything at once with fetchall().
cursor.execute("SELECT * FROM Student")
data = cursor.fetchall()
for record in data:
print(f"Name: {record[1]}, Marks: {record[2]}")