How to delete a table in SQLAlchemy?

user_1983854 0
Viewed 116476

I want to delete a table using SQLAlchemy.

Since I am testing over and over again, I want to delete the table my_users so that I can start from scratch every single time.

So far I am using SQLAlchemy to execute raw SQL through the engine.execute() method:

sql = text('DROP TABLE IF EXISTS my_users;')
result = engine.execute(sql)

However, I wonder if there is some standard way to do so. The only one I could find is drop_all(), but it deletes all the structure, not only one specific table:

Base.metadata.drop_all(engine)   # all tables are deleted

For example, given this very basic example. It consists on a SQLite infrastructure with a single table my_users in which I add some content.

from sqlalchemy import create_engine, Column, Integer, String, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite://', echo=False)
Base = declarative_base()

class User(Base):
    __tablename__ = "my_users"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __init__(self, name):
        self.name = name

# Create all the tables in the database which are
# defined by Base's subclasses such as User
Base.metadata.create_all(engine)

# Construct a sessionmaker factory object
session = sessionmaker()

# Bind the sessionmaker to engine
session.configure(bind=engine)

# Generate a session to work with
s = session()

# Add some content
s.add(User('myname'))
s.commit()

# Fetch the data
print(s.query(User).filter(User.name == 'myname').one().name)

For this specific case, drop_all() would work, but it won't be convenient from the moment I start having more than one table and I want to keep the other ones.

drop-table sqlalchemy sqlite python
5 Answers

For the special case when you don't have access to the table class and just need to delete the table by table name then use this code

import logging
from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base

DATABASE = {
   'drivername': 'sqlite',
   # 'host': 'localhost',
   # 'port': '5432',
   # 'username': 'YOUR_USERNAME',
   # 'password': 'YOUR_PASSWORD',
   'database': '/path/to/your_db.sqlite'
}

def drop_table(table_name):
   engine = create_engine(URL(**DATABASE))
   base = declarative_base()
   metadata = MetaData(engine, reflect=True)
   table = metadata.tables.get(table_name)
   if table is not None:
       logging.info(f'Deleting {table_name} table')
       base.metadata.drop_all(engine, [table], checkfirst=True)

drop_table('users')
user_22486270

How to delete a table by name

Here's an update of @Levon's answer, since MetaData(engine, reflect=True) is now deprecated. It is useful if you don't have access to the table class or want to delete a table by its table name.

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base

DATABASE = {
   'drivername': 'sqlite',
   # 'host': 'localhost',
   # 'port': '5432',
   # 'username': 'YOUR_USERNAME',
   # 'password': 'YOUR_PASSWORD',
   'database': '/path/to/your_db.sqlite'
}

engine = create_engine(URL(**DATABASE))

def drop_table(table_name, engine=engine):
    Base = declarative_base()
    metadata = MetaData()
    metadata.reflect(bind=engine)
    table = metadata.tables[table_name]
    if table is not None:
        Base.metadata.drop_all(engine, [table], checkfirst=True)

drop_table('users')

How to delete a table using the table class (preferred)

Otherwise, you may prefer to use cls.__table__.drop(engine) and cls.__table__.create(engine) instead, e.g.

User.__table__.drop(engine)
User.__table__.create(engine)
user_123257830
Related