From 0c0228a0524fb3b29cfa3d5a051ef1c8547ca2fc Mon Sep 17 00:00:00 2001 From: Amin Alaee Date: Thu, 4 Nov 2021 09:55:34 +0100 Subject: [PATCH 1/4] filter by SQLAlchemy operators (#130) --- docs/making_queries.md | 17 +++++++++++++++++ orm/models.py | 28 ++++++++++++++++++++++++---- tests/test_models.py | 21 +++++++++++++++++++++ 3 files changed, 62 insertions(+), 4 deletions(-) diff --git a/docs/making_queries.md b/docs/making_queries.md index ab6093f..8f259a8 100644 --- a/docs/making_queries.md +++ b/docs/making_queries.md @@ -43,6 +43,8 @@ notes = await Note.objects.exclude(completed=False).all() ### .filter() +#### Django-style lookup + To filter instances: ```python @@ -69,6 +71,21 @@ notes = await Note.objects.filter(text__icontains="mum").all() notes = await Note.objects.filter(id__in=[1, 2, 3]).all() ``` +#### SQLAlchemy filter operators + +The `filter` method also accepts SQLAlchemy filter operators: + +```python +notes = await Note.objects.filter(Note.columns.text.contains("mum")).all() + +notes = await Note.objects.filter(Note.columns.id.in_([1, 2, 3])).all() +``` + +Here `Note.columns` refers to the columns of the underlying SQLAlchemy table. + +!!! note + Note that `Note.columns` returns SQLAlchemy table columns, whereas `Note.fields` returns `orm` fields. + ### .limit() To limit number of results: diff --git a/orm/models.py b/orm/models.py index 7684f13..f897e8a 100644 --- a/orm/models.py +++ b/orm/models.py @@ -96,6 +96,10 @@ def table(cls): cls._table = cls.build_table() return cls._table + @property + def columns(cls) -> sqlalchemy.sql.ColumnCollection: + return cls._table.columns + class QuerySet: ESCAPE_CHARACTERS = ["%", "_"] @@ -171,11 +175,27 @@ def build_select_expression(self): return expr - def filter(self, **kwargs): - return self._filter_query(**kwargs) + def filter( + self, + clause: typing.Optional[sqlalchemy.sql.expression.BinaryExpression] = None, + **kwargs: typing.Any, + ): + if clause is not None: + self.filter_clauses.append(clause) + return self + else: + return self._filter_query(**kwargs) - def exclude(self, **kwargs): - return self._filter_query(_exclude=True, **kwargs) + def exclude( + self, + clause: typing.Optional[sqlalchemy.sql.expression.BinaryExpression] = None, + **kwargs: typing.Any, + ): + if clause is not None: + self.filter_clauses.append(clause) + return self + else: + return self._filter_query(_exclude=True, **kwargs) def _filter_query(self, _exclude: bool = False, **kwargs): clauses = [] diff --git a/tests/test_models.py b/tests/test_models.py index f816e86..5961f65 100644 --- a/tests/test_models.py +++ b/tests/test_models.py @@ -312,3 +312,24 @@ async def test_model_update_or_create(): assert created is False assert user.name == "Tom" assert user.language == "English" + + +async def test_model_sqlalchemy_filter_operators(): + user = await User.objects.create(name="George") + + assert user == await User.objects.filter(User.columns.name == "George").get() + assert user == await User.objects.filter(User.columns.name.is_not(None)).get() + assert ( + user + == await User.objects.filter(User.columns.name.startswith("G")) + .filter(User.columns.name.endswith("e")) + .get() + ) + + assert user == await User.objects.exclude(User.columns.name != "Jack").get() + + shirt = await Product.objects.create(name="100%-Cotton", rating=3) + assert ( + shirt + == await Product.objects.filter(Product.columns.name.contains("Cotton")).get() + ) From c49d0fdfced0942fc778de3323d1e3b41fcc4c6e Mon Sep 17 00:00:00 2001 From: Amin Alaee Date: Wed, 17 Nov 2021 16:00:51 +0100 Subject: [PATCH 2/4] Change private methods to internal (#133) --- orm/models.py | 20 ++++++++++---------- 1 file changed, 10 insertions(+), 10 deletions(-) diff --git a/orm/models.py b/orm/models.py index f897e8a..5d8362d 100644 --- a/orm/models.py +++ b/orm/models.py @@ -140,7 +140,7 @@ def schema(self): def pkname(self): return self.model_cls.pkname - def build_select_expression(self): + def _build_select_expression(self): tables = [self.table] select_from = self.table @@ -340,7 +340,7 @@ def select_related(self, related): ) async def exists(self) -> bool: - expr = self.build_select_expression() + expr = self._build_select_expression() expr = sqlalchemy.exists(expr).select() return await self.database.fetch_val(expr) @@ -365,7 +365,7 @@ def offset(self, offset: int): ) async def count(self) -> int: - expr = self.build_select_expression().alias("subquery_for_count") + expr = self._build_select_expression().alias("subquery_for_count") expr = sqlalchemy.func.count().select().select_from(expr) return await self.database.fetch_val(expr) @@ -373,10 +373,10 @@ async def all(self, **kwargs): if kwargs: return await self.filter(**kwargs).all() - expr = self.build_select_expression() + expr = self._build_select_expression() rows = await self.database.fetch_all(expr) return [ - self.model_cls.from_row(row, select_related=self._select_related) + self.model_cls._from_row(row, select_related=self._select_related) for row in rows ] @@ -384,14 +384,14 @@ async def get(self, **kwargs): if kwargs: return await self.filter(**kwargs).get() - expr = self.build_select_expression().limit(2) + expr = self._build_select_expression().limit(2) rows = await self.database.fetch_all(expr) if not rows: raise NoMatch() if len(rows) > 1: raise MultipleMatches() - return self.model_cls.from_row(rows[0], select_related=self._select_related) + return self.model_cls._from_row(rows[0], select_related=self._select_related) async def first(self, **kwargs): if kwargs: @@ -550,7 +550,7 @@ async def load(self): setattr(self, key, value) @classmethod - def from_row(cls, row, select_related=[]): + def _from_row(cls, row, select_related=[]): """ Instantiate a model instance, given a database row. """ @@ -561,10 +561,10 @@ def from_row(cls, row, select_related=[]): if "__" in related: first_part, remainder = related.split("__", 1) model_cls = cls.fields[first_part].target - item[first_part] = model_cls.from_row(row, select_related=[remainder]) + item[first_part] = model_cls._from_row(row, select_related=[remainder]) else: model_cls = cls.fields[related].target - item[related] = model_cls.from_row(row) + item[related] = model_cls._from_row(row) # Pull out the regular column values. for column in cls.table.columns: From a9915f87ad4eefe8250f6ece42d93f805b4504c0 Mon Sep 17 00:00:00 2001 From: Amin Alaee Date: Thu, 25 Nov 2021 09:03:39 +0100 Subject: [PATCH 3/4] Make `create_all` and `drop_all` async (#135) --- README.md | 4 +--- docs/declaring_models.md | 4 ++-- docs/index.md | 2 +- orm/models.py | 13 +++---------- requirements.txt | 1 + setup.py | 2 +- tests/conftest.py | 2 +- tests/test_columns.py | 6 +++--- tests/test_foreignkey.py | 6 +++--- tests/test_models.py | 6 +++--- 10 files changed, 19 insertions(+), 27 deletions(-) diff --git a/README.md b/README.md index 9212133..e7a36cd 100644 --- a/README.md +++ b/README.md @@ -22,8 +22,6 @@ MySQL, and SQLite. ORM is built with: Because ORM is built on SQLAlchemy core, you can use Alembic to provide database migrations. -**ORM is still under development: We recommend pinning any dependencies with `orm~=0.2`** - --- **Documentation**: [https://www.encode.io/orm](https://www.encode.io/orm) @@ -70,7 +68,7 @@ class Note(orm.Model): } # Create the tables -models.create_all() +await models.create_all() await Note.objects.create(text="Buy the groceries.", completed=False) diff --git a/docs/declaring_models.md b/docs/declaring_models.md index 9b6843b..cc1b22f 100644 --- a/docs/declaring_models.md +++ b/docs/declaring_models.md @@ -31,9 +31,9 @@ ORM can create or drop database and tables from models using SQLAlchemy. You can use the following methods: ```python -models.create_all() +await models.create_all() -models.drop_all() +await models.drop_all() ``` ## Data types diff --git a/docs/index.md b/docs/index.md index ed03770..bb1cc1d 100644 --- a/docs/index.md +++ b/docs/index.md @@ -66,7 +66,7 @@ class Note(orm.Model): } # Create the database and tables -models.create_all() +await models.create_all() await Note.objects.create(text="Buy the groceries.", completed=False) diff --git a/orm/models.py b/orm/models.py index 5d8362d..49ae2ec 100644 --- a/orm/models.py +++ b/orm/models.py @@ -1,6 +1,5 @@ import typing -import anyio import databases import sqlalchemy import typesystem @@ -28,15 +27,8 @@ def __init__(self, database: databases.Database) -> None: self.models = {} self.metadata = sqlalchemy.MetaData() - def create_all(self): + async def create_all(self): url = self._get_database_url() - anyio.run(self._create_all, url) - - def drop_all(self): - url = self._get_database_url() - anyio.run(self._drop_all, url) - - async def _create_all(self, url: str): engine = create_async_engine(url) for model_cls in self.models.values(): @@ -48,7 +40,8 @@ async def _create_all(self, url: str): await engine.dispose() - async def _drop_all(self, url: str): + async def drop_all(self): + url = self._get_database_url() engine = create_async_engine(url) for model_cls in self.models.values(): diff --git a/requirements.txt b/requirements.txt index 2dbfb80..aef7f05 100644 --- a/requirements.txt +++ b/requirements.txt @@ -6,6 +6,7 @@ twine wheel # Testing +anyio>=3.0.0,<4 autoflake black codecov diff --git a/setup.py b/setup.py index 16528f9..49557ea 100644 --- a/setup.py +++ b/setup.py @@ -51,7 +51,7 @@ def get_packages(package): packages=get_packages(PACKAGE), package_data={PACKAGE: ["py.typed"]}, data_files=[("", ["LICENSE.md"])], - install_requires=["anyio>=3.0.0,<4", "databases~=0.5", "typesystem==0.3.1"], + install_requires=["databases~=0.5", "typesystem==0.3.1"], extras_require={ "postgresql": ["asyncpg"], "mysql": ["aiomysql"], diff --git a/tests/conftest.py b/tests/conftest.py index 2fa5cff..7b3922e 100644 --- a/tests/conftest.py +++ b/tests/conftest.py @@ -1,6 +1,6 @@ import pytest -@pytest.fixture +@pytest.fixture(scope="module") def anyio_backend(): return ("asyncio", {"debug": True}) diff --git a/tests/test_columns.py b/tests/test_columns.py index d954524..c075b08 100644 --- a/tests/test_columns.py +++ b/tests/test_columns.py @@ -54,10 +54,10 @@ class User(orm.Model): @pytest.fixture(autouse=True, scope="module") -def create_test_database(): - models.create_all() +async def create_test_database(): + await models.create_all() yield - models.drop_all() + await models.drop_all() @pytest.fixture(autouse=True) diff --git a/tests/test_foreignkey.py b/tests/test_foreignkey.py index 886a007..8017198 100644 --- a/tests/test_foreignkey.py +++ b/tests/test_foreignkey.py @@ -76,10 +76,10 @@ class Person(orm.Model): @pytest.fixture(autouse=True, scope="module") -def create_test_database(): - models.create_all() +async def create_test_database(): + await models.create_all() yield - models.drop_all() + await models.drop_all() @pytest.fixture(autouse=True) diff --git a/tests/test_models.py b/tests/test_models.py index 5961f65..8e24437 100644 --- a/tests/test_models.py +++ b/tests/test_models.py @@ -33,10 +33,10 @@ class Product(orm.Model): @pytest.fixture(autouse=True, scope="function") -def create_test_database(): - models.create_all() +async def create_test_database(): + await models.create_all() yield - models.drop_all() + await models.drop_all() @pytest.fixture(autouse=True) From c6b9c0e7a2fee25509a75d1983f203a334376e95 Mon Sep 17 00:00:00 2001 From: Amin Alaee Date: Mon, 29 Nov 2021 10:20:23 +0100 Subject: [PATCH 4/4] Version 0.3.1 (#136) --- orm/__init__.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/orm/__init__.py b/orm/__init__.py index f7ea5b2..ee3ef5c 100644 --- a/orm/__init__.py +++ b/orm/__init__.py @@ -22,7 +22,7 @@ ) from orm.models import Model, ModelRegistry -__version__ = "0.3.0" +__version__ = "0.3.1" __all__ = [ "CASCADE", "RESTRICT",