diff options
Diffstat (limited to 'app/static/doc/flask-docs/patterns/sqlite3.html')
| -rw-r--r-- | app/static/doc/flask-docs/patterns/sqlite3.html | 226 |
1 files changed, 226 insertions, 0 deletions
diff --git a/app/static/doc/flask-docs/patterns/sqlite3.html b/app/static/doc/flask-docs/patterns/sqlite3.html new file mode 100644 index 0000000..8e892e3 --- /dev/null +++ b/app/static/doc/flask-docs/patterns/sqlite3.html @@ -0,0 +1,226 @@ + +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" + "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> + + +<html xmlns="http://www.w3.org/1999/xhtml"> + <head> + <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> + + <title>Using SQLite 3 with Flask — Flask 0.8 documentation</title> + + <link rel="stylesheet" href="../_static/flasky.css" type="text/css" /> + <link rel="stylesheet" href="../_static/pygments.css" type="text/css" /> + + <script type="text/javascript"> + var DOCUMENTATION_OPTIONS = { + URL_ROOT: '../', + VERSION: '0.8', + COLLAPSE_INDEX: false, + FILE_SUFFIX: '.html', + HAS_SOURCE: true + }; + </script> + <script type="text/javascript" src="../_static/jquery.js"></script> + <script type="text/javascript" src="../_static/underscore.js"></script> + <script type="text/javascript" src="../_static/doctools.js"></script> + <link rel="top" title="Flask 0.8 documentation" href="../index.html" /> + <link rel="up" title="Patterns for Flask" href="index.html" /> + <link rel="next" title="SQLAlchemy in Flask" href="sqlalchemy.html" /> + <link rel="prev" title="Deploying with Fabric" href="fabric.html" /> + + + <link rel="apple-touch-icon" href="../_static/touch-icon.png" /> + + <link media="only screen and (max-device-width: 480px)" href="../_static/small_flask.css" type= "text/css" rel="stylesheet" /> + + </head> + <body> + <div class="related"> + <h3>Navigation</h3> + <ul> + <li class="right" style="margin-right: 10px"> + <a href="../genindex.html" title="General Index" + accesskey="I">index</a></li> + <li class="right" > + <a href="sqlalchemy.html" title="SQLAlchemy in Flask" + accesskey="N">next</a> |</li> + <li class="right" > + <a href="fabric.html" title="Deploying with Fabric" + accesskey="P">previous</a> |</li> + <li><a href="../index.html">Flask 0.8 documentation</a> »</li> + <li><a href="index.html" accesskey="U">Patterns for Flask</a> »</li> + </ul> + </div> + + <div class="document"> + <div class="documentwrapper"> + <div class="bodywrapper"> + <div class="body"> + + <div class="section" id="using-sqlite-3-with-flask"> +<span id="sqlite3"></span><h1>Using SQLite 3 with Flask<a class="headerlink" href="#using-sqlite-3-with-flask" title="Permalink to this headline">¶</a></h1> +<p>In Flask you can implement the opening of database connections at the +beginning of the request and closing at the end with the +<a class="reference internal" href="../api.html#flask.Flask.before_request" title="flask.Flask.before_request"><tt class="xref py py-meth docutils literal"><span class="pre">before_request()</span></tt></a> and <a class="reference internal" href="../api.html#flask.Flask.teardown_request" title="flask.Flask.teardown_request"><tt class="xref py py-meth docutils literal"><span class="pre">teardown_request()</span></tt></a> +decorators in combination with the special <a class="reference internal" href="../api.html#flask.g" title="flask.g"><tt class="xref py py-class docutils literal"><span class="pre">g</span></tt></a> object.</p> +<p>So here is a simple example of how you can use SQLite 3 with Flask:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">sqlite3</span> +<span class="kn">from</span> <span class="nn">flask</span> <span class="kn">import</span> <span class="n">g</span> + +<span class="n">DATABASE</span> <span class="o">=</span> <span class="s">'/path/to/database.db'</span> + +<span class="k">def</span> <span class="nf">connect_db</span><span class="p">():</span> + <span class="k">return</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="n">DATABASE</span><span class="p">)</span> + +<span class="nd">@app.before_request</span> +<span class="k">def</span> <span class="nf">before_request</span><span class="p">():</span> + <span class="n">g</span><span class="o">.</span><span class="n">db</span> <span class="o">=</span> <span class="n">connect_db</span><span class="p">()</span> + +<span class="nd">@app.teardown_request</span> +<span class="k">def</span> <span class="nf">teardown_request</span><span class="p">(</span><span class="n">exception</span><span class="p">):</span> + <span class="k">if</span> <span class="nb">hasattr</span><span class="p">(</span><span class="n">g</span><span class="p">,</span> <span class="s">'db'</span><span class="p">):</span> + <span class="n">g</span><span class="o">.</span><span class="n">db</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> +</pre></div> +</div> +<div class="admonition note"> +<p class="first admonition-title">Note</p> +<p class="last">Please keep in mind that the teardown request functions are always +executed, even if a before-request handler failed or was never +executed. Because of this we have to make sure here that the database +is there before we close it.</p> +</div> +<div class="section" id="connect-on-demand"> +<h2>Connect on Demand<a class="headerlink" href="#connect-on-demand" title="Permalink to this headline">¶</a></h2> +<p>The downside of this approach is that this will only work if Flask +executed the before-request handlers for you. If you are attempting to +use the database from a script or the interactive Python shell you would +have to do something like this:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="k">with</span> <span class="n">app</span><span class="o">.</span><span class="n">test_request_context</span><span class="p">():</span> + <span class="n">app</span><span class="o">.</span><span class="n">preprocess_request</span><span class="p">()</span> + <span class="c"># now you can use the g.db object</span> +</pre></div> +</div> +<p>In order to trigger the execution of the connection code. You won’t be +able to drop the dependency on the request context this way, but you could +make it so that the application connects when necessary:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="k">def</span> <span class="nf">get_connection</span><span class="p">():</span> + <span class="n">db</span> <span class="o">=</span> <span class="nb">getattr</span><span class="p">(</span><span class="n">g</span><span class="p">,</span> <span class="s">'_db'</span><span class="p">,</span> <span class="bp">None</span><span class="p">)</span> + <span class="k">if</span> <span class="n">db</span> <span class="ow">is</span> <span class="bp">None</span><span class="p">:</span> + <span class="n">db</span> <span class="o">=</span> <span class="n">g</span><span class="o">.</span><span class="n">_db</span> <span class="o">=</span> <span class="n">connect_db</span><span class="p">()</span> + <span class="k">return</span> <span class="n">db</span> +</pre></div> +</div> +<p>Downside here is that you have to use <tt class="docutils literal"><span class="pre">db</span> <span class="pre">=</span> <span class="pre">get_connection()</span></tt> instead of +just being able to use <tt class="docutils literal"><span class="pre">g.db</span></tt> directly.</p> +</div> +<div class="section" id="easy-querying"> +<span id="id1"></span><h2>Easy Querying<a class="headerlink" href="#easy-querying" title="Permalink to this headline">¶</a></h2> +<p>Now in each request handling function you can access <cite>g.db</cite> to get the +current open database connection. To simplify working with SQLite, a +helper function can be useful:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="k">def</span> <span class="nf">query_db</span><span class="p">(</span><span class="n">query</span><span class="p">,</span> <span class="n">args</span><span class="o">=</span><span class="p">(),</span> <span class="n">one</span><span class="o">=</span><span class="bp">False</span><span class="p">):</span> + <span class="n">cur</span> <span class="o">=</span> <span class="n">g</span><span class="o">.</span><span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">query</span><span class="p">,</span> <span class="n">args</span><span class="p">)</span> + <span class="n">rv</span> <span class="o">=</span> <span class="p">[</span><span class="nb">dict</span><span class="p">((</span><span class="n">cur</span><span class="o">.</span><span class="n">description</span><span class="p">[</span><span class="n">idx</span><span class="p">][</span><span class="mi">0</span><span class="p">],</span> <span class="n">value</span><span class="p">)</span> + <span class="k">for</span> <span class="n">idx</span><span class="p">,</span> <span class="n">value</span> <span class="ow">in</span> <span class="nb">enumerate</span><span class="p">(</span><span class="n">row</span><span class="p">))</span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()]</span> + <span class="k">return</span> <span class="p">(</span><span class="n">rv</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="k">if</span> <span class="n">rv</span> <span class="k">else</span> <span class="bp">None</span><span class="p">)</span> <span class="k">if</span> <span class="n">one</span> <span class="k">else</span> <span class="n">rv</span> +</pre></div> +</div> +<p>This handy little function makes working with the database much more +pleasant than it is by just using the raw cursor and connection objects.</p> +<p>Here is how you can use it:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="k">for</span> <span class="n">user</span> <span class="ow">in</span> <span class="n">query_db</span><span class="p">(</span><span class="s">'select * from users'</span><span class="p">):</span> + <span class="k">print</span> <span class="n">user</span><span class="p">[</span><span class="s">'username'</span><span class="p">],</span> <span class="s">'has the id'</span><span class="p">,</span> <span class="n">user</span><span class="p">[</span><span class="s">'user_id'</span><span class="p">]</span> +</pre></div> +</div> +<p>Or if you just want a single result:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="n">user</span> <span class="o">=</span> <span class="n">query_db</span><span class="p">(</span><span class="s">'select * from users where username = ?'</span><span class="p">,</span> + <span class="p">[</span><span class="n">the_username</span><span class="p">],</span> <span class="n">one</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span> +<span class="k">if</span> <span class="n">user</span> <span class="ow">is</span> <span class="bp">None</span><span class="p">:</span> + <span class="k">print</span> <span class="s">'No such user'</span> +<span class="k">else</span><span class="p">:</span> + <span class="k">print</span> <span class="n">the_username</span><span class="p">,</span> <span class="s">'has the id'</span><span class="p">,</span> <span class="n">user</span><span class="p">[</span><span class="s">'user_id'</span><span class="p">]</span> +</pre></div> +</div> +<p>To pass variable parts to the SQL statement, use a question mark in the +statement and pass in the arguments as a list. Never directly add them to +the SQL statement with string formatting because this makes it possible +to attack the application using <a class="reference external" href="http://en.wikipedia.org/wiki/SQL_injection">SQL Injections</a>.</p> +</div> +<div class="section" id="initial-schemas"> +<h2>Initial Schemas<a class="headerlink" href="#initial-schemas" title="Permalink to this headline">¶</a></h2> +<p>Relational databases need schemas, so applications often ship a +<cite>schema.sql</cite> file that creates the database. It’s a good idea to provide +a function that creates the database based on that schema. This function +can do that for you:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">contextlib</span> <span class="kn">import</span> <span class="n">closing</span> + +<span class="k">def</span> <span class="nf">init_db</span><span class="p">():</span> + <span class="k">with</span> <span class="n">closing</span><span class="p">(</span><span class="n">connect_db</span><span class="p">())</span> <span class="k">as</span> <span class="n">db</span><span class="p">:</span> + <span class="k">with</span> <span class="n">app</span><span class="o">.</span><span class="n">open_resource</span><span class="p">(</span><span class="s">'schema.sql'</span><span class="p">)</span> <span class="k">as</span> <span class="n">f</span><span class="p">:</span> + <span class="n">db</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span><span class="o">.</span><span class="n">executescript</span><span class="p">(</span><span class="n">f</span><span class="o">.</span><span class="n">read</span><span class="p">())</span> + <span class="n">db</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> +</pre></div> +</div> +<p>You can then create such a database from the python shell:</p> +<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">yourapplication</span> <span class="kn">import</span> <span class="n">init_db</span> +<span class="gp">>>> </span><span class="n">init_db</span><span class="p">()</span> +</pre></div> +</div> +</div> +</div> + + + </div> + </div> + </div> + <div class="sphinxsidebar"> + <div class="sphinxsidebarwrapper"><p class="logo"><a href="../index.html"> + <img class="logo" src="../_static/flask.png" alt="Logo"/> +</a></p> + <h3><a href="../index.html">Table Of Contents</a></h3> + <ul> +<li><a class="reference internal" href="#">Using SQLite 3 with Flask</a><ul> +<li><a class="reference internal" href="#connect-on-demand">Connect on Demand</a></li> +<li><a class="reference internal" href="#easy-querying">Easy Querying</a></li> +<li><a class="reference internal" href="#initial-schemas">Initial Schemas</a></li> +</ul> +</li> +</ul> +<h3>Related Topics</h3> +<ul> + <li><a href="../index.html">Documentation overview</a><ul> + <li><a href="index.html">Patterns for Flask</a><ul> + <li>Previous: <a href="fabric.html" title="previous chapter">Deploying with Fabric</a></li> + <li>Next: <a href="sqlalchemy.html" title="next chapter">SQLAlchemy in Flask</a></li> + </ul></li> + </ul></li> +</ul> + <h3>This Page</h3> + <ul class="this-page-menu"> + <li><a href="../_sources/patterns/sqlite3.txt" + rel="nofollow">Show Source</a></li> + </ul> +<div id="searchbox" style="display: none"> + <h3>Quick search</h3> + <form class="search" action="../search.html" method="get"> + <input type="text" name="q" /> + <input type="submit" value="Go" /> + <input type="hidden" name="check_keywords" value="yes" /> + <input type="hidden" name="area" value="default" /> + </form> + <p class="searchtip" style="font-size: 90%"> + Enter search terms or a module, class or function name. + </p> +</div> +<script type="text/javascript">$('#searchbox').show(0);</script> + </div> + </div> + <div class="clearer"></div> + </div> + <div class="footer"> + © Copyright 2010, Armin Ronacher. + Created using <a href="http://sphinx.pocoo.org/">Sphinx</a>. + </div> + </body> +</html>
\ No newline at end of file |