Marginalia

I’m writing a fun little webapp using Flask and Python and Sqlalchemy, running on Heroku using a PostgreSQL database. I use a sqlite3 database file for local testing, and PostgreSQL when I deploy, so naturally there are some minor snags to be run into when switching between database engines.

Tonight I ran into a tricky issue after adding a ton more foreign-key / relationships to my database-backed models. I was getting an error like this when I tried to issue my db.drop_all() command in my python script that initializes my database tables:

sqlalchemy.exc.InternalError: (InternalError) cannot drop table pages because other objects depend on it
DETAIL:  constraint pagesections_parent_page_id_fkey on table pagesections depends on table pages
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 '\nDROP TABLE pages' {}

A bunch of searching for solutions indicated that maybe it would work if you run db.reflect() immediately before the db.drop_all(), but apparently the reflect function is broken for the current flask/sqlalchemy combination. Further searching revealed a mystical “DropEverything” function, and I finally found a copy here. I had to do a few small modifications to get it to work in the context of Flask’s use of Sqlalchemy.

def db_DropEverything(db):
    # From http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DropEverything

    conn=db.engine.connect()

    # the transaction only applies if the DB supports
    # transactional DDL, i.e. Postgresql, MS SQL Server
    trans = conn.begin()

    inspector = reflection.Inspector.from_engine(db.engine)

    # gather all data first before dropping anything.
    # some DBs lock after things have been dropped in 
    # a transaction.
    metadata = MetaData()

    tbs = []
    all_fks = []

    for table_name in inspector.get_table_names():
        fks = []
        for fk in inspector.get_foreign_keys(table_name):
            if not fk['name']:
                continue
            fks.append(
                ForeignKeyConstraint((),(),name=fk['name'])
                )
        t = Table(table_name,metadata,*fks)
        tbs.append(t)
        all_fks.extend(fks)

    for fkc in all_fks:
        conn.execute(DropConstraint(fkc))

    for table in tbs:
        conn.execute(DropTable(table))

    trans.commit()

I had to change the uses of engine to db.engine since Flask’s SQLalchemy takes care of that for you. You get the db object from the app, like this “from myapp import db”, and this is how I defined db in myapp:

from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__, etc)

# DATABASE_URL is set if we are running on Heroku
if 'DATABASE_URL' in os.environ:
    app.config['HEROKU'] = True
    app.config['SQLALCHEMY_DATABASE_URI'] = os.environ['DATABASE_URL']
else:
    app.config['HEROKU'] = False
    app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///" + os.path.join(PROJECT_ROOT, "../app.db")

db = SQLAlchemy(app)

And then this is the important parts of my db_create.py script:

from sqlalchemy.engine import reflection
from sqlalchemy.schema import (
        MetaData,
        Table,
        DropTable,
        ForeignKeyConstraint,
        DropConstraint,
        )

from cyosa import app, db

if not app.config['HEROKU'] and os.path.exists("app.db"):
    os.remove("app.db")

def db_DropEverything(db):
    # listed above

db_DropEverything(db)
db.create_all()

# add your instances of models here, be sure to db.session.commit()