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?

lenz · Accepted Answer · 2019-03-01 22:49:32Z

5

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 1, 2019 at 22:49

lenz

5,8585 gold badges27 silver badges47 bronze badges

answered Sep 14, 2016 at 14:51

daveoncode

19.8k19 gold badges108 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NumesSanguis Over a year ago

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

daveoncode Over a year ago

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

NumesSanguis Over a year ago

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.

liber · Accepted Answer · 2021-08-20 06:09:15Z

1

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)

answered Aug 20, 2021 at 6:09

liber

3042 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to delete multiple tables in SQLAlchemy

Tables

How in SQLAlchemy? EDIT

Question

2 Answers 2

UPDATE:

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Tables

How in SQLAlchemy? EDIT

Question

2 Answers 2

UPDATE:

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related