Sqlalchemy: sqlite batch mode Boolean value of this clause is not defined

[chrisjsimpson]

Describe the bug
When running a batch migration with an sqlite database to add a column, sqlalchemy throws the following TypeError: "Boolean value of this clause is not defined". This may be to do with alembic, sqlalchemy or both.

Expected behavior
That the migration batch migrates the table as per the docs: https://alembic.sqlalchemy.org/en/latest/batch.html

Versions:
Python 3.6.8
sqlalchemy.__version__ '1.3.16'
alembic.__version__ '1.4.2'

To Reproduce

Given the following upgrade operation on an sqlite database:
Note sqlite migrations require the use of batch (https://alembic.sqlalchemy.org/en/latest/batch.html)

from alembic import op                                                           
import sqlalchemy as sa                                                          

def upgrade():                                                                   
    with op.batch_alter_table("person") as batch_op:                             
        batch_op.add_column('person', sa.Column('uuid', sa.String(), nullable=True))

The following error is observed: TypeError: Boolean value of this clause is not defined

  File "venv/lib/python3.6/site-packages/flask_migrate/__init__.py", line 271, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "venv/lib/python3.6/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "venv/lib/python3.6/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "venv/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "venv/lib/python3.6/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "migrations/env.py", line 96, in <module>
    run_migrations_online()
  File "migrations/env.py", line 90, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "venv/lib/python3.6/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 520, in run_migrations
    step.migration_fn(**kw)
  File "/home/chris/project/migrations/versions/c01359894f1d_add_uuid_to_person_table.py", line 23, in upgrade
    batch_op.add_column('person', sa.Column('uuid', sa.String(), nullable=True))
  File "<string>", line 3, in add_column
  File "/home/chris/project/python/subscribie/venv/lib/python3.6/site-packages/alembic/operations/ops.py", line 1945, in batch_add_column
    if insert_before:
  File "venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 522, in __bool__
    raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined

Additional context:

sqlalchemy src: https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_3_16/lib/sqlalchemy/sql/elements.py#L522
alembic src: https://github.com/sqlalchemy/alembic/blob/rel_1_4_2/alembic/operations/ops.py#L1945

Have a nice day!

[chrisjsimpson]

updated to use @CaselIT 's issue template

[zzzeek]

this is local to alembic and the issue is that you are invoking the add_column() method incorrecltly, in batch mode the "person" table name is already stated up front by the batch mode. the Column is then coming in and being interpreted as the "insert_before" keyword argument. This kind of confusion will go away when Alembic goes Python 3 only and this argument will be made keyword only.

[chrisjsimpson]

Thank you @zzzeek makes perfect sense.

For anyone else, as Mike says, I was calling add_column incorrectly. The correct invocation is:

def upgrade():                                                                   
    with op.batch_alter_table("person") as batch_op:                             
        batch_op.add_column(sa.Column('uuid', sa.String(), nullable=True))       

Much appreciated

[zzzeek]

yes this is IMO a shortcoming in Python argument signatures that can be improved once we can be py3k only and especially py38 for positional only parameters.