Specify the return field when querying

[vvanglro]

Hi, thanks for such a great library.

I would like to know how to specify the fields of the query before the sql statement is sent(That is, specify the field when select).

[tarsil]

Hello @vvanglro could you please give me an example? I'm not sure that I'm understanding the question.

[tarsil]

Are you referring to this?

https://saffier.tarsild.io/queries/queries/#values

P.S.: Thank you for your kind words before 🙂

[vvanglro]

Are you referring to this?

https://saffier.tarsild.io/queries/queries/#values

P.S.: Thank you for your kind words before 🙂

It isn't. This VALUES is the specified field that is taken out after the query, I meant to specify the field when splicing the sql.

I printed out the last executed sql statement, which is the selected part of the picture. For example, I only want to query the field is_active.

Then the sql statement should look like this：

SELECT users.is_active
FROM users 
WHERE users.is_active = false

sqlalchemy can specify fields like this：

db.session.query(users.is_active, users.id).all()

[tarsil]

Got it. Something like this:

https://edgy.tarsild.io/queries/queries/#only

Edgy is the other ORM with a lot of extras 🙂

In Saffier that is not yet added due to the nature of the ORM itself but it's in pipeline

[vvanglro]

Understood, I've been following both projects, for me I prefer saffier because design wise.

I probably think it's better to separate the orm model from the pydantic model(In terms of maintenance, functionality, compatibility, etc.) .

That is, more pure orm functionality.

[tarsil]

That is exactly the purpose. You nailed it but no worries, soon that functionality will be out but that is what you are looking for, right?

[vvanglro]

That is exactly the purpose. You nailed it but no worries, soon that functionality will be out but that is what you are looking for, right?

yep.

[tarsil]

@vvanglro apologies for taking so long but there were a few changes that needed to be made + Christmas and I couldn't fully address this functionality.

As we discussed, the only and defer are now available in the new version of Saffier 1.3.0.

Please check the release notes as there are breaking changes but nothing major, just an environment variable that needs updating as now Saffier implements the Dymmond Lazy Settings.

https://saffier.tarsild.io/release-notes/

Enjoy 😄

Let me know if this is what you were referring and we can close this issue

[vvanglro]

The problem occurs in the setup method of the dymmond_settings library.

    def _setup(self, name: Optional[str] = None) -> None:
        """
        Load the settings module pointed to by the environment variable. This
        is used the first time settings are needed, if the user hasn't
        configured settings manually.
        """
        settings_module: str = os.environ.get(
            ENVIRONMENT_VARIABLE, "dymmond_settings.global_settings.Settings"
        )

Traceback (most recent call last):
  File "/Users/hulk/code/py/workhome/test_saffier/1.py", line 48, in <module>
    asyncio.get_event_loop().run_until_complete(run())
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "/Users/hulk/code/py/workhome/test_saffier/1.py", line 33, in run
    await models.create_all()
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/site-packages/saffier/core/connection/registry.py", line 94, in create_all
    async with self.engine.begin() as connection:
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/site-packages/saffier/core/connection/registry.py", line 78, in engine
    return self._get_engine
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/functools.py", line 967, in __get__
    val = self.func(instance)
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/site-packages/saffier/core/connection/registry.py", line 64, in _get_engine
    url = self._get_database_url()
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/site-packages/saffier/core/connection/registry.py", line 58, in _get_database_url
    elif url.driver in settings.mssql_drivers:
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/site-packages/dymmond_settings/functional.py", line 16, in inner
    self._setup()
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/site-packages/dymmond_settings/conf.py", line 30, in _setup
    settings: Type["Settings"] = import_string(settings_module)
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/site-packages/dymmond_settings/module_loading.py", line 15, in import_string
    module = import_module(module_path)
  File "/Users/hulk/miniforge3/envs/py38/lib/python3.8/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 973, in _find_and_load_unlocked
ModuleNotFoundError: No module named 'dymmond_settings.global_settings'

from datetime import datetime

import saffier
import asyncio
from saffier import Database, Registry
from sqlalchemy import func

database = Database("postgresql+asyncpg://postgres:postgres@192.168.5.201:55432/saffier")
models = Registry(database=database)

class User(saffier.Model):
    """
    The User model to be created in the database as a table
    If no name is provided the in Meta class, it will generate
    a "users" table for you.
    """

    id = saffier.IntegerField(primary_key=True)
    is_active = saffier.BooleanField(default=False)
    created = saffier.DateTimeField(default=datetime.now, server_default=func.now())
    updated = saffier.DateTimeField(default=datetime.now, server_default=func.now(), auto_now=True)

    class Meta:
        registry = models

# Create the db and tables
# Don't use this in production! Use Alembic or any tool to manage
# The migrations for you
async def run():
    await models.create_all()
    await database.connect()
    await User.query.create(is_active=False)
    user = await User.query.get(id=1)
    print(user.id)
    print(user.is_active)

    user2 = await User.query.filter(is_active=False).first()
    print(user2)

    user3 = await User.query.filter(is_active=False).values(['is_active'])
    print(user3)
    # await models.drop_all()
if __name__ == '__main__':

    asyncio.get_event_loop().run_until_complete(run())

Here's what I did to fix it after troubleshooting.

import os
os.environ['SETTINGS_MODULE'] = 'saffier.conf.global_settings.SaffierSettings'
from dymmond_settings import settings as settings

[vvanglro]

Please check the release notes as there are breaking changes but nothing major, just an environment variable that needs updating as now Saffier implements the Dymmond Lazy Settings.

We should give the SETTINGS_MODULE environment variable value correctly. Right?

[tarsil]

Please check the release notes as there are breaking changes but nothing major, just an environment variable that needs updating as now Saffier implements the Dymmond Lazy Settings.

We should give the SETTINGS_MODULE environment variable value correctly. Right?

Yes that is correct. The release notes mentioned that 🙂

[tarsil]

@vvanglro let me know if now everything is ok. The only() as we discussed is working 👍🏼

[vvanglro]

only no problem.

Environment variables are something that should be set by the library. They do not need to be set by the user.

[tarsil]

@vvanglro you actually found a bug. This should have been set automatically by the library and overwritten by the user if necessary.

I will patch it and do a release today. Thank you for this