SQL Database Connections

What is a Database?

A database is like a big filing cabinet that stores data in organized tables. SQL databases are the most common type used by companies.

Why use databases instead of files:

Handle huge amounts of data
Multiple people can access at same time
Fast searching and filtering
Data stays organized
Built-in security

Popular SQL databases:

SQLite (simple, no setup needed)
MySQL (very popular, free)
PostgreSQL (powerful, free)
SQL Server (Microsoft)

SQLite - The Easiest Start

SQLite is perfect for learning. It's built into Python, no installation needed.

What makes SQLite special:

No server needed
Database is just one file
Perfect for small to medium projects
Built into Python

Connecting to SQLite

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
print("Connected to database")

connection.close()

What this does:

Creates mydata.db file if it doesn't exist
Opens connection to database
Closes connection when done

Important: Always close connections when finished.

Creating a Table

Tables are like spreadsheets inside the database. You need to create them before adding data.

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER,
        grade TEXT
    )
""")

connection.commit()
connection.close()
print("Table created")

What this does:

cursor is like a pointer that executes commands
CREATE TABLE makes a new table
IF NOT EXISTS prevents error if table exists
INTEGER and TEXT are data types
PRIMARY KEY makes id unique
commit() saves changes

Inserting Data

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
cursor = connection.cursor()

cursor.execute("""
    INSERT INTO students (name, age, grade)
    VALUES ("John", 20, "A")
""")

connection.commit()
connection.close()
print("Data inserted")

What this does: Adds one row to the students table.

Inserting Multiple Rows

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
cursor = connection.cursor()

students = [
    ("Sarah", 22, "B"),
    ("Mike", 21, "A"),
    ("Emma", 23, "B")
]

cursor.executemany("""
    INSERT INTO students (name, age, grade)
    VALUES (?, ?, ?)
""", students)

connection.commit()
connection.close()
print("Multiple rows inserted")

What this does:

executemany() inserts multiple rows at once
Question marks are placeholders for values
Much faster than inserting one by one

Querying Data

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
cursor = connection.cursor()

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

for row in rows:
    print(row)

connection.close()

What this shows: All rows from students table. Each row is a tuple.

Example output:

(1, 'John', 20, 'A')
(2, 'Sarah', 22, 'B')
(3, 'Mike', 21, 'A')

Filtering with WHERE

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
cursor = connection.cursor()

cursor.execute("""
    SELECT name, age FROM students
    WHERE grade = "A"
""")

rows = cursor.fetchall()

for row in rows:
    name, age = row
    print("Name:", name, "Age:", age)

connection.close()

What this does: Gets only students with grade A, showing only name and age columns.

Using Parameters Safely

Never put user input directly in SQL. Use parameters instead.

Wrong way (dangerous):

code.py

grade = input("Enter grade: ")
cursor.execute("SELECT * FROM students WHERE grade = " + grade)

Right way (safe):

code.py

grade = input("Enter grade: ")
cursor.execute("SELECT * FROM students WHERE grade = ?", (grade,))

Why this matters: Prevents SQL injection attacks where hackers can damage your database.

Updating Data

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
cursor = connection.cursor()

cursor.execute("""
    UPDATE students
    SET age = 21
    WHERE name = "John"
""")

connection.commit()
print("Rows updated:", cursor.rowcount)
connection.close()

What this does: Changes John's age to 21. rowcount tells how many rows changed.

Deleting Data

code.py

import sqlite3

connection = sqlite3.connect("mydata.db")
cursor = connection.cursor()

cursor.execute("""
    DELETE FROM students
    WHERE grade = "F"
""")

connection.commit()
print("Rows deleted:", cursor.rowcount)
connection.close()

What this does: Removes all students with grade F.

Practice Example

The scenario: Build a simple product inventory system.

code.py

import sqlite3

connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        quantity INTEGER,
        price REAL
    )
""")

products = [
    ("Laptop", 5, 999.99),
    ("Phone", 10, 599.99),
    ("Tablet", 7, 399.99)
]

cursor.executemany("""
    INSERT INTO products (name, quantity, price)
    VALUES (?, ?, ?)
""", products)

connection.commit()

cursor.execute("SELECT * FROM products")
all_products = cursor.fetchall()

print("Current Inventory:")
print("-" * 40)
for product in all_products:
    product_id, name, quantity, price = product
    print("ID:", product_id)
    print("Name:", name)
    print("Stock:", quantity)
    print("Price:", price)
    print()

cursor.execute("""
    SELECT SUM(quantity * price) FROM products
""")
total_value = cursor.fetchone()[0]
print("Total inventory value:", total_value)

connection.close()

What this program does:

Creates database and products table
AUTOINCREMENT makes id increase automatically
NOT NULL means name is required
Inserts 3 products
Fetches and displays all products
Calculates total inventory value using SUM
Closes connection

Using Context Manager

Better way to handle connections automatically.

code.py

import sqlite3

with sqlite3.connect("mydata.db") as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM students")
    rows = cursor.fetchall()

    for row in rows:
        print(row)

Why this is better: Connection closes automatically, even if error occurs.

Key Points to Remember

SQLite is built into Python, perfect for learning. Database file is created automatically when you connect.

Always use cursor to execute SQL commands. cursor.execute() runs queries, connection.commit() saves changes.

Use fetchall() to get all results, fetchone() for single row. Results are tuples.

Use question mark placeholders for parameters. This prevents SQL injection attacks.

Always close connections or use with statement for automatic cleanup.

Common Mistakes

Mistake 1: Forgetting commit

code.py

cursor.execute("INSERT INTO students VALUES (?, ?, ?)", data)
connection.close()  # Changes lost! Need commit() first

Mistake 2: SQL injection

code.py

name = input("Name: ")
cursor.execute("SELECT * FROM students WHERE name = " + name)  # Dangerous!

Use parameters instead.

Mistake 3: Not closing connection

code.py

connection = sqlite3.connect("mydata.db")
# ... work with database ...
# Forgot connection.close()

Mistake 4: Wrong data types

code.py

cursor.execute("INSERT INTO students VALUES (?, ?, ?)", ("John", "twenty", "A"))

age should be number, not "twenty".

What's Next?

You now know how to connect to databases. Next, you'll learn about reading from SQL databases - advanced queries, joining tables, and data analysis.