NumesSanguis NumesSanguis - 1 year ago 198
Python Question

How to delete multiple tables in SQLAlchemy

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


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

# Making new tables now the old ones are deleted

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
, because that drops all tables).

I know the function
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:

for table in sqla_base.metadata.sorted_tables:
if table in to_delete:


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

Answer Source

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 not an 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:


anyway drop_all() is the method to use for drop all the tables in a simple command:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download