How to delete a table in SQLAlchemy?

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.

5 Answers

Just call drop() against the table object. From the docs:

Issue a DROP statement for this Table, using the given Connectable for connectivity.

In your case it should be:

User.__table__.drop() 

If you get an exception like:

sqlalchemy.exc.UnboundExecutionError: Table object 'my_users' is not bound to an Engine or Connection. Execution can not proceed without a database to execute against 

You need to pass the engine:

User.__table__.drop(engine) 

Alternative to calling cls.__table__.drop(your_engine), you can try this:

Base.metadata.drop_all(bind=your_engine, tables=[User.__table__]) 

This method as well as the create_all() method accept an optional argument tables, which takes an iterator of sqlalchemy.sql.schema.Table instances.

You can control which tables are to be created or dropped in this way.

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

Below is example code you can execute in iPython to test the creation and deletion of a table on Postgres

from sqlalchemy import * # imports all needed modules from sqlalchemy engine = create_engine('postgresql://python:python@127.0.0.1/production') # connection properties stored metadata = MetaData() # stores the 'production' database's metadata users = Table('users', metadata, Column('user_id', Integer), Column('first_name', String(150)), Column('last_name', String(150)), Column('email', String(255)), schema='python') # defines the 'users' table structure in the 'python' schema of our connection to the 'production' db users.create(engine) # creates the users table users.drop(engine) # drops the users table 

You can also preview my article on Wordpress with this same example and screenshots: oscarvalles.wordpress.com (search for SQL Alchemy).

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

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)