{"id":218,"date":"2014-01-22T00:37:31","date_gmt":"2014-01-22T05:37:31","guid":{"rendered":"http:\/\/www.mbeckler.org\/blog\/?p=218"},"modified":"2014-01-22T00:37:31","modified_gmt":"2014-01-22T05:37:31","slug":"flask-sqlalchemy-postgres-drop_all-troubles-fixed","status":"publish","type":"post","link":"https:\/\/www.mbeckler.org\/blog\/?p=218","title":{"rendered":"Flask sqlalchemy postgres drop_all() troubles, fixed"},"content":{"rendered":"<p>I&#8217;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.<\/p>\n<p>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:<\/p>\n<pre>sqlalchemy.exc.InternalError: (InternalError) cannot drop table pages because other objects depend on it\r\nDETAIL:  constraint pagesections_parent_page_id_fkey on table pagesections depends on table pages\r\nHINT:  Use DROP ... CASCADE to drop the dependent objects too.\r\n '\\nDROP TABLE pages' {}<\/pre>\n<p>A bunch of searching for solutions indicated that maybe it would work if you run <strong>db.reflect()<\/strong> immediately before the <strong>db.drop_all()<\/strong>, but apparently the reflect function is broken for the current flask\/sqlalchemy combination. Further searching revealed a mystical &#8220;DropEverything&#8221; function, and I finally found a copy <a href=\"http:\/\/www.sqlalchemy.org\/trac\/wiki\/UsageRecipes\/DropEverything\">here<\/a>. I had to do a few small modifications to get it to work in the context of Flask&#8217;s use of Sqlalchemy.<\/p>\n<pre>def db_DropEverything(db):\r\n    # From http:\/\/www.sqlalchemy.org\/trac\/wiki\/UsageRecipes\/DropEverything\r\n\r\n    conn=db.engine.connect()\r\n\r\n    # the transaction only applies if the DB supports\r\n    # transactional DDL, i.e. Postgresql, MS SQL Server\r\n    trans = conn.begin()\r\n\r\n    inspector = reflection.Inspector.from_engine(db.engine)\r\n\r\n    # gather all data first before dropping anything.\r\n    # some DBs lock after things have been dropped in \r\n    # a transaction.\r\n    metadata = MetaData()\r\n\r\n    tbs = []\r\n    all_fks = []\r\n\r\n    for table_name in inspector.get_table_names():\r\n        fks = []\r\n        for fk in inspector.get_foreign_keys(table_name):\r\n            if not fk['name']:\r\n                continue\r\n            fks.append(\r\n                ForeignKeyConstraint((),(),name=fk['name'])\r\n                )\r\n        t = Table(table_name,metadata,*fks)\r\n        tbs.append(t)\r\n        all_fks.extend(fks)\r\n\r\n    for fkc in all_fks:\r\n        conn.execute(DropConstraint(fkc))\r\n\r\n    for table in tbs:\r\n        conn.execute(DropTable(table))\r\n\r\n    trans.commit()\r\n<\/pre>\n<p>I had to change the uses of <strong>engine<\/strong> to <strong>db.engine<\/strong> since Flask&#8217;s SQLalchemy takes care of that for you. You get the <strong>db<\/strong> object from the app, like this &#8220;from myapp import db&#8221;, and this is how I defined <strong>db<\/strong> in <strong>myapp<\/strong>:<\/p>\n<pre>\r\nfrom flask.ext.sqlalchemy import SQLAlchemy\r\n\r\napp = Flask(__name__, etc)\r\n\r\n# DATABASE_URL is set if we are running on Heroku\r\nif 'DATABASE_URL' in os.environ:\r\n    app.config['HEROKU'] = True\r\n    app.config['SQLALCHEMY_DATABASE_URI'] = os.environ['DATABASE_URL']\r\nelse:\r\n    app.config['HEROKU'] = False\r\n    app.config['SQLALCHEMY_DATABASE_URI'] = \"sqlite:\/\/\/\" + os.path.join(PROJECT_ROOT, \"..\/app.db\")\r\n\r\ndb = SQLAlchemy(app)\r\n<\/pre>\n<p>And then this is the important parts of my <strong>db_create.py<\/strong> script:<\/p>\n<pre>\r\nfrom sqlalchemy.engine import reflection\r\nfrom sqlalchemy.schema import (\r\n        MetaData,\r\n        Table,\r\n        DropTable,\r\n        ForeignKeyConstraint,\r\n        DropConstraint,\r\n        )\r\n\r\nfrom cyosa import app, db\r\n\r\nif not app.config['HEROKU'] and os.path.exists(\"app.db\"):\r\n    os.remove(\"app.db\")\r\n\r\ndef db_DropEverything(db):\r\n    # listed above\r\n\r\ndb_DropEverything(db)\r\ndb.create_all()\r\n\r\n# add your instances of models here, be sure to db.session.commit()\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1],"tags":[79,80,16,50,81,82],"class_list":["post-218","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-flask","tag-posgtresql","tag-python","tag-sql","tag-sqlalchemy","tag-web"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2BznB-3w","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/218","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=218"}],"version-history":[{"count":6,"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/218\/revisions"}],"predecessor-version":[{"id":225,"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/218\/revisions\/225"}],"wp:attachment":[{"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mbeckler.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}