Basics

Overview

pylite-orm is a lightweight and relatively new SQLite ORM library for Python. It provides developers with concise and intuitive interfaces for SQLite operations.

Given that there are already many excellent Python ORMs (such as SQLAlchemy, Tortoise-ORM, Peewee), why do we still need pylite-orm ?

Because in many scenarios — desktop software, mobile applications, small CMSs, embedded systems, automation scripts — using a single-file database like SQLite with those ORMs can feel too heavy. pylite-orm offers a simple API and minimal memory footprint — it has only a small amount of syntax, and the entire library is just 70KB in size. These are the core reasons and key advantages behind the existence of pylite-orm.

Features of pylite-orm

  • Simple and easy to use, quick to get started
  • Small codebase, low memory usage
  • Support high-speed reading of tens of millions of data records
  • Support database transactions
  • Support multiple database connections
  • Support multi-table queries and relational associations (one-to-one, one-to-many)
  • Support raw SQL queries
  • Support filling query results into Python objects, lists, dictionaries, and popular models such as Pydantic models
  • Built-in data migration tool, production-ready
  • Zero dependencies. No external dependencies other than Python's built-in libraries (such as sqlite3)

If you are familiar with SQLAlchemy/SQLModel, Peewee, or other popular Python ORMs, you can start using pylite-orm immediately with zero learning cost.

If you are new to ORMs, pylite-orm allows you to deliver code quickly, and in the future, mastering other ORMs will become even easier.

Install pylite-orm

pip install pylite-orm

Getting Started Examples

1.Define Data Model

from pylite_orm import  DbModel, DbField, DbType
from datetime import datetime

class User(DbModel):
    id = DbField(db_type = DbType.INT, pk = True)
    name = DbField(db_type = DbType.TEXT)
    age = DbField(db_type = DbType.INT)
    created_at = DbField(db_type = DbType.TEXT, default_factory = datetime.now)

2.Create Table Using Migration Commands

# This is a set of operations under the command line prompt.
lite-migr init --db mydb.db
lite-migr create user_table
lite-migr upgrade

3.Connect to Database

from pylite_orm import DbConn, DbSession

my_db = DbConn('mydb.db')
dbs = DbSession(my_db)

4.Insert Data

user_info = User(name = 'Tom', age = 25)
dbs.insert(User).item(user_info).exec()

5.Query Data

# Single Query
user: User = dbs.select(User).filter(User.id == 1).first()

# Multiple Query
users: list[User] = dbs.select(User).filter(User.age > 20).all()

# Multiple Query (Populate to Dictionary)
users_dicts: list[dict] = dbs.select(User).filter(User.age > 20).serial()

6.Update Data

user.age = 35
data = user.asdict(exec_unset = True) # only modified fields
dbs.update(User).item(data).filter(User.id == user.id).exec()

7.Delete Data

dbs.delete(User).filter(User.id == user.id).exec()

It's that simple!

For project development-level, more complex queries and operations, please read the operation guide.

Environment Requirements

  • Python 3.11+
  • sqlite 3.50+

💡If you are using the built-in migration tool of pylite-orm to create and maintain the database and table, you do not need to worry about the version of sqlite. If you are using other tools to create and maintain the database, please ensure that the version is at least as the minimum required version.