🐬 Python Tutorial — MySQL Database Connectivity

Introduction 🌟

MySQL is one of the most popular relational databases used in web apps, enterprise systems, and modern backends. Python can connect to it using packages like mysql-connector-python or PyMySQL.

Note

💡 Requires MySQL Server installed
💡 Use mysql-connector-python for official Oracle support
💡 Supports CRUD operations, transactions, prepared statements, pooling

1. Installing MySQL Connector 📦

install_mysql.sh

pip install mysql-connector-python

✔ Official MySQL connector

2. Connecting to MySQL Database 🔌

connect_mysql.py

import mysql.connector

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

print("Connected!")

Note

💡 If the database doesn't exist, create it using MySQL CLI or MySQL Workbench.

3. Creating a Cursor 🖱️

cursor.py

cursor = conn.cursor()

✔ Cursor is used to execute SQL queries

4. Creating a Table 🏗️

create_table.py

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
)
""")

✔ VARCHAR, INT, TEXT, FLOAT, etc. are common MySQL types

5. Inserting Data ➕

insert.py

query = "INSERT INTO users (name, age) VALUES (%s, %s)"
data = ("Sathish", 25)

cursor.execute(query, data)
conn.commit()

print("Inserted:", cursor.rowcount)

Note

✔ Use %s placeholders — MySQL handles escaping automatically.

6. Insert Multiple Rows 📦

executemany.py

users = [
    ("Arun", 22),
    ("Kumar", 30),
    ("Priya", 27)
]

cursor.executemany("INSERT INTO users (name, age) VALUES (%s, %s)", users)
conn.commit()

print(cursor.rowcount, "rows inserted")

7. Select Data (Fetching) 🔍

Fetch All

fetch_all.py

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

Fetch One

fetch_one.py

cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
row = cursor.fetchone()
print(row)

Fetch Many

fetch_many.py

cursor.execute("SELECT * FROM users")
rows = cursor.fetchmany(2)
print(rows)

8. Updating Data ✏️

update.py

cursor.execute(
    "UPDATE users SET age = %s WHERE name = %s",
    (26, "Sathish")
)
conn.commit()

9. Deleting Data ❌

delete.py

cursor.execute("DELETE FROM users WHERE id = %s", (1,))
conn.commit()

10. ORDER BY, LIMIT, LIKE Queries 🔎

query_filters.py

cursor.execute("SELECT * FROM users ORDER BY age DESC LIMIT 5")
print(cursor.fetchall())

cursor.execute("SELECT * FROM users WHERE name LIKE 'S%'")
print(cursor.fetchall())

11. Transaction Handling 🔄

transaction.py

try:
    cursor.execute("UPDATE users SET age = age + 1")
    conn.commit()
except:
    conn.rollback()  # revert changes

✔ Critical for financial or secure systems

12. Using Dictionary Cursor 🗂️

dict_cursor.py

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM users")

for row in cursor.fetchall():
    print(row["name"], row["age"])

13. Preventing SQL Injection 🔐

Note

💡 Always use parameterized queries
💡 NEVER insert variables via string concatenation

safe_query.py

query = "SELECT * FROM users WHERE name = %s"
cursor.execute(query, (username,))

14. Using Connection Pooling 🚰

Connection pooling improves performance in production apps.

pooling.py

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host="localhost",
    user="root",
    password="your_password",
    database="testdb"
)

conn = pool.get_connection()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
print(cursor.fetchone())

15. Dropping Tables & Databases ⚠️

drop_table.py

cursor.execute("DROP TABLE IF EXISTS users")

Note

⚠️ Use carefully — irreversible!

16. Closing Connection 🔚

close.py

cursor.close()
conn.close()

17. Full CRUD Example 📝

crud_full.py

import mysql.connector

def connect():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="pass",
        database="crud_demo"
    )

def create_table():
    conn = connect()
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS tasks(id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100))")
    conn.commit()
    conn.close()

def add_task(title):
    conn = connect()
    conn.cursor().execute("INSERT INTO tasks(title) VALUES (%s)", (title,))
    conn.commit()
    conn.close()

def get_tasks():
    conn = connect()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM tasks")
    data = cursor.fetchall()
    conn.close()
    return data

def delete_task(task_id):
    conn = connect()
    conn.cursor().execute("DELETE FROM tasks WHERE id = %s", (task_id,))
    conn.commit()
    conn.close()

# Usage
create_table()
add_task("Learn SQL")
print(get_tasks())

MySQL Cheat Sheet 📘

OperationMySQL Command
Create tableCREATE TABLE ...
InsertINSERT INTO table VALUES ...
SelectSELECT * FROM table
UpdateUPDATE table SET ...
DeleteDELETE FROM table WHERE ...

Best Practices 💡

  • ✔ Always use parameterized queries
  • ✔ Use connection pooling for large apps
  • ✔ Enable dictionary cursor for readability
  • ✔ Keep transactions small and safe
  • ✔ Use indexes on frequently searched columns

Conclusion 🎉

>>“MySQL + Python = a powerful and scalable foundation for modern applications.” ✨

You now fully understand MySQL Database Connectivity in Python! Want the next topic? Try PostgreSQL, SQLAlchemy ORM, REST APIs, or Advanced MySQL Joins. Just tell me! 😊