peewee

Peewee is a Python library that lets you work with databases without writing raw SQL. Instead of typing out database commands manually, you define your data as Python classes and Peewee translates your code into the right database instructions automatically. This approach is called an ORM, short for Object-Relational Mapper.

The library supports four popular databases: SQLite (which requires no extra setup and is built into Python), MySQL, MariaDB, and PostgreSQL. You can also run Peewee in async mode if your application needs to handle many tasks at the same time. Once installed, it fits naturally alongside web frameworks like Flask and FastAPI, as well as data validation tools like Pydantic.

Setting up a database table in Peewee means writing a short Python class. For example, a User table becomes a User class with fields like username. A Tweet table can reference User with a single line. After that, you call a connect step and a create tables step, and the database is ready to use. Creating, reading, filtering, and sorting records all use Python syntax rather than raw SQL strings, which reduces the chance of mistakes and makes the code easier to read.

Queries can be chained and combined. You can filter records by one or more conditions, join tables together, count results, paginate through large sets, or perform atomic updates that are safe against race conditions. The README includes working code samples for all of these patterns, plus links to a full example app modeled after Twitter.

Beyond the core library, Peewee ships with a wide set of extensions covering things like full-text search, encrypted databases via SQLCipher, and connection pooling. The author has also published blog posts showing Peewee used in real apps: note-taking tools, analytics services, password managers, and pastebins. Installation is a single pip command, and the documentation site covers every feature in depth.