Objective:
Store, retrieve, and manipulate data efficiently in databases using Python, leveraging both direct SQL and Object-Relational Mapping (ORM) techniques.
Topics and Examples:
1. SQLite / MySQL / PostgreSQL Connection
Python supports multiple database types.
SQLite Example (built-in):
import sqlite3
# Connect to SQLite database
conn = sqlite3.connect("example.db")
cursor = conn.cursor()
# Create table
cursor.execute('''CREATE TABLE IF NOT EXISTS users
(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Chinmaya", 25))
conn.commit()
# Fetch data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()
MySQL / PostgreSQL Example (using SQLAlchemy):
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
# MySQL connection string: 'mysql+pymysql://username:password@host/dbname'
# PostgreSQL connection string: 'postgresql+psycopg2://username:password@host/dbname'
engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()
# Define table
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('age', Integer))
metadata.create_all(engine)
2. CRUD Operations
CRUD = Create, Read, Update, Delete
Example (SQLite):
# Create
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Rout", 26))
conn.commit()
# Read
cursor.execute("SELECT * FROM users WHERE age > ?", (20,))
print(cursor.fetchall())
# Update
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (27, "Rout"))
conn.commit()
# Delete
cursor.execute("DELETE FROM users WHERE name = ?", ("Rout",))
conn.commit()
3. ORM Basics with SQLAlchemy
ORM allows working with database tables as Python classes.
Example:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
# Create session
Session = sessionmaker(bind=engine)
session = Session()
# Create / Insert
new_user = User(name="Chinmaya", age=25)
session.add(new_user)
session.commit()
# Read
for user in session.query(User).all():
print(user.name, user.age)
# Update
user = session.query(User).filter_by(name="Chinmaya").first()
user.age = 26
session.commit()
# Delete
session.delete(user)
session.commit()
This section covers all essential Python database operations, including connecting to SQLite/MySQL/PostgreSQL, performing CRUD operations, and working with ORM using SQLAlchemy for clean, object-oriented database handling.
