All posts tagged sql

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


    # 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']:
        t = Table(table_name,metadata,*fks)

    for fkc in all_fks:

    for table in tbs:


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']
    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 (

from cyosa import app, db

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

def db_DropEverything(db):
    # listed above


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

SQLite is a pretty neat single-file database engine. In their own words,

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

There are SQLite libraries and interfaces available for pretty much any software language, but they also include sqlite3, which is “a terminal-based front-end to the SQLite library that can evaluate queries interactively and display the results in multiple formats. sqlite3 can also be used within shell scripts and other applications to provide batch processing features.”

If you have experience writing SQL queries, it’s easy to get started with the SQLite interface. A few non-standard commands that will help you get started are .tables , which lists the names of all tables in the current database, and .schema tablename which describes the schema of the named table. From there, you can use traditional SQL queries to add, modify, and delete rows in your tables. Have fun!

Note: Be sure to do a backup of your database file before mucking about with the sqlite3 interface, sometimes the program crashes, or you might type a dangerous command or something like that.