Connecting & Creating Tables in SQLite3

To interact with a database in Python, you follow a 3-step lifecycle:

1. Connect to the database file.
2. Create a Cursor (the worker who executes SQL statements).
3. Execute SQL commands and close the connection.

---

1. Connecting to a Database

import sqlite3

# 1. Connects to 'university.db' (Creates file automatically if it doesn't exist)
conn = sqlite3.connect("university.db")

# 2. Create the Cursor object
cursor = conn.cursor()

print("✅ Connected to SQLite database successfully!")

# 3. Always close the connection when finished
conn.close()

In-Memory Temporary Databases

If you are writing unit tests and don't want to create files on your hard drive, connect to sqlite3.connect(":memory:"). It builds the whole database in RAM and wipes it clean when the script ends.

---

2. Writing Your First CREATE TABLE Query

SQL queries are executed using cursor.execute("""..."""):

import sqlite3

conn = sqlite3.connect("university.db")
cursor = conn.cursor()

# Create 'students' table with schema definition
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    student_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    gpa REAL DEFAULT 0.0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")

# Commit (save) changes to the database file
conn.commit()

print("✅ 'students' table created successfully!")
conn.close()

---

3. SQL Keywords Explained

• INTEGER PRIMARY KEY AUTOINCREMENT: Generates a unique, non-repeating number (1, 2, 3...) automatically for every new student.
• NOT NULL: Prevents empty values for required fields (e.g. every student must have a name).
• UNIQUE: Prevents duplicate entries (e.g. no two students can register the exact same email).
• DEFAULT: Automatically populates a default value if not provided during insertion.

---

4. Modern Python Best Practice: Using Context Managers

Instead of manually typing conn.close() and conn.commit(), you can use Python's with statement for automatic transaction commit:

import sqlite3

# Context manager automatically handles commit and rollback on errors
with sqlite3.connect("university.db") as conn:
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS courses (
        course_id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_title TEXT NOT NULL,
        fees REAL
    )
    """)

---

Quick Summary

• sqlite3.connect(database_path): Connects to an existing database or creates a new .db file if absent.
• cursor Object: Used to execute SQL statements (cursor.execute(sql)).
• Constraints: PRIMARY KEY AUTOINCREMENT (unique ID generation), NOT NULL (mandatory field), UNIQUE (prevent duplicates).
• conn.commit() / conn.close(): Persists pending database transactions and releases the file handle.

---

What's Next?

Let's learn how to safely insert single and batch records using parameterized queries in Inserting Data Safely (SQL Injection Prevention)!