Day 9: Integrating MySQL with Python – Core Database Operations

In today's session, I worked on integrating MySQL with Python using the mysql-connector-python library. This is a critical skill in backend engineering, automation, and DevOps workflows where database operations often need to be programmatically managed.

This post covers all the essential operations I implemented, including connecting to MySQL, creating databases and tables, inserting and querying data, and handling errors—all through Python code.

---

1. Installing the MySQL Connector

# Install the official MySQL connector for Python
!pip install mysql-connector-python

This package allows Python to connect to and interact with MySQL databases.

---

2. Connecting to MySQL Server

import mysql.connector

# Establish connection to MySQL server
mydb = mysql.connector.connect(
    host="localhost",     # Server hostname
    user="root",           # MySQL username
    password="root"        # MySQL password
)

This connects Python to the MySQL server. A database is not selected at this point.

---

3. Creating a Cursor

# Create a cursor object to execute SQL queries
mycursor = mydb.cursor()

A cursor is required to send SQL statements and retrieve results.

---

4. Showing All Available Databases

mycursor.execute("SHOW DATABASES")
for db in mycursor:
    print(db)

Useful for verifying whether your target database already exists.

---

5. Creating and Dropping Databases

# Create a new database named demo3
mycursor.execute("CREATE DATABASE demo3")

# Drop the same database
mycursor.execute("DROP DATABASE demo3")

Common operations in the early stages of project setup or automation.

---

6. Connecting to a Specific Database

# Now connect to an existing database to run further queries
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="demo1"
)
mycursor = mydb.cursor()

All future operations will apply to the selected database (demo1 in this case).

---

7. Listing All Tables in the Database

mycursor.execute("SHOW TABLES")
for table in mycursor:
    print(table)

Verifies which tables already exist before creating new ones.

---

8. Creating a Table

mycursor.execute('''
    CREATE TABLE student (
        stdid INT PRIMARY KEY,
        name VARCHAR(50),
        age INT,
        city VARCHAR(50)
    )
''')

Creates a student table with four fields. stdid is the primary key to ensure uniqueness.

---

9. Dropping a Table

mycursor.execute("DROP TABLE student")

Deletes the table. Useful for schema redesign or cleaning up test data.

---

10. Inserting Data into a Table

Insert a Single Row

query = "INSERT INTO student (stdid, name, age, city) VALUES (%s, %s, %s, %s)"
val = (101, 'Abhay', 34, 'Pune')

mycursor.execute(query, val)
mydb.commit()  # Required to save changes

Always call .commit() to apply the insert/update/delete operations.

---

Insert Multiple Rows

vals = [
    (102, 'Atul', 23, 'Pune'),
    (103, 'Simran', 32, 'Hyderabad'),
    (104, 'Ronny', 12, 'Bihar')
]

mycursor.executemany(query, vals)
mydb.commit()

executemany() allows bulk insertion and improves performance over multiple execute() calls.

---

11. Reading Data from the Table

Fetch All Rows

mycursor.execute("SELECT * FROM student")
for row in mycursor:
    print(row)

Retrieves all records from the table.

---

Fetch Rows with Conditions

# Select records where age is greater than 30
mycursor.execute("SELECT * FROM student WHERE age > 30")
for row in mycursor:
    print(row)

---

# Select records where city is 'Pune'
mycursor.execute("SELECT * FROM student WHERE city IN ('Pune')")
for row in mycursor:
    print(row)

Filtering helps focus only on relevant data, often required in real applications.

---

12. Handling Duplicate Primary Key Errors

try:
    # Attempt to insert a duplicate primary key
    mycursor.execute(query, (101, 'Duplicate', 22, 'Delhi'))
    mydb.commit()
except mysql.connector.IntegrityError as e:
    print("Duplicate entry:", e)

This avoids script crashes and ensures your code handles database constraints gracefully.

---

13. Committing and Closing the Connection

# Final commit (if needed)
mydb.commit()

# Close cursor and database connection
mycursor.close()
mydb.close()

Always clean up resources to avoid memory leaks or locked connections.

---

Common Errors and Fixes

---

Quick Example: Table Creation and Insert Combined

# Create table safely
mycursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        stdid INT PRIMARY KEY,
        name VARCHAR(50),
        age INT,
        city VARCHAR(50)
    )
''')

# Insert data
query = "INSERT INTO students (stdid, name, age, city) VALUES (%s, %s, %s, %s)"
vals = [
    (201, 'Alice', 22, 'Delhi'),
    (202, 'Bob', 25, 'Mumbai'),
    (203, 'Carol', 28, 'Chennai')
]

mycursor.executemany(query, vals)
mydb.commit()

---

References

• MySQL Connector/Python Documentation

• MySQL SQL Syntax Guide