TypeError: Boolean value of this clause is not defined

[xnuinside]

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

Traceback during open 'Create' page (url for example): http://127.0.0.1:8000/admin/user/create

File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/starlette/templating.py", line 37, in init
content = template.render(context)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/jinja2/environment.py", line 1301, in render
self.environment.handle_exception()
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/jinja2/environment.py", line 936, in handle_exception
raise rewrite_traceback_stack(source=source)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/create.html", line 1, in top-level template code
{% extends "layout.html" %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/layout.html", line 1, in top-level template code
{% extends "base.html" %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/base.html", line 15, in top-level template code
{% block body %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/layout.html", line 49, in block 'body'
{% block content %} {% endblock %}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqladmin/templates/create.html", line 18, in block 'content'
{{ field() }}
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/fields/core.py", line 172, in call
return self.meta.render_field(self, kwargs)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/meta.py", line 64, in render_field
return field.widget(field, **render_kw)
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/widgets/core.py", line 174, in call
kwargs["value"] = field._value()
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/fields/datetime.py", line 36, in _value
return self.data and self.data.strftime(self.format[0]) or ""
File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 582, in bool
raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined

Steps to reproduce the bug

I think something in model cause in issue - I send the sample

Also we use async SQLAlchemy engine. I will be glad if you will do some input where to dig or what can be a reason of the problem.

Expected behavior

Create form open correctly without issues.

Actual behavior

Impossible to open Create form

Debugging material

No response

Environment

SQLAdmin == 0.1.9
SQLAlchemy == 1.4.37

Additional context

No response

[xnuinside]

@aminalaee and thanks for library :) I hope you can help anyhow with my issue. File with models in txt
Uploading sample.txt…

[aminalaee]

Hey @xnuinside , Thank you,
Can you please provide the model definition again? You can just paste the minimal example to reproduce this issue?

[xnuinside]

@aminalaee tried upload several times )) not sure why it's not loaded, one more time
sample.txt

[xnuinside]

@aminalaee I will try today to experiment & remove fields till I find that exactly cause a problem, but if you will have ideas to make this investigation more quick :) I will appreciate

[aminalaee]

I'm guessing that might be the server_default of Boolean field which I haven't tested, but it would be quicker if you can provide a minimal example to produce this.

[xnuinside]

@aminalaee problem not in boolean field, so everything works ok if remove DateTime fields:

    created_at = Column(DateTime, nullable=False, default=func.now())
    updated_at = Column(
        DateTime,
        nullable=False,
        default=func.now(),
        onupdate=func.now(),
    )

looks like they cause an issue

[xnuinside]

and if check traceback you can see File "/Users/iuliia_volkova2/Library/Caches/pypoetry/virtualenvs/admin-panel-service-Ji2bEy0B-py3.7/lib/python3.7/site-packages/wtforms/fields/datetime.py", line 36, in _value return self.data and self.data.strftime(self.format[0]) or "" so problem with datetime field

[xnuinside]

problem in 'default=func.now()' & 'onupdate=func.now()' if not use 'func.now()' everything is ok

[aminalaee]

Ok great, that's a good start, feel free to create a PR for it or I'll have a look later 👍

[xnuinside]

@aminalaee I will close the issue, I just recognized, that in python 'default' not 'server_default' somebody put func.now() and this is the reason of issue, if change it to python datetime - everything ok

[StorkST]

Hello

I have the same issue as @xnuinside

I didn't quite get the solution but it seems to me that my models using func.now() are correct.
If there is an error I don't see why I would need to change my models which are alright.

[aminalaee]

@StorkST
I think the solution was that @xnuinside needed to use server_default and not default. Can you provide a very minimal model to reproduce this?

[StorkST]

Hi @aminalaee

Sure, here is my code.

I hope this is good for you as an example. I think you might need to create the database with the simple users table.

So when I try to create a new User on the interface I get the error "Boolean value of this clause is not defined".

main.py file runned with uvicorn main:app --reload :

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.sql import func
from sqlalchemy.orm import sessionmaker

from fastapi import FastAPI
from sqladmin import Admin, ModelView

from sqlalchemy.ext.declarative import declarative_base

app = FastAPI()

SQLALCHEMY_DATABASE_URL = "mysql://test:test@localhost/test"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# Define admin interface
admin = Admin(app, engine)

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String(40), unique=True, index=True)
    creationDate = Column(DateTime, default=func.now())
    updateDate = Column(DateTime, onupdate=func.now())

class UserAdmin(ModelView, model=User):
    column_list = [User.id, User.email, User.creationDate, User.updateDate]

admin.add_view(UserAdmin)

[xnuinside]

@StorkST func.now() cannot be used in ‘default’ arg. Because it is DB function. default - for Python code that executes before send request to DB. Change func.now() to Python datetime.now() or use it in server_default

[aminalaee]

Yeah As far as I remember this was expected, the default should be populated in the create form, and in the case of func.now() there's no way to set that value upfront. Switching to server_default works around this.

P.S. if you think you can improve this, contributions are welcome.

[CasselKim]

I believe it would be more natural to exclude the func.now() Datetime column from the form.

class UserAdmin(ModelView, model=User):
  form_excluded_columns = [ User.creationDate, User.updateDate ]

Because using datetime.now() may cause delay issues as it returns the time when the code executes. Also, when using server_default, input box will be displayed on the template and make user set values, which pass the server_default.

It would be convenient if the datetime column is automatically excluded when func_now() is used by including in form_excluded_columns.

Thanks you.

[aminalaee]

Hmm, that might be one solution. But maybe we should exclude any columns with server_default value, not just with server_default=func.now. I think that would cover more cases.

[CasselKim]

As far as I test, func.now, func.current_date, func.current_time, func.current_timestamp, func.localtime, func.localtimestamp, and func.sysdate raise the same issue.
There can be more cases, but isn't it okay to catch only just the functions above? (in my short opinion)

[aminalaee]

I think my previous comment was not very clear.
I meant in the first step we can ignore any fields which have server_default=... or server_onupdate=... set so we don't need them in the forms.

But I think modifying the behaviour based on default=... is going to be a bit complicated.

[CasselKim]

Agreed with you. Thank you for your kindness :)