Hey everyone! Welcome to this SQLAlchemy tutorial for my 100k+ subscribers. If you're new here, smash that like button and subscribe for more Python and database magic. Let's dive into the basics using MySQL – no fluff, just code!
SQLAlchemy is a powerful Python library that serves as both a SQL toolkit and an Object-Relational Mapper (ORM). It allows you to interact with databases using Python code, abstracting away much of the raw SQL complexity while giving you full control when needed.
Key features:
SQLAlchemy has two main layers: Core and ORM.
| Aspect | Core | ORM |
|---|---|---|
| Description | The foundational SQL abstraction layer. Handles connections, transactions, and raw SQL execution. | Builds on Core to map Python classes to database tables, enabling object-oriented database interactions. |
| Use Case | Simple queries, migrations, or when you prefer raw SQL control. | Complex apps with models, relationships, and CRUD operations on objects. |
| Pros | Lighter, faster for direct SQL. | Reduces boilerplate, handles relationships automatically. |
| Cons | More manual SQL writing. | Slight overhead; steeper learning for beginners. |
In this tutorial, we'll focus on Core for simplicity, but ORM is just a layer on top!
SQLAlchemy requires Python 3.7+. Use the latest version (2.x) for modern features like async support.
Install via pip:
pip install sqlalchemyFor MySQL, we need a driver. Recommended: PyMySQL (pure Python, easy setup).
pip install pymysqlAlternative: mysql-connector-python for official Oracle driver.
Note: Ensure MySQL server is running (e.g., via Docker:
docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pass mysql).
The Engine is SQLAlchemy's core construct for managing database connections. It's thread-safe and handles pooling.
Basic creation:
from sqlalchemy import create_engine
# Replace with your details
engine = create_engine("mysql+pymysql://username:password@localhost/database_name")
This uses PyMySQL dialect. For mysql-connector: mysql+mysqlconnector://....
SQLAlchemy uses URLs to specify DB details:
dialect+driver://username:password@host:port/database?query=param.
MySQL examples:
mysql+pymysql://root:mypassword@localhost:3306/mydbmysql+pymysql://user:pass@remotehost.com:3306/prod_db?charset=utf8mb4mysql+pymysql://user:pass@localhost/mydb?pool_size=5&pool_recycle=3600
Security Tip: Use environment variables for credentials (e.g.,
os.getenv('DB_URL')) in production.
Set echo=True in create_engine to log all SQL statements and parameters to
stdout. Great for debugging!
engine = create_engine("mysql+pymysql://...", echo=True)
Output example:
INFO sqlalchemy.engine.Engine SELECT 1
INFO sqlalchemy.engine.Engine [raw sql] ()
Disable in prod: echo=False (default).
Let's write a simple script to connect and run a basic query. Save as first_sqlalchemy.py.
from sqlalchemy import create_engine, text
# Create engine with echo for logging
engine = create_engine(
"mysql+pymysql://root:mypassword@localhost:3306/mydb",
echo=True
)
# Test connection with a simple query
with engine.connect() as conn:
result = conn.execute(text("SELECT 1 AS test"))
print("Result:", result.fetchone()[0])
# Or use engine.execute for one-off (deprecated in 2.0, use connect() instead)
# with engine.begin() as conn: # Auto-commits
# result = conn.execute(text("SELECT 1"))
Run: python first_sqlalchemy.py. Watch the echo output!
Latest Standards: Use context managers (with) for connections.
text() for raw SQL in 2.x.
Now, let's create multiple engines and test them. This script demonstrates different configs.
Full script (multi_engine_test.py):
import os
from sqlalchemy import create_engine, text
# Use env vars for security (set DB_PASSWORD env)
DB_PASSWORD = os.getenv('DB_PASSWORD', 'mypassword')
DB_NAME = 'mydb'
# 1. Engine 1: Basic local MySQL with PyMySQL and echo=True
engine1 = create_engine(
f"mysql+pymysql://root:{DB_PASSWORD}@localhost:3306/{DB_NAME}",
echo=True,
pool_pre_ping=True # Validates connections (latest best practice)
)
# 2. Engine 2: With connection pool options
engine2 = create_engine(
f"mysql+pymysql://root:{DB_PASSWORD}@localhost:3306/{DB_NAME}",
echo=True,
pool_size=5,
max_overflow=10,
pool_recycle=3600
)
# 3. Engine 3: Using mysql-connector (alternative driver)
engine3 = create_engine(
f"mysql+mysqlconnector://root:{DB_PASSWORD}@localhost:3306/{DB_NAME}",
echo=True
)
def test_connection(engine, name):
"""Test connection with SELECT and INSERT/SELECT to observe echo."""
print(f"\n--- Testing {name} ---")
try:
with engine.connect() as conn:
# Test SELECT 1
result = conn.execute(text("SELECT 1 AS connection_test"))
print(f"Connection test: {result.fetchone()[0]}")
# Sample INSERT and SELECT (creates temp table if needed)
conn.execute(text("""
CREATE TABLE IF NOT EXISTS test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
value VARCHAR(50)
)
"""))
conn.execute(text("INSERT INTO test_table (value) VALUES ('Hello SQLAlchemy!')"))
result = conn.execute(text("SELECT * FROM test_table LIMIT 1"))
row = result.fetchone()
print(f"Inserted/Selected: ID={row[0]}, Value={row[1]}")
# Cleanup
conn.execute(text("DROP TABLE IF EXISTS test_table"))
print(f"✅ {name} connection successful!")
except Exception as e:
print(f"❌ {name} failed: {e}")
# Run tests
test_connection(engine1, "Engine 1 (Basic)")
test_connection(engine2, "Engine 2 (Pooled)")
test_connection(engine3, "Engine 3 (Connector Driver)")
print("\nEcho logs above show generated SQL – perfect for debugging!")
Expected Echo Output (Snippet):
INFO sqlalchemy.engine.Engine [no key 0.00002s] SELECT 1 AS connection_test
INFO sqlalchemy.engine.Engine [raw sql] ()
INFO sqlalchemy.engine.Engine [no key 0.00015s] CREATE TABLE IF NOT EXISTS test_table ...
Pro Tip: Run with export DB_PASSWORD=yourpass && python multi_engine_test.py. Observe how
echo reveals parameterized queries (safer against SQL injection).
That's your SQLAlchemy foundation with MySQL! Next video: Diving into ORM models. Drop comments: What's your fave DB? Like & subscribe for more. 🚀
Code tested with SQLAlchemy 2.0.23, Python 3.12, MySQL 8.0.