Introduction to Databases and SQL for Python Developers

Every application you build eventually needs to store data — user accounts, orders, sensor readings, messages. Without a structured storage layer, you end up juggling CSV files and Python dictionaries that break the moment two people update data at the same time. This page introduces the core ideas behind relational databases, explains what SQL is, and shows you why SQLite is the ideal starting point for your Python projects.

Data vs. Information

Raw facts on their own — "Rahul", 25, 50000, "Hyderabad" — are just data. They carry no meaning until you organize them. Information is processed data that answers a question. Put those same facts into a table with labeled columns and you instantly know: employee Rahul, aged 25, earns ₹50,000 and is based in Hyderabad.

Employee ID	Name	Age	Salary	City
101	Rahul	25	50000	Hyderabad

This is why every serious application uses a database — an organized, electronically stored collection of related data that you can access, update, and query efficiently.

Why Not Just Use Files?

Many beginners start with flat files (CSV, JSON, plain text). That works for small, single-user experiments, but it falls apart quickly in the real world.

Problems with flat files

No efficient search across millions of rows
No protection against duplicate entries
No safe concurrent access by multiple users
Referential integrity is your problem to enforce
Backup and recovery requires manual effort

What a DBMS gives you

Fast indexed lookups on any column
Unique constraints and foreign-key checks
Transactions that keep data consistent
Built-in access control and security
Automatic journaling for crash recovery

A Database Management System (DBMS) is the software layer that manages all of this for you. Examples you will encounter include SQLite, MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

Relational Databases

A relational database organizes data into tables. Each table represents one type of entity (employees, orders, products). Each row is one record, and each column is one attribute of that record.

Tables, Rows, and Columns

employees table
┌─────────────┬───────────────┬────────┬───────────┬───────────────┐
│ employee_id │ employee_name │ salary │ city      │ department_id │
├─────────────┼───────────────┼────────┼───────────┼───────────────┤
│ 101         │ Rahul Sharma  │ 65000  │ Hyderabad │ 1             │
│ 102         │ Anitha Rao    │ 52000  │ Bengaluru │ 2             │
│ 103         │ Kiran Kumar   │ 72000  │ Hyderabad │ 1             │
└─────────────┴───────────────┴────────┴───────────┴───────────────┘

Primary Keys

A primary key is a column (or set of columns) whose value uniquely identifies every row in a table. Primary keys must be:

Unique — no two rows may share the same value
Non-null — every row must have a value
Stable — they should not change once assigned

In the table above, employee_id is the primary key.

Foreign Keys

A foreign key is a column that references the primary key of another table. This is what creates the relationship in relational databases. In the employees table, department_id references department_id in a departments table, linking every employee to their department.

departments                    employees
┌───────────────┬────────────┐  ┌─────────────┬──────────────┬───────────────┐
│ department_id │ dept_name  │  │ employee_id │ name         │ department_id │
├───────────────┼────────────┤  ├─────────────┼──────────────┼───────────────┤
│ 1             │ Engineering│◄─┤ 101         │ Rahul Sharma │ 1             │
│ 2             │ HR         │  │ 102         │ Anitha Rao   │ 2             │
└───────────────┴────────────┘  └─────────────┴──────────────┴───────────────┘

Common Relationship Types

One-to-Many (1:N) — most common

One department contains many employees, but each employee belongs to exactly one department. This is the relationship you will design most often.

One-to-One (1:1)

Each employee has exactly one passport record, and each passport belongs to exactly one employee. Used when you want to split a wide table into a smaller, faster core table.

Many-to-Many (M:N)

Many students can enroll in many courses. This requires a junction table (e.g., enrollments) that holds the foreign keys from both sides.

What is SQL?

SQL (Structured Query Language) is the standard language for communicating with relational databases. You use SQL to create tables, insert data, update records, delete rows, and — most importantly — retrieve exactly the data you need.

-- Retrieve all employees earning more than ₹60,000
SELECT employee_name, salary, city
FROM employees
WHERE salary > 60000
ORDER BY salary DESC;

SQL is declarative: you describe what you want, not how to find it. The database engine figures out the most efficient execution plan.

Why Learn SQL as a Python Developer?

Python is great for processing data, but the data has to come from somewhere. As a Python developer you will:

Query databases from your Python scripts using sqlite3, psycopg2, or SQLAlchemy
Read data into Pandas DataFrames directly from SQL queries
Write FastAPI or Django endpoints that store and retrieve records
Analyze datasets that are too large to fit in memory — SQL lets the database do the heavy lifting

SQL knowledge turns you from someone who processes data into someone who can access any data in the first place.

SQL Command Categories

SQL is divided into logical sublanguages based on what they do.

DDL — Define
DML — Manipulate
DQL — Query
TCL — Control

Data Definition Language shapes the structure of your database.

Command	Purpose
`CREATE`	Create a new table, index, or view
`ALTER`	Modify an existing table structure
`DROP`	Permanently delete a table

Data Manipulation Language changes the rows inside tables.

Command	Purpose
`INSERT`	Add new rows
`UPDATE`	Modify existing rows
`DELETE`	Remove rows

Data Query Language retrieves data. The entire sublanguage is a single command.

Command	Purpose
`SELECT`	Fetch rows from one or more tables

Transaction Control Language manages groups of changes.

Command	Purpose
`BEGIN`	Start a transaction
`COMMIT`	Permanently save the changes
`ROLLBACK`	Undo all changes since `BEGIN`

Why Start with SQLite?

SQLite is a full relational database engine that ships built into Python’s standard library. You do not need to install a server, configure credentials, or manage a service.

Zero setup

import sqlite3 is all you need. The database lives in a single .db file on your disk.

Full SQL support

Supports all the SQL you will learn here — DDL, DML, DQL, JOINs, window functions, CTEs, and indexes.

Transfers to any DB

The SQL you write for SQLite is 95% compatible with PostgreSQL and MySQL. Skills transfer directly.

import sqlite3

# Create a database file (or open it if it already exists)
conn = sqlite3.connect("company.db")
cursor = conn.cursor()

cursor.execute("SELECT sqlite_version()")
print(cursor.fetchone())  # ('3.x.x',)

conn.close()

SQLite stores your entire database — all tables, indexes, and data — in a single file. You can copy it, email it, or commit it to Git. That makes it perfect for learning and prototyping.

What You Will Learn in This Section

DDL & DML — Create and populate tables

Learn CREATE TABLE with data types and constraints, then add, update, and delete rows with INSERT, UPDATE, and DELETE.

SQLite Setup — Connect Python to SQLite

Use sqlite3 to connect from Python, execute queries, commit transactions, and inspect your database with VS Code.

DQL — Query data with SELECT

Master WHERE, ORDER BY, LIMIT, aggregate functions, GROUP BY, and HAVING.

JOINs — Combine multiple tables

Use INNER JOIN, LEFT JOIN, and multi-table joins to answer questions that span more than one table.

Advanced SQL — Analytics-level queries

Write subqueries, CTEs, window functions, CASE expressions, and create indexes and views.

Throughout all five topics you will work with a consistent Employee Management database — departments, employees, salaries, and cities — so every new concept builds directly on what you already know.

Next: DDL & DML

Start building your database — learn how to create tables, define constraints, and insert your first rows.

Project Development

SQL & Databases

Data Analysis

Introduction to Databases and SQL for Python Developers

Data vs. Information

Why Not Just Use Files?

Problems with flat files

What a DBMS gives you

Relational Databases

Tables, Rows, and Columns

Primary Keys

Foreign Keys

Common Relationship Types

What is SQL?

Why Learn SQL as a Python Developer?

SQL Command Categories

Why Start with SQLite?

Zero setup

Full SQL support

Transfers to any DB

What You Will Learn in This Section

Next: DDL & DML

​Data vs. Information

​Why Not Just Use Files?

Problems with flat files

What a DBMS gives you

​Relational Databases

​Tables, Rows, and Columns

​Primary Keys

​Foreign Keys

​Common Relationship Types

​What is SQL?

​Why Learn SQL as a Python Developer?

​SQL Command Categories

​Why Start with SQLite?

Zero setup

Full SQL support

Transfers to any DB

​What You Will Learn in This Section

Next: DDL & DML

Data vs. Information

Why Not Just Use Files?

Relational Databases

Tables, Rows, and Columns

Primary Keys

Foreign Keys

Common Relationship Types

What is SQL?

Why Learn SQL as a Python Developer?

SQL Command Categories

Why Start with SQLite?

What You Will Learn in This Section