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

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/{{cookiecutter.repo_name}}/{{cookiecutter.repo_name}}/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

CodePudding user response：

By default, SQLite does not enforce foreign key constraints at the engine level (even if you have declared them in the table DDL), so you could probably just use something as simple as

insp = inspect(engine)
with engine.begin() as conn:
    for table_name in insp.get_table_names():
        conn.exec_driver_sql(f'DELETE FROM "{table_name}"')

CodePudding user response：

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)