CODEWITHSUNDEEP
pythonsqlalchemy
OpenDataAlex
OpenDataAlex

Reputation: 1475

SQLAlchemy drop_all not dropping tables

I'm trying to automate my application's database creation/deletion by using SQLAlchemy. I have created models and querying via the ORM is working without issue. However when I want to wipe and recreate the database from scratch, Base.metadata.drop_all(engine) runs but doesn't return anything when I try to troubleshoot via the Python console. It also doesn't actually delete any of the objects in my data store. I have also verified that the user account does have DROP privilege.

When I run Base.metadata.sorted_tables I do get a list of tables which matches the models. I also tried to run a for loop on sorted_tables and perform a drop per table, but got an error stating that the table didn't exist.

My full code repo can be found at:https://github.com/OpenDataAlex/process_tracker_python/tree/master/process_tracker The models are in the models/ directory while the engine creation code is in data_store.py.

I've copied what I think are the relevant bits, but after many hours reading docs and trying to solve this, it's all blurring together x.x 

# The engine creation code
engine = create_engine(data_store_type + '://' + data_store_username + ':' + data_store_password
                                           + '@' + data_store_host + '/' + data_store_name)


session = sessionmaker(bind=engine)

session = session(expire_on_commit=False)
session.execute("SET search_path TO %s" % data_store_name)

# The drop code, from data_store.py DataStore().initialize_data_store()
self.logger.warn('ALERT - DATA STORE TO BE OVERWRITTEN - ALL DATA WILL BE LOST')

Base.metadata.drop_all(self.engine)

Upvotes: 1

Views: 3370

Answers (1)

OpenDataAlex
OpenDataAlex

Reputation: 1475

The issue turned out to be that because I was using postgresql, the public schema was being used by default because I had not defined a schema in the table models. Once that was done and the schema was also added to any ForeignKey columns drop_all and create_all worked as documented.

As an example, here is what ended up working:

class MyTable(Base):
    __tablename__ = "my_table"
    __table_args__ = {"schema": "my_schema"}

    my_table_fk = Column(Integer, ForeignKey("my_schema.my_other_table.my_other_table_id"))

Upvotes: 2

Related Questions