SQLAlchemy Cheatsheet

Setup

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///chat.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

Defining Models

class Message(db.Model):
    __tablename__ = 'messages'

    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.String(500), nullable=False)
    sender = db.Column(db.String(20), nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)

Column Types

Column Options

db.Column(db.Integer, primary_key=True)        # Auto-increment ID
db.Column(db.String, nullable=False)            # Required field
db.Column(db.String, unique=True)               # No duplicates
db.Column(db.String, default='user')            # Default value
db.Column(db.DateTime, default=datetime.utcnow) # Auto timestamp

Create Tables

# In Python shell or startup
with app.app_context():
    db.create_all()

Create Records

msg = Message(content="Hello!", sender="user")
db.session.add(msg)
db.session.commit()
print(msg.id)  # Auto-assigned

Query Records

# All records
Message.query.all()

# Filter
Message.query.filter_by(sender='user').all()
Message.query.filter(Message.content.contains('hello')).all()

# Get by ID
Message.query.get(5)

# Ordering
Message.query.order_by(Message.timestamp.desc()).all()
Message.query.order_by(Message.timestamp.asc()).all()

# Limit
Message.query.limit(10).all()

# Count
Message.query.count()
Message.query.filter_by(sender='ai').count()

# First match
Message.query.first()
Message.query.filter_by(sender='user').first()

# Chain
Message.query.filter_by(sender='user').order_by(Message.timestamp.desc()).limit(5).all()

Update Records

msg = Message.query.get(1)
msg.content = "Updated text"
db.session.commit()

Delete Records

msg = Message.query.get(1)
db.session.delete(msg)
db.session.commit()

# Delete matching
Message.query.filter_by(sender='user').delete()
db.session.commit()

Convert to Dict (for JSON)

def to_dict(self):
    return {
        'id': self.id,
        'content': self.content,
        'sender': self.sender,
        'timestamp': self.timestamp.isoformat() if self.timestamp else None
    }