CODEWITHSUNDEEP
pythonsqlalchemy
NumesSanguis
NumesSanguis

Reputation: 6282

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

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?

Upvotes: 5

Views: 14416

Answers (2)

liber
liber

Reputation: 196

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)

Upvotes: 1

daveoncode
daveoncode

Reputation: 19578

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

Upvotes: 5

Related Questions