Skip to main content

​
SQL Statement Types

This script uses two primary types of SQL statements to set up the database.

​
Data Definition Language (DDL)

CREATE TABLE

DDL statements define or modify the database structure and schema.
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
)
Purpose: The CREATE TABLE command defines a new table with its columns, constraints, and data types.
The IF NOT EXISTS clause prevents errors if you run the script multiple times - it only creates the table if it doesn’t already exist.

​
Data Manipulation Language (DML)

INSERT INTO

DML statements manipulate or manage data within schema objects.
INSERT INTO users (name, email) VALUES (?, ?)
Purpose: The INSERT INTO command adds new records (rows) into the existing users table.
Always use parameterized queries (with ? placeholders) to prevent SQL injection attacks.

​
Understanding AUTOINCREMENT

The AUTOINCREMENT keyword is a column constraint applied to the primary key. 
id INTEGER PRIMARY KEY AUTOINCREMENT
The AUTOINCREMENT constraint:
  • Generates a unique value for the id column automatically
  • Creates monotonically increasing values (each new ID is strictly greater than previous ones)
  • Starts from 1 by default
  • Eliminates the need to manually specify IDs when inserting records
When you insert a new row without providing an id, SQLite automatically generates the next available integer.
In SQLite, there’s a subtle but important difference:INTEGER PRIMARY KEY alone:
  • Automatically increments
  • May reuse IDs after deletion of rows with high IDs
INTEGER PRIMARY KEY AUTOINCREMENT:
  • Guarantees strictly increasing values
  • Never reuses IDs, even after deletions
  • Tracks the highest ID ever used in sqlite_sequence table
Use AUTOINCREMENT when you need audit trails, replication, or when ID reuse could cause issues. For most applications, INTEGER PRIMARY KEY alone is sufficient.

​
Example Implementation

1

Import SQLite

import sqlite3
SQLite is built into Python, so no additional installation is required.
2

Create connection and cursor

conn = sqlite3.connect('users.db')
cursor = conn.cursor()
This creates (or opens) a database file named users.db in the current directory.
3

Create the table

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
''')
The table is created with three columns: id, name, and email.
4

Insert sample data

users_data = [
    ('Alice Smith', '[email protected]'),
    ('Bob Jones', '[email protected]')
]

cursor.executemany(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    users_data
)
Use executemany() to insert multiple rows efficiently in a single operation.
5

Commit and close

conn.commit()
conn.close()
Always call commit() to save changes and close() to release the database lock.

​
Complete Example

import sqlite3

# Connect to database (creates file if it doesn't exist)
conn = sqlite3.connect('users.db')
cursor = conn.cursor()

# Create users table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
''')

# Sample data
users_data = [
    ('Alice Smith', '[email protected]'),
    ('Bob Jones', '[email protected]'),
    ('Carol White', '[email protected]')
]

# Insert data
cursor.executemany(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    users_data
)

# Commit and close
conn.commit()
conn.close()

print("Database created and populated successfully!")

​
Key Concepts

DDL

Data Definition Language
Defines database structure (CREATE, ALTER, DROP)

DML

Data Manipulation Language
Manipulates data (INSERT, UPDATE, DELETE, SELECT)

Constraints

NOT NULL, UNIQUE, PRIMARY KEY
Enforce data integrity rules

Parameterized Queries

Use ? placeholders
Prevents SQL injection attacks
SQLite is perfect for development, testing, and small applications. For production web applications with high concurrency, consider PostgreSQL or MySQL.