Using SQL with Python

SQL (Structured Query Language) is used to interact with databases. Python, combined with SQL, allows you to manage, analyze, and manipulate data efficiently. This training will teach you how to connect Python with databases, execute queries, and handle data.

Prerequisites

• Basic knowledge of Python
• Understanding of databases and SQL
• Python installed on your system
• Access to a database (MySQL, SQLite, or PostgreSQL)

Setting Up the Environment

1. Install required Python packages:
   • For SQLite: sqlite3 (built-in)
   • For MySQL: pip install mysql-connector-python
   • For PostgreSQL: pip install psycopg2
2. Import necessary libraries in Python:

import sqlite3  # For SQLite
import mysql.connector  # For MySQL
import psycopg2  # For PostgreSQL

Connecting to a Database

• SQLite Example

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

• MySQL Example

conn = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)
cursor = conn.cursor()

• PostgreSQL Example

conn = psycopg2.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)
cursor = conn.cursor()

Executing SQL Queries

• Create Table

cursor.execute("""
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
)
""")

• Insert Data

cursor.execute("INSERT INTO students (id, name, age) VALUES (1, 'Alice', 20)")
conn.commit()

• Select Data

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
    print(row)

• Update Data

cursor.execute("UPDATE students SET age = 21 WHERE id = 1")
conn.commit()

• Delete Data

cursor.execute("DELETE FROM students WHERE id = 1")
conn.commit()

Using Parameters Safely

Use placeholders to prevent SQL injection:

cursor.execute("SELECT * FROM students WHERE age = %s", (21,))
rows = cursor.fetchall()

Closing Connection

Always close the cursor and connection:

cursor.close()
conn.close()

Best Practices

• Use parameterized queries to prevent SQL injection
• Commit changes only when necessary
• Handle exceptions with try-except blocks
• Use context managers (with statement) for automatic closing

Summary

• Python can connect to multiple types of databases.
• You can execute all types of SQL queries using Python.
• Using SQL with Python makes data management and analysis more efficient.