Autogenerate Warnings: ALTERs fail for existing sqlite3 auto-incr keys

[valhuber]

Describe the bug
I altered my models.py file to add a new class. autogenerate created that table fine, but also created ALTER statements for other tables with Integer primary keys, and these generated errors on the ALTER statement

Expected behavior
No ALTER statements for existing tables

To Reproduce
See attached screen shots.

# Insert code here

Error
(venv) val@Vals-MacBook-Pro-16 database % alembic upgrade head
hello
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 334d5fee92c0, Added Tables and Columns
/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/operations/toimpl.py:50: UserWarning: autoincrement and existing_autoincrement only make sense for MySQL
operations.impl.alter_column(
Traceback (most recent call last):
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: near "ALTER": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/Users/val/dev/servers/ApiLogicProject/venv/bin/alembic", line 8, in
sys.exit(main())
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/config.py", line 588, in main
CommandLine(prog=prog).main(argv=argv)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/config.py", line 582, in main
self.run_cmd(cfg, options)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/config.py", line 559, in run_cmd
fn(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/command.py", line 320, in upgrade
script.run_env()
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/script/base.py", line 563, in run_env
util.load_python_file(self.dir, "env.py")
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
module = load_module_py(module_id, path)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "", line 790, in exec_module
File "", line 228, in _call_with_frames_removed
File "alembic/env.py", line 78, in
run_migrations_online()
File "alembic/env.py", line 72, in run_migrations_online
context.run_migrations()
File "", line 8, in run_migrations
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
self.get_context().run_migrations(**kw)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
step.migration_fn(**kw)
File "/Users/val/dev/servers/ApiLogicProject/database/alembic/versions/334d5fee92c0_added_tables_and_columns.py", line 27, in upgrade
op.alter_column('Category', 'Id',
File "", line 8, in alter_column
File "", line 3, in alter_column
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/operations/ops.py", line 1880, in alter_column
return operations.invoke(alt)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/operations/base.py", line 392, in invoke
return fn(self, operation)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 50, in alter_column
operations.impl.alter_column(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 227, in alter_column
self._exec(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 193, in _exec
return conn.execute(construct, multiparams)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
return connection._execute_ddl(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_ddl
ret = self._execute_context(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
self.handle_dbapi_exception(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in handle_dbapi_exception
util.raise(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise
raise exception
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/Users/val/dev/servers/ApiLogicProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ALTER": syntax error
[SQL: ALTER TABLE "Category" ALTER COLUMN "Id" SET NOT NULL]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
(venv) val@Vals-MacBook-Pro-16 database %

# Copy error here. Please include the full stack trace.

Versions.

• OS: MocOS 12.3.1 (21E258)
• Python: 3.9.1
• Alembic:
• SQLAlchemy: SQLAlchemy==1.4.29
• Database: SQLite 3.37.0
• DBAPI:

Additional context

First is the database table (before migration):

CREATE TABLE IF NOT EXISTS "Category" 
(
  "Id" INTEGER PRIMARY KEY, 
  "CategoryName" VARCHAR(8000) NULL, 
  "Description" VARCHAR(8000) NULL 
);

Here are the relevant files:

• Category is a pre-existing table, not altered
• CategoryNew was added
• I ran autogenerate like this:

alembic revision --autogenerate -m "Added Tables and Columns" 
alembic upgrade head

Here is what I saw:

Have a nice day!

[zzzeek]

hi Val -

it's seeing those columns as nullable. Is this SQLite database the one that was generated directly by these revisions?

what you want to do is see what the tables look like from an inspect:

$ python
Python 3.10.0 (default, Nov  5 2021, 17:23:47) [GCC 11.2.1 20210728 (Red Hat 11.2.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import create_engine
>>> e = create_engine("sqlite:///mydb.db")
>>> from sqlalchemy import inspect
>>> inspector = inspect(e)
>>> inspector.get_pk_constraint("Category")
# show what is output here
{'constrained_columns': [], 'name': None}
>>> inspector.get_columns("Category")
# show what is output here

try pasting text into github text areas, much easier to work with than screenshots.

[valhuber]

Sure, Mike... thanks for the quick response, and here you go:

pk: {'constrained_columns': ['Id'], 'name': None}, 

cols: [{'name': 'CategoryName', 'type': VARCHAR(length=8000), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'Description', 'type': VARCHAR(length=8000), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'Id', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 1}]

[zzzeek]

well as far as I can tell this would mean the SQLite database has tables that were created either not by SQLAlchemy, or in some unusual way if using SQLAlchemy, where you have the tables having the "Id" column set as primary key but is allowing NULL, which is one of many nonsensical configurations that SQLite nonetheless "supports". SQLAlchemy doesn't support this directly.

if dropping these tables and creating them using NOT NULL for the primary key columns is not an option, you could use batch migrations which would create a new table and copy all the data from the existing tables, then dropping old tables and renaming the new ones.

[valhuber]

Quite so... a wild west version of Sqlite Northwind, having many issues. I did notice that and tried to change it, but the db tools I was using were unable to make it NonNull.

I am fine to close this, not seeing the button...

[zzzeek]

it's a discussion so just mark it as "answered"...or not...either way