How can drop the table if exists with sqlalchemy?

Question

List all tables in the sample database:

from sqlalchemy import (MetaData, Table)
from sqlalchemy import create_engine, inspect
from sqlalchemy.ext.declarative import declarative_base
db_pass = 'xxxxxx'
db_ip='127.0.0.1'
engine = create_engine('postgresql://postgres:{}@{}/sample'.format(db_pass,db_ip))
inspector = inspect(engine)
print(inspector.get_table_names())
['pre', 'sample']

There are two tables in the sample database,now i want to drop the table pre:

Base = declarative_base()
metadata = MetaData(engine)
if inspector.has_table('pre'):Base.metadata.drop_all(engine, ['pre'], checkfirst=True)

It run into an error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 4959, in drop_all
    bind._run_ddl_visitor(
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3228, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2211, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 959, in visit_metadata
    unsorted_tables = [t for t in tables if self._can_drop_table(t)]
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 959, in <listcomp>
    unsorted_tables = [t for t in tables if self._can_drop_table(t)]
  File "/home/debian/.local/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 1047, in _can_drop_table
    self.dialect.validate_identifier(table.name)
AttributeError: 'str' object has no attribute 'name'

How can drop the table if exists with sqlalchemy?

Answer 1

The issue you encountered is due to the fact that the drop_all() method expects a list of Table objects, not a list of table names as strings. To drop a specific table if it exists, you can use the Table object with the drop() method and the checkfirst=True argument:

pre_table = Table('pre', metadata)
pre_table.drop(engine, checkfirst=True)