Using SQLite 3 with Flask

    Here is a simple example of how you can use SQLite 3 with Flask:

    Now, to use the database, the application must either have an activeapplication context (which is always true if there is a request in flight)or create an application context itself. At that point the function can be used to get the current database connection. Whenever thecontext is destroyed the database connection will be terminated.

    Note: if you use Flask 0.9 or older you need to useflask._app_ctx_stack.top instead of g as the flask.gobject was bound to the request and not application context.

    Example:

    1. @app.route('/')def index():    cur = get_db().cursor()    …

    Note

    Please keep in mind that the teardown request and appcontext functionsare always executed, even if a before-request handler failed or wasnever executed. Because of this we have to make sure here that thedatabase is there before we close it.

    The upside of this approach (connecting on first use) is that this willonly open the connection if truly necessary. If you want to use thiscode outside a request context you can use it in a Python shell by openingthe application context by hand:

    1. with app.app_context():
    2.     # now you can use get_db()

    Now in each request handling function you can access get_db() to get thecurrent open database connection. To simplify working with SQLite, arow factory function is useful. It is executed for every result returnedfrom the database to convert the result. For instance, in order to getdictionaries instead of tuples, this could be inserted into the function we created above:

    This will make the sqlite3 module return dicts for this database connection, which are much nicer to deal with. Even more simply, we could place this in get_db instead:

    1. db.row_factory = sqlite3.Row
    1. >>> # You can get values based on the row's name
    2. >>> r['FirstName']
    3. >>> # Or, you can get them based on index
    4. >>> r[1]
    5. John
    6. # Row objects are also iterable:
    7. >>> for value in r:
    8. ...     print(value)
    9. 1
    10. John
    11. Doe

    Additionally, it is a good idea to provide a query function that combinesgetting the cursor, executing and fetching the results:

    This handy little function, in combination with a row factory, makesworking with the database much more pleasant than it is by just using theraw cursor and connection objects.

    Here is how you can use it:

    1. for user in query_db('select * from users'):
    2.     print user['username'], 'has the id', user['user_id']

    Or if you just want a single result:

    1. user = query_db('select * from users where username = ?',
    2.                 [the_username], one=True)
    3. if user is None:
    4.     print 'No such user'
    5. else:
    6.     print the_username, 'has the id', user['user_id']

    To pass variable parts to the SQL statement, use a question mark in thestatement and pass in the arguments as a list. Never directly add them tothe SQL statement with string formatting because this makes it possibleto attack the application using .

    You can then create such a database from the Python shell:

    1. >>> from yourapplication import init_db