使用搜索查询进行分页

SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 20;

-- 创建索引
CREATE INDEX idx_column_name ON table_name(column_name);

-- 使用索引进行查询
SELECT * FROM table_name WHERE column_name = 'value' ORDER BY id LIMIT 10 OFFSET 20;

-- 基于游标的分页
SELECT * FROM table_name WHERE id > last_seen_id ORDER BY id LIMIT 10;

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

def get_paginated_items(page, per_page):
    items = session.query(Item).order_by(Item.id).offset((page - 1) * per_page).limit(per_page).all()
    return items

# 获取第2页，每页10条数据
items = get_paginated_items(2, 10)
for item in items:
    print(item.name)