ORM – SQL Alchemy 2.0
- We need to understand 3 core concepts
- Connection URL:
- which database
- where is database
- username
- password
- Connection URL:
- Sample Connection URLS
| Database | Example Connection URL | Driver / Notes |
|---|---|---|
| SQLite | sqlite:///example.db |
Local file-based DB (no server needed). Use sqlite:///:memory: for in-memory DB. |
| PostgreSQL (psycopg2) | postgresql+psycopg2://user:password@localhost:5432/mydatabase |
Most common PostgreSQL driver. |
| PostgreSQL (asyncpg) | postgresql+asyncpg://user:password@localhost/mydatabase |
For async usage (e.g., with FastAPI + async SQLAlchemy). |
| MySQL (PyMySQL) | mysql+pymysql://user:password@localhost:3306/mydatabase |
Most popular MySQL connector for Python. |
| MySQL (mysqlclient) | mysql+mysqldb://user:password@localhost/mydatabase |
Faster C-based driver, but needs compilation. |
| MariaDB | mariadb+pymysql://user:password@localhost:3306/mydatabase |
Compatible with MySQL, use mariadb dialect. |
| Oracle | oracle+cx_oracle://user:password@hostname:1521/?service_name=myservice |
Requires cx_Oracle. Use service name or SID. |
| Microsoft SQL Server (pyodbc) | mssql+pyodbc://user:password@server/database?driver=ODBC+Driver+17+for+SQL+Server |
Install ODBC driver 17 or newer. |
| Microsoft SQL Server (aioodbc) | mssql+aioodbc://user:password@server/database |
Async SQL Server access. |
| IBM DB2 | ibm_db_sa://user:password@host:port/database |
Requires ibm_db_sa. |
| Firebird | firebird+fdb://user:password@localhost:3050/path/to/db.fdb |
Use fdb driver. |
| Sybase | sybase+pysybase://user:password@hostname:5000/mydatabase |
Less common, older system. |
- SQL Alchemy can connect to database using
- URL
- Driver
- SQL Alchemy Engine: This engine can connect to any driver (database)
- To create models (Tables) we need to create a Base which is derived from Declarative Base
Exercise 1: SQLite/mysql/postgres
- Lets create a table in SQLite using SQL Alchemy
- Lets create a new project with uv and venv activate
- Add a package SQLAlchemy
uv add SQLAlchemy
- For performing crud operations we need a session
- Refer Here for notebooks where we have used the same code for different database engines
- For every engine the url and drivers will differ
