SQL (Structured Query Language) is the standard programming language used to store, manipulate, retrieve, and manage data stored in Relational Database Management Systems (RDBMS) such as PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server.

Core Concepts

• Data Organization: Relational databases structure data into tables composed of rows (records) and columns (attributes/fields).

• Interaction: SQL uses structured, declarative commands to interact with and manage these tables.

• SQL Command Subsets:

  • DQL (Data Query Language): Fetches data from the database (SELECT).

  • DML (Data Manipulation Language): Modifies existing data records (INSERT, UPDATE, DELETE).

  • DDL (Data Definition Language): Defines and modifies schema structures (CREATE, ALTER, DROP, TRUNCATE).

  • DCL (Data Control Language): Manages user permissions and access rights (GRANT, REVOKE).

  • TCL (Transaction Control Language): Manages database transactions (COMMIT, ROLLBACK, SAVEPOINT).

Basic Syntax Examples

• Create a Table:

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    department VARCHAR(50),

    salary DECIMAL(10, 2)

);

• Insert Data:

INSERT INTO employees (id, name, department, salary)

VALUES (1, 'Alice Smith', 'Engineering', 85000.00);

• Query Data:

SELECT name, salary 

FROM employees 

WHERE department = 'Engineering' AND salary > 60000 

ORDER BY salary DESC;

• Update and Delete:

-- Update salary

UPDATE employees 

SET salary = 90000.00 

WHERE id = 1;

-- Delete record

DELETE FROM employees 

WHERE id = 1;

SQLite is a lightweight, serverless, self-contained SQL database engine. Unlike traditional client-server database management systems (like PostgreSQL or MySQL), SQLite reads and writes directly to standard disk files without a separate server process.

Core Characteristics

• Serverless: Runs directly within the host application process, eliminating network overhead and setup complexity.

• Single-File Storage: An entire database—tables, indices, triggers, and data—is stored in a single cross-platform disk file (.db or .sqlite).

• Zero Configuration: No installation, service daemons, or user permissions to configure.

• ACID-Compliant: Full transaction support with atomic commits and rollbacks, even across power outages.

• Cross-Platform & Portable: Database files are binary-compatible across 32-bit and 64-bit architectures, big-endian and little-endian systems.

• Dynamic Typing: Uses type affinity rather than rigid column typing; values are typed based on the data inserted rather than strictly by the column definition.

Python includes built-in support for SQLite via the standard library module sqlite3—no extra installations required (pip install is not needed).

1. Basic Setup: Connect, Create Table, and Insert Data

Use parameterized queries (? placeholders) instead of string formatting to prevent SQL injection attacks.

import sqlite3

# Connect to a file-based database (or use ':memory:' for an in-memory DB)

conn = sqlite3.connect("app.db")

# Create a cursor object to execute SQL commands

cursor = conn.cursor()

# Create a table

cursor.execute("""

   CREATE TABLE IF NOT EXISTS users (

       id INTEGER PRIMARY KEY AUTOINCREMENT,

       name TEXT NOT NULL,

       email TEXT UNIQUE NOT NULL,

       age INTEGER

   )

""")

# Insert a single record using parameterized queries

cursor.execute(

   "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",

   ("Alice", "alice@example.com", 29),

)

# Save changes and close the connection

conn.commit()

conn.close()

2. Inserting Multiple Records (executemany)

import sqlite3

users_data = [

   ("Bob", "bob@example.com", 34),

   ("Charlie", "charlie@example.com", 22),

   ("Diana", "diana@example.com", 41),

]

with sqlite3.connect("app.db") as conn:

   cursor = conn.cursor()

   cursor.executemany(

       "INSERT OR IGNORE INTO users (name, email, age) VALUES (?, ?, ?)",

       users_data,

   )

   # The 'with' context manager automatically commits on exit

3. Querying Records (fetchone, fetchall)

import sqlite3

conn = sqlite3.connect("app.db")

cursor = conn.cursor()

# Query with parameterized filtering

cursor.execute("SELECT id, name, email, age FROM users WHERE age > ?", (25,))

# Fetch all matching rows (returns a list of tuples)

rows = cursor.fetchall()

for row in rows:

   print(row)

   # Returns individual values from the tuple

   user_id, name, email, age = row

   print(f"ID: {user_id} | Name: {name} | Email: {email} | Age: {age}")

conn.close()

4. Accessing Columns by Name (sqlite3.Row)

By default, SQLite returns rows as standard tuples. Setting conn.row_factory = sqlite3.Row allows dictionary-like key access.

import sqlite3

conn = sqlite3.connect("app.db")

conn.row_factory = sqlite3.Row  # Enables column-name indexing

cursor = conn.cursor()

cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))

user = cursor.fetchone()

if user:

   print(f"Name: {user['name']}, Email: {user['email']}, Age: {user['age']}")

conn.close()

5. Updates, Deletes, and Safe Transaction Handling

Using Python's try/except block with conn.rollback() ensures database integrity if an error occurs.

import sqlite3

conn = sqlite3.connect("app.db")

cursor = conn.cursor()

try:

   # Update a record

   cursor.execute(

       "UPDATE users SET age = ? WHERE name = ?",

       (30, "Alice"),

   )

   # Delete a record

   cursor.execute(

       "DELETE FROM users WHERE name = ?",

       ("Charlie",),

   )

   # Commit only if all operations succeed

   conn.commit()

   print("Updates and deletes committed successfully.")

except sqlite3.Error as e:

   conn.rollback()

   print(f"Database error: {e}")

finally:

   conn.close()

Python uses database-specific driver packages and Object-Relational Mappers (ORMs) to connect to SQL databases. Most follow the standard Python DB-API 2.0 (PEP 249) interface. 

• PostgreSQL

  • psycopg (v3): Modern, feature-rich standard driver (pip install psycopg[binary])

  • asyncpg: High-performance asynchronous driver for asyncio setups (pip install asyncpg)

• MySQL / MariaDB

  • mysql-connector-python: Official Oracle-maintained pure Python driver (pip install mysql-connector-python)

  • PyMySQL: Lightweight, popular pure Python alternative (pip install pymysql)

• SQLite

  • sqlite3: Serverless, file-based database included directly in the Python standard library (no installation needed)

• Microsoft SQL Server

  • pyodbc: Standard ODBC-based connector (pip install pyodbc)

  • pymssql: Lightweight TDS-protocol-based driver (pip install pymssql)

• Oracle Database

  • python-oracledb: Official thin-client driver, formerly cx_Oracle (pip install oracledb)

• Cloud & Data Warehouses

  • Snowflake: snowflake-connector-python (pip install snowflake-connector-python)

  • Google BigQuery: google-cloud-bigquery (pip install google-cloud-bigquery)

  • Amazon Redshift: redshift-connector (pip install redshift-connector)

• Database Abstraction & ORMs

  • SQLAlchemy: Industry-standard toolkit and ORM that wraps underlying drivers to provide connection pooling, query building, and multi-database support (pip install sqlalchemy)

  • SQLModel: Combines SQLAlchemy and Pydantic, popular for FastAPI backends (pip install sqlmodel)

  • Django ORM: Built into the Django framework for declarative model querying (pip install django)

SQLAlchemy is the standard Python toolkit for database access, bridging Python code with relational database management systems (like PostgreSQL, MySQL, SQLite, and Oracle).

It operates on two distinct layers: SQLAlchemy Core (a Pythonic SQL expression language and connection abstraction) and SQLAlchemy ORM (Object-Relational Mapping that maps Python classes directly to database tables).

+-------------------------------------------------------+

|                 SQLAlchemy ORM                        |

|  - Maps Python classes to database tables             |

|  - Manages identity, transactions (Session / Unit of  |

|    Work pattern)                                      |

+-------------------------------------------------------+

|

+-------------------------------------------------------+

|                 SQLAlchemy Core                       |

|  - Schema definition (Table, Column, MetaData)        |

|  - SQL Expression Language (select, insert, update)   |

|  - Connection pooling & raw DBAPI execution (Engine)  |

+-------------------------------------------------------+

  |

+-------------------------------------------------------+

|                  Database Driver (DBAPI)              |

|        (e.g., psycopg2, asyncpg, pymysql, sqlite3)    |

+-------------------------------------------------------+

Key Components

• Engine: The central connection interface that manages database credentials, dialect translation, and connection pooling.

• Declarative Base / ORM Models: Python classes subclassed from a base class where class attributes represent database columns.

• Session: The primary workspace for ORM persistence. It tracks dirty, new, and deleted Python objects and translates their state changes into SQL queries inside transactions.

• Dialects: Adapters that convert standardized SQLAlchemy expressions into vendor-specific SQL syntax.

To use SQLAlchemy, you must first install the package via pip:

pip3 install sqlalchemy

from sqlalchemy import create_engine, String, select, update, delete

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

# 1. Initialize Engine

engine = create_engine("sqlite:///app2.db", echo=False)

# 2. Define Schema via Declarative Base

class Base(DeclarativeBase):

   pass

class User(Base):

   __tablename__ = "users"

   # Auto-increments automatically; autoincrement=True is implied

   id: Mapped[int] = mapped_column(primary_key=True)

   name: Mapped[str] = mapped_column(String(50))

   email: Mapped[str] = mapped_column(String(100), unique=True)

# Create tables

Base.metadata.create_all(engine)

# 3. Insert, Update, Query Data and Delete with Session

with Session(engine) as session:

   # Add records

   new_user = User(name="Alex", email="alex@example.com")

   session.add(new_user)

   new_user2 = User(name="Alan", email="alan@example.com")

   session.add(new_user2)

   session.commit()

   # Update the record

   stmt = (

       update(User)

       .where(User.name == "Alex")

       .values(email="alex.new@example.com")

   )

   session.execute(stmt)

   session.commit()

   # Query to select all records

   stmt = select(User)

   users = session.scalars(stmt).all()

   for user in users:

       print(f"ID: {user.id} | Name: {user.name} | Email: {user.email}")

   # Query to select a particular record

   stmt = select(User).where(User.name == "Alex")

   user = session.scalars(stmt).first()

   print(f"Found: {user.id} {user.name} ({user.email})")

   # Delete the record

   stmt = delete(User).where(User.name == "Alex")

   session.execute(stmt)

   session.commit()

Example: Connecting MSSQL to Pandas using SQLAlchemy

If you want to run MSSQL in Docker, you can run this command:

docker run -e "ACCEPT_EULA=Y" \

                -e "MSSQL_SA_PASSWORD=YourStrong!Password2025" \

                -e "MSSQL_PID=Developer" \

                -p 1433:1433 \

                --name mssql2025 \

                --restart unless-stopped \

                -d mcr.microsoft.com/mssql/server:2025-latest

This script at the bottom provides an end-to-end data pipeline that provisions a SQL Server database, populates it, extracts and processes the records, and renders an interactive visualization—all without relying on system-level ODBC drivers.

Architecture Breakdown

• Direct DBAPI Connection (pymssql): Instead of using the Microsoft ODBC Driver, the script uses pymssql (built on FreeTDS). This allows a lightweight, pure-Python setup that connects via standard TCP/IP (mssql+pymssql://...) without requiring OS-level driver installations or DSN configurations.

• Idempotent Database Creation: Connects to the default master database with AUTOCOMMIT enabled (required by SQL Server for DDL operations) and runs a conditional T-SQL check (IF NOT EXISTS) to create the database only if it does not already exist.

• Data Ingestion (to_sql): Loads a structured pandas DataFrame into SQL Server, automatically inferring data types and creating the target table schema.

• Querying & In-Memory Analysis: Executes a SQL query via SQLAlchemy connection pooling into a pandas DataFrame. It uses .groupby() and .agg() to calculate category-level sales totals.

• Interactive Visualization (plotly.express): Renders a modern donut chart (hole=0.35) featuring custom tooltips, formatted currency metrics, and automatic slice percentage calculations.

Make sure to install the following libraries:

pip install pymssql sqlalchemy pandas plotly

import pandas as pd

import plotly.express as px

from sqlalchemy import create_engine, text

# ---------------------------------------------------------

# 1. Connection Parameters

# ---------------------------------------------------------

server = 'localhost:1433'       # e.g., 'localhost' or '127.0.0.1' (specify port if needed: 'localhost:1433')

username = 'sa'

password = 'YourStrong!Password2025'

target_db = 'SalesAnalyticsDB'

# ---------------------------------------------------------

# 2. Connect to 'master' and Create Database

# ---------------------------------------------------------

# pymssql connection string format: mssql+pymssql://user:password@host:port/database

master_url = f"mssql+pymssql://{username}:{password}@{server}/master"

master_engine = create_engine(

   master_url,

   isolation_level="AUTOCOMMIT"  # Required for CREATE DATABASE

)

with master_engine.connect() as conn:

   conn.execute(text(f"""

       IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{target_db}')

       BEGIN

           CREATE DATABASE [{target_db}];

       END

   """))

   print(f"Database '{target_db}' is ready.\n")

# ---------------------------------------------------------

# 3. Connect to the Target Database

# ---------------------------------------------------------

target_url = f"mssql+pymssql://{username}:{password}@{server}/{target_db}"

db_engine = create_engine(target_url)

# ---------------------------------------------------------

# 4. Insert Sample Data

# ---------------------------------------------------------

sample_data = pd.DataFrame({

   'OrderID': [101, 102, 103, 104, 105, 106, 107, 108, 109, 110],

   'Category': [

       'Electronics', 'Furniture', 'Electronics', 'Clothing',

       'Home Appliances', 'Furniture', 'Clothing', 'Electronics',

       'Home Appliances', 'Clothing'

   ],

   'Region': ['North', 'South', 'West', 'East', 'North', 'West', 'South', 'East', 'North', 'West'],

   'SalesAmount': [1250.00, 450.50, 2300.00, 180.00, 890.00, 620.00, 310.00, 1450.00, 420.00, 275.00]

})

with db_engine.begin() as conn:

   sample_data.to_sql(

       name='Sales',

       con=conn,

       if_exists='replace',

       index=False

   )

print("Table 'Sales' populated successfully.\n")

# ---------------------------------------------------------

# 5. Query into Pandas & Aggregate

# ---------------------------------------------------------

query = "SELECT Category, Region, SalesAmount FROM Sales"

with db_engine.connect() as conn:

   df = pd.read_sql(query, conn)

category_summary = (

   df.groupby('Category', as_index=False)['SalesAmount']

   .sum()

   .sort_values(by='SalesAmount', ascending=False)

)

print("Aggregated Sales:")

print(category_summary)

# ---------------------------------------------------------

# 6. Plot Interactive Pie Chart

# ---------------------------------------------------------

fig = px.pie(

   category_summary,

   names='Category',

   values='SalesAmount',

   title=f'Sales Distribution by Category ({target_db})',

   hole=0.35,

   color_discrete_sequence=px.colors.qualitative.Safe

)

fig.show()