<p align="center">
<img src="docs/images/logo.png" alt="SQLActive" />
</p>
<p align="center">
<a href="https://pypi.org/project/sqlactive" target="_blank">
<img src="https://img.shields.io/pypi/pyversions/sqlactive" alt="Supported Python versions">
</a>
<a href="https://pypi.org/project/sqlactive" target="_blank">
<img src="https://img.shields.io/pypi/v/sqlactive" alt="Package version">
</a>
<a href="https://pypi.org/project/SQLAlchemy" target="_blank">
<img src="https://img.shields.io/badge/SQLAlchemy-2.0%2B-orange" alt="Supported SQLAlchemy versions">
</a>
<a href="https://github.com/daireto/sqlactive/actions" target="_blank">
<img src="https://github.com/daireto/sqlactive/actions/workflows/publish.yml/badge.svg" alt="Publish">
</a>
<a href='https://coveralls.io/github/daireto/sqlactive?branch=main'>
<img src='https://coveralls.io/repos/github/daireto/sqlactive/badge.svg?branch=main' alt='Coverage Status' />
</a>
<a href="/LICENSE" target="_blank">
<img src="https://img.shields.io/badge/License-MIT-green" alt="License">
</a>
</p>
<!-- omit in toc -->
# SQLActive
SQLActive is a lightweight and asynchronous ActiveRecord-style wrapper for
SQLAlchemy. Bring Django-like queries, automatic timestamps, nested eager
loading, and serialization/deserialization for SQLAlchemy models.
Heavily inspired by
[sqlalchemy-mixins](https://github.com/absent1706/sqlalchemy-mixins/).
Visit the [documentation website](https://daireto.github.io/sqlactive/).
<!-- omit in toc -->
## Table of Contents
- [Features](#features)
- [Requirements](#requirements)
- [Installation](#installation)
- [Usage](#usage)
- [1. Define the Models](#1-define-the-models)
- [2. Initialize the Database](#2-initialize-the-database)
- [3. Perform CRUD Operations](#3-perform-crud-operations)
- [4. Perform Bulk Operations](#4-perform-bulk-operations)
- [5. Perform Queries](#5-perform-queries)
- [6. Manage Timestamps](#6-manage-timestamps)
- [7. Serialization and Deserialization](#7-serialization-and-deserialization)
- [Testing and Linting](#testing-and-linting)
- [Unit Tests](#unit-tests)
- [Coverage](#coverage)
- [Linting](#linting)
- [Documentation](#documentation)
- [Contributing](#contributing)
- [License](#license)
- [Support](#support)
## Features
- **Asynchronous Support**: Async operations for better scalability.
- **ActiveRecord-like methods**: Perform CRUD operations with a syntax similar
to [Peewee](https://docs.peewee-orm.com/en/latest/).
- **Django-like queries**: Perform intuitive and
[expressive queries](https://docs.djangoproject.com/en/1.10/topics/db/queries/#lookups-that-span-relationships).
- **Nested eager loading**: Load nested relationships efficiently.
- **Automatic timestamps**: Auto-manage `created_at` and `updated_at` fields.
- **Serialization/deserialization**: Serialize and deserialize models to/from
dict or JSON easily.
## Requirements
- Python 3.10+
- sqlalchemy 2.0+
## Installation
You can simply install sqlactive from
[PyPI](https://pypi.org/project/sqlactive/):
```bash
pip install sqlactive
```
## Usage
### 1. Define the Models
The `ActiveRecordBaseModel` class provides a base class for your models.
It inherits from:
* [`ActiveRecordMixin`](https://daireto.github.io/sqlactive/api/active-record-mixin/):
Provides a set of ActiveRecord-like helper methods for interacting with
the database.
* [`TimestampMixin`](https://daireto.github.io/sqlactive/api/timestamp-mixin/):
Adds the `created_at` and `updated_at` timestamp columns.
* [`SerializationMixin`](https://daireto.github.io/sqlactive/api/serialization-mixin/):
Provides serialization and deserialization methods.
It is recommended to define a `BaseModel` class that inherits from
`ActiveRecordBaseModel` and use it as the base class for all models
as shown in the following example:
```python
from sqlalchemy import String, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlactive import ActiveRecordBaseModel
# Define a base class for your models (recommended)
class BaseModel(ActiveRecordBaseModel):
__abstract__ = True
# Define the models
class User(BaseModel):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)
username: Mapped[str] = mapped_column(String(18), nullable=False, unique=True)
name: Mapped[str] = mapped_column(String(50), nullable=False)
age: Mapped[int] = mapped_column(nullable=False)
posts: Mapped[list['Post']] = relationship(back_populates='user')
comments: Mapped[list['Comment']] = relationship(back_populates='user')
class Post(BaseModel):
__tablename__ = 'posts'
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)
title: Mapped[str] = mapped_column(String(100), nullable=False)
body: Mapped[str] = mapped_column(nullable=False)
rating: Mapped[int] = mapped_column(nullable=False)
user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
user: Mapped['User'] = relationship(back_populates='posts')
comments: Mapped[list['Comment']] = relationship(back_populates='post')
class Comment(BaseModel):
__tablename__ = 'comments'
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)
body: Mapped[str] = mapped_column(nullable=False)
post_id: Mapped[int] = mapped_column(ForeignKey('posts.id'))
user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
post: Mapped['Post'] = relationship(back_populates='comments')
user: Mapped['User'] = relationship(back_populates='comments')
```
> [!WARNING]
> When defining a `BaseModel` class, don't forget to set `__abstract__` to
> `True` in the base class to avoid creating tables for the base class.
> [!TIP]
> The models can directly inherit from the `ActiveRecordBaseModel` class:
> ```python
> from sqlactive import ActiveRecordBaseModel
>
> class User(ActiveRecordBaseModel):
> __tablename__ = 'users'
> # ...
> ```
> However, it is recommended to define a base model class for your models and
> inherit from it.
>
> Your base model class can also inherit directly from the mixins.
> For example, if you don't want to implement automatic timestamps don't inherit
> from `ActiveRecordBaseModel` class. Instead, inherit from `ActiveRecordMixin`
> and/or `SerializationMixin`:
> ```python
> from sqlactive import ActiveRecordMixin, SerializationMixin
>
> class BaseModel(ActiveRecordMixin, SerializationMixin):
> __abstract__ = True
> ```
### 2. Initialize the Database
```python
from asyncio import current_task
from sqlalchemy.ext.asyncio import (
create_async_engine,
async_sessionmaker,
async_scoped_session,
)
from sqlactive import ActiveRecordBaseModel
# Connect to the database
DATABASE_URL = 'sqlite+aiosqlite://'
async_engine = create_async_engine(DATABASE_URL, echo=False)
async_sessionmaker = async_sessionmaker(
bind=async_engine,
expire_on_commit=False,
)
async_scoped_session = async_scoped_session(
async_sessionmaker,
scopefunc=current_task,
)
# Set the session
BaseModel.set_session(async_scoped_session)
# Initialize the tables
async with async_engine.begin() as conn:
await conn.run_sync(BaseModel.metadata.create_all)
```
The use of the `expire_on_commit` flag is explained in the warning of
[this section](#4-perform-bulk-operations).
> [!TIP]
> Use the `DBConnection` class as a shortcut to initialize the database.
> The `DBConnection` class is a wrapper around the `async_engine`,
> `async_sessionmaker` and `async_scoped_session` objects:
> ```python
> from sqlactive import DBConnection
>
> DATABASE_URL = 'sqlite+aiosqlite://'
> conn = DBConnection(DATABASE_URL, echo=False)
> await conn.init_db(BaseModel)
> ```
> Check the documentation of
> [DB Connection Helper](https://daireto.github.io/sqlactive/api/db-connection-helper/)
> for more information.
### 3. Perform CRUD Operations
```python
user = await User.insert(username='John1234', name='John Doe', age=25)
user # <User #1>
user.name = 'Johnny Doe'
user.age = 30
await user.save()
user.name # Johnny Doe
user = await User.get(1)
user # <User #1>
await user.update(name='John Doe', age=20)
user.age # 20
await user.delete()
```
> [!CAUTION]
> The `delete()` and `remove()` methods are not soft deletes methods.
> Both of them will permanently delete the row from the database.
> So, if you want to keep the row in the database, you can implement
> a custom soft delete method, i.e. using `save()` method to update
> the row with a flag indicating if the row is deleted or not
> (i.e. a boolean `is_deleted` column).
> [!TIP]
> If you need to create a record for a short period of time, you can use the
> `with` statement:
> ```python
> with User(name='Bob', age=30) as user:
> ...
> ```
> The `with` statement will create the record and delete it at the end of the
> block.
>
> Check the [Temporary Records documentation](https://daireto.github.io/sqlactive/api/active-record-mixin/#temporary-records)
> for more information.
>
> Also, check the [Active Record Mixin API Reference](https://daireto.github.io/sqlactive/api/active-record-mixin/#api-reference)
> to see all the available methods.
### 4. Perform Bulk Operations
```python
users = [
User(username='John1234', name='John Doe', age=20),
User(username='Jane1234', name='Jane Doe', age=21),
User(username='Bob1234', name='Bob Doe', age=22),
]
await User.insert_all(users)
users = await User.find(username__endswith='Doe').all()
users # [<User #1>, <User #2>]
await User.delete_all(users)
users = await User.find(username__endswith='Doe').all()
users # []
```
> [!TIP]
> Check the [Active Record Mixin API Reference](https://daireto.github.io/sqlactive/api/active-record-mixin/#api-reference)
> to see all the available methods.
### 5. Perform Queries
Perform simple and complex queries with eager loading:
```python
from sqlactive import JOINED, SUBQUERY
user = await User.where(name='John Doe').first()
user # <User #1>
posts = await Post.where(rating__in=[2, 3, 4], user___name__like='%Bi%').all()
posts # [<Post #1>, <Post #2>, <Post #3>]
posts = await Post.sort('-rating', 'user___name').all()
posts # [<Post #3>, <Post #1>, <Post #2>, <Post #4>, ...]
comments = await Comment.join(Comment.user, Comment.post).unique_all()
comments # [<Comment 1>, <Comment 2>, <Comment 3>, <Comment 4>, <Comment 5>, ...]
user = await User.with_subquery(User.posts).first()
user # <User #1>
user.posts # [<Post #1>, <Post #2>, <Post #3>]
schema = {
User.posts: JOINED,
User.comments: (SUBQUERY, {
Comment.post: SELECT_IN
}),
}
user = await User.with_schema(schema).unique_first()
user.comments[0].post.title # Lorem ipsum
```
> [!WARNING]
> All relations used in filtering/sorting/grouping should be explicitly set,
> not just being a `backref`.
> See the [About Relationships](https://daireto.github.io/sqlactive/api/active-record-mixin/#about-relationships) section for more information.
> [!TIP]
> Check the [Active Record Mixin API Reference](https://daireto.github.io/sqlactive/api/active-record-mixin/#api-reference)
> to see all the available methods.
For more flexibility, the low-level
[`filter_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#filter_expr),
[`order_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#order_expr),
[`column_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#columns_expr)
and [`eager_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#eager_expr)
methods can be used.
**Example of `filter_expr()` method usage**
> ```python
> Post.filter(*Post.filter_expr(rating__gt=2, body='text'))
> # or
> session.query(Post).filter(*Post.filter_expr(rating__gt=2, body='text'))
> ```
> It's like [filter in SQLALchemy](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.filter),
> but also allows magic operators like `rating__gt`.
> [!IMPORTANT]
> Low-level `filter_expr()`, `order_expr()`, `column_expr()` and
> `eager_expr()` methods are very low-level and does NOT do magic
> Django-like joins. Use `smart_query()` for that:
> ```python
> query = User.smart_query(
> criterion=(or_(User.age == 30, User.age == 32),),
> filters={'username__like': '%8'},
> sort_columns=(User.username,),
> sort_attrs=('-created_at',),
> group_columns=(User.username,),
> group_attrs=['age'],
> schema={
> User.posts: JOINED,
> User.comments: (SUBQUERY, {
> Comment.post: SELECT_IN
> }),
> },
> )
> ```
> [!TIP]
> Check the [Smart Query Mixin API Reference](https://daireto.github.io/sqlactive/api/smart-query-mixin/#api-reference)
> for more details about the `smart_query()` method and the low-level methods.
To perform native SQLAlchemy queries asynchronously,
you can use the `execute()` method:
```python
from sqlalchemy import select, func
from sqlactive import ActiveRecordBaseModel, execute
class BaseModel(ActiveRecordBaseModel):
__abstract__ = True
class User(BaseModel):
__tablename__ = 'users'
# ...
query = select(User.age, func.count(User.id)).group_by(User.age)
result = await execute(query, BaseModel)
# [(20, 1), (22, 4), (25, 12)]
```
See the [Native SQLAlchemy queries](https://daireto.github.io/sqlactive/api/native-sqlalchemy-queries/)
documentation for more information.
### 6. Manage Timestamps
Timestamps (`created_at` and `updated_at`) are automatically managed:
```python
user = await User.insert(username='John1234', name='John Doe', age=25)
user.created_at # 2024-12-28 23:00:51
user.updated_at # 2024-12-28 23:00:51
await asyncio.sleep(1)
await user.update(name='Johnny Doe')
user.updated_at # 2024-12-28 23:00:52
```
> [!TIP]
> Check the [`TimestampMixin`](https://daireto.github.io/sqlactive/api/timestamp-mixin/)
> class to know how to customize the timestamps behavior.
### 7. Serialization and Deserialization
Models can be serialized/deserialized to/from dictionaries using
the `to_dict()` and `from_dict()` methods:
```python
user = await User.insert(username='John1234', name='John Doe', age=25)
user_dict = user.to_dict()
user_dict # {'id': 1, 'username': 'John1234', 'name': 'John Doe', ...}
user = User.from_dict(user_dict)
user.name # John Doe
```
Also, models can be serialized/deserialized to/from JSON using
the `to_json()` and `from_json()` methods:
```python
user = await User.insert(username='John1234', name='John Doe', age=25)
user_json = user.to_json()
user_json # {"id": 1, "username": "John1234", "name": "John Doe", ...}
user = User.from_json(user_json)
user.name # John Doe
```
## Testing and Linting
### Unit Tests
To run the tests, simply run the following command from the root directory:
```bash
python -m unittest discover -s tests -t .
```
To run a specific test, use the following command:
```bash
python -m unittest tests.<test_name>
```
**Available tests**
- `test_active_record.py`
- `test_async_query.py`
- `test_db_connection.py`
- `test_execute.py`
- `test_inspection.py`
- `test_serialization.py`
- `test_smart_query.py`
### Coverage
First, install the `coverage` package:
```bash
pip install coverage
```
To check the coverage, run the following command:
```bash
python -m coverage run -m unittest discover -s tests -t .
```
To generate the coverage report, run the following command:
```bash
python -m coverage report -m
```
To generate the HTML report, run the following command:
```bash
python -m coverage html -d htmlcov
```
### Linting
First, install the `ruff` package:
```bash
pip install ruff
```
To check the code style, run the following command:
```bash
python -m ruff check .
```
## Documentation
Find the complete documentation [here](https://daireto.github.io/sqlactive/).
## Contributing
Please read the [contribution guidelines](CONTRIBUTING.md).
## License
This project is licensed under the [MIT License](LICENSE).
## Support
If you find this project useful, give it a ⭐ on GitHub to show your support!
Also, give it a ⭐ to
[sqlalchemy-mixins](https://github.com/absent1706/sqlalchemy-mixins/)
which inspired this project!
Raw data
{
"_id": null,
"home_page": null,
"name": "sqlactive",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "sqlalchemy, active record, activerecord, orm, django-like, django queries, eager load, timestamps, serialization",
"author": null,
"author_email": "Dairo Mosquera <dairoandres115@outlook.com>",
"download_url": "https://files.pythonhosted.org/packages/1b/e2/13e2b476334ccee719f20afdbeb3e3dbb59a3a92567fdefb05188d235c09/sqlactive-0.3.0.tar.gz",
"platform": null,
"description": "<p align=\"center\">\n <img src=\"docs/images/logo.png\" alt=\"SQLActive\" />\n</p>\n\n<p align=\"center\">\n <a href=\"https://pypi.org/project/sqlactive\" target=\"_blank\">\n <img src=\"https://img.shields.io/pypi/pyversions/sqlactive\" alt=\"Supported Python versions\">\n </a>\n <a href=\"https://pypi.org/project/sqlactive\" target=\"_blank\">\n <img src=\"https://img.shields.io/pypi/v/sqlactive\" alt=\"Package version\">\n </a>\n <a href=\"https://pypi.org/project/SQLAlchemy\" target=\"_blank\">\n <img src=\"https://img.shields.io/badge/SQLAlchemy-2.0%2B-orange\" alt=\"Supported SQLAlchemy versions\">\n </a>\n <a href=\"https://github.com/daireto/sqlactive/actions\" target=\"_blank\">\n <img src=\"https://github.com/daireto/sqlactive/actions/workflows/publish.yml/badge.svg\" alt=\"Publish\">\n </a>\n <a href='https://coveralls.io/github/daireto/sqlactive?branch=main'>\n <img src='https://coveralls.io/repos/github/daireto/sqlactive/badge.svg?branch=main' alt='Coverage Status' />\n </a>\n <a href=\"/LICENSE\" target=\"_blank\">\n <img src=\"https://img.shields.io/badge/License-MIT-green\" alt=\"License\">\n </a>\n</p>\n\n<!-- omit in toc -->\n# SQLActive\n\nSQLActive is a lightweight and asynchronous ActiveRecord-style wrapper for\nSQLAlchemy. Bring Django-like queries, automatic timestamps, nested eager\nloading, and serialization/deserialization for SQLAlchemy models.\n\nHeavily inspired by\n[sqlalchemy-mixins](https://github.com/absent1706/sqlalchemy-mixins/).\n\nVisit the [documentation website](https://daireto.github.io/sqlactive/).\n\n<!-- omit in toc -->\n## Table of Contents\n- [Features](#features)\n- [Requirements](#requirements)\n- [Installation](#installation)\n- [Usage](#usage)\n - [1. Define the Models](#1-define-the-models)\n - [2. Initialize the Database](#2-initialize-the-database)\n - [3. Perform CRUD Operations](#3-perform-crud-operations)\n - [4. Perform Bulk Operations](#4-perform-bulk-operations)\n - [5. Perform Queries](#5-perform-queries)\n - [6. Manage Timestamps](#6-manage-timestamps)\n - [7. Serialization and Deserialization](#7-serialization-and-deserialization)\n- [Testing and Linting](#testing-and-linting)\n - [Unit Tests](#unit-tests)\n - [Coverage](#coverage)\n - [Linting](#linting)\n- [Documentation](#documentation)\n- [Contributing](#contributing)\n- [License](#license)\n- [Support](#support)\n\n## Features\n\n- **Asynchronous Support**: Async operations for better scalability.\n- **ActiveRecord-like methods**: Perform CRUD operations with a syntax similar\n to [Peewee](https://docs.peewee-orm.com/en/latest/).\n- **Django-like queries**: Perform intuitive and\n [expressive queries](https://docs.djangoproject.com/en/1.10/topics/db/queries/#lookups-that-span-relationships).\n- **Nested eager loading**: Load nested relationships efficiently.\n- **Automatic timestamps**: Auto-manage `created_at` and `updated_at` fields.\n- **Serialization/deserialization**: Serialize and deserialize models to/from\n dict or JSON easily.\n\n## Requirements\n\n- Python 3.10+\n- sqlalchemy 2.0+\n\n## Installation\n\nYou can simply install sqlactive from\n[PyPI](https://pypi.org/project/sqlactive/):\n\n```bash\npip install sqlactive\n```\n\n## Usage\n\n### 1. Define the Models\n\nThe `ActiveRecordBaseModel` class provides a base class for your models.\n\nIt inherits from:\n\n* [`ActiveRecordMixin`](https://daireto.github.io/sqlactive/api/active-record-mixin/):\n Provides a set of ActiveRecord-like helper methods for interacting with\n the database.\n* [`TimestampMixin`](https://daireto.github.io/sqlactive/api/timestamp-mixin/):\n Adds the `created_at` and `updated_at` timestamp columns.\n* [`SerializationMixin`](https://daireto.github.io/sqlactive/api/serialization-mixin/):\n Provides serialization and deserialization methods.\n\nIt is recommended to define a `BaseModel` class that inherits from\n`ActiveRecordBaseModel` and use it as the base class for all models\nas shown in the following example:\n\n```python\nfrom sqlalchemy import String, ForeignKey\nfrom sqlalchemy.orm import Mapped, mapped_column, relationship\nfrom sqlactive import ActiveRecordBaseModel\n\n\n# Define a base class for your models (recommended)\nclass BaseModel(ActiveRecordBaseModel):\n __abstract__ = True\n\n\n# Define the models\nclass User(BaseModel):\n __tablename__ = 'users'\n\n id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)\n username: Mapped[str] = mapped_column(String(18), nullable=False, unique=True)\n name: Mapped[str] = mapped_column(String(50), nullable=False)\n age: Mapped[int] = mapped_column(nullable=False)\n\n posts: Mapped[list['Post']] = relationship(back_populates='user')\n comments: Mapped[list['Comment']] = relationship(back_populates='user')\n\n\nclass Post(BaseModel):\n __tablename__ = 'posts'\n\n id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)\n title: Mapped[str] = mapped_column(String(100), nullable=False)\n body: Mapped[str] = mapped_column(nullable=False)\n rating: Mapped[int] = mapped_column(nullable=False)\n user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))\n\n user: Mapped['User'] = relationship(back_populates='posts')\n comments: Mapped[list['Comment']] = relationship(back_populates='post')\n\n\nclass Comment(BaseModel):\n __tablename__ = 'comments'\n\n id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, index=True)\n body: Mapped[str] = mapped_column(nullable=False)\n post_id: Mapped[int] = mapped_column(ForeignKey('posts.id'))\n user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))\n\n post: Mapped['Post'] = relationship(back_populates='comments')\n user: Mapped['User'] = relationship(back_populates='comments')\n```\n\n> [!WARNING]\n> When defining a `BaseModel` class, don't forget to set `__abstract__` to\n> `True` in the base class to avoid creating tables for the base class.\n\n> [!TIP]\n> The models can directly inherit from the `ActiveRecordBaseModel` class:\n> ```python\n> from sqlactive import ActiveRecordBaseModel\n>\n> class User(ActiveRecordBaseModel):\n> __tablename__ = 'users'\n> # ...\n> ```\n> However, it is recommended to define a base model class for your models and\n> inherit from it.\n>\n> Your base model class can also inherit directly from the mixins.\n> For example, if you don't want to implement automatic timestamps don't inherit\n> from `ActiveRecordBaseModel` class. Instead, inherit from `ActiveRecordMixin`\n> and/or `SerializationMixin`:\n> ```python\n> from sqlactive import ActiveRecordMixin, SerializationMixin\n>\n> class BaseModel(ActiveRecordMixin, SerializationMixin):\n> __abstract__ = True\n> ```\n\n### 2. Initialize the Database\n\n```python\nfrom asyncio import current_task\nfrom sqlalchemy.ext.asyncio import (\n create_async_engine,\n async_sessionmaker,\n async_scoped_session,\n)\nfrom sqlactive import ActiveRecordBaseModel\n\n# Connect to the database\nDATABASE_URL = 'sqlite+aiosqlite://'\nasync_engine = create_async_engine(DATABASE_URL, echo=False)\nasync_sessionmaker = async_sessionmaker(\n bind=async_engine,\n expire_on_commit=False,\n)\nasync_scoped_session = async_scoped_session(\n async_sessionmaker,\n scopefunc=current_task,\n)\n\n# Set the session\nBaseModel.set_session(async_scoped_session)\n\n# Initialize the tables\nasync with async_engine.begin() as conn:\n await conn.run_sync(BaseModel.metadata.create_all)\n```\n\nThe use of the `expire_on_commit` flag is explained in the warning of\n[this section](#4-perform-bulk-operations).\n\n> [!TIP]\n> Use the `DBConnection` class as a shortcut to initialize the database.\n> The `DBConnection` class is a wrapper around the `async_engine`,\n> `async_sessionmaker` and `async_scoped_session` objects:\n> ```python\n> from sqlactive import DBConnection\n>\n> DATABASE_URL = 'sqlite+aiosqlite://'\n> conn = DBConnection(DATABASE_URL, echo=False)\n> await conn.init_db(BaseModel)\n> ```\n> Check the documentation of\n> [DB Connection Helper](https://daireto.github.io/sqlactive/api/db-connection-helper/)\n> for more information.\n\n### 3. Perform CRUD Operations\n\n```python\nuser = await User.insert(username='John1234', name='John Doe', age=25)\nuser # <User #1>\n\nuser.name = 'Johnny Doe'\nuser.age = 30\nawait user.save()\nuser.name # Johnny Doe\n\nuser = await User.get(1)\nuser # <User #1>\n\nawait user.update(name='John Doe', age=20)\nuser.age # 20\n\nawait user.delete()\n```\n\n> [!CAUTION]\n> The `delete()` and `remove()` methods are not soft deletes methods.\n> Both of them will permanently delete the row from the database.\n> So, if you want to keep the row in the database, you can implement\n> a custom soft delete method, i.e. using `save()` method to update\n> the row with a flag indicating if the row is deleted or not\n> (i.e. a boolean `is_deleted` column).\n\n> [!TIP]\n> If you need to create a record for a short period of time, you can use the\n> `with` statement:\n> ```python\n> with User(name='Bob', age=30) as user:\n> ...\n> ```\n> The `with` statement will create the record and delete it at the end of the\n> block.\n>\n> Check the [Temporary Records documentation](https://daireto.github.io/sqlactive/api/active-record-mixin/#temporary-records)\n> for more information.\n>\n> Also, check the [Active Record Mixin API Reference](https://daireto.github.io/sqlactive/api/active-record-mixin/#api-reference)\n> to see all the available methods.\n\n### 4. Perform Bulk Operations\n\n```python\nusers = [\n User(username='John1234', name='John Doe', age=20),\n User(username='Jane1234', name='Jane Doe', age=21),\n User(username='Bob1234', name='Bob Doe', age=22),\n]\n\nawait User.insert_all(users)\nusers = await User.find(username__endswith='Doe').all()\nusers # [<User #1>, <User #2>]\n\nawait User.delete_all(users)\n\nusers = await User.find(username__endswith='Doe').all()\nusers # []\n```\n\n> [!TIP]\n> Check the [Active Record Mixin API Reference](https://daireto.github.io/sqlactive/api/active-record-mixin/#api-reference)\n> to see all the available methods.\n\n### 5. Perform Queries\n\nPerform simple and complex queries with eager loading:\n\n```python\nfrom sqlactive import JOINED, SUBQUERY\n\nuser = await User.where(name='John Doe').first()\nuser # <User #1>\n\nposts = await Post.where(rating__in=[2, 3, 4], user___name__like='%Bi%').all()\nposts # [<Post #1>, <Post #2>, <Post #3>]\n\nposts = await Post.sort('-rating', 'user___name').all()\nposts # [<Post #3>, <Post #1>, <Post #2>, <Post #4>, ...]\n\ncomments = await Comment.join(Comment.user, Comment.post).unique_all()\ncomments # [<Comment 1>, <Comment 2>, <Comment 3>, <Comment 4>, <Comment 5>, ...]\n\nuser = await User.with_subquery(User.posts).first()\nuser # <User #1>\nuser.posts # [<Post #1>, <Post #2>, <Post #3>]\n\nschema = {\n User.posts: JOINED,\n User.comments: (SUBQUERY, {\n Comment.post: SELECT_IN\n }),\n}\nuser = await User.with_schema(schema).unique_first()\nuser.comments[0].post.title # Lorem ipsum\n```\n\n> [!WARNING]\n> All relations used in filtering/sorting/grouping should be explicitly set,\n> not just being a `backref`.\n> See the [About Relationships](https://daireto.github.io/sqlactive/api/active-record-mixin/#about-relationships) section for more information.\n\n> [!TIP]\n> Check the [Active Record Mixin API Reference](https://daireto.github.io/sqlactive/api/active-record-mixin/#api-reference)\n> to see all the available methods.\n\nFor more flexibility, the low-level\n[`filter_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#filter_expr),\n[`order_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#order_expr),\n[`column_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#columns_expr)\nand [`eager_expr()`](https://daireto.github.io/sqlactive/api/smart-query-mixin/#eager_expr)\nmethods can be used.\n\n**Example of `filter_expr()` method usage**\n\n> ```python\n> Post.filter(*Post.filter_expr(rating__gt=2, body='text'))\n> # or\n> session.query(Post).filter(*Post.filter_expr(rating__gt=2, body='text'))\n> ```\n\n> It's like [filter in SQLALchemy](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.filter),\n> but also allows magic operators like `rating__gt`.\n\n> [!IMPORTANT]\n> Low-level `filter_expr()`, `order_expr()`, `column_expr()` and\n> `eager_expr()` methods are very low-level and does NOT do magic\n> Django-like joins. Use `smart_query()` for that:\n> ```python\n> query = User.smart_query(\n> criterion=(or_(User.age == 30, User.age == 32),),\n> filters={'username__like': '%8'},\n> sort_columns=(User.username,),\n> sort_attrs=('-created_at',),\n> group_columns=(User.username,),\n> group_attrs=['age'],\n> schema={\n> User.posts: JOINED,\n> User.comments: (SUBQUERY, {\n> Comment.post: SELECT_IN\n> }),\n> },\n> )\n> ```\n\n> [!TIP]\n> Check the [Smart Query Mixin API Reference](https://daireto.github.io/sqlactive/api/smart-query-mixin/#api-reference)\n> for more details about the `smart_query()` method and the low-level methods.\n\nTo perform native SQLAlchemy queries asynchronously,\nyou can use the `execute()` method:\n\n```python\nfrom sqlalchemy import select, func\nfrom sqlactive import ActiveRecordBaseModel, execute\n\nclass BaseModel(ActiveRecordBaseModel):\n __abstract__ = True\n\nclass User(BaseModel):\n __tablename__ = 'users'\n # ...\n\nquery = select(User.age, func.count(User.id)).group_by(User.age)\nresult = await execute(query, BaseModel)\n# [(20, 1), (22, 4), (25, 12)]\n```\n\nSee the [Native SQLAlchemy queries](https://daireto.github.io/sqlactive/api/native-sqlalchemy-queries/)\ndocumentation for more information.\n\n### 6. Manage Timestamps\n\nTimestamps (`created_at` and `updated_at`) are automatically managed:\n\n```python\nuser = await User.insert(username='John1234', name='John Doe', age=25)\nuser.created_at # 2024-12-28 23:00:51\nuser.updated_at # 2024-12-28 23:00:51\n\nawait asyncio.sleep(1)\n\nawait user.update(name='Johnny Doe')\nuser.updated_at # 2024-12-28 23:00:52\n```\n\n> [!TIP]\n> Check the [`TimestampMixin`](https://daireto.github.io/sqlactive/api/timestamp-mixin/)\n> class to know how to customize the timestamps behavior.\n\n### 7. Serialization and Deserialization\n\nModels can be serialized/deserialized to/from dictionaries using\nthe `to_dict()` and `from_dict()` methods:\n\n```python\nuser = await User.insert(username='John1234', name='John Doe', age=25)\nuser_dict = user.to_dict()\nuser_dict # {'id': 1, 'username': 'John1234', 'name': 'John Doe', ...}\n\nuser = User.from_dict(user_dict)\nuser.name # John Doe\n```\n\nAlso, models can be serialized/deserialized to/from JSON using\nthe `to_json()` and `from_json()` methods:\n\n```python\nuser = await User.insert(username='John1234', name='John Doe', age=25)\nuser_json = user.to_json()\nuser_json # {\"id\": 1, \"username\": \"John1234\", \"name\": \"John Doe\", ...}\n\nuser = User.from_json(user_json)\nuser.name # John Doe\n```\n\n## Testing and Linting\n\n### Unit Tests\n\nTo run the tests, simply run the following command from the root directory:\n\n```bash\npython -m unittest discover -s tests -t .\n```\n\nTo run a specific test, use the following command:\n\n```bash\npython -m unittest tests.<test_name>\n```\n\n**Available tests**\n- `test_active_record.py`\n- `test_async_query.py`\n- `test_db_connection.py`\n- `test_execute.py`\n- `test_inspection.py`\n- `test_serialization.py`\n- `test_smart_query.py`\n\n### Coverage\n\nFirst, install the `coverage` package:\n\n```bash\npip install coverage\n```\n\nTo check the coverage, run the following command:\n\n```bash\npython -m coverage run -m unittest discover -s tests -t .\n```\n\nTo generate the coverage report, run the following command:\n\n```bash\npython -m coverage report -m\n```\n\nTo generate the HTML report, run the following command:\n\n```bash\npython -m coverage html -d htmlcov\n```\n\n### Linting\n\nFirst, install the `ruff` package:\n\n```bash\npip install ruff\n```\n\nTo check the code style, run the following command:\n\n```bash\npython -m ruff check .\n```\n\n## Documentation\n\nFind the complete documentation [here](https://daireto.github.io/sqlactive/).\n\n## Contributing\n\nPlease read the [contribution guidelines](CONTRIBUTING.md).\n\n## License\n\nThis project is licensed under the [MIT License](LICENSE).\n\n## Support\n\nIf you find this project useful, give it a \u2b50 on GitHub to show your support!\n\nAlso, give it a \u2b50 to\n[sqlalchemy-mixins](https://github.com/absent1706/sqlalchemy-mixins/)\nwhich inspired this project!\n",
"bugtrack_url": null,
"license": null,
"summary": "SQLActive is a lightweight and asynchronous ActiveRecord-style wrapper for SQLAlchemy. Bring Django-like queries, automatic timestamps, nested eager loading, and serialization/deserialization for SQLAlchemy models.",
"version": "0.3.0",
"project_urls": {
"Documentation": "https://daireto.github.io/sqlactive/",
"Homepage": "https://github.com/daireto/sqlactive",
"Issues": "https://github.com/daireto/sqlactive/issues",
"Repository": "https://github.com/daireto/sqlactive"
},
"split_keywords": [
"sqlalchemy",
" active record",
" activerecord",
" orm",
" django-like",
" django queries",
" eager load",
" timestamps",
" serialization"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "de06b40c6af6792abf5f905717b3ab041709fb6a38ca8a4188d9856788dffd55",
"md5": "a77fc880af3c5a1583785dcc6d7c7c7c",
"sha256": "80ae64e94594cb9e5a085daa6ddf3fbf8fe89632a811f3ba59f94105ec8002cf"
},
"downloads": -1,
"filename": "sqlactive-0.3.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "a77fc880af3c5a1583785dcc6d7c7c7c",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.10",
"size": 46804,
"upload_time": "2025-02-22T19:55:17",
"upload_time_iso_8601": "2025-02-22T19:55:17.478058Z",
"url": "https://files.pythonhosted.org/packages/de/06/b40c6af6792abf5f905717b3ab041709fb6a38ca8a4188d9856788dffd55/sqlactive-0.3.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "1be213e2b476334ccee719f20afdbeb3e3dbb59a3a92567fdefb05188d235c09",
"md5": "82c29c5eea4cd9c4e28e1f672492543f",
"sha256": "35a24575493f83ecc4dd869bbbae3c13ce56431e30a523540e8fe7c04282f955"
},
"downloads": -1,
"filename": "sqlactive-0.3.0.tar.gz",
"has_sig": false,
"md5_digest": "82c29c5eea4cd9c4e28e1f672492543f",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 55459,
"upload_time": "2025-02-22T19:55:19",
"upload_time_iso_8601": "2025-02-22T19:55:19.656975Z",
"url": "https://files.pythonhosted.org/packages/1b/e2/13e2b476334ccee719f20afdbeb3e3dbb59a3a92567fdefb05188d235c09/sqlactive-0.3.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-02-22 19:55:19",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "daireto",
"github_project": "sqlactive",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [
{
"name": "aiosqlite",
"specs": [
[
"==",
"0.20.0"
]
]
},
{
"name": "colorlog",
"specs": [
[
"==",
"6.9.0"
]
]
},
{
"name": "sqlalchemy",
"specs": [
[
"==",
"2.0.36"
]
]
}
],
"lcname": "sqlactive"
}
JSON
