1Using SQLite 3 with Flask
2=========================
3
4In Flask you can easily implement the opening of database connections on
5demand and closing them when the context dies (usually at the end of the
6request).
7
8Here is a simple example of how you can use SQLite 3 with Flask::
9
10    import sqlite3
11    from flask import g
12
13    DATABASE = '/path/to/database.db'
14
15    def get_db():
16        db = getattr(g, '_database', None)
17        if db is None:
18            db = g._database = sqlite3.connect(DATABASE)
19        return db
20
21    @app.teardown_appcontext
22    def close_connection(exception):
23        db = getattr(g, '_database', None)
24        if db is not None:
25            db.close()
26
27Now, to use the database, the application must either have an active
28application context (which is always true if there is a request in flight)
29or create an application context itself.  At that point the ``get_db``
30function can be used to get the current database connection.  Whenever the
31context is destroyed the database connection will be terminated.
32
33Note: if you use Flask 0.9 or older you need to use
34``flask._app_ctx_stack.top`` instead of ``g`` as the :data:`flask.g`
35object was bound to the request and not application context.
36
37Example::
38
39    @app.route('/')
40    def index():
41        cur = get_db().cursor()
42        ...
43
44
45.. note::
46
47   Please keep in mind that the teardown request and appcontext functions
48   are always executed, even if a before-request handler failed or was
49   never executed.  Because of this we have to make sure here that the
50   database is there before we close it.
51
52Connect on Demand
53-----------------
54
55The upside of this approach (connecting on first use) is that this will
56only open the connection if truly necessary.  If you want to use this
57code outside a request context you can use it in a Python shell by opening
58the application context by hand::
59
60    with app.app_context():
61        # now you can use get_db()
62
63
64Easy Querying
65-------------
66
67Now in each request handling function you can access `get_db()` to get the
68current open database connection.  To simplify working with SQLite, a
69row factory function is useful.  It is executed for every result returned
70from the database to convert the result.  For instance, in order to get
71dictionaries instead of tuples, this could be inserted into the ``get_db``
72function we created above::
73
74    def make_dicts(cursor, row):
75        return dict((cursor.description[idx][0], value)
76                    for idx, value in enumerate(row))
77
78    db.row_factory = make_dicts
79
80This 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::
81
82    db.row_factory = sqlite3.Row
83
84This would use Row objects rather than dicts to return the results of queries. These are ``namedtuple`` s, so we can access them either by index or by key. For example, assuming we have a ``sqlite3.Row`` called ``r`` for the rows ``id``, ``FirstName``, ``LastName``, and ``MiddleInitial``::
85
86    >>> # You can get values based on the row's name
87    >>> r['FirstName']
88    John
89    >>> # Or, you can get them based on index
90    >>> r[1]
91    John
92    # Row objects are also iterable:
93    >>> for value in r:
94    ...     print(value)
95    1
96    John
97    Doe
98    M
99
100Additionally, it is a good idea to provide a query function that combines
101getting the cursor, executing and fetching the results::
102
103    def query_db(query, args=(), one=False):
104        cur = get_db().execute(query, args)
105        rv = cur.fetchall()
106        cur.close()
107        return (rv[0] if rv else None) if one else rv
108
109This handy little function, in combination with a row factory, makes
110working with the database much more pleasant than it is by just using the
111raw cursor and connection objects.
112
113Here is how you can use it::
114
115    for user in query_db('select * from users'):
116        print user['username'], 'has the id', user['user_id']
117
118Or if you just want a single result::
119
120    user = query_db('select * from users where username = ?',
121                    [the_username], one=True)
122    if user is None:
123        print 'No such user'
124    else:
125        print the_username, 'has the id', user['user_id']
126
127To pass variable parts to the SQL statement, use a question mark in the
128statement and pass in the arguments as a list.  Never directly add them to
129the SQL statement with string formatting because this makes it possible
130to attack the application using `SQL Injections
131<https://en.wikipedia.org/wiki/SQL_injection>`_.
132
133Initial Schemas
134---------------
135
136Relational databases need schemas, so applications often ship a
137`schema.sql` file that creates the database.  It's a good idea to provide
138a function that creates the database based on that schema.  This function
139can do that for you::
140
141    def init_db():
142        with app.app_context():
143            db = get_db()
144            with app.open_resource('schema.sql', mode='r') as f:
145                db.cursor().executescript(f.read())
146            db.commit()
147
148You can then create such a database from the Python shell:
149
150>>> from yourapplication import init_db
151>>> init_db()
152