doc/core/howto/rdbms.html

   1 <?xml version="1.0" encoding="utf-8"?><!DOCTYPE html  PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN'  'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'><html lang="en" xmlns="http://www.w3.org/1999/xhtml">
   2   <head>
   3 <title>Twisted Documentation: twisted.enterprise.adbapi: Twisted RDBMS support</title>
   4 <link href="stylesheet.css" rel="stylesheet" type="text/css"/>
   5   </head>
   6
   7   <body bgcolor="white">
   8     <h1 class="title">twisted.enterprise.adbapi: Twisted RDBMS support</h1>
   9     <div class="toc"><ol><li><a href="#auto0">Abstract</a></li><li><a href="#auto1">What you should already know</a></li><li><a href="#auto2">Quick Overview</a></li><li><a href="#auto3">How do I use adbapi?</a></li><li><a href="#auto4">Examples of various database adapters</a></li><li><a href="#auto5">And that's it!</a></li></ol></div>
  10     <div class="content">
  11     <span/>
  12
  13     <h2>Abstract<a name="auto0"/></h2>
  14
  15     <p>Twisted is an asynchronous networking framework, but most
  16     database API implementations unfortunately have blocking
  17     interfaces -- for this reason, <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.html" title="twisted.enterprise.adbapi">twisted.enterprise.adbapi</a></code> was created. It is
  18     a non-blocking interface to the standardized DB-API 2.0 API,
  19     which allows you to access a number of different RDBMSes.</p>
  20
  21     <h2>What you should already know<a name="auto1"/></h2>
  22
  23     <ul>
  24       <li>Python :-)</li>
  25
  26       <li>How to write a simple Twisted Server (see <a href="servers.html" shape="rect">this tutorial</a> to learn how)</li>
  27
  28       <li>Familiarity with using database interfaces (see <a href="http://www.python.org/dev/peps/pep-0249/" shape="rect">
  29       the documentation for DBAPI 2.0</a> or this <a href="http://www.amk.ca/python/writing/DB-API.html" shape="rect">article</a>
  30       by Andrew Kuchling)</li>
  31     </ul>
  32
  33     <h2>Quick Overview<a name="auto2"/></h2>
  34
  35     <p>Twisted is an asynchronous framework. This means standard
  36     database modules cannot be used directly, as they typically
  37     work something like:</p>
  38 <pre class="python"><p class="py-linenumber"> 1
  39  2
  40  3
  41  4
  42  5
  43  6
  44  7
  45  8
  46  9
  47 10
  48 </p><span class="py-src-comment"># Create connection... </span>
  49 <span class="py-src-variable">db</span> = <span class="py-src-variable">dbmodule</span>.<span class="py-src-variable">connect</span>(<span class="py-src-string">'mydb'</span>, <span class="py-src-string">'andrew'</span>, <span class="py-src-string">'password'</span>)
  50 <span class="py-src-comment"># ...which blocks for an unknown amount of time </span>
  51
  52 <span class="py-src-comment"># Create a cursor </span>
  53 <span class="py-src-variable">cursor</span> = <span class="py-src-variable">db</span>.<span class="py-src-variable">cursor</span>()
  54
  55 <span class="py-src-comment"># Do a query... </span>
  56 <span class="py-src-variable">resultset</span> = <span class="py-src-variable">cursor</span>.<span class="py-src-variable">query</span>(<span class="py-src-string">'SELECT * FROM table WHERE ...'</span>)
  57 <span class="py-src-comment"># ...which could take a long time, perhaps even minutes.</span>
  58 </pre>
  59
  60     <p>Those delays are unacceptable when using an asynchronous
  61     framework such as Twisted. For this reason, twisted provides
  62     <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.html" title="twisted.enterprise.adbapi">twisted.enterprise.adbapi</a></code>, an
  63     asynchronous wrapper for any <a href="http://www.python.org/dev/peps/pep-0249/" shape="rect">
  64     DB-API 2.0</a>-compliant module.</p>
  65
  66     <p><code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.html" title="twisted.enterprise.adbapi">enterprise.adbapi</a></code> will do
  67     blocking
  68     database operations in separate threads, which trigger
  69     callbacks in the originating thread when they complete. In the
  70     meantime, the original thread can continue doing normal work,
  71     like servicing other requests.</p>
  72
  73     <h2>How do I use adbapi?<a name="auto3"/></h2>
  74
  75     <p>Rather than creating a database connection directly, use the
  76     <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.ConnectionPool.html" title="twisted.enterprise.adbapi.ConnectionPool">adbapi.ConnectionPool</a></code>
  77     class to manage
  78     a connections for you. This allows <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.html" title="twisted.enterprise.adbapi">enterprise.adbapi</a></code> to use multiple
  79     connections, one per thread. This is easy:</p>
  80 <pre class="python"><p class="py-linenumber">1
  81 2
  82 3
  83 </p><span class="py-src-comment"># Using the &quot;dbmodule&quot; from the previous example, create a ConnectionPool </span>
  84 <span class="py-src-keyword">from</span> <span class="py-src-variable">twisted</span>.<span class="py-src-variable">enterprise</span> <span class="py-src-keyword">import</span> <span class="py-src-variable">adbapi</span>
  85 <span class="py-src-variable">dbpool</span> = <span class="py-src-variable">adbapi</span>.<span class="py-src-variable">ConnectionPool</span>(<span class="py-src-string">&quot;dbmodule&quot;</span>, <span class="py-src-string">'mydb'</span>, <span class="py-src-string">'andrew'</span>, <span class="py-src-string">'password'</span>)
  86 </pre>
  87
  88     <p>Things to note about doing this:</p>
  89
  90     <ul>
  91       <li>There is no need to import dbmodule directly. You just
  92       pass the name to <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.ConnectionPool.html" title="twisted.enterprise.adbapi.ConnectionPool">adbapi.ConnectionPool</a></code>'s constructor.</li>
  93
  94       <li>The parameters you would pass to dbmodule.connect are
  95       passed as extra arguments to <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.ConnectionPool.html" title="twisted.enterprise.adbapi.ConnectionPool">adbapi.ConnectionPool</a></code>'s constructor.
  96       Keyword parameters work as well.</li>
  97     </ul>
  98
  99    <p>Now we can do a database query:</p>
 100
 101 <pre class="python"><p class="py-linenumber"> 1
 102  2
 103  3
 104  4
 105  5
 106  6
 107  7
 108  8
 109  9
 110 10
 111 11
 112 </p><span class="py-src-comment"># equivalent of cursor.execute(statement), return cursor.fetchall():</span>
 113 <span class="py-src-keyword">def</span> <span class="py-src-identifier">getAge</span>(<span class="py-src-parameter">user</span>):
 114     <span class="py-src-keyword">return</span> <span class="py-src-variable">dbpool</span>.<span class="py-src-variable">runQuery</span>(<span class="py-src-string">&quot;SELECT age FROM users WHERE name = ?&quot;</span>, <span class="py-src-variable">user</span>)
 115
 116 <span class="py-src-keyword">def</span> <span class="py-src-identifier">printResult</span>(<span class="py-src-parameter">l</span>):
 117     <span class="py-src-keyword">if</span> <span class="py-src-variable">l</span>:
 118         <span class="py-src-keyword">print</span> <span class="py-src-variable">l</span>[<span class="py-src-number">0</span>][<span class="py-src-number">0</span>], <span class="py-src-string">&quot;years old&quot;</span>
 119     <span class="py-src-keyword">else</span>:
 120         <span class="py-src-keyword">print</span> <span class="py-src-string">&quot;No such user&quot;</span>
 121
 122 <span class="py-src-variable">getAge</span>(<span class="py-src-string">&quot;joe&quot;</span>).<span class="py-src-variable">addCallback</span>(<span class="py-src-variable">printResult</span>)
 123 </pre>
 124
 125     <p>This is straightforward, except perhaps for the return value
 126     of <code>getAge</code>. It returns a <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.internet.defer.Deferred.html" title="twisted.internet.defer.Deferred">twisted.internet.defer.Deferred</a></code>, which allows
 127     arbitrary callbacks to be called upon completion (or upon
 128     failure).  More documentation on Deferred is available <a href="defer.html" shape="rect">here</a>.</p>
 129
 130     <p>In addition to <code>runQuery</code>, there is also <code>runOperation</code>,
 131     and <code>runInteraction</code> that gets called with a callable (e.g. a function).
 132     The function will be called in the thread with a <code class="API"><a href="http://twistedmatrix.com/documents/12.1.0/api/twisted.enterprise.adbapi.Transaction.html" title="twisted.enterprise.adbapi.Transaction">twisted.enterprise.adbapi.Transaction</a></code>,
 133     which basically mimics a DB-API cursor. In all cases a database transaction will be
 134     commited after your database usage is finished, unless an exception is raised in
 135     which case it will be rolled back.</p>
 136
 137 <pre class="python"><p class="py-linenumber"> 1
 138  2
 139  3
 140  4
 141  5
 142  6
 143  7
 144  8
 145  9
 146 10
 147 11
 148 12
 149 13
 150 14
 151 15
 152 16
 153 17
 154 18
 155 19
 156 20
 157 21
 158 </p><span class="py-src-keyword">def</span> <span class="py-src-identifier">_getAge</span>(<span class="py-src-parameter">txn</span>, <span class="py-src-parameter">user</span>):
 159     <span class="py-src-comment"># this will run in a thread, we can use blocking calls</span>
 160     <span class="py-src-variable">txn</span>.<span class="py-src-variable">execute</span>(<span class="py-src-string">&quot;SELECT * FROM foo&quot;</span>)
 161     <span class="py-src-comment"># ... other cursor commands called on txn ...</span>
 162     <span class="py-src-variable">txn</span>.<span class="py-src-variable">execute</span>(<span class="py-src-string">&quot;SELECT age FROM users WHERE name = ?&quot;</span>, <span class="py-src-variable">user</span>)
 163     <span class="py-src-variable">result</span> = <span class="py-src-variable">txn</span>.<span class="py-src-variable">fetchall</span>()
 164     <span class="py-src-keyword">if</span> <span class="py-src-variable">result</span>:
 165         <span class="py-src-keyword">return</span> <span class="py-src-variable">result</span>[<span class="py-src-number">0</span>][<span class="py-src-number">0</span>]
 166     <span class="py-src-keyword">else</span>:
 167         <span class="py-src-keyword">return</span> <span class="py-src-variable">None</span>
 168
 169 <span class="py-src-keyword">def</span> <span class="py-src-identifier">getAge</span>(<span class="py-src-parameter">user</span>):
 170     <span class="py-src-keyword">return</span> <span class="py-src-variable">dbpool</span>.<span class="py-src-variable">runInteraction</span>(<span class="py-src-variable">_getAge</span>, <span class="py-src-variable">user</span>)
 171
 172 <span class="py-src-keyword">def</span> <span class="py-src-identifier">printResult</span>(<span class="py-src-parameter">age</span>):
 173     <span class="py-src-keyword">if</span> <span class="py-src-variable">age</span> != <span class="py-src-variable">None</span>:
 174         <span class="py-src-keyword">print</span> <span class="py-src-variable">age</span>, <span class="py-src-string">&quot;years old&quot;</span>
 175     <span class="py-src-keyword">else</span>:
 176         <span class="py-src-keyword">print</span> <span class="py-src-string">&quot;No such user&quot;</span>
 177
 178 <span class="py-src-variable">getAge</span>(<span class="py-src-string">&quot;joe&quot;</span>).<span class="py-src-variable">addCallback</span>(<span class="py-src-variable">printResult</span>)
 179 </pre>
 180
 181     <p>Also worth noting is that these examples assumes that dbmodule
 182     uses the <q>qmarks</q> paramstyle (see the DB-API specification). If
 183     your dbmodule uses a different paramstyle (e.g. pyformat) then
 184     use that. Twisted doesn't attempt to offer any sort of magic
 185     paramater munging -- <code class="python">runQuery(query,
 186     params, ...)</code> maps directly onto <code class="python">cursor.execute(query, params, ...)</code>.</p>
 187
 188         <h2>Examples of various database adapters<a name="auto4"/></h2>
 189
 190         <p>Notice that the first argument is the module name you would
 191         usually import and get <code class="python">connect(...)</code>
 192         from, and that following arguments are whatever arguments you'd
 193         call <code class="python">connect(...)</code> with.</p>
 194
 195 <pre class="python"><p class="py-linenumber"> 1
 196  2
 197  3
 198  4
 199  5
 200  6
 201  7
 202  8
 203  9
 204 10
 205 </p><span class="py-src-keyword">from</span> <span class="py-src-variable">twisted</span>.<span class="py-src-variable">enterprise</span> <span class="py-src-keyword">import</span> <span class="py-src-variable">adbapi</span>
 206
 207 <span class="py-src-comment"># Gadfly</span>
 208 <span class="py-src-variable">cp</span> = <span class="py-src-variable">adbapi</span>.<span class="py-src-variable">ConnectionPool</span>(<span class="py-src-string">&quot;gadfly&quot;</span>, <span class="py-src-string">&quot;test&quot;</span>, <span class="py-src-string">&quot;/tmp/gadflyDB&quot;</span>)
 209
 210 <span class="py-src-comment"># PostgreSQL PyPgSQL</span>
 211 <span class="py-src-variable">cp</span> = <span class="py-src-variable">adbapi</span>.<span class="py-src-variable">ConnectionPool</span>(<span class="py-src-string">&quot;pyPgSQL.PgSQL&quot;</span>, <span class="py-src-variable">database</span>=<span class="py-src-string">&quot;test&quot;</span>)
 212
 213 <span class="py-src-comment"># MySQL</span>
 214 <span class="py-src-variable">cp</span> = <span class="py-src-variable">adbapi</span>.<span class="py-src-variable">ConnectionPool</span>(<span class="py-src-string">&quot;MySQLdb&quot;</span>, <span class="py-src-variable">db</span>=<span class="py-src-string">&quot;test&quot;</span>)
 215 </pre>
 216
 217     <h2>And that's it!<a name="auto5"/></h2>
 218
 219     <p>That's all you need to know to use a database from within
 220     Twisted. You probably should read the adbapi module's
 221     documentation to get an idea of the other functions it has, but
 222     hopefully this document presents the core ideas.</p>
 223   </div>
 224
 225     <p><a href="index.html">Index</a></p>
 226     <span class="version">Version: 12.1.0</span>
 227   </body>
 228 </html>