Marginalia

Mostly so I don't forget

Flask sqlalchemy postgres drop_all() troubles, fixed

Posted by Matthew on January 22, 2014
Posted in: Uncategorized. Tagged: flask, posgtresql, python, sql, sqlalchemy, web.

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

Share this:

  • Print
  • Email
  • Twitter
  • Facebook
  • LinkedIn
  • Reddit
  • Pinterest
  • Tumblr
  • Pocket

Posts navigation

← Minecraft on Ubuntu Linux 64-bit
New RSS Feed URL →
  • Recent Posts

    • Quick setup guide for Tor Snowflake relay
    • Cat-proof puzzle table cover
    • 3D printable blower adapter for Platypus filter hoses
    • ZFS plans and logs: 2x mirroring VDEVs with future expansion
    • Repairing a broken cable on Koss SB-49 headphones
  • Recent Comments

    • Heather on Homemade Trunk for Harry Potter Hardcover Box Set
    • Elizabeth Murray on Homemade Trunk for Harry Potter Hardcover Box Set
    • Matthew on Homemade Trunk for Harry Potter Hardcover Box Set
    • Paul on Homemade Trunk for Harry Potter Hardcover Box Set
    • Matthew on Homemade Trunk for Harry Potter Hardcover Box Set
  • Archives

    • October 2022
    • February 2021
    • July 2020
    • September 2019
    • June 2018
    • May 2018
    • February 2018
    • January 2018
    • July 2017
    • January 2017
    • December 2016
    • June 2016
    • February 2016
    • December 2015
    • November 2015
    • April 2014
    • March 2014
    • February 2014
    • January 2014
    • December 2013
    • October 2013
    • July 2013
    • June 2013
    • May 2013
    • April 2013
    • March 2013
    • February 2013
    • January 2013
    • November 2012
    • October 2012
    • September 2012
    • August 2012
    • July 2012
  • Categories

    • 3D Printing
    • Uncategorized
  • Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
Proudly powered by WordPress Theme: Parament by Automattic.