drop_all for SQLite raises "(sqlite3.OperationalError) table sqlite_sequence may not be dropped"

Describe the bug

Running

meta.reflect(engine)
meta.drop_all(engine)

on an existing, nonempty SQLite database results in the following exception:

(sqlite3.OperationalError) table sqlite_sequence may not be dropped

I realize that with SQLite, the easiest way to wipe out a persisted DB is to simply os.remove() it. But in order to satisfy an architectural requirement and continue using drop_all(), I implemented the following workaround:

meta.reflect(engine)

# SQLAlchemy's SQLite dialect of drop_all attempts to drop SQLite system
# tables such as sqlite_sequence, which raises an error. Avoid this by
# dropping only non-system tables and then deleting all sequences.
tables_to_drop = [table for table in meta.tables.values() if not table.name.startswith("sqlite_")]
meta.drop_all(engine, tables=tables_to_drop)
with engine.connect() as conn:
    conn.execute("DELETE FROM sqlite_sequence")  # Reset (delete) all autoincrement sequences

I suggest that this logic be implemented in the SQLite dialect of drop_all() (if not already implemented in 2.0), or be included in the SQLite dialect documentation.

To Reproduce

import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

meta = sqlalchemy.MetaData()
Base = declarative_base(metadata=meta)

class Test(Base):
    __tablename__ = "test"
    __table_args__ = {"sqlite_autoincrement": True}

    id = Column(Integer, primary_key=True)
    value = Column(String, nullable=False)

engine = sqlalchemy.create_engine("sqlite:///test.db")
with engine.connect() as conn_create_all:
    meta.create_all(engine)
    
with engine.connect() as conn_drop_all:
    meta.reflect(engine)
    meta.drop_all(engine)

Error

Traceback (most recent call last):
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: table sqlite_sequence may not be dropped

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

Traceback (most recent call last):
  File "sqlite_dropall.py", line 21, in <module>
    meta.drop_all(engine)
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4814, in drop_all
    bind._run_ddl_visitor(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3117, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2113, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 1023, in visit_metadata
    self.traverse_single(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 1100, in visit_table
    self.connection.execute(DropTable(table))
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_ddl
    ret = self._execute_context(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
    util.raise_(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/home/vscode/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table sqlite_sequence may not be dropped
[SQL: 
DROP TABLE sqlite_sequence]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Versions

• OS: Ubuntu 20.04.3
• Python: 3.8.12
• SQLAlchemy: 1.4.31
• Database: SQLite 3.34.1
• DBAPI: sqlite3

Additional context

No response

this seems extremely special case. I think the main thing happening here that's surprising is that meta.reflect() for SQLite reflects system tables, since they are all in the same namespace. But we definitely can't change that either. We could at least have the SQLite dialect, when it reflects these tables, to tag the tables it knows as "system", and then meta.drop_all() would then know to skip tables marked as "system". If you had interest in working on that as a PR, we can target that at 2.0.

as for the "delete from sqlite_sequence" part, that does not appear to be something that would be appropriate for the default behavior of drop_all(). that command could be implemented as an "after_drop" event if this were in a recipe.

Thanks for your response. And yes, I'd like to work on it as a PR for 2.0. I understand why you don't think it's appropriate to do "delete from sqlite_squence" as part of drop_all() for SQLite. At least let's add a relevant SQLite-specific comment next to drop_all() entry in the 2.0 docs.

Is there a way to explciitly ignore the sqlite_sequence table when doing the reflect ? Like: meta.reflect(engine, exclude=['sqlite_sequence']) ?

EDIT: I found the only which accepts a callable. So, I can do: meta.reflect(engine, only=lambda x: x not in ['sqlite_sequence']) Which is pretty cool :+1:

This has been fixed in v2. The sqlite dialect will ingnore all internal tables by default, unless the new reflection argument sqlite_include_internal is set to true