Sqlalchemy

Introduction When implementing scheduled tasks in FastAPI applications, Celery is often the go-to choice. However, Celery is relatively heavyweight and requires dependencies like Redis or RabbitMQ as message brokers. For smaller-scale services that don’t already utilize Redis or RabbitMQ, introducing these dependencies solely for scheduled task functionality adds unnecessary operational overhead. Another popular Python scheduling framework is APScheduler, but it presents two significant challenges in practice: Duplicate Execution in Multi-Process Environments: When running with Uvicorn’s multi-process mode, each worker process independently executes scheduled tasks, leading to duplicate executions. Poor Asynchronous Function Compatibility: Although APScheduler provides AsyncIOScheduler, its support for asynchronous functions is incomplete. The official documentation explicitly states: “If you’re running an asynchronous web framework like aiohttp, you probably want to use a different scheduler in order to take some advantage of the asynchronous nature of the framework.” For APScheduler users facing these issues, potential solutions include: ...

Introduction Both SQLite and PostgreSQL support UPSERT operations, which means “update if exists, insert if not.” The conflict column must have a unique constraint. Syntax: PostgreSQL: INSERT ... ON CONFLICT (column) DO UPDATE/NOTHING SQLite: INSERT ... ON CONFLICT(column) DO UPDATE/NOTHING (note the parentheses position) Scenario PostgreSQL SQLite Notes Basic UPSERT ON CONFLICT (col) DO UPDATE SET ... ON CONFLICT(col) DO UPDATE SET ... Slight difference in parentheses placement Conflict Ignore ON CONFLICT (col) DO NOTHING ON CONFLICT(col) DO NOTHING Same syntax Reference New Values EXCLUDED.col excluded.col PostgreSQL uses uppercase, SQLite uses lowercase Return Results RETURNING * RETURNING * Same syntax Conditional Update WHERE condition WHERE not supported SQLite limitation Key Considerations The conflict column must have a unique constraint PostgreSQL and SQLite syntax is similar but has subtle differences. Pay attention when using raw SQL. SQLite does not support WHERE clauses in UPSERT operations; use CASE expressions or application-level filtering instead. SQLite version 3.35+ is required for RETURNING support. EXCLUDED and RETURNING EXCLUDED EXCLUDED represents the new values that were intercepted due to a conflict. ...