SQLite + SQLAlchemy from Scratch

from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, create_engine
from sqlalchemy.orm import declarative_base, relationship
from datetime import datetime

Base = declarative_base()

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True, nullable=False)
    email = Column(String)
    posts = relationship('Post', back_populates='author')

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    body = Column(String)
    rating = Column(Float, default=0.0)
    created_at = Column(DateTime, default=datetime.utcnow)
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship('Author', back_populates='posts')

engine = create_engine('sqlite:///blog.db', echo=True)
Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
from models import engine, Author, Post

Session = sessionmaker(bind=engine)
session = Session()

# Create
ada = Author(name='Ada', email='ada@x.com')
session.add(ada)
session.commit()  # writes to DB, ada.id is now set

post1 = Post(title='Hello', body='First post', author=ada)
post2 = Post(title='World', body='Second post', rating=4.5, author=ada)
session.add_all([post1, post2])
session.commit()

# Read
all_posts = session.query(Post).all()
print('all posts:', [p.title for p in all_posts])

# Filter
top = session.query(Post).filter(Post.rating >= 4).all()
print('top rated:', [p.title for p in top])

# Order + limit
recent = (session.query(Post)
          .order_by(Post.created_at.desc())
          .limit(5).all())

# Join via relationship
ada_posts = session.query(Author).filter_by(name='Ada').first().posts
print('ada posts:', [p.title for p in ada_posts])

# Update
post1.rating = 5.0
session.commit()

# Delete
session.delete(post2)
session.commit()

session.close()

pip install sqlalchemy

# Create tables
python models.py

# Run CRUD operations
python queries.py
# SQL queries are echoed to stdout (echo=True)

# Inspect the DB
sqlite3 blog.db
# sqlite> .tables
# authors  posts
# sqlite> SELECT * FROM authors;
# 1|Ada|ada@x.com