Python and SQL Connectivity

Connecting Python with SQL databases allows you to interact with data programmatically. This page demonstrates the steps for Python and SQL connectivity, focusing on MySQL (popular for larger systems) and SQLite (built-in).

Connecting to MySQL

For larger databases, MySQL is widely used. Use the mysql-connector-python library for connectivity.

Steps to Connect

1. Install the MySQL connector library:

   pip install mysql-connector-python
2. Import the library and establish a connection.
3. Execute queries using a cursor object.
4. Commit changes and close the connection.

Example: MySQL Connection

import mysql.connector
 
# Connect to MySQL server
connection = mysql.connector.connect(
    host="localhost",
    user="root",         # Replace with your username
    password="password", # Replace with your password
    database="test_db"   # Replace with your database name
)
 
cursor = connection.cursor()
 
# Create a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary FLOAT
)
""")
print("Table created successfully!")
 
# Insert data
cursor.execute("INSERT INTO employees (name, salary) VALUES (%s, %s)", ("John Doe", 50000))
cursor.execute("INSERT INTO employees (name, salary) VALUES (%s, %s)", ("Jane Smith", 60000))
connection.commit()  # Save changes
 
# Fetch data
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
print("Employees in database:", rows)
 
# Close the connection
connection.close()

Connecting to SQLite

SQLite is a lightweight database built into Python. It’s perfect for small applications or learning.

Steps to Connect

1. Import the sqlite3 module.
2. Establish a connection to the database file (.db).
3. Create a cursor object to execute SQL commands.
4. Execute queries and fetch results.
5. Close the connection.

Example: SQLite Connection

import sqlite3
 
# Connect to SQLite database (creates file if it doesn't exist)
connection = sqlite3.connect("example.db")
cursor = connection.cursor()
 
# Create a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")
print("Table created successfully!")
 
# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 30))
connection.commit()  # Save changes
 
# Fetch data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print("Users in database:", rows)
 
# Close the connection
connection.close()

Key Differences Between MySQL and SQLite

Best Practices for Python-SQL Connectivity

1. Use Parameterized Queries: Prevent SQL injection by using placeholders (? for SQLite, %s for MySQL).

   cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
2. Handle Exceptions: Always wrap your database operations in try-except blocks.

   try:
       # Database operations
   except Exception as e:
       print("Error:", e)
3. Close Connections: Always close the connection to avoid resource leaks.

   connection.close()

Try It Yourself

Problem 1: Create a Database and Table (MySQL)

Task: Write a program to create a database named school and a table students with columns id, name, and class.

import mysql.connector
 
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password"
)
 
cursor = connection.cursor()
 
cursor.execute("CREATE DATABASE IF NOT EXISTS school")
cursor.execute("""
CREATE TABLE IF NOT EXISTS school.students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    class INT
)
""")
print("Table 'students' created in 'school' database")
connection.close()

Problem 2: Navigate SQLite Database

Task: Write a program to connect to a SQLite database and fetch all rows from a table products.

import sqlite3
 
connection = sqlite3.connect("shop.db")
cursor = connection.cursor()
 
cursor.execute("SELECT * FROM products")
rows = cursor.fetchall()
 
for row in rows:
    print(row)
 
connection.close()

Python’s flexibility with databases makes it a powerful tool for backend development and data manipulation.