Python and SQL Connectivity

In the world of programming, data is king. Applications often need to store, retrieve, update, and delete large amounts of information. While simple files can store data, they are not efficient for complex operations. This is where SQL databases come in.

Connecting Python to a SQL database allows your application to interact with a robust, scalable, and persistent data storage system. This guide will walk you through connecting Python to two popular SQL databases: MySQL (a powerful, production-grade database) and SQLite (a lightweight, file-based database built into Python).

Why Connect Python to a SQL Database?

Persistent Storage: Unlike variables that lose their data when a program ends, a database stores information permanently.
Scalability: Databases are designed to handle vast amounts of data and high volumes of traffic efficiently.
Data Integrity: They enforce rules to ensure data is consistent and reliable.
Concurrent Access: Multiple users or processes can access and modify data simultaneously without conflict.
Structured Queries: SQL (Structured Query Language) provides a powerful and standardized way to interact with data.

The Core Components of Database Connectivity

Regardless of the database you choose, the workflow generally involves the same key components:

The Library (Connector): A specific Python library that knows how to communicate with your target database (e.g., mysql-connector-python for MySQL, sqlite3 for SQLite).
The Connection Object: Represents the active connection to the database. You use it to manage transactions and eventually close the link.
The Cursor Object: A control structure that allows you to execute SQL queries. Think of it as a pointer or a cursor in a text editor that moves through the database records. It’s the primary tool for interacting with your data.

Connecting to MySQL

MySQL is one of the world’s most popular open-source relational databases, known for its performance and reliability. It’s a great choice for web applications and large-scale systems.

Step 1: Install the Connector Library

First, you need to install the official driver that allows Python to talk to MySQL.

pip install mysql-connector-python

If you are using a virtual environment, make sure it’s activated before running this command.

Step 2: Establish a Connection

To connect, you need to provide credentials, known as the connection string. This includes the host (server address), username, password, and the specific database you want to use.

import mysql.connector
 
try:
    # Connection parameters
    connection = mysql.connector.connect(
        host="localhost",       # Or your database server's IP address
        user="your_username",   # Replace with your MySQL username
        password="your_password", # Replace with your MySQL password
        database="your_db_name"   # Replace with your database name
    )
    print("Connection to MySQL database successful!")
except mysql.connector.Error as err:
    print(f"Error: {err}")

Always wrap your connection attempt in a try...except block to gracefully handle potential errors like wrong credentials or a down server.

Step 3: Create a Cursor and Execute Queries

Once connected, you create a cursor to run SQL commands. The cursor.execute() method is used to send queries to the database.

A crucial best practice is to use a try...finally block to ensure the connection is always closed, even if errors occur.

import mysql.connector
 
# Database credentials
db_config = {
    "host": "localhost",
    "user": "your_username",
    "password": "your_password",
    "database": "your_db_name"
}
 
try:
    # Establish the connection
    connection = mysql.connector.connect(**db_config)
    cursor = connection.cursor()
 
    # 1. Create a table (if it doesn't already exist)
    create_table_query = """
    CREATE TABLE IF NOT EXISTS employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        salary DECIMAL(10, 2)
    )
    """
    cursor.execute(create_table_query)
    print("Table 'employees' created or already exists.")
 
    # 2. Insert data into the table
    # Using parameterized queries to prevent SQL injection
    insert_query = "INSERT INTO employees (name, salary) VALUES (%s, %s)"
    employee_data = ("Alice Johnson", 75000.00)
    cursor.execute(insert_query, employee_data)
 
    # Commit the transaction to save the changes
    connection.commit()
    print(f"{cursor.rowcount} record inserted successfully.")
 
    # 3. Fetch and display data
    cursor.execute("SELECT id, name, salary FROM employees")
    records = cursor.fetchall() # Fetch all rows
 
    print("\nEmployee Data:")
    for row in records:
        print(f"ID: {row[0]}, Name: {row[1]}, Salary: {row[2]}")
 
except mysql.connector.Error as err:
    print(f"Database Error: {err}")
finally:
    # Ensure the connection is closed
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("\nMySQL connection is closed.")

⚠️

Never use f-strings or string formatting (% or .format()) to insert variable values directly into your SQL queries. This is a major security vulnerability called SQL Injection.

Incorrect (Vulnerable):

# DANGEROUS! DO NOT DO THIS!
user_input = "Alice"
query = f"SELECT * FROM users WHERE name = '{user_input}'"

Correct (Safe): Always use parameterized queries, where the library safely handles the values. The placeholder for MySQL is %s.

# SAFE
user_input = "Alice"
query = "SELECT * FROM users WHERE name = %s"
cursor.execute(query, (user_input,)) # Pass values as a tuple

Connecting to SQLite

SQLite is a serverless, self-contained, and transactional SQL database engine. It’s incredibly lightweight because it reads and writes directly to a single file on your disk. Python has a built-in library, sqlite3, so no installation is needed.

SQLite is perfect for:

Small to medium-sized applications.
Prototyping and development.
Applications that need a portable database.

Step 1: Import the `sqlite3` Library

Since it’s part of the standard library, you just need to import it.

import sqlite3

Step 2: Connect to the Database

Connecting to SQLite is as simple as providing a file path. If the file doesn’t exist, SQLite will create it for you.

# This will create 'mydatabase.db' in the same directory if it doesn't exist
connection = sqlite3.connect("mydatabase.db")

Step 3: Execute Queries and Handle Data

The process is very similar to MySQL, but with a few key differences:

The placeholder for parameterized queries is ? instead of %s.
The with statement can be used to manage connections, automatically committing or rolling back transactions.

import sqlite3
 
db_file = "company.db"
 
try:
    # The 'with' statement handles the connection lifecycle
    with sqlite3.connect(db_file) as connection:
        cursor = connection.cursor()
 
        # 1. Create a table
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            due_date DATE
        )
        """)
        print("Table 'projects' created or already exists.")
 
        # 2. Insert multiple records using executemany()
        projects_to_add = [
            ("Project Alpha", "2025-12-31"),
            ("Project Beta", "2026-06-30"),
            ("Project Gamma", "2026-12-31")
        ]
        # The placeholder for sqlite3 is '?'
        cursor.executemany("INSERT INTO projects (name, due_date) VALUES (?, ?)", projects_to_add)
        print(f"{cursor.rowcount} records inserted.")
 
        # 3. Fetch and display data
        print("\nProjects Data:")
        for row in cursor.execute("SELECT id, name, due_date FROM projects"):
            print(f"ID: {row[0]}, Name: {row[1]}, Due Date: {row[2]}")
 
except sqlite3.Error as err:
    print(f"Database Error: {err}")
finally:
    # The 'with' statement automatically closes the connection,
    # but we can add a message for clarity.
    print("\nSQLite connection process finished.")

MySQL vs. SQLite: Which One to Choose?

You are building a web application or a client-server system.
You need to handle multiple simultaneous users/connections (high concurrency).
You require advanced database features like user management, stored procedures, and complex replication.
Your dataset is expected to grow very large.

Best Practices Summary

Always use parameterized queries to prevent SQL injection.
Use try...except...finally or with statements to ensure your database connections are always closed.
Never hardcode credentials directly in your source code. Use environment variables or a configuration file to store them securely.
Commit your transactions. Changes like INSERT, UPDATE, and DELETE are not saved until you call connection.commit().
Fetch only what you need. Use fetchone() to get a single record or fetchmany(size) for batches to avoid loading huge amounts of data into memory.

Try it Yourself - Practice Problems