❮ Previous Next ❯

Inserting & Fetching Data into SQL

  • Insert & fetch records into MySQL tables using Python SQL commands.

  • INSERT INTO Query (Add Data to Table)

    The INSERT INTO SQL command is used to add records to a table.

    Syntax:

    INSERT INTO table_name (column1, column2, ...)

    VALUES (value1, value2, ...);

Insert One Student Record

This code inserts one student into the students table. 

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# Insert single record
sql = "INSERT INTO students (name, age, course, enrollment_date) VALUES (%s, %s, %s, %s)"
values = ("Radha", 27, "MCA", "2026-01-10")

cursor.execute(sql, values)
conn.commit()  # Save changes

print("1 record inserted successfully")

conn.close()

  • Explanation:

    • cursor.execute(sql, values) → Executes SQL with provided data

    • conn.commit() → Saves the changes permanently

Insert Multiple Student Records

This code inserts many students at once. 

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# Insert multiple records
sql = "INSERT INTO students (name, age, course, enrollment_date) VALUES (%s, %s, %s, %s)"
values = [
    ("Amit", 21, "MCA", "2026-01-11"),
    ("Neha", 23, "BCA", "2026-01-12"),
    ("Ravi", 22, "MCA", "2026-01-13")
]

cursor.executemany(sql, values)  # Insert all records at once
conn.commit()

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

conn.close()

  • Explanation:

    • cursor.executemany(sql, values) → Inserts multiple rows in one go

    • cursor.rowcount → Shows number of rows inserted

    SELECT Query (Fetch Data from Table)

    The SELECT command retrieves data from a table.

    Syntax:

    SELECT column1, column2 FROM table_name;

    SELECT * → fetches all columns

Fetch One Student Record 

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

cursor.execute("SELECT * FROM students")  # Fetch all students

student = cursor.fetchone()  # Fetch only first row
print("First student:", student)

conn.close()

  • Explanation:

    • fetchone() → Returns one row from result

    • If called again, it returns the next row

Fetch All Student Records 

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

cursor.execute("SELECT * FROM students")

all_students = cursor.fetchall()  # Fetch all rows
for student in all_students:
    print(student)

conn.close()

  • Explanation:

    • fetchall() → Returns all rows as a list of tuples

    • Loop through the list to print or process each record

❮ Previous Next ❯