Python MySQL — Full CRUD & Queries

Python MySQL — Get Started

MySQL is a popular relational database. Python connects via mysql-connector-python or PyMySQL.

Install: pip install mysql-connector-python. You need a running MySQL server.

Real-life example: MySQL is a filing room with labeled cabinets — Python is the clerk fetching files.

Create Connection

mysql.connector.connect(host, user, password, database) opens a session.

Use environment variables for passwords — never commit secrets.

Real-life example: Connection is your ID badge — without it, the database door stays locked.

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
)
print(mydb.is_connected())

Create Database

cursor.execute('CREATE DATABASE IF NOT EXISTS knowvora') creates a schema.

mydb.commit() saves DDL changes when autocommit is off.

Real-life example: Creating a database is opening a new warehouse wing for one project's boxes.

import mysql.connector

mydb = mysql.connector.connect(host="localhost", user="root", password="pass")
cur = mydb.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS knowvora")
print("Database ready")

Create Table

CREATE TABLE defines columns and types — INT, VARCHAR, DATE, PRIMARY KEY.

Switch to database with database='knowvora' in connect or USE knowvora.

Real-life example: A table is one spreadsheet tab with fixed column headers.

cur.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    score INT
)
""")

Insert

INSERT INTO adds rows. Parameterized queries (%s) prevent SQL injection.

executemany() bulk-inserts efficiently.

Real-life example: Insert is adding a new row to the attendance register.

sql = "INSERT INTO students (name, score) VALUES (%s, %s)"
cur.execute(sql, ("Riya", 92))
mydb.commit()

Select

SELECT * FROM students fetches rows. fetchall(), fetchone(), fetchmany() read results.

Real-life example: Select is photocopying rows you need from the register.

cur.execute("SELECT name, score FROM students")
for row in cur.fetchall():
    print(row)

Where

WHERE filters rows — score > 80, name = 'Riya'. Combine with AND, OR.

Real-life example: Where is a search filter — only students from Section A.

cur.execute("SELECT * FROM students WHERE score >= %s", (90,))
print(cur.fetchall())

Order By

ORDER BY score DESC sorts results. Default is ascending.

Real-life example: Order By is ranking students by marks on the notice board.

cur.execute("SELECT name, score FROM students ORDER BY score DESC")
print(cur.fetchall())

Update

UPDATE students SET score = %s WHERE id = %s changes existing rows.

Always use WHERE on update — missing WHERE updates every row.

Real-life example: Update is correcting one student's score after re-checking the paper.

cur.execute("UPDATE students SET score = %s WHERE name = %s", (95, "Riya"))
mydb.commit()

Delete

DELETE FROM students WHERE id = %s removes matching rows.

Real-life example: Delete is erasing one line from the register — confirm the right row first.

cur.execute("DELETE FROM students WHERE score < %s", (50,))
mydb.commit()

Drop

DROP TABLE students removes the whole table structure and data.

Real-life example: Drop is shredding an entire spreadsheet tab — not just clearing cells.

cur.execute("DROP TABLE IF EXISTS old_students")

Limit

LIMIT 10 returns only first N rows — pagination with OFFSET.

Real-life example: Limit is reading only the top 10 headlines, not the whole newspaper.

cur.execute("SELECT * FROM students ORDER BY score DESC LIMIT 5")
print(cur.fetchall())

Join

JOIN combines tables on matching keys — INNER JOIN most common.

Real-life example: Join is matching student IDs in one sheet with course names in another.

cur.execute("""
SELECT students.name, courses.title
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON courses.id = enrollments.course_id
""")