Of course! This is a very common and important topic in Python development. Let's break down the differences and usage of MySQL and SQLite, with clear code examples for each.

The Core Difference: Architecture

The most fundamental difference between MySQL and SQLite is their architecture and purpose.

SQLite: The Simple, Self-Contained Database

SQLite is perfect for when you need a database without the overhead of a separate server. It's like storing your data in a highly organized, powerful file.

When to use SQLite:

• Desktop applications (e.g., saving user preferences, app data).
• Mobile applications (it's the standard for iOS and Android).
• Small websites or blogs.
• Prototyping a larger application quickly.
• Data analysis tasks where you need a fast, local SQL database.

Python sqlite3 Example

The great thing about SQLite is that it's built into Python's standard library. No installation needed!

import sqlite3
import os
# --- 1. Connection and Creation ---
# If the database file 'example.db' doesn't exist, it will be created.
# If it exists, it will be connected to.
conn = sqlite3.connect('example.db')
print("Database connection established.")
# A 'cursor' is used to execute SQL commands.
cursor = conn.cursor()
# --- 2. Create a Table ---
# Use 'IF NOT EXISTS' to avoid errors on subsequent runs.
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER
    )
''')
print("Table 'users' created or already exists.")
# --- 3. Insert Data (Using parameterized queries to prevent SQL injection) ---
users_to_insert = [
    ('Alice', 'alice@example.com', 30),
    ('Bob', 'bob@example.com', 25),
    ('Charlie', 'charlie@example.com', 35)
]
cursor.executemany("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", users_to_insert)
print(f"{cursor.rowcount} users inserted.")
# --- 4. Commit the changes to the database ---
# Without conn.commit(), your changes will be lost when the connection closes.
conn.commit()
# --- 5. Query Data ---
print("\n--- All Users ---")
cursor.execute("SELECT * FROM users")
# fetchall() gets all rows from the last executed statement.
all_users = cursor.fetchall()
for user in all_users:
    print(user)
# --- 6. Update Data ---
print("\n--- Updating Bob's age ---")
new_age = 26
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (new_age, 'Bob'))
conn.commit()
print(f"Bob's age updated to {new_age}.")
# --- 7. Query a Single User ---
print("\n--- Fetching Bob's updated data ---")
cursor.execute("SELECT * FROM users WHERE name = ?", ('Bob',))
bob = cursor.fetchone() # fetchone() gets the first row
print(bob)
# --- 8. Delete Data ---
print("\n--- Deleting Charlie ---")
cursor.execute("DELETE FROM users WHERE name = ?", ('Charlie',))
conn.commit()
print("Charlie has been deleted.")
# --- 9. Close the connection ---
# This is crucial to free up resources and ensure data is written to disk.
conn.close()
print("Database connection closed.")
# You can verify the file was created
if os.path.exists('example.db'):
    print("\nDatabase file 'example.db' exists.")

MySQL: The Powerful, Client-Server Database

MySQL is a robust, multi-user database system. It requires a server to be running, and your Python application acts as a client that connects to it.

When to use MySQL:

• Web applications (e.g., built with Django, Flask, Ruby on Rails).
• Applications requiring high levels of concurrency (many users at once).
• Applications where data needs to be accessed by multiple, separate services.
• Large-scale applications with complex data relationships.

Setup for MySQL

1. Install MySQL Server: You need to have a MySQL server running on your machine or a remote server. You can download it from the official MySQL website.

2. Install a Python Driver: You need a library to connect Python to MySQL. The most popular one is mysql-connector-python.

   pip install mysql-connector-python

Python mysql-connector-python Example

import mysql.connector
from mysql.connector import Error
# --- 1. Connection and Creation ---
# Note: You need to have a MySQL server running and a user created.
# Replace with your actual database credentials.
try:
    # This will connect to the MySQL server. The database 'python_db' will be
    # created if it doesn't exist when we use 'CREATE DATABASE'.
    conn = mysql.connector.connect(
        host='localhost',
        user='your_mysql_user',      # Replace with your MySQL username
        password='your_mysql_password' # Replace with your MySQL password
    )
    if conn.is_connected():
        print("Successfully connected to MySQL server")
        cursor = conn.cursor()
        # --- 2. Create a Database (if it doesn't exist) ---
        cursor.execute("CREATE DATABASE IF NOT EXISTS python_db")
        print("Database 'python_db' created or already exists.")
        # Switch to the new database
        cursor.execute("USE python_db")
        # --- 3. Create a Table ---
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                price DECIMAL(10, 2) NOT NULL,
                stock INT
            )
        ''')
        print("Table 'products' created or already exists.")
        # --- 4. Insert Data ---
        products_to_insert = [
            ('Laptop', 1200.50, 10),
            ('Mouse', 25.00, 50),
            ('Keyboard', 75.75, 30)
        ]
        # executemany is efficient for inserting multiple rows
        cursor.executemany("INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)", products_to_insert)
        conn.commit() # Commit the transaction
        print(f"{cursor.rowcount} products inserted.")
        # --- 5. Query Data ---
        print("\n--- All Products ---")
        cursor.execute("SELECT * FROM products")
        all_products = cursor.fetchall()
        for product in all_products:
            print(product)
        # --- 6. Update Data ---
        print("\n--- Updating Laptop price ---")
        new_price = 1150.00
        cursor.execute("UPDATE products SET price = %s WHERE name = %s", (new_price, 'Laptop'))
        conn.commit()
        print("Laptop price updated.")
        # --- 7. Query a Single Product ---
        print("\n--- Fetching Laptop's updated data ---")
        cursor.execute("SELECT * FROM products WHERE name = %s", ('Laptop',))
        laptop = cursor.fetchone()
        print(laptop)
        # --- 8. Delete Data ---
        print("\n--- Deleting Mouse ---")
        cursor.execute("DELETE FROM products WHERE name = %s", ('Mouse',))
        conn.commit()
        print("Mouse has been deleted.")
except Error as e:
    print(f"Error while connecting to MySQL: {e}")
finally:
    # --- 9. Close the connection ---
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("\nMySQL connection is closed.")

Summary: Which One Should You Choose?

In short:

• SQLite is for local, self-contained data.
• MySQL is for centralized, multi-user data.