How to delete multiple tables in SQLAlchemy

Question

Inspired by this question: How to delete a table in SQLAlchemy?, I ended up with the question: How to delete multiple tables.

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).

Tables

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)

How in SQLAlchemy? EDIT

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

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

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

for table in sqla_base.metadata.sorted_tables:
    if str(table) in self.to_delete:
        table.drop(checkfirst=True)

Question

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

score 3 · Accepted Answer · edited Mar 01 '19 at 22:49

3

The error you get is perfectly clear:

AttributeError: 'str' object has no attribute '__table__'

You are not iterating on Table objects, but on table names (aka strings!), so of course a string has no attribute __table__, so your statement:

tablename.__table__.drop() or tablename.__table__.drop(engine)

is wrong! It should be:

table_instance.__table__.drop() or table_instance.__table__.drop(engine)

You can access table instances from the metadata. Take a look here:

http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.sorted_tables

UPDATE:

Anyway drop_all() is the method to use for dropping all the tables in a simple command: http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.drop_all

edited Mar 01 '19 at 22:49

lenz

5,658
5
24
44

answered Sep 14 '16 at 14:51

daveoncode

18,900
15
104
159

I'm aware of why my example code doesn't work, I only put it there to show my line of reasoning. I also tried for example with: `for table in sqla_base.metadata.tables:`, but that also returned a string. As by your suggestion: `for table in sqla_base.metadata.sorted_tables:` works :) I didn't expect that to work, because just `.tables` returned a string, so why would `.sorted_tables` return a table_instance? (and I don't need it sorted). – NumesSanguis Sep 14 '16 at 15:14
1

you should always use sorted_tables, since that way you are able to drop them properly in order of dependencies... anyway use drop_all() (read my updated answer) ;) – daveoncode Sep 14 '16 at 15:21
I wanted to avoid using `drop_all`, because my intention was to only drop certain tables. I updated my question (see Edit) to include the working code I got by using your `sorted_tables`. Thank you. – NumesSanguis Sep 14 '16 at 15:23

score 1 · Answer 2 · answered Aug 20 '21 at 06:09

As WeiHao answered in https://stackoverflow.com/a/49644099/5892421:
drop_all accept an optional argument tables indicating the tables to be deleted

to_deletes = [
   job.__table__,
   company.__table__,
]
Base.metadata.drop_all(bind=your_engine, tables=to_deletes)
# create tables
Base.metadata.create_all(bind=your_engine, tables=to_deletes)

How to delete multiple tables in SQLAlchemy

Tables

How in SQLAlchemy? EDIT

Question

2 Answers2

UPDATE:

Linked