In python pyramid web framework, how can I drop all db table rows before seeding?

Question

I am using a cookiecutter to make a pyramid web app. It has a function to seed the db here: https://github.com/Pylons/pyramid-cookiecutter-starter/blob/latest/%7B%7Bcookiecutter.repo_name%7D%7D/%7B%7Bcookiecutter.repo_name%7D%7D/sqlalchemy_scripts/initialize_db.py#L15

But if I run it twice, or change the entries that I am adding, I get duplicate entries and errors. I am using a sqlite db with sqlalchemy.

What code can I add inside setup_models that will drop db all db rows before writing the new model instances? It would be great if this looped over all models and deleted all instances of them.

def setup_models(dbsession):
    """
    Add or update models / fixtures in the database.
    """
    model = models.mymodel.MyModel(name='one', value=1)
    dbsession.add(model)

I am updating the db by running:

# to run the initial migration that adds the tables to the db, run this once
venv/bin/alembic -c development.ini upgrade head
# seed the data, I want to be able to keep editing the seed data
# and re-run this command and have it will wipe the db rows and insert the seed data defined in setup_models
venv/bin/initialize_suppah_db development.ini

Answer 1

One can do this by:

• looping over all model classes
• making all instances of those classes as to be deleted
• committing the session/transaction to delete them
• THEN seeding the data the below code does this:

import transaction
from ..models.meta import Base

def delete_table_rows(dbsession):
    model_clases = [cls for cls in Base.__subclasses__()]
    with transaction.manager as tx:
        for model_clases in model_clases:
            for instance in dbsession.query(model_clases).all():
                dbsession.delete(instance)
        transaction.commit()


def setup_models(dbsession):
    """
    Add or update models / fixtures in the database.

    """
    delete_table_rows(dbsession)
    # your custom seed code here
    model = models.mymodel.MyModel(name='one', value=1)
    dbsession.add(model)