How to delete multiple tables in SQLAlchemy
<p>Inspired by this question: <a href="http://stackoverflow.com/questions/35918605/how-to-delete-a-table-in-sqlalchemy">How to delete a table in SQLAlchemy?</a>, I ended up with the question: How to delete multiple tables.</p>

<p>Say I have 3 tables as seen below and I want to delete 2 tables (imagine a lot more tables, so no manually table deletion).</p>

<h2>Tables</h2>

<pre><code>import sqlalchemy as sqla
import sqlalchemy.ext.declarative as sqld
import sqlalchemy.orm as sqlo

sqla_base = sqld.declarative_base()

class name(sqla_base):
    __tablename__ = 'name'
    id = sqla.Column(sqla.Integer, primary_key=True)
    name = sqla.Column(sqla.String)

class job(sqla_base):
    __tablename__ = 'job'
    id = sqla.Column(sqla.Integer, primary_key=True)
    group = sqla.Column(sqla.String)

class company(sqla_base):
    __tablename__ = 'company'
    id = sqla.Column(sqla.Integer, primary_key=True)
    company = sqla.Column(sqla.String)

engine = sqla.create_engine("sqlite:///test.db", echo=True)
sqla_base.metadata.bind = engine

# Tables I want to delete
to_delete = ['job', 'function']

# Get all tables in the database
for table in engine.table_names():
    # Delete only the tables in the delete list
    if table in to_delete:
        sql = sqla.text("DROP TABLE IF EXISTS {}".format(table))
        engine.execute(sql)

# Making new tables now the old ones are deleted
sqla_base.metadata.create_all(engine)
</code></pre>

<h2>How in SQLAlchemy? EDIT</h2>

<p>This works, however I was wondering if I can do the same in SQLAlchemy style instead of executing raw SQL code with <code>sqla.text("DROP TABLE IF EXISTS {}".format(table))</code> (not using <code>sqla_base.metadata.drop_all()</code>, because that drops all tables).</p>

<p>I know the function <code>tablename.__table__.drop()</code> or <code>tablename.__table__.drop(engine)</code> exists, but I don't want to type it manually for every table.</p>

<p>From the answer given by @daveoncode, the following code does what I want (EDIT 2: added <code>checkfirst=True</code>, in case it didn't exist in db yet and str()):</p>

<pre><code>for table in sqla_base.metadata.sorted_tables:
    if str(table) in self.to_delete:
        table.drop(checkfirst=True)
</code></pre>

<h2>Question</h2>

<p>How do I drop multiple tables in SQLAlchemy style, achieving the same as the raw SQL code above?</p>

<p>The error you get is perfectly clear:</p>

<pre><code>AttributeError: 'str' object has no attribute '__table__'
</code></pre>

<p>You are not iterating on Table objects, but on table names (aka <strong>strings</strong>!), so of course a string has not an attribute <code>__table__</code>, so your statement:</p>

<pre><code>tablename.__table__.drop() or tablename.__table__.drop(engine)
</code></pre>

<p>is wrong! it should be:</p>

<pre><code>table_instance.__table__.drop() or table_instance.__table__.drop(engine)
</code></pre>

<p>You can access table instances from the metadata, take a look here:</p>

<p><a href="http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.sorted_tables" rel="nofollow">http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.sorted_tables</a></p>

<pre><code>UPDATE:
</code></pre>

<p>anyway <code>drop_all()</code> is the method to use for drop all the tables in a simple command: <a href="http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.drop_all" rel="nofollow">http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.drop_all</a></p>

python sqlalchemy