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