🔗 Databases

Python ↔ SQL Connectivity

पायथन और SQL कनेक्टिविटी

⏱ 3 hr3 topicsInteractive

🎯 By the end: You can outline the steps to connect Python to a MySQL database, run queries from Python, fetch results, and commit changes.

SQL stores and queries data; Python builds applications. Connectivity joins them — your Python program can talk to a MySQL database, run queries and use the results. This is the bridge between the two halves of the course. (These programs need a real MySQL server, so the code here is to study and run on a computer, not in the browser.)

1Connecting: connect() and cursor()

Python connects to MySQL through a connector module — mysql.connector. Two objects do the work:

A connection — the live link to the database, made with connect().
A cursor — the object you use to send SQL and read results, made with connection.cursor().

import mysql.connector

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

cur = con.cursor()    # create a cursor to run queries

Think of the connection as the phone line to the database and the cursor as the conversation you have over it — you send SQL through the cursor and read replies from it.

Key points

Use the mysql.connector module to connect Python to MySQL.
connect(host, user, passwd, database) opens the connection.
connection.cursor() creates a cursor — the object used to run queries and read results.

2Executing queries and fetching results

Run SQL with the cursor's execute() method, then retrieve rows:

Method	Returns
`cur.execute(query)`	Runs the SQL query
`cur.fetchone()`	The next single row (as a tuple), or None
`cur.fetchall()`	All remaining rows (as a list of tuples)
`cur.rowcount`	How many rows the last query affected/returned

cur.execute("SELECT name, marks FROM student")
rows = cur.fetchall()        # list of tuples
for row in rows:
    print(row[0], row[1])    # name, marks

print("Rows:", cur.rowcount)

Key points

cur.execute(query) runs an SQL statement.
fetchone() gets one row; fetchall() gets all rows (as a list of tuples).
cur.rowcount tells how many rows were affected or returned.

3Transactions: commit() for changes

Queries that change data (INSERT, UPDATE, DELETE) are not saved permanently until you commit the transaction. Forgetting commit() is the classic bug — the program runs, but the database is unchanged after it closes.

cur.execute(
    "INSERT INTO student (roll, name, marks) VALUES (7, 'Dev', 90)"
)
con.commit()      # save the change permanently

con.close()       # close the connection when done

A plain SELECT does not need commit() (it reads, it doesn't change). But every INSERT, UPDATE or DELETE must be followed by con.commit() or the change is lost.

Key points

Data-changing queries (INSERT/UPDATE/DELETE) need connection.commit() to be saved permanently.
SELECT does not need commit — it only reads.
Close the connection with connection.close() when finished.

★ Practical: connect and query (run on a computer)

On a machine with MySQL + mysql.connector installed, write a program that:

Connects to a database and creates a cursor.
Runs a SELECT and prints every row using fetchall() and a loop.
Inserts a new student, then calls commit(), and prints rowcount.
Closes the connection at the end.

Ready for the chapter test?

Answer 5 questions. Score 60% or more to mark this chapter complete.

Start the test →

💡 Log in to save your progress and earn the certificate.

← Previous

SQL Functions, Grouping & Joins

→

Back to syllabus